Parameters and Simple Reports

Use the Parameters dialog to prompt the user for input when reports are generated.

Add <%FieldName | PromptString | DefaultValue | Type%> to the SQL string for the report to display the Parameters dialog.

• 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.
• 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 checkbox for input.

Note: 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.

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%>'" yields a dialog which looks like this:

 

Note: FieldName is the only required parameter; the rest are optional. But note that if you do not specify a default value, the field list is not populated at design time.

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'.

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%>.

Note: Both report queries must contain the same field (so the main report must have a categoryID field and the subreport also must have a categoryID field.

Walkthrough: Using Parameters with Simple Reports | Walkthrough: Using Parameters with Subreports

 

 

---

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