Footer 1

logo

198 West 21th Street, Suite 721, New York NY

5109 Pacific Coast Highway, Los Angeles CA

Email: youremail@yourdomain.com

Phone: +88 (0) 101 0000 000

Fax: +88 (0) 202 0000 001

Top Bar Follow Us icons

Latest Posts

    No posts were found.

 

Dashboard Slicers

Dashboard Slicers

All data on dashboard could be filtered (sliced) using OLAP and SQL dashboard filters.

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. 

OLAP filters inheritance to child dashboards

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.

SQL data filtering

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.

01. 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.

02Select 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.

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

Creating SQL queries with ‘Input Parameters’

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.