none
Month Filter best practices RRS feed

  • Question

  • I was successfully able to implement a Time Intelligence Post Formula filter.  My dashboard has four elements -- one scorecard, one analytic grid, and two ProClarity reports.  The dashboard delivers data at the month level.  One of the ProClarity reports is a chart giving me a rolling 13 months worth of data, based on the selected month.  I used the following formula for the filter link to the ProClarity report:  Month:Month-13 .  The other elements use the formula Month .

    That all works fine.  However, since my dashboard is at the month level, I don't want to use the calendar control to select a particular date when I'm just using the month that date falls into.  So my first trial was to use a Time Intelligence filter.  I set up the values to give me two years worth of data going back:  Month, Month-1, Month-2, etc.  Using this new filter, my ProClarity report works fine with the formula Month .  Introducing any form of subtraction to the month does not work at all.  For example Month-1 does not work just as Month:Month-13 does not work with the Time Intelligence forumula.  In these cases, I just get an unknown error on the dashboard preview.  The event viewer yields the following error message: The  function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

    So my ultimate question is, what is the best approach to take when wanting to supply the end-user with a list of non-Date-level (Month/Year/Quarter/etc.) values to use to link to various objects, including objects that expect either a single member or a set?



    Wednesday, July 8, 2009 3:14 PM

Answers

  •    You can create a filter based on existing time dimension. This gives you the freedom to display members from dimension selectively.Lets say your time dimension hierarchy has structure like Year-->Half Year-->Quarter-->Month-->Week--> Day. But you don't want to the user to select Day. So while creating a filter, select Member Selection, select your time dimension and then select only the members you want to display (excluding days in your case), display view as Tree View.
       When attaching filter to the dashboard, use the link filter formula to move around the hierarchy. So if user selected Q1 2009, in filter link formula you can write <<UniqueName>>.Lag(3):<<UniqueName>> to show last four quarters of data. Similarly you can use other MDX functions as well. If user had selected M1 2008, it willl show last four months data. One of the advantage of using this approch is that user is not restricted to selecting only month, he can see quarterly performance and yearly performance as well.Check this link for more information on filter link formulas

    About using MDX in Dashboard Designer



    In your case, if you are concerned only about monthly data, then select only Months in your filter members.


    Hope this is helpful.



    • Marked as answer by cincysql Wednesday, July 8, 2009 4:41 PM
    Wednesday, July 8, 2009 3:57 PM

All replies

  •    You can create a filter based on existing time dimension. This gives you the freedom to display members from dimension selectively.Lets say your time dimension hierarchy has structure like Year-->Half Year-->Quarter-->Month-->Week--> Day. But you don't want to the user to select Day. So while creating a filter, select Member Selection, select your time dimension and then select only the members you want to display (excluding days in your case), display view as Tree View.
       When attaching filter to the dashboard, use the link filter formula to move around the hierarchy. So if user selected Q1 2009, in filter link formula you can write <<UniqueName>>.Lag(3):<<UniqueName>> to show last four quarters of data. Similarly you can use other MDX functions as well. If user had selected M1 2008, it willl show last four months data. One of the advantage of using this approch is that user is not restricted to selecting only month, he can see quarterly performance and yearly performance as well.Check this link for more information on filter link formulas

    About using MDX in Dashboard Designer



    In your case, if you are concerned only about monthly data, then select only Months in your filter members.


    Hope this is helpful.



    • Marked as answer by cincysql Wednesday, July 8, 2009 4:41 PM
    Wednesday, July 8, 2009 3:57 PM
  • Thanks a ton.  That link helped a lot.  I wasn't aware one could use MDX-style syntax in the formula editor.  It works great.   I have a member selection filter displaying all of my months, and connected successfully to all dashboard components.  It looks like my next challenge will be to default the selected month to the current (or possibly previous) month.  It looks like my only option to try here will be an MDX Query filter.

    Wednesday, July 8, 2009 4:41 PM