Time Intelligence Post Formula Filters


Time Intelligence Post Formula Filters

The Time Intelligence Post Formula Filters allow you to pass dynamic time periods into your scorecards and report views and have each view display a different set of time values, but all based on the selected date from the filter. (If you are new to Time Intelligence, you will also find this blog entry useful for a higher level overview.) This functionality allows for similar functionality as the Edit Filter Formula functionality in the other filters. Additionally, the selection control for the Time Intelligence Post Formula Filter is a calendar control that will always default to the current calendar date. This is unlike the other filter controls that will persist your last selected value, the Time Intelligence Post Filter Formula will always dynamically reset to the current calendar date. To create a Time Intelligence Post Filter Formula, follow the below steps:

1. Select New Filter from the Dashboard Filters tab.

2. Select the Time Intelligence Post Formula filter type and click OK.

3. Name your filter, optionally provide a description and click Next.

4. Select the data sources you want your filter to apply to, click Next. This list would include all of the data sources being used in your dashboard that have Time Intelligence mapped.

5. Click Finish. Note, you are not prompted for any time intelligence formulas during the filter creation, these all happen during the mapping of the filter to the view. Additionally, there is only one display for your filter and that will be a calendar display.

Now, add the filter to your dashboard and map it to your views as follows:

1. Select the correct data source in the available fields of the parameter and drag that into the input field of your first scorecard/report view. For this example, I will map it to a scorecard.

2. Within the Edit Filter Link dialog, select the dashboard item endpoint (in this example, I will place it on columns).

tipostformula1

3. Click the Filter Link Formula button (note, this is required for Time Intelligence Post Formula Filters). Enter your Time Intelligence Filter formula in the dialog, an example is shown below:

tipostformula2

4. All formulas will be based on the selected date in the Time Intelligence Filter. By default, the Time Intelligence Post Filter will default to the current date (Aug 22, 2007 at the time of this post), so this formula would show quarters Q1-Q4 of 2006, basically taking the current date and backing up 6 quarters to Q1, 2006 and running through 3 quarters back to Q4, 2006.

5. Click OK.

6. Repeat steps 1-5 for each view that you wish to map the filter to. Here is an example of mapping the formula to an Analytic Chart, where I want to display the 12 months of 2006.

tipostformula3

7. Note, that I map my Time Intelligence Filter to the Time Calendar hierarchy in my analytic chart. My Filter Link Formula is as follows:

tipostformula4

8. The resulting dashboard displays as follows:

tipostformula5

Changing the filter brings up a calendar as follows:

tipostformula6

Navigate through months by clicking on the arrows, years by clicking on the Year.

tipostformula7 tipostformula8

Selecting June 22, 2007 would yield the following:

tipostformula9

Time Intelligence Post Filter Formulas are most powerful when used with your existing cubes are updated regularly. This allows for a very time dynamic dashboard that will continuously provide your users with current active data. Unfortunately, the sample cubes don’t exhibit this changing data but hopefully you can extrapolate the value from what I have shown in these samples.

 

Alyson Powell Erwin  (alysonp@microsoft.com)


Comments (11)

  1. sgrice says:

    I love the calendar and the ease of linking it to my cube; however, we never have data for "Today" which is the default date.  Therefore, our dashboards are always blank when they first come up and our client has to select a date.  Is there a way to change the default date?

  2. MSDN Archive says:

    No sorry, but I will enter this as a feature suggestion.

    Thanks,

    Alyson

  3. keruibo says:

    sgrice,

    I’ve struggled with this same default date issue (my data is usually "real-time yesterday").

    I worked out a solution to this using the MDX filter that works very well.  Here is a link with description:

    http://blogs.msdn.com/performancepoint/archive/2007/08/23/time-intelligence-post-formula-filters.aspx

    –Rob

  4. paul__hickman@hotmail.com says:

    Is there any way to make it aware of UK date formats. At the calendar is defaulting to 6th March when it is actually 3rd June.

  5. MSDN Archive says:

    Hi Paul –

    This issue should be resolved with SP1 – releasing later this week.

    Thanks,

    Alyson

  6. gerrydiya says:

    Hi,

    The calender here defaults to de system calender.i.e., from jan to dec. What if de year is 4m sept to august. How do i change de format of de calender.

    Thanks,

    Diya

  7. vpfaiz says:

    I want to display two Time filters where the user can select a "To Date" and a "From Date" and the scorecard and reports will be filtered to records that fall ‘between’ these two dates. Is this possible and if yes, how?

  8. hortoristic says:

    In Performance Point you can pass parameters from a score-card to surrounding other Performance Point reports and those reports refresh using those filters.   We also have gotten an ASP page which is our GIS piece to accept these parameters and that is working fine.  

    The Time Intelligence parameter does not seem to be passed to our ASP page and we don’t understand why?  It passes as a filter to other types of reports but not an ASP page like the other filters do.

  9. Jordy van B says:

    To work with a start and end date you need to create 2 calendars. Then you link one calendar to <<StartDate>> and the other to <<Enddate>>. As formula you just enter Day:Day.

    In the report you will have to create a set called ‘DateRange’ or something similar.

    WITH

    SET [DateRange] AS

       <<StartDate>>.Item(0) :  <<EndDate>>.Item(0)

    SELECT

    NON EMPTY

    [DateRange]

    ON COLUMNS,

    { [Measures].[Price Total] }

    ON ROWS

    FROM [TurnoverCube]

  10. Ken says:

    This doesn't work for me, I have followed the steps to the T and nothing shows up on the report.  There is data for the date range chosen.  I must be doing something wrong.  Any help would be welcomed ken.kolk@medcor.com

  11. shekhar says:

    when i select any date from the calender the calender shud automatically select weekend i.e sunday .

    how to do this . pls suggest!!