setting data with SetDataSource in c#

[AdamN]

I'm trying to transfer the execution of sql queries and building a dataSet to the application and then only set the ready data in the report. I have a report that has subreport (In Page Header section) linked to the field in main report table.

In the section  “group footer” I have the "New Page After" option checked.

Normally when generating Crystal runs one query for the main report table:

exec "sqldb_dev"."dbo"."Masterproc";1 '20240830'

Then probably for each record or for each page? A separate query is run for the subreport:

exec "sqldb_dev"."dbo"."detailProc";1 '01-F-000-0', '', 0, 0, 0, 0, 0, '', 'EUR', '20240830', 0

My question is whether I can do something like that myself. I want to send the same SQL from a C# application, build a DataSet and set it in the report using the SetDataSource method in the ReportDocument object.
Normally something like this works. I send a query, set the prepared DataSet with a table and rows in the report (Using method SetDataSource) and the pages are generated. The problem is how to set the data for subreports?. If I give many rows in the subreport table I will get the error "Page header or footer exceeds the length of the page". If there is only one row in the DataTable for the subreport. After printing, the same data will be on each page in the subreport section.

I didn't find any event for page generation so that after generating the page I can run another query and replace the dataTable with a new one

(I don't use CrystalReportViewer because it's a server application only for generating reports) 

Attaching RowChanged events etc. to DataTable doesn't work.

ds.RowChanged += (object sender, DataRowChangeEventArgs e)=> {…}

I tried to set up links between the master data set and the detail data set, but unfortunately it did not produce any results.

Is there any method, event or way to build a DataSet in c# Net. that will allow you to use different data on each page for a subreport. Without interfering with the report itself. Just from c# code

[DonWilliams]

Hi Adam,

In DataSet1_Click note the routine does look for each subreport but it uses the same DS as the main report. So you do need to use 2 queries, one for the main report to dump into DS and then another one for the subreport and dump that into DSSub().

The Dataset for the subreport should not have any filters on it, all the data should be there, it's possible you are filtering the subreport query so it only returns one row. CR will filter the data when the report is ran per the subreport Data Link.

As for the same subreport data I suspect either the query is not returning all data for the subreports or the Parameter is the same for each.

I can't say for sure why but I find exporting the Report to RPT format saves data with the report so you can then open the new report in CR Designer and have a look at what the SQL is for the subreport and if the Subreport links are there.

You could also enable CRLogger, CR's database logging component and look at the log to see what the subreport is doing, compare it the CR Designer logs to see what it's doing in your app.

1653533 - How to debug database issues using crlogger logs on Windows and Linux

1701312 - How to analyze CR Logger trace logs

Here's a routine for exporting to RPT format in code:

if (ExportTypeSelected == "crReportExportFormatCrystalReports")
#region RPT
{
    // This works do not alter
    // this gets the report name and sets the export name to be the same less the extension
    string outputFileName = "";
    string MyRptName = rpt.FileName.ToString();
    outputFileName = MyRptName.Substring(9, rpt.FileName.Length - 9);
    outputFileName = outputFileName.Substring(0, (outputFileName.Length - 4)) + "1.rpt";

    try
    {
        if (File.Exists(outputFileName))
        {
            File.Delete(outputFileName);
        }

        CrystalDecisions.ReportAppServer.ReportDefModel.RPTExportFormatOptions RasRPTExpOpts = new RPTExportFormatOptions();

        try
        {
            //RasRPTExpOpts = rptClientDoc.get_SavedExportOptions(CrReportExportFormatEnum.crReportExportFormatCrystalReports);
        }
        catch (Exception ex)
        {
            btnSQLStatement.Text = "ERROR: " + ex.Message;
            //return;
        }

        // Set them now:
        //RasPDFExpOpts.CreateBookmarksFromGroupTree = false;
        //RasPDFExpOpts.EndPageNumber = 1;
        //RasPDFExpOpts.StartPageNumber = 1;

        CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions exportOpts1 = new CrystalDecisions.ReportAppServer.ReportDefModel.ExportOptions();
        exportOpts1.ExportFormatType = CrReportExportFormatEnum.crReportExportFormatCrystalReports;
        exportOpts1.FormatOptions = RasRPTExpOpts;

        // And Export
        rptClientDoc.PrintOutputController.ExportEx(exportOpts1).Save(outputFileName, true);
        MessageBox.Show("Export to RPT Completed. NOTE: report is *1.RPT", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

    }
    catch (Exception ex)
    {
        btnSQLStatement.Text = "ERROR: " + ex.Message;
        return;
    }
    // This works do not alter
}
#endregion RPT

[DonWilliams]

Hi Adam,

Does it work in CR Designer? Always test what you want to in the Designer first then in code it should work.

I believe what you need to do is link the subreport to the main reports parameter so the subreport filters on the parameter in it's WHERE clause.

If you don't link it you will always get the last value in the query, which sounds like is what is happening.

Remember, for each result returned in the main report query the subreport runs, so if 100 records are returned in the main report the subreport runs 100 times.

Next is since you are using a Parameter and a Stored Procedure you need to set the parameter value before setting the log on info.

See my test app for more info:

how-to-parameters-in-crystal-reports-for-visual-studio-net

Note also the subreport needs to have it's data source set also, it's in the sample also.

Once you get the report working in the designer you can use the DataSet in code.

[AdamN]

In designer it works, the report is generated, the subreport query is called for each record from the master. However, I want to do the same thing from the c# code using SetDataSource. I want to execute the SQL query myself, build the DataTable, DataSet and set it in the report using SetDataSource. Unfortunately, I don't know how to do it? It looks like I have to call SetDataSource with a new row for each generated page (How can I catch a page change in c# without CrystalReportViewer?). I also don't want to open every report I have with Designer and change anything.

[DonWilliams]

Did you look at my sample app, there is a SetDataSource function in there that whoudl work for you

[AdamN]

In the application you mentioned in the SetData_Click method it is set by SetDataSource, however it is not set for subreports. It is also in DataSet1_Click, however there one DataSet is set for Master and subreports, which also does not work for me.

You wrote "Remember, for each result returned in the main report query the subreport runs, so if 100 records are returned in the main report the subreport runs 100 times." 

This is exactly what I want to do from C# code. Without opening the report with Designer and making changes. CR is not supposed to build and execute SQL queries, but me in my application, CR is only supposed to get a ready DataSet.

The steps I want to perform here are:

1) I want to execute a query for the master that will return 100 records. Based on this, build a DataSet and substitute it with the SetDataSource method

2) Execute another query for each returned record from the master, this time for the subreport. I would execute 100 queries and get 100 rows as a result.

2.1) If I substitute a table containing 100 rows for the subreport, I will get the error "page header or footer exceeds page length."

2.2) If I substitute a table containing 1 record for the subreport, the same data will be substituted on each page.

CR itself runs a query for the subreport for each record from the master. Maybe there is a way to catch this event? And at this point also run the query, but from my application and replace it again for the DataSet subreport

You wrote: "Note also the subreport needs to have it's data source set also, it's in the sample also. Once you get the report working in the designer you can use the DataSet in code. "

In the Designer the report works. In the attached application there is a DataSet1_Click method, I do it similarly. Even if I substitute a table with 1 row for the subreport, the report will be generated, but on each page there will be the same data in the subreport section. In my case, each page is to have different, unique data. Therefore, CR ran a separate query for each page and I don't know how to repeat such an action from the code by substituting only the DataSet.