You can add dynamic interactivity to a published report such that when a user executes or views it, he can specify how to constrain certain parts of the query data. This is called parameterization. Follow the procedure below to parameterize a report by creating a custom formula.
- Open the report you want to parameterize.
- Right-click the Parameters item in the Data pane, then select Add Parameter... from the context menu. The Add Parameter dialogue will appear.
- Select or change the options according to the definitions specified in Simple SQL Output Parameterization.
- Go to the Structure pane, then select Master Report.
- In the Attributes pane, click the round green + icon in the name field of the Query section. The Expression window will appear.
- Click [...]. The Formula Editor will appear.
- In the Formula field, use a SELECT DISTINCT statement to parameterize the data structure with your previously defined parameter, as shown in the example below (paramexample is a placeholder for the name of the parameter you created earlier, COL1 is the example name of the element in your report that will be parameterized, and PRODUCTS is an example table name in your database). Note: The spaces after DISTINCT and before AS are extremely important. Do not omit them.
="SELECT DISTINCT " & [paramexample] & " AS COL1 FROM PRODUCTS"
- Click OK when you are done with the query, then click Close in the Expression window.
- Add a field of the appropriate data type to your report, and name it according to the AS statement you defined in your query. In the example above, the name of the text field would be COL1.
- Publish or preview the report.