Dynamically Setting a Parameter from a C# Variable in Crystal Reports

• Download source - 3 MB

Introduction

I used Visual Studio 2012 to develop the sample code. The application framework used is .NET 4.5 with Entity Framework 5 for ease of querying.

The database used is SQL Server Compact 4.0. It should be available within the project.

You will need to have SAP Crystal Reports, developer version for Microsoft Visual Studio installed on your machine that includes the Crystal Reports Runtime Engine. You can download it here.

Once you have downloaded the sample project (CrystalReportsGetsVariable), unzip it, open the folder and click on CrystalReportsGetsVariable.sln to open it in Visual Studio 2012. You can the click on start to run the application.

For developers familiar with Crystal Reports, It is fairly easy to add a parameter on a report so that when a user clicks on the report they want to view, they can enter a parameter, click OK and then go on to view the report.

Background

Sometimes, a developer may want to make a report that displays information based on a variable that has been set such that the Crystal Report parameter does not have to be keyed in manually. This tip shows how to achieve this by setting a parameter based on a variable.

Using the Code

Make sure that you can see the Crgv.sdf database in the project. It already contains sample data. You can view the sample data by going opening Server Explorer -> drill down on the Crgv.sdf database until you reach the Clients table -> right click on the Clients table and then click on Show Table Data.

You will see a table with the fields, ClientId, FirstName, LastName, Address, Telephone, BusinessName.

There are 6 clients in total. Each of them belongs under a Business Name. The code below will show how to create a report to show the clients under each business name.

This code can be used for any other purpose apart from what is shown. It can be edited to suit a developers needs. The method ClientReport includes the code needed to set the parameter based on a variable.

// Method to load the report
        private void ClientReport()
        {
            try
            {
                // Fetch the data
                // Crgv.sdf should be included in the download
                // NB: SqlCeConnection is used for SQL Server Compact Edition. Use SqlConnection for all
                // other SQL connections
                SqlCeConnection conn = new SqlCeConnection(@"Data Source=|DataDirectory|\Crgv.sdf");
                SqlCeCommand cmd = new SqlCeCommand();
                SqlCeDataAdapter adapter;
                ParameterFields paramFields = new ParameterFields();
                ParameterField paramField = new ParameterField();
                ParameterDiscreteValue discreteVal = new ParameterDiscreteValue();
                paramField.ParameterFieldName = "Business Name";

                // Set the discrete value and pass it to the parameter
                discreteVal.Value = GlobalClass.BusinessName;
                paramField.CurrentValues.Add(discreteVal);

                // Add parameter to the parameter fields collection.
                paramFields.Add(paramField);

                conn.Open();
                cmd.CommandText = "SELECT ClientId, FirstName, LastName, Address, Telephone, BusinessName FROM Clients";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;

                // Create dataset and use dataadapter to fill it
                adapter = new SqlCeDataAdapter(cmd);

                DataSet ds = new DataSet();

                adapter.Fill(ds, "Clients");

                cmd.Dispose();
                if (conn.State != ConnectionState.Closed)
                    conn.Close();

                // Call the report
                cReport = new ClientReport();
                cReport.SetDataSource(ds);
                crystalReportViewer.ParameterFieldInfo = paramFields;
                crystalReportViewer.ReportSource = cReport;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        } 

The line...

paramField.ParameterFieldName = "Business Name";

...is of importance to us. In the project, I created a parameter named "Business Name" on the report - ClientReport.

Once you have a parameter named "Business Name", you right click on the report -> Select "Report" -> Select "Selection Formula" -> then click on "Record".

You then add {Clients.BusinessName} = {?Business Name} in the formula editor and save. Note that the parameter name given in Crystal Reports is the same as in the parameter field name in the code.

paramField.ParameterFieldName = "Business Name";  

The Crystal Report gets data from the CrystalReportsGetsVariable.CrgvDataSet. This dataset is created when adding a new connection in Server Explorer -> Adding the Crgv database -> Adding the database as an existing item in the Solution Explorer -> Creating the Dataset.

When creating the report, you will find the CrystalReportsGetsVariable.CrgvDataSet by going to DataBase Expert -> ADO.NET DataSets.

For more details, please look through the code in the sample project. There are comments to help understand the workings.

I put up this together from bits and pieces of code available on developer forums. I hope it may be of help to some.