Click here to Skip to main content
14,983,300 members
Articles / Database Development / SQL Server
Posted 2 Apr 2014


14 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
4.91/5 (9 votes)
2 Apr 2014CPOL3 min read
I have written this tip to help developers automatically set Crystal Report parameters from variables in code (WinForms) at report run time.


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.

Image 1

Figure 1


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.

Image 2

Figure 2

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()
                // 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;

                // Add parameter to the parameter fields collection.

                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");

                if (conn.State != ConnectionState.Closed)

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

The line...

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

Image 3

Figure 3

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

Image 4

Figure 4

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";  

Image 5

Figure 5

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.

Image 6

Figure 6

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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Kinyanjui Kamau
Software Developer (Senior) GetNet
Kenya Kenya

Comments and Discussions

GeneralMy vote of 2 Pin
Member 1095718331-Jul-14 20:55
MemberMember 1095718331-Jul-14 20:55 
GeneralRe: My vote of 2 Pin
Kinyanjui Kamau31-Jul-14 21:01
professionalKinyanjui Kamau31-Jul-14 21:01 
QuestionNice! Pin
Volynsky Alex6-Apr-14 1:24
professionalVolynsky Alex6-Apr-14 1:24 
AnswerRe: Nice! Pin
Kinyanjui Kamau6-Apr-14 9:30
professionalKinyanjui Kamau6-Apr-14 9:30 
GeneralRe: Nice! Pin
Volynsky Alex6-Apr-14 9:39
professionalVolynsky Alex6-Apr-14 9:39 
QuestionGreat! Pin
Zachery Hysong4-Apr-14 6:50
MemberZachery Hysong4-Apr-14 6:50 
AnswerRe: Great! Pin
Kinyanjui Kamau4-Apr-14 7:31
professionalKinyanjui Kamau4-Apr-14 7:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.