Parameters

In a Section report, you can use the Parameters collection to pass values directly into a control, or you can also use it to display a subset of data in a particular instance of the report.

There are several ways to set up parameters in a report:

• Enter syntax like the following in your SQL query to filter the data displayed in a report:

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

• Add parameters through the Report Explorer.

• Add parameters through the code behind the report, inside the ReportStart event.

Add Parameters via SQL Query

Section report allows you to add parameters to a report's Parameters collection via the SQL query. You can add build queries with parameters using following easy steps:

1. Create a new section report or open an existing report and in the Detail section band, click the DataSource icon to view the Report Data Source dialog.

2. Connect the report to 'Nwind.mdb' data source, an OleDB data source. See OLEDB Provider for more information.

3. In the Query field, enter a SQL query like the one below, which contains the parameter syntax to prompt for parameter values at runtime.

   SELECT * FROM Products
   INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID= [Order Details].OrderID)
   ON Products.ProductID = [Order Details].ProductID
   WHERE Products.SupplierID = <%SupplierID|Enter Supplier ID|7|||%>
   AND OrderDate >= '<%OrderDate|Order date from|11/1/1994|D|true|yyyy/MM/dd%>'                 
   AND Discontinued = '<%Discontinued|Is this checked?|true|B||%>'

You can also add SQL query using the Visual Query Designer. To do so, click on the Query Designer button, select the Design option, and select the desired fields to create the query. To preview the query, click on the SQL option and then click Save.

> type=note
> **Note**: In section reports, the Visual Query Designer is only available for the OLEDB, SQL, and Custom Data Sources.

4. Click OK to save the data source and return to the report design surface.

   
   

   The SQL query above causes ActiveReports to display the following dialog to the user at runtime. The user can accept these or input other values to select report data.

   
   

   
   

The values below are used while creating the parameter syntax. The values in the parameter syntax are separated by the pipe character (|).

For creating a parameter syntax, the first value (Name) is required. However, if the DefaultValue is not specified, the fields are not populated at the design time. In the syntax, you can provide only the Name value (without pipe character), or you can provide some values along with the Name value and use pipes without space for the missing values. For example, <%ProductID||||False|%>.

• Name: A unique identifier or name assigned to the parameter that corresponds to the key property in parameters entered via code.

• Prompt: The string that is displayed in the user prompt to let the user know what sort of value to enter (at runtime).

• DefaultValue: The initial value assigned to the parameter that allows ActiveReports to populate the bound field 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: Refers to the data type of the parameter's value. By default, the value is set to 'S' (String), defines the 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, as explained below:

  • S (string): Provides a textbox into which the user can enter the string. 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 picker from which the user can select a date. Depending on your data source, you may need to put number signs around the parameter syntax.

    For example, #<%MyDateParameter%>#

  • B (Boolean): Provides a checkbox that the user can select or clear. 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.

  type=note

  Note: In the case of Microsoft Access Database, the default value for Boolean parameter is specified as -1(true) or 0(false).

• PromptUser: This Boolean allows you to indicate whether to display parameters to the user at runtime. If set to True, the parameters are displayed at runtime. 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. The supported PromptUser values are: True and False.

• DisplayFormat: Choose a format for the 'Date' data type. The selected format is displayed in the preview in the Parameters pane.

Add Parameter from Report Explorer

The steps to add a parameter from Report Explorer are as follows:

1. In the Report Explorer, right-click the Parameters node and select Add. This adds a parameter (Parameter1) as a child to the Parameters node.

   
   

2. Select the added parameter to open the Properties Panel and set values in the following properties. The description of each of them is given in the previous section:

   • Name

   • DefaultValue

   • Prompt

   • PromptUser

   • Type

   • DisplayFormat

3. Pass the parameter to a field on the report.

Add Parameters at Runtime

You can add, edit, and delete parameters at runtime. The following code demonstrates how to add a parameter and display its value in a Textbox control.

1. Double-click in the gray area below the report to create an event-handling method for the ReportStart event.

2. Add code to the handler to set parameters at runtime.

   
   

   To write the code in Visual Basic.NET

   Imports GrapeCity.ActiveReports.SectionReportModel

   Dim myParam1 As New Parameter()
                                   myParam1.Key = "myParam1"
                                   myParam1.Type = Parameter.DataType.String
   
                                   'Set to False if you do not want input from user.
                                   myParam1.PromptUser = True
                                   myParam1.Prompt = "Enter Data:"
                                   myParam1.DefaultValue = "Default Value"
                                   Me.Parameters.Add(myParam1);
   
                                   'Set to True to display parameter dialog box when report is run.
                                   Me.ShowParameterUI = True

   To write the code in C#

   using GrapeCity.ActiveReports.SectionReportModel;

   Parameter myParam1 = new Parameter();
                                   myParam1.Key = "myParam1";
                                   myParam1.Type = Parameter.DataType.String;
   
                                   //Set to false if you do not want input from user.
                                   myParam1.PromptUser = true;
                                   myParam1.Prompt = "Enter Data:";
                                   myParam1.DefaultValue = "Default Value";
                                   this.Parameters.Add(myParam1);
   
                                   //Set to true to display parameter dialog box when report is run.
                                   this.ShowParameterUI = true;

3. In the design view, click the gray area below the report to select it and open the Properties Panel.

4. Click the events icon in the Properties Panel to display available events for the report.

5. Double-click FetchData. This creates an event-handling method for the report's FetchData event.

6. Add code to the handler to pass the parameter at runtime.

   
   

   To write the code in Visual Basic.NET

   'Set textbox text equal to the value of the parameter.
                               Me.txtParam1.Text = Me.Parameters("myParam1").Value

   To write the code in C#

   //Set textbox text equal to the value of the parameter.
                               this.txtParam1.Text = this.Parameters["myParam1"].Value;

   The run-time implementation above displays the following dialog. You can enter any text in this prompt dialog and display it on the report.

   
   

Prompt for Parameter Values

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

• At least one parameter should exist in the Parameters collection of the report.

• The PromptUser property for at least one parameter must be 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 set to 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 gets displayed using the specified values.

Values of a parameter added through the Report Explorer can be applied to a parameter in the SQL query by specifying the param: prefix for a parameter in the SQL query. This prefix relates the current parameter to the one in the Report Explorer.

For e.g., 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.

type=info

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