Using Pass through Variables
Pass through Variables enable the queries (or SQL statements) that are sent to the Database Systems during report execution to be affected at run time. Where ordinary Parameters are used to modify the filtering element (or WHERE clause) of a query only Pass through Variables can be used with Parameters to modify other parts of the query. Pass through Variables must be used in conjunction with a report's Parameters to be effective.
Pass through Variables are an advanced report writing facility and require a strong understanding of the Alchemex Administrator Functions and of basic database query concepts. Pass through Variables are a special type of Container Expression and are defined within containers in the Alchemex Administrator Tool.
Pass through Variables can also be effectively used to pass common Parameter Values through a series of Union Child Reports within a Union Report. In this way a Union Report may be configured to use one pop up Parameter screen to parameterise a number of Union Child Reports.
Terminology
· Pass through Variable = A special type of Container Expression used to hold a variable value that can be used within a report.
· Pass through Variable Code = The Expression Source property defined as a unique code prefixed and suffixed with @ symbols. This code can be used in Report Filters, Report Aggregate Filters and in other Expression Source properties. Where literal values are usually placed in filter comparison values the Pass through Variable Code may be substituted.
· Pass through Variable Value = the value held within a Pass through Variable during the execution of a Report. Note that the value can only be set through a Report Parameter based on the Pass through Variable. Note that the lifetime of the Pass through Variable Value is from when the value is set during report parameterisation to when the report execution completes.
What can Pass through Variables be used for?
· Dynamically modify the outcome of a Report Column through Display Fields that are based on Expressions which include Variables.
· To filter data in a report.
· Parameterise multiple Union Child Reports in a Report through a single Parameter selection
· Parameterise multiple Reports in a report batch through a single Parameter selection.
· Apply multiple Filters that use a single Parameter which is based on a Pass through Variable.
· Create Aggregate Parameters by using Aggregate Filters with Pass through Variable based Parameters.
· Set Stored Procedure input arguments.
Defining a Pass through Variable step by step
1. Add an expression to a container.
2. When prompted for the type of expression choose “Pass through Variable".
3. You will be prompted for a descriptive name for the Variable. Enter a name e.g. Company Name.
4. You will then be prompted for a Code for the Pass through Variable. The code is a unique identifier for the Pass through Variable. The code must be prefixed and suffixed with @ symbols. E.g. @COMPANY@. This code that you choose for the Pass through Variable has no relation to any existing Expressions in the Container. The Code must however be unique
within the Container and should preferably be unique within your Alchemex System to prevent conflicts when using Pass through Variable in Union Reports.
Note: having done this you may then use the code @COMPANY@ in any of the containers Expressions or any Report Filters to dynamically affect queries at report run time.
To set the Pass through Variable Value you must define a Parameter on a report that uses the Pass through Variable. Add a Parameter to a report in the usual way but specifying the defined Pass through Variable as the expression to use for the Report. When you run the report you will be prompted for a Parameter which will then set the Pass through Variable Value and any occurrences of the variable within the underlying queries will be replaced with variable value.
Using a Pass through Variable within an Expression
1. Modify the Source Property of the Expression to include the Pass through Variable Code.
2. Add a Parameter to your report and choose the Pass through Parameter Expression as the source.
3. Run your report and enter a value for the Parameter "Company". The value that you enter for the parameter will be placed into the variable @COMPANY@ and substituted into the expression "Company & Department".
Using a Pass through Variable to filter data
1. Make sure that the Container that is used in your report has Pass through Variable defined as explained above.
2. Add a Parameter to your report based on the Pass through Variable. Note that running the report and entering a value for the Pass through Variable will have no effect on the report output until the Pass through Variable Code has been added to a filter (or aggregate filter) as in step 3.
3. Add a filter to the report that you wish to based on an Expression that must be filtered in conjunction with the Pass through Variable Code. Choose the relevant Comparison Method for the filter (e.g. Is Equal To) and then enter the code for the Pass Through variable (e.g. @COMPANY@).
4. Runthe Report. The Parameter prompt will be used to set the Pass through Variable value. This value will be substituted for the Pass Through Variable code (@COMPANY@) in the Filter set in step 3 and will thus effect the output of the report Query (through the SQL WHERE clause)..