Dashboard Filtering Options

Dashboard could contain tiles that have ‘Child dashboard’ configured to open. If parent dashboard have OLAP filters defined, opening ‘Child dashboard’ will pass (inherit) all OLAP filters from parent to child dashboard.



Child dashboard has gray OLAP filters that are inherited by opening from parent dashboard.

Data on dashboard could also be filtered by adding SQL data filters. For this concept to work, SQL Queries with ‘Input parameters’ have to be created that requires some basic SQL knowledge (see 8.2.1 Creating SQL queries with ‘Input parameters’). Once you get dashboard chart that is based on SQL query with ‘Input parameters’ you can add SQL filters that would filter / slice data on the dashboard. SQL Filters could be static ‘Numeric’ and ‘Date Time’ input fields, or they could be drop down list of values that comes from SQL queries defined as ‘Query Type’ = ‘Filter values’. While selecting query for visualization on the dashboard, user can see if created query accepts ‘Input parameters’.



After adding query to dashboard visualization in design mode, moving mouse over tile title will display ‘Input parameters’ this visualization accepts. Now, this dashboard tile visualization is ready to be filtered.



Adding SQL filter will show dialog to choose another query that returns drop down list of values that would be used to filter data or to select static input field that would be used for filtering. 'Number' or 'Date Time' filter type.

• Selecting query for drop down list of values for filtering. Filter will be applied only to visualizations with same ‘Input parameter’ name as defined of query filter.



• Select input field that will be used for SQL filtering. Filter will be applied only to visualizations with same ‘Input parameter’ name as defined for ‘Output parameter’ on this input field.



• Applying SQL filters immediately show sliced data for the dashboard tiles with ‘Input parameters’ that match added filter ‘Output parameter’ name.

To create SQL query with ‘Input Parameters’ some basic SQL knowledge is required for query manipulation. For example, following query…

select top 20 englishproductname, salesamount, totalproductcost from factinternetsales left join dimproduct on factinternetsales.productKey = dimproduct.productKey

… returns values without option to be filtered in dashboard.

Now, we would like to have ability to filter products based on “color” input.

declare @color nvarchar(50)=null select top 20 englishproductname, salesamount, totalproductcost from factinternetsales left join dimproduct on factinternetsales.productKey = dimproduct.productKey where (@color is null or color in (@color))

… and now we have SQL query with ‘Input paramter’ @color.

Important
1) Declared variable must end with ‘=null’ (like in above sample)
2) In ‘Where’ clause, referenced variable must be in brackets ‘(@color)’ (like in above sample)

We have added on the beginning of the query, declaration of input parameter and its type and used it after “where” clause in SQL statement as a filter for ‘color’ table column. Adding parameters on this way guaranties that query will be successfully executed, if parameter is not used and if it is used it will be applied as a filter.