I am fortunate to have colleagues that are contributing to my Blog. Time doesn’t allow us to know all areas of our products. Denise Benjamin, one our technical writers for the PPS information worker content, has given me some content on creating filters for PPS Dashboards. This is a little different (even refreshing) from the more IT Pro related topics I post. I appreciate her contribution. I also borrow some from Nick Barclay’s book on Monitoring. I also link to Blogs at the bottom of this content that have contributed to the topic of dashboard filters.
[Update: There are a few questions that customers have asked that Denise responds to at the bottom of this post. I encourage you to post questions to the Monitoring and Analytics forum and the PerformancePoint Server Planning forum where there are lengthier discussions about filtering. ]
Of the five major steps to creating a dashboard, creating filters is step 2. Before that, the assumption is that you have created a layout.
- Defining a layout
- Creating filters
- Adding items
- Configuring item links
- Previewing and testing
In dashboard elements, you can define objects that contain a collection of values for users to select. Nick states, “The selected values can be passed to other dashboard items (scorecards or reports) in order to filter the data contained in them.” Denise says, “The power of using filters is that you can make it easy for your dashboard users to focus on the information that is the most relevant to them.”
The following are six types of filter templates available that provide different methods for populating values.
- MDX Query
- Member Selection
- Named Sets
- Tabular Values
- Time Intelligence
- Post Formula
After you have established a source for your data and selected the filter template, you select the display method which looks like one of the following.
You can create a variety of filters for your PerformancePoint dashboards, including the following:
- Filters that appear stand-alone elements in a dashboard
- Filters that are hidden queries or background items in reports or scorecards
- Filters that are scorecard KPIs linked to other reports in a dashboard
You can create a named set called Quota Items and place it in the background of a report. That way, the report displays only the quota sales that the manager is interested in. Or, you can create a stand-alone filter that contains three items: All Sales, Quota Sales, and Non-Quota Sales. The sales manager can then use the filter to view results for each of those categories.
Creating Stand-Alone Filters
When you create stand-alone filters, you use the Create a Filter wizard in Dashboard Designer. You begin by selecting one of six templates, which vary by the data source and the method that you use to create the list of items for your filter. The following table describes the different filter templates that are available in Dashboard Designer.
|Select this template||to do this||using this data|
Create a filter by specifying a Multidimensional Expressions (MDX) query that Monitoring Server uses to create a list of items in your filter.
Note Before you use this template, you should be familiar with the database and with MDX. For more information about MDX, see the Multidimensional Expressions (MDX) Reference on TechNet.
SQL Server 2005 Analysis Services
SQL Server 2000 Analysis Services
Create a filter by selecting individual members in your data source.
Note The data source you select for your filter must contain dimension members.
|Create a filter by selecting a named set, which is a group of members that is defined in the database.||
SQL Server 2005 Analysis Services
SQL Server 2000 Analysis Services
|Create a filter by selecting members that are in a table.||
Create a Time filter by specifying a formula that uses the Simple Time Period Specification (STPS) syntax. Your filter must contain a list of time periods, such as a number of most recent years, months, or dates. When you link your filter to scorecards and reports, you can display information for those time periods in your dashboard.
Note Before you use this template, you should be familiar with the database and with STPS. For more information on STPS, see About syntax for Time Intelligence expressions.
Create a Time filter by specifying a formula that uses the STPS syntax. Use the filter, which appears as a calendar control in the dashboard, to display different time periods in various reports and scorecards. For example, you can use a single Time Intelligence Post Formula filter to display data for years in one report or scorecard, and months in another.
Note Before you use this template, you should be familiar with the database and STPS. For more information on STPS, see About syntax for Time Intelligence expressions.
Any data source that meets the following conditions:
The data source contains a time dimension that contains the time periods you want to use in your filter.
The time and aggregation settings for the data source have been configured to work with Time Intelligence formulas. For more information, see Configure time and aggregation settings.
After you create your filter and publish it to PerformancePoint Monitoring Server, your next step is to add it to a dashboard page, and then link it to one or more scorecards and reports in your dashboard. (See Link filters to scorecards and reports for more information.)
Creating Filters That Are Embedded as Hidden Queries or Background Items
When you create filters that are essentially hidden as custom queries or background items in reports or scorecards, you typically do so when you want to display very specific information in a single report or scorecard. That is, you do not intend to use that filter for any other elements on your dashboard page.
You typically create these kinds of filters for analytic charts and grids. You do this by placing one or more dimension members in the Background section in the workspace for a report (using the Editor tab in the center pane of the workspace).
Dashboard consumers can see whether any items are placed in the background of a report or scorecard by right-clicking on the report and then selecting Show Information. Any filters or background items that are applied to the report are listed across the top of the report view.
You typically create hidden queries by specifying a custom MultiDimensional Expressions (MDX) query when you create your report. In the case of analytic charts and grids, instead of using the drag-and-drop interface to place dimension members and measures into the Series or Bottom Axis sections (for an analytic chart) or in the Rows and Columns sections (for an analytic grid), you can click the Query tab in the center pane of the workspace and specify your MDX query there. (See About using MDX in Dashboard Designer for more information.)
Note: When you use a custom MDX query to display data in an analytic chart or grid, your dashboard consumers may not be able to drill up or down in the report to see higher or lower levels of detail.
Creating Filters That Are Scorecard KPIs Linked to Other Reports
When you create filters that are scorecard key performance indicators (KPIs) linked to other reports, you provide an easy way for dashboard consumers to view additional information for one or more KPIs. You typically do so when you want to make a lot of information available to your dashboard users without cluttering up your scorecard. Dashboard consumers can click a KPI, and any reports that it is linked to automatically refresh to display data for that KPI.
You can link KPIs to analytic charts, analytic grids, and Reporting Services reports. You can use KPIs that are cube members, or you can use KPIs that are directly associated with a measure in an analytic reports. (See Link scorecard KPIs to analytic reports for more information.)
Dashboard Designer provides you with a wide range of tools and methods to create filters for your dashboard pages. This enables you to create dashboards that not only display useful, relevant information to your dashboard consumers, but you also create an inviting environment that encourages users to explore data and analyze results. For more information on creating filters in Dashboard Designer, visit the Filters section for PerformancePoint at Office Online.
Good stuff. Here are some more great links for dashboard filters.
Tell me if there are more so I can include them. Thanks!
Is there a way to create links on cell in PPS dashboard? I need that it shd open the link if user clicks any part of a cell in pps
[deniseb] There is a way to link cells in a scorecard to other reports in a dashboard. For example, you can link a KPI to an otherwise hidden analytic chart or grid by using Display Condition (http://office.microsoft.com/en-us/performancepoint/HA102524931033.aspx). You can link an item in a scorecard to a Reporting Services report (http://office.microsoft.com/en-us/performancepoint/HA102694451033.aspx), and you can link use a KPI as a filter for analytic charts or grids (http://office.microsoft.com/en-us/performancepoint/HA102694441033.aspx). vibs should probably start with “How do I link a scorecard item to a report by using Dashboard Designer?” (http://office.microsoft.com/en-us/performancepoint/HA102411051033.aspx
How do I reference a filter I created in PerformancePoint from a SSAS cube in the Request.Params syntax. For example, I pulled Country in from my cube as a filter in my dashboard. I have a redirect aspx that is looking at getting the value from this filter by a Request.Params[“Country”], but nothing happens after I link them.
Now if I create my country values in a tabular list and the column name is Country then all works fine but not if the filter is from a cube…can you help?
[deniseb] Do we know which filter template Mindy used when pulling Country in from the data cube as a filter in the dashboard? And do we know if Mindy used Dashboard Designer to create that filter, or SharePoint? Filters that are created by using SharePoint won’t connect properly to PerformancePoint Web Parts, which means PerformancePoint dashboards can only use filters that are created in Dashboard Designer. If feasible, it might be best for Mindy to create the filter by using the Tabular Values filter template. I’d also recommend the following resources:
· “How do I create a dashboard filter?” (http://office.microsoft.com/en-us/performancepoint/HA102446551033.aspx)
· “Dashboard Filters” on MSDN (http://msdn.microsoft.com/en-us/library/cc159446.aspx)
I have two date list filters one is for Fromdate and another is for Todate. My KPI should get the actual values from the cube for given date range(Between Fromdate and Todate).Can u show me how to create MDX tuple formula for this.
Thanks in advance.
My mail id is email@example.com.
ANSWER: [deniseb] Not sure if we have the info that Bobby needs, but here are some articles to try:
· Create a custom MDX filter (http://office.microsoft.com/en-us/performancepoint/HA102433971033.aspx)
· About syntax for Time Intelligence expressions (http://office.microsoft.com/en-us/performancepoint/HA102411381033.aspx)
· About using MDX in Dashboard Designer (http://office.microsoft.com/en-us/performancepoint/HA102436151033.aspx)
MDX Language Reference on TechNet (http://technet.microsoft.com/en-us/library/ms145595.aspx)
I am working on cascading prompts. I tried to deploy your above sample.As per the URL:http://blogs.msdn.com/performancepoint/archive/2008/02/12/creating-a-custom-dashboard-filter-for-performancepoint-server-2007-code-sample.aspx
I did all the steps mentioned in the install.txt and also followed the msdn links which you said about,I still faced ceratin errors regarding the same:
1. When I open Report “AW Grid” it says that “data source is not available”
2. In the dashboard I do not see the SQL Filter , when I try to create the filter I do not see the SQL Filter in the template list.
Please help me on the same. Please consider this as an urgent basis. Your answer would be highly appreciated.
I would send to you an detailed mail about the steps, I carried.
[deniseb] It sounds like there is a permissions issue somewhere in Office SharePoint Server 2007 or Windows SharePoint Services 3.0. I think that’s why the “data source is not available” message appears. Debasish might want to see some of the “How do I…” articles on MSDN (http://msdn.microsoft.com/en-us/library/bb836266.aspx).