This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

Report Parameters

Report parameters are used to customize a data query depending on the user-entered values. Common uses of parameters:

Filter report data at the data source by writing dataset query that contain variables.
Enable users to specify values to customize the data in a report. For example: configure report parameters for filtering by location, company, contact name, date range etc.
Switch between several tables or views for more efficient database utilization. For example, report may use data from pre-grouped data table depending on the parameter value.
Apply row-level data access rules in case of secure reports embedding.

Also parameters are needed if you want to enable data filtering for published reports.
Note: interactive features for published reports (including inputs for parameters) are available only for accounts with advanced publishing subscription.

Parameters Setup

All possible report parameters should be declared in the Cube Configuration form:

Each parameter should have an unique name; with this name you can use actual parameter value in datasource-specific query or in expression of another parameter. For more details about parameters usage in dataset query see help page for concrete data source.

It is possible to specify which UI editor to use for the parameter:

Textbox

Suitable for any kind of parameters. In case of multivalue parameter several values should be separated with comma ,

Checkbox

Used when in case of two-state parameters (like boolean true/false). Also can be used as a switch: depending on checkbox state other report parameter(s) may be applied in a different way.

Dropdown

Used when user can choose an option(s) from a pre-defined drop-down items. For non-multivalue parameter first entry should match parameter's default value (in most cases this is just an empty value).

Dropdown(dynamic)

Dropdown with dynamically-populated items list. Items may be loaded from some dimension of the parameter's cube or any other cube.

Technically data is loaded with internal pivot-table like report query that contains specified dimension(s). This guarantees only unique items in the dropdown. Another consequence is that this query result is cached for a short time like any other pivot table data.

Notice for reports that are embedded in a secure way: all report parameters that are passed in JWT payload may be used in dropdown-dynamic queries.

How end-user can enter report parameters:

Report Designer (inside SeekTable app)

Published Report

Note: inputs for parameters in pubished reports require "Advanced publishing" subscription

Calculated Parameters

In some cases you might need to modify parameter value before passing it to the query, and this is possible with Expression option. The syntax of this expression is the same as for calculated cube members. Typical usages:

Automatically add prepend/append % for 'like' comparison:
```
"%"+Parameter["param_name"]+"%"
```
You can create filter 'presets' with dropdown-editor parameter, and then use special hidden 'switch' parameters to include predefined conditions according to the selected dropdown option:
```
Parameter["dropdown_param"] == "preset1" ?  "1" : null
```
create a parameter to switch between different SQL tables depending on dimension(s) used in the report. This is useful when you want to use pre-aggregated data from materialized view for high-level summary reports, and allow users to drill-down to details when needed:
```
Dimensions.Contains("date_day") ? 1 : null
```
In the Select Query: @contains_day[table_name_with_day;table_name_aggregated_without_day]
create a parameter that controls some JOIN inside Select Query - to include this JOIN into the query only when, say, a concrete measure is used in the report:
```
Measures.Contains("SumOfHours") ? 1 : null
```
In the Select Query: @contains_hours[LEFT JOIN table_with_hour ON (...)]
Use (possibly, specially formatted) parameter's value as a 'raw' SQL:
```
Sql.Raw( Parameter["use_table"]=="view" ? "table_materialized_view" : "table" )
```
In the Select Query: SELECT * FROM @use_table
This capability should be used carefully, as in this way you can allow users to include ANYTHING into the SQL query; we strongly recommend to validate user's input in the expression to ensure that only allowed values will be included as 'raw' SQL. However, if reports are embedded, no problems if 'raw SQL' value is passed in JWT payload as in this case users cannot change it in any way.
Perform necessary conversions for a date-range filtering.

Important: in parameter's expression you may use parameter values that are passed to the report with Parameter["param_name"]. Note that if specified parameter has own Expression you'll get an original parameter value and not a calculation result.