How do I set a report's parameter with values when using SetDataSource?

[Former Member]

I am getting the following error when trying to load a report:

This field name is not known.

Details: errorKind Error in File MyReport.rpt {7A17AD68-F569-45A8-9655-0BDB71FCA28A}.rpt:

Error in formula Section_Visibility:

'{?P_ETHNICITY} <> 'G01' '

This field name is not known.

Details: errorKind

This report uses an Oracle database as its data source. Parameters are passed in at run time and the report engine connects to the database to retrieve its data. The report has an expression

{?P_ETHNICITY} <> 'G01'

for the Suppress property of one of its footer sections (where P_ETHNICITY is one of the parameters).

The report is used in a legacy VB6 system which I am rewriting in C# and using Crystal Reports for VS2010 (SP 1 (v13.0.1)). For reasons unrelated to this thread, I do not want the report to retrieve its own data as it once did. Rather, I want the C# code to retrieve the data in a DataTable and pass it to the report. This works well for other reports when I use the following code:

private void LoadReportData(
    CrystalDecisions.CrystalReports.Engine.ReportDocument reportDoc, 
    string reportFilePath, 
    System.Data.DataTable reportData)
{
    reportDoc.Load(reportFilePath, OpenReportMethod.OpenReportByDefault);
    reportDoc.Database.Tables[0].SetDataSource(reportData);
}

But for this report (and several others) it fails with the error above. I have attempted (unsuccessfully) to set the report's parameters with values by modifying the code to the following:

private void LoadReportData(
    CrystalDecisions.CrystalReports.Engine.ReportDocument reportDoc,
    string reportFilePath,
    System.Data.DataTable reportData,
    string[] reportParameterValues)
{
    reportDoc.Load(reportFilePath, OpenReportMethod.OpenReportByDefault);
 
    // Start at index 1 because the first parameter in the stored proc is the
    // ref cursor and the reportParameterValues array contains only values
    // for the other parameters.
    for (int index = 1; index < reportDoc.ParameterFields.Count; index++)
    {
        reportDoc.SetParameterValue(index, reportParameterValues[index - 1]);
    }
    
    reportDoc.Database.Tables[0].SetDataSource(reportData);
}

I have also tried calling SetDataSource before the loop that calls SetParameterValue but it makes no difference.

The stored proc that the report once used to retrieve data (and also the proc that the C# uses to retrieve the DataTable) has a signature of:

procedure rpt_ethnicity_proc (
    ethnicity     in out    rpt_generic_pkg.rpt_generic_cur,
    p_area        in        varchar2,
    p_location    in        varchar2,
    p_gender      in        varchar2,
    p_ethnicity   in        varchar2)

The first parameter is the cursor for the resulting data but the reportParameterValues array passed to the LoadReportData method only has values for the remaining parameters. This is why the index variable starts at 1 and why the reportParameterValues argument is referenced by index - 1.

What am I doing wrong? Is it even possible to use such a report in this way? I have searched everywhere but cannot find anyone with the same problem.

[former_member183750 · Accepted solution]

Typically the error means that the field either does not exist in the database or is of a type that is different than what the report is expecting.

See if you can do the same conversion you are doing in code, in the CR designer. I suspect you will either get the same error, or a field mapping dialog.

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

Got Enhancement ideas? Try the [SAP Idea Place|https://ideas.sap.com/community/products_and_solutions/crystalreports]

[Former Member · Accepted solution]

I've not had much experience with designing a report and the ~800 reports I'm working with are well before my time. It turns out (I discovered, after playing with the aforemetioned report a bit more) that not only is the stored proc bound to the report but the parameters are too. As soon as I set the datasource location (using the Set Datasource Location dialog) to a matching xsd (and, of course, the parameters are removed) then I can manually add identical unbound parameters back in and everything works perfectly fine.

It just doesn't make sense to me that I can override the datasource but I can't also override the parameter values.

Essentially I was hoping to be able to run the ~800 reports that use the "pull" method by using the "push" method and without having to modify the reports. Most of them work, but for the ones that reference the parameters.

[Former Member · Accepted solution]

Hi there,

I am facing the same situation. After I use setdatasource to point to a dataset and run the report, report can no longer refer to any parameter fields. I am using report document object and I am able to see the parameters in the parameterfields property. But when I use the ExporttoDisk method, i get an exception that there is an error in the formula that refers a parameter. Is there a work around for this?

Thanks

Ajith

[Former Member]

I finally gave up. I wrote a small method that very crudely detected which of my reports have this problem. I then re-set the datasource and created parameters manually for each one as I described in my previous post.