|
You can use the ActiveReports Parameters collection to pass parameters to the report's SQL string at run time. This can be done with the parameters dialog, which will prompt the user for input when reports are generated, or, if you're using parameters to synchronize subreports or passing the parameters in from other code, you can set the report's ShowParameterUI property to False. The syntax for adding parameters to the report's Parameters collection via the SQL string is: <%FieldName|PromptString|DefaultValue|Type%> - FieldName is the name of a valid field in the report's data source to be used as a parameter (e.g. CustomerID or LastName). required
- PromptString is a string value which specifies the text that will appear in the dialog next to the control (e.g. "Enter Customer ID:"). optional
- The DefaultValue can be used to allow users to just hit "Enter" if, for example, you have a report that generates based on a date, and you have the default for the field set to the current date. optional
- Type indicates what type of data will be requested. optional
- The default is String (with a textbox for input)
- S specifies string (with a textbox for input)
- D specifies date (with a calendar drop-down control for input)
- B specifies Boolean (with a checkbox for input)
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%>' Passing Parameters to an ActiveReport Once you have added parameters to your report's Parameters collection through the SQL statement, you can access them with code: Dim rpt as New rptYourReport()
rpt.Parameters(0).Value = 1
'A variable which takes its value from your user interface may be passed in here
rpt.Parameters(1).Value = #4/1/2003#
rpt.Parameters(2).Value = 'False'
rpt.Run() Stored Procedures and Parameters 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 main and subreport queries must contain the same field (so the main report must have a CategoryID field and the subreport also must have a CategoryID field.
|