Facebook
Linkedin
Twitter
Download
Contact Us
Partners
Blog
Product
On-Premise BI Tools
Product Videos
Product Updates
Product Documentation
Product Tutorials
Scheduled Reports Delivery
Multitenant BI
Embedded BI
Planning & Budgeting
Download
FAQ
OLAP Analysis
OLAP Tools Overview
OLAP Analysis & Reporting Guide
SSAS OLAP Reporting
OLAP Analysis - Decomposition
OLAP Drillthrough
SSAS Reporting & Analysis Solution
OLAP Time Dimension Filtering
Planning & Budgeting with OLAP
OLAP Client
Embedded Analytics
OLAP Tools Gallery
Self-Service BI
Self-Service BI Tools
Self-Service BI Tutorial
Drill-down Analysis & Reports
Self-Service BI Gallery
Self-Service BI User Manual
Dashboards
BI Dashboards On-Premise Solution
Self-Hosted Dashboards Software
KPI Dashboard
Planning & Budgeting Dashboards
Analytical Dashboard
SQL Based Dashboards
GeoAnalytics
Mobile BI
Dashboards Tools
Connect With Your Data
Design Your Dashboard
Dashboard Visualization Options
Dashboard Filters
Dashboard 'Table' Chart Visualization
MDX Query on Dashboards
Dashboard Slideshow
Embedded Analytics & Visualizations
Dashboard Tools Gallery
Galleries
BI Tools Videos
OLAP Tools Gallery
Dashboard Tools Gallery
License
Free Personal Edition
Product Subscription License
Product Permanent License
Planning & Budgeting Subscription License
Home
Product Tutorials
Dashboard Filtering Options
Setup
Planning Setup
Server Side Prerequisites
Client Side Prerequisites
Kyubit User
Kyubit Database
Subscription Service Setup
How To Upgrade
Troubleshooting
Configuration
Authentication Options
Configuration Settings
Multilingual User Interface
Multitenant Mode
General Usage
Administrator Role
Object Permissions
Folders
Analysis & Dashboard Subscriptions
OLAP Analysis
Set OLAP Data Source
OLAP Grid Analysis
Drill-Down Features
Filtering Features
Sorting Options
User Named Sets
User Calculated Measures
OLAP Chart Analysis
Create OLAP Reports
Custom Drillthrough
OLAP Grid KPI
Create OLAP Reports
Peek Analysis
Decomposition OLAP Analysis
Dashboards
Create & Design Dashboards
Dashboard Tiles
Dashboard Layout Options
Dashboard Geo Maps
Queries
Manage KPIs
Scorecards
Dashboard Slicers
Mobile-BI
Multipage Reports
Embedded BI
Embedded Analytics with HTML/Javascript
Embedded Analytics with IFRAME
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.
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.
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.
We use cookies to ensure that we give you the best experience on our website. By using our site, you acknowledge that you have read and understand our
Privacy Policy
.
Ok