Formatting issue

[Former Member]

Hi Experts,

I am having a bit of a problem with the formatting sheet.

I am using the formatting sheet to put calculations on the input sheet. However i am having an issue on the last line.

So the first part is when the entity is a base level and version = plan and week no does not <> 0 then put the calculation this part works fine.

as shown in format 1 attachment. and comes through perfectly on as shown in format 2 picture.

The issue is when the entity = central income and version = plan and time period <> 0 it is putting in the formula from the example above even though in the formatting sheet in is below the calculation shown in format 3 picture. I need to be able to put a number in here rather than the formula that is being used. Currently is it set to 10,000 and all in the formatting sheet as shown in format 1 picture.

Any help would be great.

Many thanks in advance

JJ

[Former Member]

Hi John,

Which version of EPM add in are you using?

Andy

[Former Member]

Hi Andy,

I am using BPC 10.1 on HANA SP20

Regards

JJ

[former_member186338]

Hi John,

Can you isolate the issue and provide some simple test case with inline screenshot? It's very hard to understand your requirements...

"I am using the formatting sheet to put calculations on the input sheet." - I don't like this idea! It's better to use local member or dimension member formula!

Vadim

[Former Member]

Hi Vadim,

Many thanks for getting back to me. As you can see below Q55 is store 92 and is bring through the correct format and calculation from the formatting sheet however Q75 is bringing through the correct format but is also bring through the formula as per Q55 where is should be bring through a 0 value where I am able to input through.

Formatting Sheet

JJ

[former_member186338]

Sorry John,

You have no time to isolate the issue to the single cell, clear formula etc... I have no time to dig into your formatting sheet.

Vadim

[Former Member]

Hi Vadim,

Maybe i was not clear enough on my previous response.

As you can see from the screen shots below. This is coming through as expected

Formatting sheet for the above

This part works fine and is coming through as expected. However this formula in the formatting sheet is being applied to the below when it should be giving me a value to put in there to submit / save.

Format for the above

I hope this is a bit more clear sorry for any confusion.

regards

JJ

[former_member186338]

I am sorry, but I am unable to interpret this:

What do you mean by "However this formula in the formatting sheet is being applied to the below when it should be giving me a value to put in there to submit / save."?

Vadim

[Former Member]

Hi Vadim,

Many thanks for getting back to me.

What I mean by that is the correct format is being applied as per the formatting sheet but is should be pulling back what is in the data base so i can change it if required. However it is pulling through the formula as per the yellow cell.

Regards

JJ

[former_member186338]

If you don't want to replace the content of data cell with the formula defined in the format sheet - then DON'T USE THIS CONTENT OVERRIDE FORMAT FOR THIS CELL!

Double click on the "Use" cell and instead of "All" select what do you want, except "Content".

Hope, I understood you correctly!

Vadim

[Former Member]

Hi Vadim,

Many thanks for assisting me.

I have tried your suggestion and the formula is still pulling through. I have tried using everything apart from Content with no joy.

Regards

JJ

[former_member186338]

If "I have tried your suggestion and the formula is still pulling through" then you apply this condition to the incorrect cell or some other condition overwrites the reports cell. In my test the solution works correctly.

Please, test everything on a VERY SIMPLE report with only few conditions! And then it will work!

Vadim