How to handle Crystal Reports Parameters in a .NET application
created by Don Williams on February 16, 2016 3:16 PM
Purpose
The documents applies to Crystal Reports 2011 and above and SAP Crystal Reports, Developer Version for Visual Studio .NET and .NET SDK for BI 4.x .NET SDK.
Overview
Crystal Reports has three basic types of parameters and can be modified through the .NET SDK:
- Crystal Reports Parameter, Static and Dynamic
- Stored Procedure parameter
- Command Object Parameter
THE APPLICATION:
The Basics – Loading the application
See Kbase article 2281780 ( same title) to get the sample application I am using in this document and for recent updates.
(Note: Our new search engine currently does not allow attachments so I have attached the sample app to this Document. Unzip the file, Rename the attachment to RAS2010_Printing.zip)
Attached files cannot have a .zip extension so to get around this I have renamed the zip file and appended a .txt extension. Download the files, remove the .txt extension and then unzip.
Requires Service Pack 14 or above available from here: http://scn.sap.com/docs/DOC-7824
To get things going open the application attached in the above KBA and the Form1.cs file.
This application uses both the Engine ( ClientDocument ) and RAS ( Report Application Server -ReportClientDocument)
On application load it gets the localization to set the values to Imperial or Metric so it can calculate the number of twips etc. 1440 twips = 1 inch.
The next section queries the AppDomain to get the version of the CR Engine assembly which is installed with every CR .NET product. This confirms the version you are loading shown in Fig. 1 as the runtime version 13.0.14.1720 which is Service Pack 14 or higher )
New Feature add is the use of GDIPlus.dll Interpolation mode:
The default is NearestNeighbor, use High and/or test to see what looks best for your images.
public frmMain()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
// added GDIPlus interpolation mode function
LstInterpolationMode.Enabled = true;
Array CRinterpolationMode = Enum.GetValues(typeof(System.Drawing.Drawing2D.InterpolationMode));
foreach (object obj in CRinterpolationMode)
{
//CRInterpolMode.GetTypeCode(CRinterpolationMode);
LstInterpolationMode.Items.Add(obj);
}
LstInterpolationMode.SelectedItem = System.Drawing.Drawing2D.InterpolationMode.NearestNeighbor;
if (System.Globalization.RegionInfo.CurrentRegion.IsMetric)
isMetric = 567;
else
isMetric = 1440;
foreach (Assembly MyVerison in AppDomain.CurrentDomain.GetAssemblies())
{
if (MyVerison.FullName.Substring(0, 38) == "CrystalDecisions.CrystalReports.Engine")
{
//File: C:\Windows\assembly\GAC_MSIL\CrystalDecisions.CrystalReports.Engine\13.0.2000.0__692fbea5521e1304\CrystalDecisions.CrystalReports.Engine.dll
//InternalName: Crystal Reports
//OriginalFilename:
//FileVersion: 13.0.9.1312
//FileDescription: Crystal Reports
//Product: SBOP Crystal Reports
//ProductVersion: 13.0.9.1312
//Debug: False
//Patched: False
//PreRelease: False
//PrivateBuild: False
//SpecialBuild: False
//Language: English (United States)
System.Diagnostics.FileVersionInfo fileVersionInfo = System.Diagnostics.FileVersionInfo.GetVersionInfo(MyVerison.Location);
txtRuntimeVersion.Text += fileVersionInfo.FileVersion.ToString();
CRVer = fileVersionInfo.FileVersion.Substring(0, 2);
return;
}
}
//End Class
//
// TODO: Add any constructor code after InitializeComponent call
//
}
Now onto the main routines….
The test applications main form looks like this:
Some important info below:
Above shows the Report location, report version ( 14.1 ) Revision number (1) Type of report
Runtime version and database driver used
And then the history of the report when saved ( read Only property and only updated via the Report Designer in VS or standalone CR Designer.
Lots of report object info on the right using the drop down box:
NOTE: most parts are complete but some are still incomplete, no call for info on them so I did not complete them.
The best place to start once the report is loaded is to get the list of parameters which shows their type and values if applicable also note some times you use the Engine to get the info and other times you need to use RAS to get the object info:
This is the routine that gets the values:
private void getParameterFields(CrystalDecisions.CrystalReports.Engine.ReportDocument rpt)
{
string textBox1 = "";
string textBox2 = "";
string MyObjectType = ReportObjectComboBox1.SelectedItem.ToString();
btnReportObjects.Text = "";
iCnt = -1;
// NOTE: WHEN GETTING THE PARAMETER LISTS IT DOES NOT MATTER THE TYPE OF PARAMETER. ONLY WHEN SETTING THE VALUES DOES IT MATTER.
// SEE SetParam_Click, ReportDocumentSetParameters AND SetCrystalParam FOR MORE DETAILS ON HOW TO
if (rptClientDoc.DataDefController.DataDefinition.ParameterFields.Count > 0) //there are parameters
{
btnCount.Text = rptClientDoc.DataDefController.DataDefinition.ParameterFields.Count.ToString();
foreach (CrystalDecisions.ReportAppServer.DataDefModel.ParameterField paramfield in rptClientDoc.DataDefController.DataDefinition.ParameterFields)
{
iCnt++;
// this line gets the parameter name by index
//rptClientDoc.DataDefController.DataDefinition.ParameterFields[1].LongName.ToString();
switch (paramfield.ValueRangeKind)
{
case CrParameterValueRangeKindEnum.crParameterValueRangeKindDiscrete:
{
try
{
if (paramfield.ReportName.ToString() != null)
btnReportObjects.Text += "Subreport Name: " + paramfield.ReportName.ToString() + "\n";
}
catch (Exception ex)
{
{ } // main report does not have a name so ignore the exception
}
btnReportObjects.Text += "Discrete Param Name: \"";
btnReportObjects.AppendText(paramfield.Name.ToString() + "\"");
textBox1 = "";
getDiscreteValues(paramfield);
break;
}
case CrParameterValueRangeKindEnum.crParameterValueRangeKindDiscreteAndRange:
{
try
{
if (paramfield.ReportName.ToString() != null)
btnReportObjects.Text += "Subreport Name: " + paramfield.ReportName.ToString() + "\n";
}
catch (Exception ex)
{
{ } // main report does not have a name so ignore the exception
}
//getRangeAndDiscreteValues(paramfield);
btnReportObjects.Text += "Discrete and Range Param Name: ";
btnReportObjects.AppendText(paramfield.Name.ToString() + "\"");
textBox1 = "";
getDiscreteValues(paramfield);
IsParamRange = true;
break;
}
case CrParameterValueRangeKindEnum.crParameterValueRangeKindRange:
{
try
{
if (paramfield.ReportName.ToString() != null)
btnReportObjects.Text += "Subreport Name: " + paramfield.ReportName.ToString() + "\n";
}
catch (Exception ex)
{
{ } // main report does not have a name so ignore the exception
}
btnReportObjects.Text += "Range Parameter Name: ";
btnReportObjects.AppendText(paramfield.Name.ToString() + "\"");
textBox1 = "";
//getRangeValues(paramfield);
getDiscreteValues(paramfield);
IsParamRange = true;
break;
}
}
}
}
}
At this point you can now determine what type of Parameter they are which also dictates which ENUM to use when setting/updating/adding values.
Lets try a basic report now using CR Static Parameter. (Report attached - ParameterOrderField.rpt)
Run the application and load the above report and you will see the following:
General info about the report in the top left area explained earlier.
- Record Selection Formula text box shows you the formula with comments.
- SQL Statement: text box gives you info on the connection properties
- Under the Report Objects text there is another text box for general object info and messages
Now look at the parameter using the drop down Report Objects drop down feature and select Parameters ( 3rd listed 😞
- As you can see it is a Discrete Static parameter
- It has a Current Initial value and default values.
Now to set the CR parameter you can set the logon info first or set the parameter value first. In this case it does not matter.
NOTE: When using a Stored Procedure the Parameter MUST be set first before sett the log on info because the connection requires it.
So now fill in the connection info, I imported our legacy xtreme.mdb (attached) into my SQL 2008 Server
Now that you are connected you can now see the SQL has been updated:
Click the Parameter Set button and the SQL statement area now is updated with:
Parameters Set
You can now preview the report, not the selection formula does not use the parameter value but easy to do so in CR Designer or in code but that's for a different Document...
The code to set the value to 1003 is below:
private void SetParam_Click(object sender, EventArgs e)
{
try
{
foreach (CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition pField in (ParameterFieldDefinitions)rpt.DataDefinition.ParameterFields)
{
if (pField.ParameterType == ParameterType.StoreProcedureParameter)
{
rpt.SetParameterValue("@test", "1003");
}
if (pField.ParameterType == ParameterType.ReportParameter)
{
if (pField.ParameterFieldName == "test")
SetCrystalParam(rpt, "@test", "1003");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
btnSQLStatement.Text += "\nParameters Set";
}
And example of using a Dynamic parameter in a report and getting that info:
Update to the application to get the SQL from the subreport is below.
In the logon routine I've added some code to preset the values, If the subreport is linked to the main report in the record selection formula the value will need to be set for that parameter the same way as above.
The tricky part now is the subreport is based on a field, possibly, screen shot below shows the parameter in the subreport view:
Now what you need to do is get the Parameter Links from that collection and set the values specifically:
Search the app for"// Get subreport SQl " and you'll find the update I did below to set the value when logging on.
First add a global variable to hold the value:
int subreportLinkValue = 0;
Then search for this in the log on routine and update it:
//pass the necessary parameters to the connectionInfo object
//crConnectioninfo.ServerName = btrDataFile.Text.ToString();
crConnectioninfo.ServerName = btrDataFile.Text.ToString();
if (!mainSecureDB)
{
crConnectioninfo.ServerName = btrDataFile.Text.ToString();
crConnectioninfo.UserID = btrFileLocation.Text.ToString(); // "sa";
crConnectioninfo.Password = btrPassword.Text.ToString(); // Password
crConnectioninfo.DatabaseName = btrSearchPath.Text.ToString(); // XTREME.CUSTOMER
}
else
{
crConnectioninfo.IntegratedSecurity = true;
}
// this is where you set the main report parameter values
// if the subreport is not linked by the record selection formula it returns the first value, or last value depending on the subreport link
// so in this case where the subreport is linked via the record selection formula the value needs to be passed to the subreport Link.
subreportLinkValue = 1005;
SetCrystalParam(rpt, "@My Parameter", subreportLinkValue.ToString());
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.ConnectionInfo = crConnectioninfo;
try
{
crTable.ApplyLogOnInfo(crTableLogOnInfo);
}
catch (Exception ex)
{
btnSQLStatement.Text += "ApplyLogOnInfo failed: " + ex.ToString();
}
// Get subreport SQl
foreach (String resultField in rptClientDoc.SubreportController.GetSubreportNames())
{
SubreportController subreportController = rptClientDoc.SubreportController;
SubreportClientDocument subreportClientDocument = subreportController.GetSubreport(resultField);
// this gets the subreport linked fields so if more than one you will need to handle the values individually
// since the main report parameter and record selection formula are only linked in the record selection formula you need to get the actual field the subreport is linked on to be able to get the SQL statement
try
{
SubreportLinks SubLinks = rptClientDoc.SubreportController.GetSubreportLinks(resultField.ToString());
for (int I = 0; I < SubLinks.Count; I++)
{
SubreportLink subLink = SubLinks[I];
string trimCurlies = subLink.LinkedParameterName.ToString();
char[] charsToTrim = { '{', '}', '?' }; // remove the {? ... } from the Parameter field name
trimCurlies = trimCurlies.Trim(charsToTrim);
subreportClientDocument.DataDefController.ParameterFieldController.SetCurrentValue(subreportClientDocument.Name.ToString(), trimCurlies.ToString(), subreportLinkValue);
}
subreportClientDocument.DatabaseController.LogonEx(crConnectioninfo.ServerName, crConnectioninfo.DatabaseName, crConnectioninfo.UserID, crConnectioninfo.Password);
GroupPath gp1 = new GroupPath();
gp1.FromString("");
string sql = String.Empty;
subreportClientDocument.RowsetController.GetSQLStatement(gp1, out sql);
btnSQLStatement.Text += "Subreport: " + resultField.ToString() + "\n:" + sql;
btnSQLStatement.AppendText("\n");
break;
}
catch (Exception ex)
{
{
if (nvp2Sub.Name.ToString() == crConnectioninfo.DatabaseName.ToString())
Now you can get the SQL from the subreprort but be aware if there are multiple links you will need to provide those values and handle them accordingly.
This is the basics and should get you going on creating your own routines to manage your parameters
More info on this product:
CR for VS no longer allows connecting to Business Objects Enterprise also known as Managed Reports. There is a separate .NET SDK for BOE 4.1 that eventually will have the above API’s but for now this is limited to CR for VS only.
Conclusion
When using various Parameters in Crystal Reports be aware of the various types used, if they are range types, static, dynamic etc. Using this application should get you going.
If you need any more details or examples please search our SCN Forums at:
SAP Crystal Reports, version for Visual Studio
If you have specific requests and didnot find an example or how to on the forum please post a new question.
Also, when Designing reports you are able to save errors, after all the Designer is a design tool and assume it will be fixed later.
The SDK loads all objects so if there are errors it can cause problems so validate all object before releasing them and using the report in your application.
A common issue is in Formulae being saved with invalid field names being used. This application will valid all formula and report any that have issues. Good this to do in your application....
Extreme example of a bad report with multiple formula errors:
The error reported is typically like this:
Error in formula :
Error in formula ~:
'DatetimeVar Field_Name;'
This field name is not known.
Details: errorKind
Update: 2016-02-09 added the default values to the get Parameter info routine
Update: 2016-02-24 fixed IsDCP objAttributes["FieldID"] try/catch
Update: 2016-06-10 added ability to get subreport SQL by setting the value in the Logon routine.
Update: 2016-12-01 updated Subreport log info again and get query
Update: 2017-04-26 Fixed some Subreport SQL issues and other parts as well as added Interpolation mode functionality - GDIPlus image handler.
Update: 2021-04-01 Added getting subreport Command SQL
- SAP Managed Tags:
3 Comments
