Skip to Content
0
Former Member
Jul 11, 2016 at 07:32 PM

Crystal Report 2008 with Stored Procedure to CR 2008 - DataSet - Stored Procedure - returns “Missing parameter values” only

600 Views

We have reports and the results are via Stored Procedures. The original project started out long ago on C#, VS 2008, CR 2008 and SQL Server 2008 and IIS. The front end is all web forms on IIS so are the user interface for the reports. Current setup is C#, VS 2013, CR 2008 /CRRuntime_12_4_mlb.msi/ changes still in VS 2008 and database SQL Server 2008 R2.

Reports are working as designed with some performance issues. I have to make some changes as below. The changes are working for one report which does not required anything via the "Parameter Fields". The CR reports which do require "Parameter Fields" returning only a "Missing parameter values" message instead of the full report.

  1. Create a dataset in VS 2013 based on the stored procedure.
  2. Open the CR report in VS 2008 and change the source from SP to the dataset created above.
  3. Compile the project for the reports in VS 2013.
  4. Make the following changes the C# code as below.

1st file change:

case clsEnumeration.REPORT_TYPE.REPORT_NAME:
//changing cr report to connect to dataset
//repDoc = clsReportFactory.GetReport(typeof(ReportName));
repDoc = clsReportFactory.GetReport(typeof(solNAME.Reports. IAM_HEALTH.NewReports.ReportName));
DataTable dtReportName = mclsReportArgs.MReportDataTable;
clsReportHelper.SetParameter(repDoc, "FieldName", mclsReportArgs.MStrRefLocationName);
repDoc.SetDataSource(dtReportName);

//clsReportHelper.SetParameter(repDoc, "@dtDateBegin", mclsReportArgs.MDatDateFrom);
//clsReportHelper.SetParameter(repDoc, "@dtDateEnd", mclsReportArgs.MDatDateTo);
//clsReportHelper.SetParameter(repDoc, "FieldName", mclsReportArgs.MStrRefLocationName);
//end of change

2nd file change

private void BindReport()
{
if (IsValid)
{
RefLocation entRefLocation = DataRepository.RefLocationProvider.GetByRefLocationId(mintRefLocation);

clsReportArgs _args = new clsReportArgs(clsEnumeration.REPORT_TYPE.REPORT_NA ME);
_args.MStrRefLocationName = entRefLocation.RefLocationName;
_args.MDatDateFrom = clsDateFunctions.GetBeginningOfDayDateTime((DateTi me)wdcDateFrom.Value);
_args.MDatDateTo = clsDateFunctions.GetEndOfDayDateTime((DateTime)wdc DateTo.Value);
_args.MDecProjectedADG = -1;

//changing cr to connect to ds
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = "sp_ReportName";

//sqlCommand.Parameters.AddWithValue("@FieldName", _args.MStrRefLocationName);
sqlCommand.Parameters.AddWithValue("@dtDateBegin", _args.MDatDateFrom);
sqlCommand.Parameters.AddWithValue("@dtDateEND", _args.MDatDateTo);

sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandTimeout = 60 * 1000 * 5;
DataSet ds = DataRepository.Provider.ExecuteDataSet(sqlCommand) ;

_args.MReportDataTable = ds.Tables[0];
//change end

clsUrlBuilder _urlView = clsReportHelper.BuildReportViewerURL(_args);
clsDHTMLModal _clsDHTMLModal = new clsDHTMLModal(btnReportViewClose, _urlView, "Report Detailed Name");
_clsDHTMLModal.RegisterStartupScript(this, "report_viewer");
}