Skip to Content
0

Formatting for Row and Column Local Member intersection

Nov 07, 2016 at 04:05 PM

298

avatar image

Hi BPC Friends,

I am trying to implement formatting for one our reports in v 10.0 as below ,tried several ways but not successful .

The issue is formatting H48 and I48 different to row F and G 48 . Our expected result is to hide H48 with white format and I48 as a text without percent . Columns H and I are local members and row 48 is a local member.

Right now I have given formatting on local members selecting them individually in dimension member/property formatting

May I get some suggestions for achieving this

Thanks,

Krishna

fomatting.png (23.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Vadim Kalinin Nov 07, 2016 at 04:41 PM
1

Sorry, but not clear!

You can use formula in the local member for row 48 to check the column and for H column use "" and for I column - some required text...

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Vadim,

I am trying to use formula to achieve this but due to circular referencing I couldn't make it work. Are there any workarounds? My requirement is that the cell J20 shows the difference of G20-I20 and other cells who doesn't qualify in if condition should get a divided value. Row 20 , Column I,J and K are local members with priority to rows.

Thanks,

Krishna

lm2.jpg (92.6 kB)
0

Sorry, but I am unable to understand the logic of your formula. Bad explanation.

You can always check the current column with if(column()=5,...

0

Hi Vadim,

I have taken a better explained screenshot now, the requirement is cell I20 should show subtracted value of Scenario(G)and Budget (H) and all other cells in this row 20 should show a divided values as in the formula. This appears to be local member limitation, I am trying several ways but the light still didnt stirke

lm3.jpg

lm3.jpg (160.8 kB)
0
Priyanka Jagtap Nov 08, 2016 at 06:28 AM
1

Hi Krishna,

Have you tried it this way?

formatting-sheet-lm.png

lm-output.png

Regards,

JP


Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi JP,

Thanks for the your suggestion and appreciate the effort you have taken to answer this.

Both your and Vadim's suggestions are helpful , I have to tweak the solutions to accomplish this.

I will get back on how I did this soon .

Thanks,

Krishna

0
Vadim Kalinin Nov 25, 2016 at 04:40 PM
0

Looks like you don't understand what I am writing!

Your local member formula has to be something like:

Column I is column number 9.

=IF(COLUMN()=9,Subtract,Divide)

Is it clear?

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi Vadim ,

Thanks for you advice, the challenge I have was with the cell referencing in the local member but not how to reference/flag required columns in the local member. I got the desired result when I removed excel cell referencing for the local member.

Thanks,

Krishna

0

Sorry, but not clear. Without formulas and samples I can't help you.

0

Thanks Vadim

I was trying to say my issue was solved after I removed tick on Excel cell references in the local member formula.

Regards

Krishna

0

Still not clear how Excel cell references will affect this particular case, but OK! Please close the discussion.

0