Dynamic parameter allows user to execute the custom query or stored procedure dynamically based on the parameter while viewing the dashboard. User can also use the parameter in the expression columns.
User can create the dashboard Parameter at datasource level. The dashboard parameter icon is provided as shown in the following image.
Open the Dashboard Parameter window. A default parameter will be added as follows.
There are two different modes in the dashboard parameter as follows:
Literal
: Defines the static value of the parameter.
List
: Users can select the multiple values that can be used for initial execution.
The literal mode allows you to add the text, number, date, boolean, and range type of values.
Select the String or Number Type and enter the Current Value as shown in the following screenshot.
Select the Date type and also select the date in the date picker for date type parameter.
Select the Boolean type and also select true or false for boolean type parameter.
User can add the description for the dashboard parameter.
Click Save
to save the added parameter. After the parameter saved successfully, the success message is shown in the footer of the dialog and Add
will be enabled at the footer.
User can provide the range values for number and date type parameters on choosing Selection Type
as range.
To add or edit the relative date range for date type parameters, Select the Add Relative Date Range
option from drop-down. User can create the relative date ranges.
To remove all relative date range for the data type parameter, Select the Clear Relative Date
option from drop-down. User can remove all relative ranges.
There are two different types in the list mode as follows:
Manual
: The list mode allows user to add any number of values from a same data type, which can be a text, number, or date.
Data Source
: The list mode allows user to select the multiple values from the data source.
The list type allows user to add values to the list manually.
User can edit the added values using the double click the value. Also, uncheck or check a eye icon to include or exclude the values.
Users can uncheck or check the values and include or exclude the values using the multi selection combo box.
The list values will be shown as follows while viewing the dashboard, and user can select a value from the list.
User can create parameters with the same name for more than one data sources. When dashboard previewing, data source name will be appended with the parameter name.
There are three different types in the list data source type as follows:
Data Source
: Data fetched from fields for currently dragged tables.
Table
: Data fetched from any of the table from current data source.
Custom Query
: User can also fetch the data by using the custom query option.
User can select the type of data fetched from Field From
drop-down.
User can select the field where the data to be fetched from Field
drop-down.
User can select or unselect and include or exclude the data from the multi selection combo box.
In Table
type parameter, user can select the table from the current data source from Table
drop-down.
In Custom Query
type parameter, select the Run
to execute the query.
When the Show in Viewer
option is enabled in the Dashboard Parameters window, then the parameters added in the dashboard will be available in the dashboard preview.
When the Set first value
option is enabled in the Dashboard Parameters window, only then first value is selected from the list of selected values for the parameter.
Select a parameter from the left panel. Modify the parameters and click the Update
.
Select a parameter from the left panel and remove the parameter using the delete icon.
The dashboard parameters can be used in the expression, stored procedures, and custom query.
Select the parameter from the Column Settings
or use the following syntax to create a expression with dashboard parameter.
Syntax: @{{:ParameterName}}
For range type parameter, syntax should be provided as follows.
Syntax:@{{:ParameterName.START}}
or @{{:ParameterName.END}}
Those parameters, which are created using the list mode, will not be used in expression.
Choose the parameter option from the stored procedure parameter window. Available parameter will be listed in value drop-down box.
You can use the range type parameters as follows.
Press the key Shift+2
to list the available parameters. You can use it anywhere in query editor window.
Another way is, user can manually type the required parameter in code view using the syntax.
Syntax: @{{:ParameterName}}
For range type parameter, syntax should be provided as follows.
Syntax: @{{:ParameterName.START}}
or @{{:ParameterName.END}}
Expression
: You can prefer the ‘CONCAT’ function.
Example: CONCAT([Parameter1])
Stored Procedure
: You should create a stored procedure using the logic trick. Because, SQL stored procedure accepts only single object as parameter. However, you can pass multiple values as comma separated single string to the parameter by a trick.
Custom Query
: You can use this dashboard parameter with multiple values only using IN query.
Example: Select * from Table_Name where ColumnName in (@{{:ParameterName}})
User can use the parameter from the cross data source using the following syntax.
Syntax: @{{:DatasourceName.ParameterName}}
The dashboard parameters used the dashboards, user can update the parameter value dynamically in view mode.
Click dashboard parameter icon to update the dashboard parameters.
Now, the list of added parameters will be shown in the following dialog.
You can change the values and click Apply
to render the dashboard based on the updated value.
The dashboard parameters used the dashboards, user can update the parameter value in URL.
You can pass the parameters to a dashboard by including them in a dashboard URL. Passing parameter values within URL will apply filter in the dashboard on initial load itself.
To set a dashboard parameter within a URL, use the following syntax:
For single parameter:
<parameter1>=<value>
For multiple parameter:
<parameter1>=<value>&&<parameter>=<value>
Using the syntax, you can pass the dashboard parameter value in URL as follows.
https://<servername>/dashboards/<dashboardid>/<category>/<dashboardname>?&&<parameter1>=<value1>&&<parameter2>=<value2>
You can pass the stored procedure parameters to a dashboard by including them in a dashboard URL. Passing parameter values within URL will apply filter in the dashboard on initial load itself.
To set a dashboard parameter within a URL, use the following syntax:
For single parameter:
@<parameter>=<value>
For multiple parameter:
@<parameter1>=<value1>&&@<parameter2>=<value2>
For dashboard parameter:
@<parameter>=<@{{:DashboardParameterName}}>
NOTE: Parameter name is case sensitive.
• Character and String
• Numeric
• Date and time
• Binary
For date type parameter values, you can provide the server (where the query will be executed) supported date format as parameter value.
To append your query string with URL, add a prefix (?) to the query string. If (?) is already there in the URL, add a prefix (&&) to the query string.
https://<servername>/dashboards/<dashboardid>/<category>/<dashboardname>?@<parameter1>=<value1>&&@<parameter2>=<value2>
Using the dashboard parameter syntax, you can pass the dashboard parameter value in URL as follows.