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 and place them on the report as TextBox controls to pass values into them.
• Add parameters through the code behind the report, inside the ReportStart event.

Add Parameters via the SQL Query

When you add a single parameter to a report's Parameters collection via the SQL query, the query looks like this:

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

You can also create a parameterized query from the Visual Query Designer. See Query Builder in Microsoft SQL Client and OLEDB Providers for further information on how to create a parameterized query using the interactive query designer.

There are six 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.
• Prompt: The string that is displayed in the user prompt to let the user know what sort of value to enter (at runtime).
• DefaultValue: Providing a default value to use for the parameter 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:  This value which defaults to 'S' for 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.
  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 tell ActiveReports whether to prompt the user for a value. This is set to True to use parameters 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/True and false/False.

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

When you add SQL parameters to a report, ActiveReports displays an 'Enter Report Parameters' dialog where the user can enter the values to fetch from the database. The steps to add a parameter to an SQL query are as follows:

1. 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 Microsoft 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.

   Query	Copy Code
   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||%>

          
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. The user can accept these or input other values to select report data.
   

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 which 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

   Visual Basic.NET code. Paste at beginning of code view.	Copy Code
   Imports GrapeCity.ActiveReports.SectionReportModel

   Visual Basic.NET code. Paste INSIDE the ReportStart event.	Copy Code
   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#

   C# code.  Paste at beginning of code view.	Copy Code
   using GrapeCity.ActiveReports.SectionReportModel;

   C# code. Paste INSIDE the ReportStart event.	Copy Code
   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

   Visual Basic.NET code. Paste INSIDE the FetchData event.	Copy Code
   'Set textbox text equal to the value of the parameter. Me.txtParam1.Text = Me.Parameters("myParam1").Value

   To write the code in C#

   C# code. Paste INSIDE the FetchData event.	Copy Code
   //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.