Configuring Dashboard Parameters

Dynamic parameter allows users to execute the custom query/stored procedure dynamically based on the parameter while viewing in the Dashboard Viewer or Dashboard Server. Users can also use the parameter in the expression columns and map the parameter values in user based filters.

Add dashboard parameters

The Dashboard Parameters option is provided in the Dashboard menu as shown in the following image.

Dashboard parameter in menu

Click the Parameters… menu item to launch the Dashboard Parameters window. This can be also achieved by using the keyboard shortcut Ctrl+Shift+D.

Add dashboard parameter

Click Add to add a new parameter.

Add button

A new dashboard parameter will be added as follows.

Literal mode

Modes

There are three different modes in the dashboard parameters as follows:

1.Literal: Defines the static value of the parameter. User can give only a single value and can pass the parameter to different modules.

2.List: Users can add values to a collection. The default value will be used for initial execution.

3.Data Column: Users can add a list of values from an existing column of a data source. The values will be copied to the parameter. The default value will be used for initial execution.

Literal mode

The literal mode allows you add the text, number, date , Boolean, and date-range-data type of values.

Type selection

Select the Type and enter the Default Value as shown in the following screenshot.

Default value in literal mode

Click Save to save the added parameter.

Date range

The date range parameter defines start date and end date that can pass to different modules for execution. Also, the date-range type provides the relative date ranges support.

Date range parameter

To add relative date range, click Add Relative Date Range option.

Custom date range

Relative date range parameter

Dashboard Server logged in user details

You can use the name, full name, and email address of the logged in user as a dashboard parameter. To
achieve this, use the @CurrentUserName, @CurrentUserFullName, and @CurrentUserEmail in the literal mode.

IMPORTANT

In Public dashboards , the current user related dashboard parameters are not applicable and if any current user parameter is configured it will be ignored.

NOTE

The keywords are case sensitive. While using these syntax, make sure that the Show in Viewer and Show Prompt should be in unchecked state.

Server logged in details

Server logged in details

How to view or change the dashboard parameters in Dashboard Viewer

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 viewer tool bar.

Enable show in viewer option

Viewer page

Now, the list of added parameters will be shown in the following dialog.

Show list of parameter

You can change or select the values and apply the values.

When the Show Prompt option is enabled in the Dashboard Parameters window, the parameters window will be shown as a prompt window while loading the dashboard in the viewer.

Enable show prompt option

Show prompt in viewer

You can select and apply the required value from the window.

Deleting a parameter

You can delete a saved parameter using the Delete option.

Delete parameter

List mode

List mode

The list mode allows you add any number of values from a same data type, which can be a text, number, or date.

List mode types

The list will be shown as drop-down list in the Dashboard Viewer, and you can select a value from the list in the viewer.

List mode in viewer

Conditions to use list-mode type with multiple selected values

a. Query view: You can use this dashboard parameter with multiple values only using IN query.

Example: Select *from Table_Name where ColumnName in (@)

b. 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. This link explains about the trick.

c. Expression Editor: You can prefer the ‘CONCAT’ function.

Example: CONCAT([Parameter1])

Data column mode

This mode allows you add the fields available in the added data sources.

Data column mode

Value and display members

You can select a different Display member for a value member, the display member will be shown in the preview and server:

Data column mode

The data column mode allows you select any number of values from a same data type which can be a text, number, or date.

Data column mode multiselection

The list will be shown as drop-down list in the Dashboard Viewer, and you can select a value from the list in the viewer.

Data column mode in viewer

If you want to edit the display member values, you can able to edit like below.

Data column display member edit option

It will be reflected in the viewer as shown in the following screenshot.

Data column display member viewer

Conditions to use data-column-mode type with multiple selected values

a. Query view: You can use this dashboard parameter with multiple values only using IN query.

Example: Select * from Table_Name where ColumnName in (@)

b. Stored Procedure: You should create a stored procedure using the logic trick. Because, SQL stored procedure accepts only a single object as parameter. However, you can pass multiple values as comma-separated single string to the parameter by a trick. Refer to this link for more details.

Uses of dashboard parameters

The dashboard parameters can be used in the stored procedures, Query view,Expression editor, and user based filters.

Stored procedure

The added parameters are listed in the value-drop-down box in the parameters window.

Stored procedure

You can use Date range parameter in Stored procedure as follows.

Date range in stored procedure

Query view

The parameters can be used in the query view, and the number of available parameters will be listed in the query editor when you press the @ key.

Query view

You can use Date range parameter in Query view as follows.

Date range in query view

Expression editor

The dashboard parameters can be used in the Expression editor.

Expression editor

Parameter as expression

You can use Date range parameter in Expression Editor as follows.

Date range as Expression

Date range as Expression

User based filters

The parameters option will be listed with the added data sources in the Choose Data Column window.

User based filters

While clicking the parameters item in the drop-down list, the list mode and data-column-mode parameters can be used in the user-based filters.

Choose parameter

Troubleshooting

Passing a comma-delimited parameter to a stored procedure

To create a stored procedure, follow these steps:

Step 1

Create a database as follows.

CREATE DATABASE TestParserLogic
GO
USE TestParserLogic
GO

Create a parser counter table. This table is used for parsing the comma-delimited parameter.

CREATE TABLE tableToolsStringParserCounter
(
    ID INT
)

You have to populate the table once, when the table is not dropped from the database. For example: In the following, a loop is used from 1 to 1000 and each value is inserted during the looping process.

DECLARE @i INT
  SELECT  @i = 1
WHILE (@i <= 1000)
BEGIN
    INSERT INTO tableToolsStringParserCounter SELECT @i
    SELECT @i = @i + 1
END
GO

Step 2

Here is the query that parses the comma-delimited string in the rows:

SELECT  Convert(Int, NullIf(SubString(',' + @IDs + ',' , 
  ID , CharIndex(',' , ',' + @IDs + ',' , ID) - ID) , '')) AS IDList 
FROM    tableToolsStringParserCounter
WHERE   ID <= Len(',' + @IDs + ',') AND 
  SubString(',' + @IDs + ',' , ID - 1, 1) = ',' 
AND     CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0 

The results of the statement, resemble the output from executing a single column select statement. Now, you can use the given query.

DECLARE @IDs var char(100)
SELECT  @IDs = '429,446,552,1001, 332 , 471' 
SELECT  Convert(Int, NullIf(SubString(',' + @IDs + 
  ',' , ID , CharIndex(',' , ',' + @IDs + ',' , ID) - 
  ID) , '')) AS IDList 
FROM    tableToolsStringParserCounter
WHERE   ID <= Len(',' + @IDs + ',') AND SubString(',' + 
  @IDs + ',' , ID - 1, 1) = ',' 
AND     CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0 

Step 3

You can make some test data up by creating tables (TABLE tableCity and TABLE tableSalesman), and then populating them with data.

CREATE TABLE tableCity
(    CityID Int IDENTITY (1, 1) NOT NULL,
  City var char(12) NOT NULL
)
GO
INSERT INTO tableCity (City) VALUES ('Houston')
INSERT INTO tableCity (City) VALUES ('New Orleans')
INSERT INTO tableCity (City) VALUES ('Atlanta')
INSERT INTO tableCity (City) VALUES ('Orlando')

CREATE TABLE tableSalesman
(    SalesmanID Int IDENTITY (1, 1) NOT NULL,
  SalesmanName var char(10) NOT NULL,
    CityID Int NOT NULL,
)
GO
INSERT INTO tableSalesman (SalesmanName, CityID) VALUES ('George', 1)
INSERT INTO tableSalesman (SalesmanName, CityID) VALUES ('Mark', 2)
INSERT INTO tableSalesman (SalesmanName, CityID) VALUES ('Greg', 3)
INSERT INTO tableSalesman (SalesmanName, CityID) VALUES ('Susie', 4)
INSERT INTO tableSalesman (SalesmanName, CityID) VALUES ('Kevin', 3)
INSERT INTO tableSalesman (SalesmanName, CityID) VALUES ('Bobby', 1)
INSERT INTO tableSalesman (SalesmanName, CityID) VALUES ('Terry', 1)
INSERT INTO tableSalesman (SalesmanName, CityID) VALUES ('Betty', 2)
INSERT INTO tableSalesman (SalesmanName, CityID) VALUES ('Carl', 2)
INSERT INTO tableSalesman (SalesmanName, CityID) VALUES ('Gary', 4)

Step 4

Create a procedure to query all the cities from a comma-delimited list of SalesmanIDs. Within the procedure, you should create a temp table #1 to hold the IDs that are passed to the procedure.

Remember that users want to know the city of their each selected salesman.

CREATE PROCEDURE storedProcedure_CityBySalesman
(
    @IDs as var char(100) --SalesmanIDs
)
AS
CREATE TABLE #1
(
    IDList Int
)
-- Optional index on the temp table
CREATE INDEX idx1 ON #1 (IDList)
INSERT INTO #1
SELECT  Convert(Int, NullIf(SubString(',' + @IDs + ',' , ID ,
CharIndex(',' , ',' + @IDs + ',' , ID) - ID) , '')) AS IDList 
FROM    tableToolsStringParserCounter
WHERE   ID <= Len(',' + @IDs + ',') AND SubString(',' + 
  @IDs + ',' , ID - 1, 1) = ',' 
AND     CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0  

NOTE

Now, use the temp table #1 containing the list of SalesmanIDs that are passed by the parameter @IDs to obtain all Salesman’s cities respectively in the final select statement of the procedure.

SELECT  SalesmanName, City
FROM  tableSalesman s,
    #1 t,
    tableCity c
WHERE  s.CityID = c.CityID
AND  t.IDList = s.SalesmanID
GO

Step 5

Execute the stored procedure in the Dashboard Designer and view the results.

Add the multiple values in the list.

Select multi values in list

Pass this parameter to the stored procedure as follows.

Multi value in stored procedure

Results:

Result window