- Add dashboard parameters
- Modes
- Deleting a parameter
- Uses of dashboard parameters
- Troubleshooting
Contact Support
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.
Click the Parameters… menu item to launch the Dashboard Parameters window. This can be also achieved by using the keyboard shortcut Ctrl+Shift+D.
Click Add to add a new parameter.
A new dashboard parameter will be added as follows.
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.
Select the Type and enter the Default Value as shown in the following screenshot.
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.
To add relative date range, click Add Relative Date Range option.
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
andShow Prompt
should be in unchecked state.
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.
Now, the list of added parameters will be shown in the following dialog.
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.
You can select and apply the required value from the window.
Deleting a parameter
You can delete a saved parameter using the Delete option.
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.
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.
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.
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:
The data column mode allows you select any number of values from a same data type which can be a text, number, or date.
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.
If you want to edit the display member values, you can able to edit like below.
It will be reflected in the viewer as shown in the following screenshot.
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.
You can use Date range
parameter in Stored procedure
as follows.
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.
You can use Date range
parameter in Query view
as follows.
Expression editor
The dashboard parameters can be used in the Expression editor.
You can use Date range
parameter in Expression Editor
as follows.
User based filters
The parameters option will be listed with the added data sources in the Choose Data Column window.
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.
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.
Pass this parameter to the stored procedure as follows.
Results: