Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Time Intelligence Post Formula Filter Template in Performance Point Server


By:   |   Last Updated: 2008-09-11   |   Comments (7)   |   Related Tips: > Performance Point

Problem
I'm trying to setup a time intelligence filter in a Performance Point dashboard.  I've heard about the Time Intelligence Post Formula filter template but I'm unsure of how to implement it.  Can you provide the details and an example of how to do it?

Solution

As the name suggests, the Time Intelligence Post Formula template filter allows you to specify a formula to be used as a filter on the dashboard.  The formula is based on the Simple Time Period Specification (STPS) which defines the types of expressions that can be used in a time intelligence filter.  The key feature of this filter is that you can link it to one or more objects on the dashboard and specify a formula for each dashboard object.  This allows you to use a single filter to calculate different date ranges; e.g. last 5 years, last 8 quarters, last 4 quarters, last 12 months, etc. all on the same dashboard.  The filter provides a reference date which is used in the formula.  By default the reference date is the current date, but you can set it to any date you like using the familiar calendar picker control. 

Let's take a look at a sample dashboard that we will build to demonstrate how to implement the time intelligence filter:

The above dashboard has an Analytic Chart, a Scorecard, and an Analytic Grid; all three objects are filtered using the Date filter at the top.   Each object uses the formula Quarter:Quarter-3 which translates to the last four quarters based on the reference date specified in the Date filter.  Assuming a calendar year, the date September 30, 2006 is the third quarter of the year 2006, so the filter will include the following quarters: Q4-2005, Q1-2006, Q2-2006, and Q3-2006.

As a starting point we will use the following dashboard:

Note that in the above dashboard there are no filters.  Let's add the time intelligence filter and hook it up to each object. 

Step 1: Launch the Dashboard Designer from the Microsoft Office PerformancePoint Server 2007 program group and open the workspace file mssqltips_tipf_filter_start.bswx (you can download the file here).  You will see the following in the Workspace Browser (on the left hand side of the Dashboard Designer):

Right click and select Publish from the context menu for each item under the Data Sources, Reports, Scorecards, and Dashboards.

Step 2: Click PDW under Data Sources then click the Time tab.  You should see the following:


The Time tab is an important point to note.  It represents a mapping of the Time dimension in the cube to the standard member levels that are used by the Time Intelligence Post Formula template filter.  In other words it specifies what column in the Time dimension is the Year, the Quarter, etc.  In the above example the column names are the same as the Member Level.  The Time tab allows exists so that the column names can be whatever you like.  Pick the lowest level in the hierarchy that you want to use in any formula (Day in this case), select a sample member, enter the date of the member, then map the column names to their respective Member Level.

Step 3: Select the Dashboard under Dashboards in the Workspace Browser and you should see the following:

Step 4: Click the Filters tab, then click the New Filter icon:

Step 5: Select Time Intelligence Post Formula as the Dashboard Filter Template:

Step 6: You will be led through a wizard to define the filter.  Use Date as the filter name.  Select PDW as the Data Source (this is a sample SQL Server Analysis Services cube for PerformancePoint).  The filter will be added under the Filters node in the Details pane (on the right side of the Dashboard Designer).

Step 7: Click on the Editor tab to return to the designer page for the dashboard.  Drag the Date filter you just created from the Details pane and drop it onto the Header zone of the dashboard:

Step 8: Hover the mouse over the Date filter in the Header; drag PDW (it's the name of the Data Source that the filter is based on) and drop it onto the Drop fields to create links rectangle of the Analytic Chart:

When you drop the Date filter onto the "Drop fields to create links" rectangle of the Analytic Chart, the Edit Filter Link dialog will be displayed.  The purpose of this dialog is to specify the object to link to the filter.  Select Time.Calendar (see explanation below) from the Dashboard item endpoint dropdown list then click the Filter Link Formula button:

Enter the formula Quarter:Quarter-3 (this will get us the last 4 quarters based on the reference date in the Date filter):

The Analytic Chart was originally created to display the last 4 quarters by using the Last 4 Cal Quarters w/ Sales named set defined in the cube.  The Analytic Chart is defined in the Dashboard Designer as follows:

 

The named set always gets the latest 4 quarters by using the latest sale date as a point of reference.  The Date filter allows the user to set the point of reference (which can be any date) and the last 4 quarters are calculated based on the date in the filter.   Selecting Time.Calendar from the Dashboard item endpoint dropdown list overrides the Last 4 Cal Quarters w/ Sales named set with the formula per the Date filter.  After deploying the dashboard to a SharePoint site (or just previewing it), you can click the glyph next to the Date filter to display the standard calendar picker and change the reference date from the current date to the date of your choice; e.g.:

 

For this type of filter I like to select the ending date of the quarter.  The quarter is determined based on the date selected.  For any day in September the quarter will be 3.  The date is only used to determine the quarter; it is not used to filter the data; i.e. you will always get all data for the quarter.

Step 9: Hover the mouse over the Date filter in the Header; drag PDW and drop it onto the "Drop fields to create links" rectangle of the Scorecard (this is the same as step 5 except you are applying the Date filter to the Scorecard).  Once again the Edit Filter Link dialog will be displayed.  Select Column from the Dashboard item endpoint dropdown list.  This will display the four quarters as columns in the scorecard.  The other options are Row which will display the four quarters under each row in the scorecard or Filters which will apply the filter in the background (no visible indicator of the date range).

Step 10: Hover the mouse over the Date filter in the Header; drag PDW and drop it onto the Drop fields to create links rectangle of the Analytic Grid.  This again is the same as step 5 except you are applying the filter to the Grid.  Once again the Edit Filter Link dialog will be displayed.  Select Time.Calendar from the Dashboard item endpoint dropdown list then click the Filter Link Formula button and enter the formula Quarter:Quarter-3.  The Analytic Grid is defined in the Dashboard Designer as follows:

Selecting Time.Calendar from the Dashboard item endpoint dropdown list overrides the Last 4 Cal Quarters w/ Sales named set with the formula per the Date filter.

Step 11: Click the Preview icon on the Edit tab at the top of the Dashboard Designer to see what the dashboard will look like when it's rendered in a web page.  Here is screen shot after setting the Date filter to September 30, 2006:

Next Steps

  • Take a look here for examples of expressions using the Simple Time Period Specification.  You can create a formula in a filter to get just about any range of dates.
  • Download the sample SQL Server Analysis Services database backup here.  This was used in the above examples.  You will need to restore it to your SQL Server Analysis Services instance.
  • Download the sample Dashboard Designer workspaces here to experiment with creating time intelligence filters using the Time Intelligence Post Formula filter template.


Last Updated: 2008-09-11


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, February 20, 2012 - 4:15:07 PM - Hua Yang Back To Top

Ray,

Thank you Ray for your post and all your replies to my questions. Now I better understand Time Intelligence Post Formula filter and know how to use it.

Thank you again for your patience and time.

Hua

 

 


Monday, February 20, 2012 - 6:20:11 AM - Ray Barley Back To Top

I think you can put the time dimension in the Background bucket of the analytic grid then configure the filter using the formula for YTD which is something like Year.FirstMonth:Month.  I don't know how to do it with your MDX query.


Sunday, February 19, 2012 - 9:02:17 PM - Hua Yang Back To Top

Ray,

In Sharepoint 2007, how would you do  if you want use the calendar filter for a anylitic Chart which has the following Query.

SELECT
{ [Measures].[Sales Amount USD], [Measures].[Gross Margin USD] } * {YTD like YTD( [Date].[Calendar].CurrentMember ) } }
ON COLUMNS,

HIERARCHIZE( { DESCENDANTS( [Business Unit].[Business Unit].[All], , AFTER ) } )
ON ROWS

FROM [Invoice History]

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR

 

 

FROM [Invoice History]

The the timeCals has a member YTD like YTD( [Date].[Calendar].CurrentMember )

I want to use the calendar as point of reference too.

 

Thank you again.

 

Hua Yang

 

 

 

 

 


Sunday, February 19, 2012 - 8:44:26 PM - Ray Barley Back To Top

The tip was written for PPS 2007.  PPS 2010 is quite a bit different so the steps in the tip for creating the time intelligence filter are not going to match.

I haven't used these type of filters in 2010 so I don't have anything more to add.


Sunday, February 19, 2012 - 8:23:58 PM - Hua Yang Back To Top

Thank you Ray, for your quick response.

is the end point "Time.Calendar" is the dimension hierarchy you based your Calender Filter on?

I followed exactly steps you listed with my cube. I do not see an end point "Time.Calendar" on the list. I am using Sharepoint 2010.

Thank you

Hua


Sunday, February 19, 2012 - 8:13:40 PM - Ray Barley Back To Top

Time.Calendar is used in the report as the point of reference for the named set that grabs the last 4 quarters.  The Time.Calendar filter value determines the last 4 quarters based on the date you select.  The date you select is the last day of the 4th quarter so the report includes the three months that end on that date.


Sunday, February 19, 2012 - 7:41:52 PM - Hua Yang Back To Top

Hi Ray,

Thank you for your article. That is exact solution to my problem. But I do not understand it completely.

At step 8, when you say "Select Time.Calendar (see explanation below) from the Dashboard item endpoint dropdown" to connect your calendar. Where did you use Time.Calendar in the Analytic Chart?

 

Thank you in advance!

Hua


Learn more about SQL Server tools