Formatting of EPMCopyRange() function - BPC 10

[Former Member]

Hi,

How can formatting such as bold calculated members be applied to columns that have been generated using the EPMCopyRange function?

Background

I am using the EPMCopyRange() function to display EPMComparison() variances on a single report with multiple columns. Each column has varying dimension member configuration (as shown in image attached).

I have explored options of using LocalMembers, but apart from the auto-fill down functionality, it does not meet the requirements of the report I need to build, in terms of flexibility and dynamic referencing of the Local Member formula.

The EPMCopyRange() function is essentially enabling the auto-fill down functionality, ensuring every row in the report has the variance formula, whilst allowing users to add columns where-ever they wish on the report without the variance column references becoming broken.

Attached is an illustration of the conflict between The EPMFormattingSheet, and the EPMCopyRange(). Calculated members should have a Heading, and Data with a Red background

I found a IdeaSpace post regarding this issue: https://cw.sdn.sap.com/cw/ideas/11869 which doesn't look promising.

Any advice on how I could apply the formatting or do the Variance formulas as I require I would really appreciate it!

Many Thanks,

Nick

[Former Member]

Hi,

I know this is an old post but I had a similar problem, but could get resolution by using conditional formatting.

If we apply conditional formatting on the EPM copy range Row then that will be applied at the very end and that way we can resolve the issue.  Screenshots will give more details on the same.

Hope this helps.

Thanks,

Udath

[Former Member]

very helpful this is already quite well known.  did you also get the encryption/Decryption issue fixed?  template looks good..  thanks.. Gaja

[Former Member]

Nick, My friend, try playing with "Priorty to row\column" (two small check box at top left hand side in formatting page). I resolved one of same issue in past using same method but that was on MS.

Thanks Rohit

[Former Member]

Hi Rohit,

I've tried changing the formatting sheet to switch to Priority to Row (which I would expect the calculated member formatiting of making it bold, would override any EPMCopyRange that is in Columns), but unfortunately it doesn't do that.

It is as-if the EPMCopyRange() runs after any formatting from the Formatting Sheet has been applied, like the EVAfterRange in BPC 7.5.

Any other thoughts of suggestions would be much appreciated!

Nick

[Former Member]

Hi, Nick

I know this is old post, but did you resolve this issue of EPMCopyRange() conflicting with the Formatting Sheet?

We are using BPC 10, EPM SP16 and would like to use the CopyRange function to implement the Comments in Columns solution

Unfortunately, the CopyRange overwrites all our nice formatting.

Any suggestions?

[former_member190501]

Hi Roxane,

EPMCopyRange will execute after formatting. That is why your formatting is gone. For similar requirement I did as follows instead of using EPMCopyRange.

Just give a try:

Create 2 local members

local member one - Blank Formula insert at end of column axis

local member Tow - Create with EPMSaveComment referring to local member 1

Do the formatting as per your requirement for these 2 local members.

Hope it helps...

regards,

Raju

[Former Member]

Perfect.  Exactly what is needed.

Thank you.