Parameters and Simple Reports

The Parameters dialog can be used to prompt the user for input when reports are generated. If you add <% FieldName | PromptString | DefaultValue | Type%> to the report* s SQL string, it will cause the Parameters dialog to be displayed.

The Field name is the name of the field you wish to request (e.g. CustomerID or LastName). The Prompt string is a string value indicating text that will appear in the dialog next to the control (e.g. Enter Customer ID: ). Setting the default value will automatically set a default value. For example, if you have a report that generates based on a date, you can have the default for the field set to the current date so users can just hit "Enter", unless they want to generate a report based on a new date. Type indicates what type of data will be requested.

The values can be: nothing(string), S for string, D for date, B for Boolean. A string type will give a textbox for input, a D type will give a calendar drop-down control for input and a B type will give a checkbox for input.

Note For Strings: If you specify a default value that is enclosed in single or double quotes, it will be recognized and will output the same quotes to SQL when replacing. For Booleans : if you specify true/false for DefaultValue it will generate true/false for SQL output. If you specify 0,1, it will output 0 or 1.

Example: "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%>'"

Note The FieldName is the only required parameter; the rest are optional.

Stored procedures can be used the same way as parameters in ActiveReports. The Sql statement would need to have the stored procedure call and placeholders for the parameters: "CustOrderHist '<%ID|Enter Customer ID:|AFLKI%>'". ActiveReports will then replace the parameter text "<%...%>" with whatever the user types into the dialog to create a call like this: CustOrderHist 'AFLKI'.

Parameters and Subreports

Parameters can be used with subreports to connect the subreport to the parent report. By setting a parameter for the field that links the parent report to the child subreport, the parent report can pass the information to the child through the parameters. The main differences when working with subreports and parameters are:

• The subreport* s ShowParametersUI should be set to False.

• The subreport* s SQL query should be set to use the parameter syntax = <%fieldname%>.

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

0 0

Post a comment