Summarizing Range of Time Dimension Members using BPC 10 functions & Macro

[former_member216978]

Purpose

To create a Report whose page axis can change dynamically upon user selection parameters (from epmcontextmember).

Suppose I have a project whose start and end date is maintained in properties.So when I select the project I want to see the sum total of values (between start period to end period years.)I have taken only years e.g. 2012.TOTAL members,but this macro can work for any general scenario to summarize any combination of dimensions.

Format

In the above Example I have 2 project members .The Project1 is running in multiple years and Project2 is running only for single year.

Scenario 1.

When we select Project1 in POV

The Page Axis should be filtered with TIME members 2012.TOTAL,2013.TOTAL,2014.TOTAL.

Scenario 2.

When we select Project2 in POV

The Page Axis should be filtered with TIME members 2012.TOTAL

The Macro Code to create function String:

We will create function which will generate 2 different strings which will have the same design as EPMOLAPMEMBERO or EPMOLAPMULTIMEMBER.

The start and end periods are populated using write these 2 formulae

==EPMMemberProperty(,"ORGANIZATION","START_PERIOD")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function GETTOTALPROJECT(start_period As Range, end_period As Range)
    Dim curr_period As String
    Dim curr_year As String
    Dim start_year As String
    Dim end_year As String
    Dim count As Integer
    Dim prefix As String
    Dim suffix As String
    Dim Desc_year As String
    Dim Start_Olapo As String
    Dim start_olapmulti As String
   
    Start_Olapo = "=EPMOlapMemberO("
    start_olapmulti = "=EPMOlapMultiMember("
           
    prefix = """[TIME].[PARENTH1].["
    suffix = ".TOTAL]"""
    
    start_year = Left(start_period.Value, 4)
    end_year = Left(end_period.Value, 4)
   
    Desc_year = """" & start_year
   
    If end_year > start_year Then

curr_year = start_year

GETTOTALPROJECT = "," & """000""," & prefix & curr_year & suffix
           
    Do
        curr_year = curr_year + 1
       
    If end_year = curr_year Then
       
        GETTOTALPROJECT = GETTOTALPROJECT & "," & prefix & curr_year & suffix & ")"
        Desc_year = Desc_year & "," & curr_year & """"
    Else
        GETTOTALPROJECT = GETTOTALPROJECT & "," & prefix & curr_year & suffix
       
        Desc_year = Desc_year & "," & curr_year
   
    End If
       
       
        Loop While curr_year <> end_year
       
        GETTOTALPROJECT = start_olapmulti & Desc_year & GETTOTALPROJECT
       
       
        Else
         GETTOTALPROJECT = Start_Olapo & prefix & start_year & suffix & "," & """""" & "," & """" & start_year & """" & "," & """""" & "," & """000"")"
       
           
        End If
             
           
End Function

IN the above "000" refers to default report.You can change this to 001 or 002 to apply this to Report1 , Report 2.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

After Creating this function ,create another function to call this function in a cell  (where EPMOLAPMEMBERO or EPMMULTIMEMBER of page axis will appear).

E4 = cell for formula

K33 = POV Selected Project e.g. PROJECT1,PROJECT2

C2 = start period    =EPMMemberProperty(,$K$33,"START_PERIOD")

C3 = end period   = =EPMMemberProperty(,$K$33,"END_PERIOD")

This event will be called before refresh to enable the function.

Function BEFORE_REFRESH()

Range("E4") = ""

Range("E4").Formula = GETTOTALPROJECT(Range("C2"), Range("C3"))

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Output

Scenario 1

POV - Project1

String Created by GETTOTALPROJECT =

=EPMOlapMultiMember("2012,2013,2014","002","[TIME].[PARENTH1].[2012.TOTAL]","[TIME].[PARENTH1].[2013.TOTAL]","[TIME].[PARENTH1].[2014.TOTAL]")

Scenario 2

POV - Project2

String Created by GETTOTALPROJECT =

=EPMOlapMemberO("[TIME].[PARENTH1].[2012.TOTAL]","","2012","","002")