Integrate OLAP Reports & Dashboards With Your App
OLAP Tools Overview
OLAP Analysis - Decomposition
OLAP Time Intelligence
Integrate OLAP Reports With Your App
Dashboard Tools Overview
Dashboard Layout & Style
Dashboard Visualization Options
Geo Data on Dashboards
Dashboard 'Table' Chart Visualization
MDX Query on Dashboards
Integrate Dashboard With Your App
BI Tools Videos
OLAP Tools Gallery
Dashboard Tools Gallery
Free Personal Edition
Purchase Full Edition
Dashboard Filtering Options
Server Side Prerequisites
Client Side Prerequisites
Subscription Service Setup
How To Upgrade
Multilingual User Interface
Analysis & Dashboard Subscriptions
Set OLAP Data Source
OLAP Grid Analysis
User Named Sets
User Calculated Measures
OLAP Chart Analysis
Create OLAP Reports
OLAP Grid KPI
Create OLAP Reports
Decomposition OLAP Analysis
Create & Design Dashboards
Dashboard Layout Options
Dashboard Geo Maps
IFRAME OLAP Analysis/Dashboard Integration
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.
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.
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.
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.