Parameters and Simple Reports

In ActiveReports for .NET 3.0, you can allow users to filter the amount of information exposed in a report through the use of parameters. Adding parameters to the report results in a Parameters dialog that the user can manipulate against the database to return pertinent data.

For example, the SQL statement "SELECT * FROM products INNER JOIN categories ON products.categoryid = categories.categoryid WHERE products.supplierID =<%SupplierID|Enter supplierID|1000%> and OrderDate=#<%Date|Order date:|1/1/2001|D%># and Discount=<%bool| Is this checked ?|true|B%>;" contains unique syntax that ActiveReports for .NET uses to generate the following dialog. 

 

Breaking down the SQL statement, the syntactical elements <%SupplierID|Enter supplierID|1000%>, <%Date|Order date:|1/1/2001|D%>,  and <%bool| Is this checked ?|true|B%> comprise the report parameters. The user can accept these or input other values to further define report data. Each of these parameters follow the syntactical pattern <%FieldName | PromptString | DefaultValue | Type%> where:

• FieldName is the name of the field you wish to request (e.g. CustomerID or LastName). This is the only part of the syntax which is required, so you can use <%FieldName%> if you do not wish to use the other values.

  Although FieldName is the only required parameter, please note that if you do not specify a default value for the other parameters the field list is not populated at design time and you will see a SQL error message. Setting a default value for all parameters allows you to see fields in the Report Explorer instead of an error message.

• PromptString is an optional string value which sets the text that appears in the dialog next to the control (e.g. "Enter Customer ID: ").
• DefaultValue sets a default value for the parameter. For example, if you have a date parameter, you can set the DefaultValue for the field to the current date so users can just hit ENTER unless they want to generate a report based on a new date.
• Type indicates the type of the data requested. The possible values are nothing (which defaults to string), S for string, D for date, and B for Boolean. A string type provides a textbox for input, a date type provides a calendar drop-down control for input, and a Boolean type provides a check box for input.

  For Strings, if you specify a default value that is enclosed in apostrophes or quotation marks, ActiveReports sends the same marks to SQL. For Boolean parameters, if you specify true/false for the DefaultValue it generates true/false for SQL output. If you specify 0,1, it generates 0 or 1.

 

You can use stored procedures in the same way as parameters in ActiveReports. The SQL statement has the stored procedure call and placeholders for the parameters: "CustOrderHist <%ID|Enter Customer ID:|AFLKI%>". ActiveReports replaces the parameter text "<% %>" with whatever the user types into the dialog to create a call like this: CustOrderHist 'AFLKI'.

To add parameters in SQL statements as in the above example, you will need to add them to the query via the Datasource dialog that is accessed by clicking the datasource icon in the ActiveReports designer.

Adding Parameters Via the Report Explorer

In addition to adding parameters in SQL statements, ActiveReports for .NET 3.0 provides for the addition of parameters via the Report Explorer. To do so, right-click the Parameters node in the Report Explorer and click "Add" from the shortcut menu. This will add a child to the Parameters node.

 

Once a parameter is added to the Report Explorer, you can set its properties from the Properties grid. 

Adding Parameters at Run time

Custom parameters can be added, edited, and deleted at run time. The following demonstrates how to add a parameter to the Report_Start event and assign its value to an ActiveReports' textbox control.

'Visual Basic
Private Sub rptParam_ReportStart(ByVal sender As System.Object, ByVal e As System.EventArgs)_ 
   Handles MyBase.ReportStart
	
   Dim myParam1 As New Parameter()
   myParam1.Key = "myParam1"
   myParam1.Type = Parameter.DataType.String
   myParam1.PromptUser = True 'set to False if you do not want input from user
   myParam1.Prompt = "Enter last name:"
   myParam1.DefaultValue = "This is myParam1 default value"
   Me.Parameters.Add(myParam1)
   
   'Set textbox text equal to the value of the parameter
   Me.txtParam1.Text = Me.Parameters("myParam").Value
End Sub

//C#
private void NewActiveReport1_ReportStart(object sender, System.EventArgs e)
   {
    Parameter myParam1 = new Parameter();
    myParam1.Key = "myParam1";
    myParam1.Type = Parameter.DataType.String;
    myParam1.PromptUser = true; //set to false if you do not want input from user
    myParam1.Prompt = "Enter last name:";
    this.Parameters.Add(myParam1);
    
    //Set textbox text equal to the value of the parameter
    this.txtParam1.Text = this.Parameters["myParam"].Value;			
   }	

Parameters and Subreports

You can use parameters with subreports to connect the subreport to the parent report. If you set a parameter for the field that links the parent report to the child subreport, the parent report passes the information to the child through the parameters. Keep the following in mind when working with subreports and parameters:

• Set the subreport's ShowParametersUI property to False.
• Set the subreport's SQL query to use the parameter syntax = <%fieldname%>.

See Also

 

 

---

Copyright © 2004-2007 Data Dynamics, Ltd. All rights reserved.