This procedure requires a JDBC data source type.
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 adding an SQL WHERE statement to your query.
Note: You can only use this procedure to parameterize data returned by a query. You
cannot use a WHERE statement to dynamically choose columns or change the structure
of tabular data. If you need to go beyond the capabilities of the method explained
in this section, see
Advanced SQL Output Parameterization to create a custom formula instead.
-
Open the report you want to parameterize.
-
Click the Data tab in the upper right pane.
-
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 below:
Field |
Purpose |
Name |
The name of the parameter within Report Designer |
Label |
The label of the parameter that will be shown to report readers
-- a "friendly name" |
Value Type |
The data type of the column you chose in the Value field
above |
Data Format |
Determines how the data specified by Value Type is formatted.
For instance, dates and times can be formatted in a variety of
different ways |
Default Value |
The value from the Value column that you want to pre-populate
the parameter object with. This is a text field, so you must know
the values in advance |
Default Value Formula |
Allows the Default Value to change dynamically, based on the
formula you specify |
Post-Processing Formula |
Allows you to update a selected value according to conditions
you specify in your formula |
Mandatory |
A checkbox which determines whether this parameter is required
in order to display any data in the report |
Hidden |
A checkbox which hides the parameter from appearing when the
value is already passed in a session variable |
Display Type |
The method of selection for this parameter; determines how
report readers choose different values |
Query |
A drop-down list of queries that you have already defined. If
you need to define a new query, use the toolbar above the left
pane |
Value |
The value that is substituted into the query |
Display Value Formula |
Forces the display value to change depending on the conditions specified in your formula |
-
Edit your target data source by double-clicking its entry in the Structure
pane.
-
Below your FROM statement, add a WHERE statement that specifies
which column you would like to query the user about, assigned to a parameter
that has a name descriptive enough for users to understand.
This should be one of the columns you have a SELECT statement for in
the same query.
-
Click OK to save the query.
-
Include the parameterized fields in your report by dragging them onto the
canvas.
-
Publish or preview your report.
When a user runs this report, he will be presented with an interactive field
that specifies an adjustable constraint for the column you specified. For instance, in
the example below, the constraint would be a specific product line from the PRODUCTLINE
column of the PRODUCTS table.
SELECT
PRODUCTLINE,
PRODUCTVENDOR,
PRODUCTCODE,
PRODUCTNAME,
PRODUCTSCALE,
PRODUCTDESCRIPTION,
QUANTITYINSTOCK,
BUYPRICE,
MSRP
FROM
PRODUCTS
WHERE PRODUCTLINE = ${ENTER_PRODUCTLINE}
ORDER BY
PRODUCTLINE ASC,
PRODUCTVENDOR ASC,
PRODUCTCODE ASC