Active Form formatting calculated rules

Post Reply
User avatar
vovanenok
Posts: 62
Joined: Mon Jun 23, 2014 4:54 pm
OLAP Product: TM1
Version: 2.0.3
Excel Version: 2010
Location: Toronto, Canada
Contact:

Active Form formatting calculated rules

Post by vovanenok » Tue Nov 28, 2017 1:40 pm

Hi all
I have an active form perspectives report with TM1 Report Row in column B. Column A contains my formatting rule keys. I want to calculate a format key based on the values of active form cells. Let's say I get values in columns C and D using DBRW function. Then I calculate column E as the difference C-D. And I want to calculate my formatting column A as IF(E>0, 1, 0).
I see correct values in column A, but formatting rules are totally messed up.
If I use other active form cells containing DBRW formula only, everything works fine.

Is it an expected behavior? Is it because formatting and custom calculations are done in the same pass?

Thanks

User avatar
Steve Rowe
Site Admin
Posts: 1687
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Active Form formatting calculated rules

Post by Steve Rowe » Tue Nov 28, 2017 2:47 pm

Is it an expected behavior? Is it because formatting and custom calculations are done in the same pass?
Not convinced I have tried exactly this but you are probably right.

I would think that you are better off putting a conditional format rule in the cell format itself at the top for formats that have to be calculated in "real time".

If you can't do conditional cell formats then you could try changing the report to use DBR instead of DBRW by changing the first row of the report but, 1. Not sure if the form will reset itself to DBRWs and 2. Expect performance to suffer, depending on volumes it could suffer a lot.

User avatar
gtonkin
MVP
Posts: 530
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Active Form formatting calculated rules

Post by gtonkin » Tue Nov 28, 2017 6:24 pm

I have previously added attributes to the dimension used in column B and read these in to column A to drive the formatting-depends on your needs but may work. You could obviously create composite values and format accordingly.

User avatar
vovanenok
Posts: 62
Joined: Mon Jun 23, 2014 4:54 pm
OLAP Product: TM1
Version: 2.0.3
Excel Version: 2010
Location: Toronto, Canada
Contact:

Re: Active Form formatting calculated rules

Post by vovanenok » Wed Nov 29, 2017 4:28 pm

So after chatting with IBM support, the only found solution is to use Excel Conditional formatting. I tested it works as expected

Post Reply