> For the complete documentation index, see [llms.txt](https://docs.pentaho.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.pentaho.com/pba-report-designer/output-parameterization-by-report-designer-cp/advanced-sql-output-parameterization.md).

# Advanced SQL output parameterization

You can add dynamic interactivity to a published report so when you execute or view it, you can specify how to constrain specific parts of the query data. This process is called parameterization.

This procedure requires a JDBC (Custom) data source type. You must establish this data source before continuing with the instructions below. You do not need to construct a query yet.

**Note:** This option allows you to parameterize both structure and values. If you only need to parameterize values, see [Simple SQL output parameterization](/pba-report-designer/output-parameterization-by-report-designer-cp/simple-sql-output-parameterization.md) instead.

Perform the following steps to parameterize a report by creating a custom formula.

1. Open the report you want to parameterize.
2. Right-click the **Parameters** item in the **Data** pane, then select **Add Parameter** from the context menu.

   The Add Parameter dialog box appears.
3. Select or change the options according to the definitions specified in [Simple SQL Output Parameterization](/pba-report-designer/output-parameterization-by-report-designer-cp/simple-sql-output-parameterization.md).
4. Go to the **Structure** pane, then select **Master Report**.
5. In the **Attributes** pane, click the round green Plus Sign (**+**) in the name field of the **Query** section.

   The Expression window appears.
6. Click the Ellipses button .

   The Formula Editor appears.
7. 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.

   ```
   ="SELECT DISTINCT " & *\[paramexample\]* & " AS COL1 FROM PRODUCTS"
   ```

   The *paramexample* is a placeholder for the name of the parameter you created earlier. `COL1` is the example name of the element to be parameterized in your report, and `PRODUCTS` is an example table name in your database.

   **Note:** The spaces after `DISTINCT` and before `AS` are important. Do not omit them.
8. Click **OK** when you are done with the query, then click **Close** in the Expression window.
9. Add a field of the applicable 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`.
10. Publish or preview the report.

When you run this report, you are presented with an interactive field that specifies the source of the column you specified.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.pentaho.com/pba-report-designer/output-parameterization-by-report-designer-cp/advanced-sql-output-parameterization.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.