none
Cumulative Sum to Start Based on a PPS Filter Selected Date Range RRS feed

  • Question

  • The short and direct:

    We need the ability to show a cumulative total column based on a data range (monthly summary) that is dynamically filtered.

    Example   1

    Date

    Month Value

    Cumulative

    1/1/2012

    500

    500

    2/1/2012

    750

    1250

    3/1/2012

    1000

    2250

    Date Range:   1/1/2012 - /03/01/2012

    Example   2

    Date

    Month Value

    Cumulative

    2/1/2012

    750

    750

    3/1/2012

    1000

    1750

    Date Range:   2/1/2012 - 03/01/2012

    The cumulative column must sum starting from the user selected date range, from the first period to the last time period regardless of which date range is selected.

    Using:

    1. SSAS Cube

    2. PPS Scorecard

    3. Using a Year, Quarter, and Month Time Multi-select tree filter. This should trigger data ranger to run calculations based of the selections.

    FIlter

    Thank you,

    JOsh

    Wednesday, August 22, 2012 12:01 AM

Answers

  • As far as I know, it would be get pretty complex as you are not restricting your time dimension to any level meaning user can give date range as well as quarter ranges. So you would be doing whole lot of conditional checks for hierachy level.

    Then I am not sure how your range would fit in, as PPS sends all members selected in tree as a set to SSAS, so you would need to parse out the first item and last item then use in RANGE ':' operator for running total.

    Alternate would be to use analytics report or SSRS report with 2 time filters; easier to build and manage.


    http://dailyitsolutions.blogspot.com/

    • Marked as answer by Josh Dev Co Thursday, August 30, 2012 4:53 PM
    Wednesday, August 22, 2012 1:57 PM