EPMFormattingSheet

[Former Member]

Hi Expert,

i am having a trouble in formatting my time dimension, i wanted to edit the cell where you can define the all the properties or dimension, i wanted to make an "IF Statement" on that cell, because i wanted to make my time a dynamic. i create a dummy member in time dimension where you can input data on year prospective.

for example i select year 2015, i have a column on monthly basis for the year 2015 which is true member of time dimension and inputted cell, and i need to show the 3 years time but on year basis which the dummy member i created which i need to input data as well, and show 5th to 20th yrs which is a dummy member but calculated cell. all my dummy member have a same property value.

that is why i need to create a IF statement on my epmformattingsheet because i need to color and unlock the cell to those inputted data cells and locked the calculated one.

i hope someone out there to help me on my problem.

Thanks and Best Regards,

Marlon Manalo

[Former Member]

Hi Experts,

Thanks you very much for the support, i already found the solutions for my problem by dividing the 1 reports into 2. and apply the epmrange.

Again thank you very much for the support.

Regards,

Marlon

[Former Member]

HI Experts,

Sorry but i still don't get it what is the best answer for me, my input schedule is too complex for me, this is for budgeting, the data field will change the color and locked all the cell if actual is selected, also i have one more category called "outlook", and if selected it will also have own format, i am done to those two, but in budget category, i need those 3 yrs to be formatted base on what i mentioned on above query.

Thanks and Best Regards,

Marlon Manalo

[Astha_M]

Hi Marlon,

If the columns are fixed, you can put some values say "1" for all the monthly columns, "2", for next 3 years column, "3" for last 2 Years columns and can do conditional formatting using formula w.r.t. cell.

If say Cell AA7 = 1, then format as Yellow and allow input (not locked)

Similarly for other formats and apply the formatting to the cell in the formatting sheet through which you are formatting your columns

Hope it helps.

Thanks

Astha

[Former Member]

Hi Marlon,

Can you explain exact requirement with the help of screen shot? What exactly you want to achieve.

[Former Member]

Hi Marlon,

Try to use  formatting on specific member/ property. Try to use "CALC" property use different formatting for "CALC =Y" and use other formatting for "CALC=N".

or

Maintain  property that will distinguish the member and use that property for formatting.

Hope this will  help you to solve your problem.

[Former Member]

Hi,

here is the screenshot and put some notes on the screenshot.

the green box are inputted cell which in year prospective, i created a dummy member in time dimension will vary depend on selected time.

[Former Member]

Hi,

this is only applicable  if i have a calculated member, in may case for example,

i selected 2016, i will show the 2016 base member and can be inputted, i created a dummy member in time dimension for the year 2017, 2018, 2019 and so on, which is a base member. For the year 2017 to 2019, i will put some data as well, but for 2020 to 2025

for bigger picture: my column consist of: 2016+BaseMember, 2017, 2018, 2019 (which is inputted) 2020, 2021, 2022 (calculated using excel formula)

Regards,

Marlon Manalo

[Shrikant_Jadhav]

Hi Marlon,

If your column structure is fix i.e. number of column are fix then you can use EPMCopyRange to apply required format as well as formula.

Just try on simple report then you will get idea about function.

I think its very simple and solve your issue.

Shrikant

[Former Member]

Hi Shrikant,

My number of column is fix and only the time ID is changing depending on selected time, but my row is dynamic and it will expand according on selected dimension.

Thanks and Best Regards,

Marlon Manalo

[Shrikant_Jadhav]

Hi Marlon,

In this case EPMCopyRange will solve your issue.

Try once, if required more derails let me know.

Shrikant

[Former Member]

Hi Shrinkant,

thank you very much, but can please help me more further, i am new in sap bpc 10 and i don't know how to use the EPMCopyRange yet.

can you give me a screenshot of example for this?

Thank you very much and Regards,

Marlon Manalo

[Shrikant_Jadhav]

Hi Marlon,

Refer below link,

http://help.sap.com/businessobject/product_guides/boeo10/en/EPMofc_10_user_en.pdf

topic 32.8.3  page no 274

Shrikant

[Former Member]

Hi Shrinkant,

thank you for the information, but still i cannot understand, where should i put the epmcopyrange? to the cell where should i need to edit the cell itself or in any space of my report or input schedule?

sorry about this, i assume that this will be the correct answer to my problems.

Thanks and Best Regards,

Marlon Manalo

[Shrikant_Jadhav]

Hi,

Create simple template, outside grid write epmcopyrange function, give report id "000" (if default report),in rows write  "TRUE", source range - give excel cell reference (apply desired format to that cell).

May be helpful.

Shrikant

[former_member186338]

Hi Marlon,

If you have a fixed columns number - then use

and format columns directly on the input schedule without epmformattingsheet.

Vadim

[Former Member]

Hi Shrinkat,

i still don't get it, when i tried to format the cell, the epmcopyrance is applying to all data field.

here is the screenshot what i wanted to happen:

i only wanted the green box which column AF to AH to be input with color yellow, similar to the red box which is inputted fields.

Thanks and Best Regards,

Marlon Manalo

[Shrikant_Jadhav]

while specifying source range give it from the start of report i.e. R to AJ (considering your template start from column R up to AJ) . And apply respective format to each cell.

Shrikant

[Former Member]

Hi Shrikant,

sorry but i dont get what you mean, i only need to change the format for column AF to AH.

the rest i am using the EPMFormattingSheet, i just cant find ways how to change to those 3 column because it has a same property for column AI onwards.

sorry for the trouble and thank you very much for the support.

Regards,

Marlon Manalo

[Former Member]

Hi Vamin,

is this applicable to the dynamic row but static in column?

please see my example above, i still need to use the epmformattingsheet due to changing all the data field depend on selected category.

Thanks and Best Regards,

Marlon Manalo

[former_member186338]

Hi Marlon,

"is this applicable to the dynamic row but static in column?" - Yes!

The only issue can be that all rows will be formatted the same way!

You can also use conditional formatting in epmformattingsheet based on column number...

Vadim

[Former Member]

Hi Marlon,

You can use EPMCOPYRANGE also in your case.

Synatx:-EPMCopyRange(ReportId, Row, Range)

Parameters-

ReportId- ID of one or several reports.

Row-

True- Indicates Column.

False- Indicates Row.

Range-Specify the range of given condition.

Through EPMcopyrange function you can apply formulas also, function  will apply only on the data region. Simply manually lock the cell which you want to lock  for user and apply copy range function. EPM function work dynamically, but it well act only on the data region.

Take look at the below screen shot.

[Former Member]

Hi Vadim,

how can i use the conditional formatting?  what should i put in the red box and to green box?

sorry for giving some troubles i just have tight schedules to make this, i have 20 inputs schedules similar on this case.

Thanks and Best Regards,

Marlon Manalo

[Former Member]

Hi,

i can now create the epmcopyrange, but this function bypassing the EPMFormattingSheet, i only need to format the 3 columns.

Is there anyways i can this?

Thanks and Best Regards,

Marlon Manalo

[Former Member]

Hi Marlon,

It is system default behavior when you use EPMCOPYRANGE function this function will overwrite the formatting sheet. first formatting sheet will apply then function will work so at theend EPM function overwrite the formatting sheet.

[Former Member]

Hi,

in addition to my query above, how can i do the formatting using the epmcopyrange to row, my row is dynamic, and all the calculated member i am changing the color.

Regards,

Marlon Manalo

[Former Member]

Hi ,

If you observe copy range function applies only on the data region. You can use formatting sheet to format row (Label).

[former_member186338]

"how can i use the conditional formatting..." - conditional formatting is an Excel feature, please experiment with it without EPM to understand how it works!

Vadim

[Former Member]

HI

you can use if conditions in function also. In below screen shot i have done, if category is actual then formatting will apply from range G6:Y6 else G7:Y7. and using formatting sheet rows (Label) are formatted.

Hope this will clear you.

[Former Member]

Hi,

Not just the Label that i only need to change color, but the data field as well to all calculated member.

Thanks and Best Regards,

Marlon Manalo

[Former Member]

Hi,

Thanks for info, now my problem is my ROW data field, which i need to change the color of all calculated member and lock the cell, i tried using the epmformattingsheet but still my epmcopyrange is bypassing the my epmformattingsheet, i can only edit the LABEL but not in data region.

Thanks and Best Regards,

Marlon Manalo

[former_member186338]

I do not recommend using epmcopyrange - not compatible with epmformattingsheet.

For locking - use static column axis and lock/unlock required columns directly.

For column formatting - use conditional formatting.

Vadim

[Former Member]

Hi Vadim,

i have a dynamic row, which i need to lock and change the color of all calculated member, to all base member are unlocked and different color as well,

for my column, i have static column which i need to show the selected parent time (e.g. 2015 total + Base member) and (2016 total + Base member) and 2017 2018 2019 (those 3 years are dummy member i created those as base member, so that we can be able to input some data those member) then for 2020 2021 2022 (are same member as 2017 2018 2019) these years are calculated using excel formula. they cannot input data to those member but can be save data to those years.

my slicer is my category dimension, my category have 3 types namely as:

ACTUAL= if selected, all my data region will be locked and will same color.

BUDGET V1 etc.. = if selected the data region for 2015 base member, 2016 base member, 2017 2018 and 2019 will have same color and can be inputed for 2020 2021 2022 will be locked.

OUTLOOK = i will not explain this because i dont have much problem here.

i tried to used the EPMCOPYRANGE, which it can cater my problem for 2017 2018 and 2019, but the data region for all calculated member in ROW, have the same format which is NOT correct.

i tried the CONDITIONAL FORMATTING, but i don't know what SHOULD i put in my EMPFORMATTINGSHEET, see image below:

for the RED box from image what and how i need to apply.

applying the conditional formatting directly to cell of column is not working it will always follow what ever i put in my EPMFORMATTINGSHEET, even i disabled all the formatting was still not working my conditional formatting.

Thank you very much for all the support you've guys giving to me.

by the way i am using SAP BPC 10 NW.

Thanks in advance

Regards,

Marlon Manalo

[former_member186338]

Conditional formatting can be applied to the cell in the formatting sheet (in green box). Condition format formula has to be properly created based on the absolute and relative references (relative - use relative distance in the formatting sheet!).

Please, spend some time understanding how conditional formatting works in general.

Also sometimes it's better to use data validation instead of cell locking (with the same result).

Vadim

[Former Member]

Hi Vamin,

yes i understand that i need to do the condition formatting in green box, but my problem is what should i put in the red box, i cannot assign specific id or member of my time dimension, because it will change adjust depends on selected time.

Thanks and Best Regards,

Marlon Manalo

[former_member186338]

"what should i put in the red box" - nothing specific to some member (time or other)!

You have to use excel formula in conditional format formula condition to check for some member ID's etc...

Sorry, I can't do all the work instead of you . Start with something very simple (only few columns etc...)

Vadim