How to Use SQL Query Parameters in Your C# .NET Reports

ActiveReports offers several ways for you to interact with and tailor your data, both directly and indirectly. One such method is using parameters, which are changeable values/variables you can build into your report to affect the layout, the data that comes in, or just to keep track of information on the back end. Usually, they are part of the report itself, but you can create a parameter for your Section Reports without even using the designer. This method allows you to define a parameter and use it within the SQL query itself, while still allowing the end user of the report to provide input.

To start, let us review what a parameter is. A parameter is a value that can be changed between renderings of the report to affect it in different ways. Some parameters control whether sections of the report are rendered, but by far the most common use case is filtering data. This can be done at the data source/data set level or at the report level. However, in most cases, it is better to do it at the data level if your data source is queryable, such as a SQL database. As this avoids render time spent loading values that the report does not use.

In this blog, we will explore the use of query parameters in SQL databases and discuss some use cases.

Want to Try Out the Latest Release? Download ActiveReports.NET Today!

Understanding Parameter Syntax

To start off, let us review the structure we will need for these parameters within the query:

<%Name|Prompt|DefaultValue|Type|PromptUser|DataFormat%>

Breaking Down Each Parameter Component

Each part of this parameter constructor has a use, and you can interact with as many as you need.

Name

A name will be needed for every parameter you use. If using a subreport, you are able to use the name of a field from the main report to pull a value into the subreport. For example, if you want to pass over the OrderID from the main report to use as the query for the subreport.

Prompt

A prompt is a message displayed when the user is prompted. It is the default for main reports.

Default Value

The default value will be the parameter value if the user does not make any changes.

Type

Type will be a letter indicating the type of parameter to be used.

S for string, D for date, and B for bool. These are the 3 parameter types allowed in section reports. So, when working with numbers, you would still use a String, since there are no int or double types. If this is an issue on the data side, you can cast it within the query.

PromptUser

PromptUser is a simple bool that indicates whether the user is prompted in the viewer. If false, the report will not open the parameter panel for that parameter, and it will behave as if it were hidden.

DataFormat

DataFormat is used by dates to control the format, e.g., dd/mm/yyyy vs mm/dd/yy

If you do not need to use all the sections, you can leave the other areas between the pipes blank.

e.g. <%ProductID||||False|%>.

You can also directly refer to parameters that you made on the report via the use of this syntax:

<%param:Parameter1%>

Passing Parameters Between Reports

Connecting Main Reports and Subreports

Now that we know the basics of how to make these parameters. We'd like to review a use case that shows their use.

Let us say we have a main report and a subreport that both use the same data source, but each has a different dataset. There are some foreign keys we can use to connect the data. The main report would contain customer information, and the subreport would contain the orders for that customer.

Passing OrderID Values into a Subreport Query

In this case, we will use Northwind as the database. We can use the OrderID column from the main report to automatically filter the subreport by including it in our query with a WHERE clause. This, since it is on the subreport, it will be effectively a hidden parameter, but we do not need to worry about the values:

SELECT *
FROM [Northwind].[dbo].[Order Details]
WHERE [OrderID] = '<%OrderID%>'

**Please note that the name seen here is verbatim the name of the column from the main report. Subreports can access selected information from the main report. In this case, we are using the dataset info from the main report and the specific OrderID value for the row the main report is looking at when the subreport is called.

Building a Customer Filtering Example

Configuring CustomerID Query Parameters

Then, on the main report, we will likely not be filtering for specific customers all the time, but more for where they are from or other info. Having the ability to generate a report for a specific customer is always useful. So you wouldn't want to use a query like this with a default value that skips filtering.

SELECT * FROM [Northwind].[dbo].[Orders] 
WHERE ( [CustomerID] = '<%CustID|CustomerID for report|XX%>' OR '<%CustID|CustomerID for report|XX%>' = 'XX' ) 

**Please note that calling the same parameter script will not cause multiple parameters on the report.

Combining Query Parameters with Report Parameters

Creating a Country Parameter on the Main Report

Now that we have these queries with the built-in parameters, let us review how you could set up the main reports parameter for a country outside of the query, and then we can bring it in with that other form of the script. We will also use the same default value, as some users would want to see all customers and countries, while others may want to get specific mixes.

SELECT * FROM [Northwind].[dbo].[Orders] 
WHERE ( [CustomerID] = '<%CustID|CustomerID for report|XX%>' OR '<%CustID|CustomerID for report|XX%>' = 'XX' ) 
AND ( [ShipCountry] = '<%param:Country%>' OR '<%param:Country%>' = 'XX')

Reviewing the Final Report Structure

Now that we have everything built, let us review the final report while showing the parameter logic flow through it.

Main Report:

Subreport:

Runtime Behavior and User Interaction

Understanding the Parameter Prompt Window

Now, let us move on to what happens at runtime.

To start, when the report is run, we see the parameter input area for the main report.

How Default Values Control Filtering Logic

You can overwrite the default XX and enter a country or CustomerID, then click “View report”. Based on your selection, the WHERE clause will use the parameter to check whether it has an XX and ignore filtering the report, or it will check for a value and filter the query for that value. In this case, this can fire for either Country or CustomerID.

Then, based on the query, all customers will show up, or only those for the queried country or customer.

Using Subreports for Dynamic Detail Data

After that, in the detail section, we have our subreport control. This subreport will fire whenever a new row of data is rendered for the detail section. Each of these runs will have its own query, and a value from the main report, “OrderID,” will be used as a parameter for the Order Detail table. And when the subreport is rendered, it will display only orders for a specific OrderID. And given how the main report is set up, it will go through all reports for a given customer before moving onward.

You can also set up sorting/ordering and groupings via the Groupings in AR, or in the query itself by adding something like this: ORDER BY [CustomerID]

Please note: In the above image, the grey areas indicate that the subreport is being used for them.  

Here is an example of getting a specific ID:

Here is an example of getting a specific Country:

Conclusion

Want to Try Out the Latest Release? Download ActiveReports.NET Today!