BI Dashboard for ASP.NET Core - How to pass a hidden dashboard parameter to a custom SQL query

This example shows how to pass a hidden dashboard parameter to a custom SQL query. In this example, the DashboardConfigurator.CustomParameters is handled to change the dashboard parameter's default value before it is passed to the query.

Warning: A user can get sensitive information from dashboard parameters. Encode the passed parameter value if possible. Do not store any sensitive information in dashboard parameters that isn't encrypted.

Example Overview

To pass a hidden dashboard parameter to a custom SQL query, do the following.

Create a Dashboard Parameter

To create a dashboard parameter, open the dashboard menu and go to the Parameters section. In this section, create a parameter and specify its settings. Disable the Visible checkbox to hide the parameter from users in Viewer mode. In this example, the dashboard parameter's name is CountryDashboardParameter and its default value is France:

Create a Custom Query

Set the DashboardConfigurator.AllowExecutingCustomSql property to true to allow custom SQL query execution on the server. To allow users to edit a custom SQL string in the SQL String editor, call the DataSourceWizardOptionBuilder.EnableCustomSql method and pass true as a parameter.

Warning: The use of custom SQL queries can lead to inadvertent or unauthorized modifications to your data/database structure. Ensure that you follow best practices and implement the appropriate user read/write privileges at database level.

You can see the query specified for the data source in the Data Source Wizard:

This query contains a query parameter named CountryParameter.

Bind the Query Parameter to the Dashboard Parameter

To be able to change the query parameter's value, bind it to the CountryDashboardParameter dashboard parameter. To do this, on the second page of the Data Source Wizard set the parameter's type to Expression and specify the corresponding dashboard parameter in the Value field.

Change the Default Parameter Value in Code

Handle the DashboardConfigurator.CustomParameters event and specify the default value: DashboardUtils.cs.

As the result, a user sees a dashboard based on the data from the SQL query with the CountryParameter query parameter's value specified in the DashboardConfigurator.CustomParameters event handler (Brazil).

Files to Review

• DashboardUtils.cs
• Index.cshtml

Documentation

• Dashboard Parameters
• Query Parameters
• Custom SQL Queries

More Examples

• Dashboard for Web Forms - How to pass a hidden dashboard parameter to a custom SQL query
• Dashboard for Web Forms - How to specify dashboard parameter values on the client side
• Dashboard for Web Forms - How to update the parameter value when the item's master filter state is changed