The parameters in the Sage Intelligence Report Manager lets you easily filter your reports with values you need to enter at run time. In a Union Report you may have two or more sub reports that you would like to filter by the same value. You could achieve this by adding the same parameter to all of the sub reports, but this means that at run time you’ll be asked for the same value several times. By combining the Report Manager filters with a Pass Through Variable, you can filter as many sub reports as you like and will only need to enter the value once. Let us show you how.
In this example we have a Union Report that compares Sales with Stock Orders. The Sales Details sub report uses a Sales Details container and the Orders report uses a Stock Orders container. We want to be able to filter both sub reports by the product ID.
1. Set up a Pass Through Variable
The first step is to set up your Pass Through Variable in the Connector. You will need to add the same variable to the container of each report you want to filter in your Union Report.
To add a Pass Through Variable, do the following:
a. In the Connector, right click on the container you want to add the variable to and select Add Expressions
b. Select Pass Through Variable from the list and click OK
c. Give it a name and click OK
d. Enter a code for it, i.e. a unique name beginning and ending with an ‘@’ symbol and click OK
The variable will now be added to the container.
Repeat steps a to d for each container you need to add a variable to.
2. Add your Parameter
Next, you will need to add a parameter to your Union Report to capture the value you want to filter your sub reports by. This will make use of the Pass Through Variable we have just set up. It is recommended that the parameter be added to the first sub report that will run. This ensures that the value is set right at the start and is available to any of the sub reports during execution. Since sub reports run in reverse order, in our case, this will be the Sales Details report.
If your sub report is hidden, or you’re not sure which one it is, you can get to it easily by doing the following:
a. Double click on your Union Report. The Union Sub Reports object will appear
b. Double click on Union Sub Reports. The list of sub reports will appear
c. Right click on the sub report you want to view and select Go to Sub Report
The sub report will be selected.
We can now add our parameter to it.
d. Add a parameter to the report by selecting the Parameter tab and then clicking the Add button
e. In the Choose Filter Fields list select the Pass Through Variable you created and click OK
f. For the Enter an Optional Default field, leave it blank and click OK
The pass through parameter will now be added to the sub report.
When the Union Report is run, you will be prompted to enter a value for the parameter. The value entered will be assigned to the Pass Through Variable, in memory, making it available for use.
3. Add Filters to your reports
The last thing to do is add a filter to each report that you want filtered. Each filter must assign the Pass Through Variable to the field that you want to filter by and that is common between each of the sub reports. In this example the field we want to filter by is product ID which is in both reports. To add a filter do as follows:
a. Select the first sub report and then select the Filters tab and click Add
b. In the Choose Filter Fields list, select the field you want to filter by and click OK
c. In the Choose Comparison Method list, select Equal To and click OK
d. For Enter Comparison Value, enter the code of your Pass Through Variable and click OK
The filter will now be added to the report.
Repeat steps a to d for each sub report you want filtered.
Now, when you run your Union Report, and enter a value for the parameter, that value will be applied to each of the sub reports you created a filter for. Your Union Report will be filtered accordingly.
