This procedure requires a JDBC (Custom) data source type. Establish this data
source before continuing with the instructions below. You do not need to construct a
query yet.
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.
Note: This is the nuclear option for adding user interactivity to a report; it allows
you to parameterize both structure and values. If you only need to parameterize
values, see
Simple SQL Output Parameterization
instead.
-
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.
When a user runs this report, he will be presented with an interactive field that
specifies the source of the column you specified.