SAP BPC NW Distribution Template - Set TIME member with current month

[esilitonga]

Dear all and Vadim,

When configuring distribution template, we have 2 options to set a value for the TIME dimension, either in the Variable Time field, or in the Section area (fixed or variable members). This is how I set up my distribution template:

As per above picture, I make this template to read the PERIOD from the EPM context of the BPC report (see variable member PERIOD).

This is the picture of the EPM context member I have in my report (the same report configured in the distribution template):

My requirement is to distribute the report with the TIME = current month without manual intervention from the user.

Can you please advise how I can achieve this?

Thanks in advance.

Best regards,

Edward.

[former_member186338]

In general I recommend you to create very simple fresh report and to check the proposed solution

[former_member186338 · Accepted solution]

You can use Excel functions to generate current month ID:

=YEAR(TODAY())&"."&RIGHT("0"&MONTH(TODAY()),2)

Result: 2021.09

[esilitonga · Accepted solution]

Thanks Vadim for your prompt response.

However, I cannot do any excel function in the Distribution Template as I can only select the TIME member OR I can refer to a TIME context of the BPC report used by this distribution template. Any other suggestion please?

[former_member186338 · Accepted solution]

esilitonga

Sorry, but there is no magic solution for all cases! It depends on how your template is developed. And you provide no info about your template.

If you report is using TIME in some axis you can link the cell to the formula in my answer.

[esilitonga · Accepted solution]

Hi Vadim,

Updated the question to make it more clear. Kindly suggest. Thanks.

[former_member186338 · Accepted solution]

esilitonga

Still not clear!

Show me the formula in the cell with 2019.TOTAL

And how this cell is used in other cells.

[esilitonga · Accepted solution]

It is a =EPMContextMember("Consolidation","PERIOD",) and there is no TIME either in row nor in column, page axis of the report.

[former_member186338 · Accepted solution]

esilitonga

Please, read my comment to the end:

"And how this cell is used in other cells." - for example page axis of some report can be linked to this cell...

Or this cell is only used to change context by double click on it?

[esilitonga · Accepted solution]

Sorry, I have revised my reply previously. But you are correct, this cell is only used to change context by double click on it.

The PERIOD dimension is not in row, column nor page axis.

[former_member186338 · Accepted solution]

esilitonga

If you add time to report page axis you can use the following formula instead of standard page axis formula:

=@ EPMOlapMemberO(YEAR(TODAY())&"."&RIGHT("0"&MONTH(TODAY());2);"[TIME].[PARENTH1].[2021.09]";"2021.09 - Sep 2021";"";"000")

P.S. Replace ";" with "," in the formula according to you regional settings

P.P.S. Context change can be done only using vba - read my second answer

[esilitonga · Accepted solution]

I have added the time to report page and use the formula written above. However, I have got following error message:

[former_member186338 · Accepted solution]

esilitonga

Then your statement:

"The PERIOD dimension is not in row, column nor page axis" - is false!

Go to Edit Report and check

[esilitonga · Accepted solution]

Hi Vadim,

It was my bad for not correctly put the formula in the correct cell. It works and thanks so much. Have a wonderful day.

[former_member186338]

As an alternative you can use VBA like:

SetContextOptions

to lock context for time...