You can use the ActiveReports Parameters collection to pass values directly into a textbox or a chart on a report, or to choose what subset of data from your data source to display in a particular instance of a report, or to pass values from a main report into a subreport. There are several ways in which you can collect values for parameters:

• You can prompt the user for parameter values.
• You can get parameter values from the main report and pass them into a subreport.
• You can collect parameter values from a control in a Web form or a Windows form.

There are also several ways in which you can set up parameters for a report:

• You can enter syntax like the following into your SQL query: <%Name | PromptString | DefaultValue | DataType | PromptUser%>
• You can add parameters to the Report Explorer.
• You can add parameters to the code behind the report, in the ReportStart event.

Collecting Parameter Values

In order to prompt the user for parameter values, all of the following must be in place:

• At least one parameter exists in the Parameters collection of the report.
• The PromptUser property for at least one parameter is set to True.
• On the report object, the ShowParameterUI property must be set to True.

When there are parameters in the collection and the ShowParameterUI property is True, the user prompt automatically displays when the report is run. When the user enters the requested values and clicks the OK button, the report displays using the specified values.

Values of a parameter added to the Report Explorer can be applied to a parameter in the SQL query - you should just specify the "param:" prefix for a parameter in the SQL query. Specifying the "param:" prefix for a parameter in the SQL query relates this parameter to the one in the Report Explorer. For example, "select * from customers where customername = '<%param:Parameter1%>'". In this case, the parameter with the "param:" prefix in the SQL query is updated with values of the corresponding parameter in the Report Explorer.

Tip: Within the same report, you can prompt users for some parameters and not for others by setting the PromptUser property to True on some and False on others. However, if the report object's ShowParameterUI property is set to False, the user prompt does not display for any parameters regardless of its PromptUser setting.

In order to collect parameters from a main report to pass into a subreport, all of the following must be in place:

• The SQL queries for both reports must contain the same field.
• The subreport's ShowParameterUI property must be set to False.
• The subreport's SQL query must contain the parameter syntax with the Name value set to the name of the field that is common to both reports.

To collect parameter values from a Windows form or a Web form, use code to collect the values into variables, and then pass them into the report's ReportStart event. See sample code in the Add Parameters topic. In this case, the report's ShowParameterUI property must be set to False.

Adding Parameters to the Parameters Collection via the SQL Query

When you add a single parameter to a report's Parameters collection via the SQL query, a query that looks like the following creates a user prompt like the one pictured below.

SELECT * FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID 
WHERE Products.SupplierID = <%SupplierID|Enter a Supplier ID|1|S|True%>

There are five values in the parameter syntax, separated by the pipe character: |

Only the first value (Name) is required, but if you do not specify the third value (DefaultValue), the field list is not populated at design time. You can provide only the Name value and no pipes, or if you wish to provide some, but not all of the values, simply provide pipes with no space between them for the missing values. For example, <%ProductID||||False%>

• Name This is the unique name of the parameter, and corresponds to the Key property in parameters entered via code.
• PromptString This string is displayed in the user prompt to let the user know what sort of value to enter.
• DefaultValue Providing a default value to use for the parameter allows ActiveReports to populate the bound fields list while you are designing your report, enabling you to drag fields onto the report. It also populates the user prompt so that the user can simply click the OK button to accept the default value.
• Type This value, which defaults to S for string, tells ActiveReports what type of data the parameter represents. It also dictates the type of control used in the user prompt. The type can be one of three values.
  • S (string) provides a textbox into which the user can enter the string.

    Note: Depending on your data source, you may need to put apostrophes (single quotes) or quotation marks around the parameter syntax for string values. For example, '<%MyStringParameter%>' Also, if you provide a default value for a string parameter that is enclosed in apostrophes or quotation marks, ActiveReports sends the apostrophes or quotation marks along with the string to SQL. For example, <%MyStringParameter||"DefaultValue"|S|False%>

  • D (date) provides a drop-down calendar control from which the user can select a date.

    Note: Depending on your data source, you may need to put number signs around the parameter syntax. For example, #<%MyDateParameter%>#

    B (Boolean) provides a checkbox which the user can select or clear.

    Note: If you provide a default value of True or False, or 0 or 1 for a Boolean parameter, ActiveReports sends it to SQL in that format.

• PromptUser This Boolean allows you to tell ActiveReports whether to prompt the user for a value. This can be set to True for some parameters and False for others. If you set the report's ShowParameterUI property to False, users are not prompted for any parameters, regardless of the PromptUser value set for any parameter in the report.

For a date parameter, you can use a SQL query like the following to allow users to select a beginning and ending date.

SELECT * FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID 
WHERE OrderDate BETWEEN #<%StartDate|Start date|1/1/1994|D|True%># AND 
#<%EndDate|End date|12/31/1994|D|True%>#

Note: Specifying the "param:" prefix for a parameter in the SQL query relates this parameter to the one in the Report Explorer. For example, "select * from customers where customername = '<%param:Parameter1%>'". In this case, the parameter with the "param:" prefix in the SQL query is updated with values of the corresponding parameter in the Report Explorer.