Cascading Parameters
- 5 minutes to read
Create cascading parameters to filter a list of predefined parameter values based on another parameter’s values. The following image illustrates cascading parameters where the pProducts parameter values are filtered by the selected category:
In case of two parameters, the first parameter is used to filter the data source for the second parameter with dynamic list settings.
Create Cascading Parameters in the UI
The dashboard in this example is connected to a Northwind database (an SQL Database) and contains three queries: Categories, Products, and OrderReports. The Grid item visualizes data from the OrderReports query.
In this tutorial, you will create two dashboard parameters:
- The pCategory parameter filters the Products query. The Products query is a data source for the pProducts parameter.
- The pProducts parameter filters the OrderReports query.
The steps below create cascading parameters in the WinForms Dashboard Designer:
Create a dashboard parameter called pCategory with dynamic list settings. Use the Categories query as a data member and the CategoryID as a value member.
The parameter settings may look as follows:
Use the created pCategory parameter to filter the Products query.
To do this, invoke the Query Builder and click the Filter… button to specify the filter criteria in the Filter Editor. Choose the Bind To option to automatically bind a query parameter to the created dashboard parameter:
The resulting query looks as follows:
[Products.CategoryID]=?pCategoryCreate a dashboard parameter called pProducts with dynamic list settings. Use the Products query as a data member and the ProductID as a value member.
The parameter settings may look as follows:
Use the pProducts dashboard parameter to filter the OrderReports query.
To do this, invoke the Query Builder and click the Filter… button to specify the filter criteria in the Filter Editor. Choose the Bind To option to automatically bind a query parameter to the created dashboard parameter:
The resulting query looks as follows:
[OrderReports.ProductID] In ?pProductsCreate a Grid item to visualize data from the filtered OrderReports query.
Tip
When using a multi-value parameter to filter a query, create the condition with the Is any of or Is none of operator. For more information, refer to the following topic: Pass a Multi-Value Dashboard Parameter Value to a Query.
Create Cascading Parameters in Code
This section shows how to create cascading parameters in code. The snippet below does the following:
- Creates a dashboard.
- Creates an SQL Data Source with a Microsoft SQL Server database.
- Adds three queries (Categories, Products, Orders).
- The Categories query is used as a data source for the pCategory dashboard parameter.
- The Products query is filtered by the categoryQueryParam query parameter. The categoryQueryParam query parameter is bound to the pCategory dashboard parameter.
- The Orders query is filtered by the productsQueryParam query parameter. The productsQueryParam query parameter is bound to the pProducts dashboard parameter.
- Adds two dashboard parameters:
- The pCategory parameter is a dynamic-list parameter that uses the Categories query as a data source.
- The pProducts parameter is a dynamic-list parameter that uses the Products query as a parameter values data source.
- Creates a Grid dashboard item that the visualizes the filtered data from the Orders query.
// Create a dashboard.
var dashboard = new Dashboard();
// Create an SQL Data Source.
MsSqlConnectionParameters msSqlParams = new MsSqlConnectionParameters();
msSqlParams.AuthorizationType = MsSqlAuthorizationType.Windows;
msSqlParams.ServerName = "localhost";
msSqlParams.DatabaseName = "Northwind";
DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("Data Source 1", msSqlParams);
// Add queries.
SelectQuery queryCategories = SelectQueryFluentBuilder
.AddTable("Categories")
.SelectAllColumns()
.Build("Categories");
sqlDataSource.Queries.Add(queryCategories);
SelectQuery queryProducts = SelectQueryFluentBuilder
.AddTable("Products")
.SelectAllColumns()
.Filter("[Products.CategoryID] = ?categoryQueryParam")
.Build("Products");
queryProducts.Parameters.Add(new QueryParameter("categoryQueryParam", typeof(Expression), new Expression("?pCategory")));
sqlDataSource.Queries.Add(queryProducts);
SelectQuery queryOrders = SelectQueryFluentBuilder
.AddTable("Orders")
.Join("Order Details", SqlJoinType.Inner, "OrderID")
.Join("Products", SqlJoinType.Inner, "ProductID")
.SelectAllColumns()
.Filter("[Products.ProductID] = ?productsQueryParam")
.Build("Orders");
queryOrders.Parameters.Add(new QueryParameter("productsQueryParam", typeof(Expression), new Expression("?pProducts")));
sqlDataSource.Queries.Add(queryOrders);
dashboard.DataSources.Add(sqlDataSource);
// Create a Grid dashboard item.
var grid = new GridDashboardItem();
grid.DataSource = sqlDataSource;
grid.DataMember = "Orders";
grid.Columns.Add(new GridDimensionColumn(new Dimension("OrderDate", DateTimeGroupInterval.MonthYear)));
grid.Columns.Add(new GridDimensionColumn(new Dimension("ProductName")));
grid.Columns.Add(new GridMeasureColumn(new Measure("UnitPrice", SummaryType.Max)));
dashboard.Items.Add(grid);
// Assign the Dashboard to the Dashboard Designer.
dashboardDesigner.Dashboard = dashboard;
// Create dashboard parameters.
dashboard.Parameters.Add(new DashboardParameter("pCategory", typeof(int), 1, string.Empty, true, new DynamicListLookUpSettings {
DataSource = sqlDataSource,
DataMember = "Categories",
ValueMember = "CategoryID",
DisplayMember = "CategoryName",
}));
dashboard.Parameters.Add(new DashboardParameter("pProducts", typeof(int), 1, string.Empty, true, new DynamicListLookUpSettings {
DataSource = sqlDataSource,
DataMember = "Products",
ValueMember = "ProductID",
DisplayMember = "ProductName",
}));