All data on dashboards that is based on OLAP data source could be manipulated with OLAP data slicers that could be added in design or production time. If slicer is added in design time, it will be part of the dashboard whenever dashboard is opened. Also users who are not dashboard designers, but only use dashboard could also add dashboard slicer that will be only a temporary supplement to the dashboard. Adding slicer for certain OLAP data source will automatically filter (refresh) all tiles based on same OLAP data source with slicer dimension members. Slicers could be additional changed, reordered and removed to provide fine analysis tool while exploring OLAP data.
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.
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.
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.