TM1 Active Form Formatting

恭喜发财1

Preview: Active Form Formatting Issue
Hi All,

Could someone please help me with my issue. I'm relatively new to Active forms and what works in Excel design does not necessarily work in TM1 web, making me want to tear my hair out .

I have the format area in the active forms as:
[Begin Format Range]
Cell A2 0 - Dark Blue
Cell A3 1 - Blue
Cell A4 2 - Lighter Blue
Cell A5 3 - Lighter Lighter Blue
Cell A6 D - Lighter Lighter Lighter Blue
Cell A7 N - White
Cell A8 4 - Orange
[End Format Range]

In Column A - being the first row of the actual report I have written the following formula (listed at the very bottom):
Essentially what I'm saying in the IF statement if certain categories are GP or Total expense use cell A2 otherwise use a different cell such as A5.
This formula seems to work in excel when applied and the formula is copied down to the remainder of the report, however when published, none of it works.
1) are IF statements usable in TM1 Web
2) is my IF statement far too long and is rejected by TM1 web
3) How can I keep the formatting as alternative solution.

I really appreciate your help.


=IF(OR(B20="Gross Profit",B20="Total Operating Expenses",B20="Profit / Loss Before Allocation"),$A$3,IF(OR(B20="Operating Profit / Loss",B20="Profit / Loss Before Tax",B20="Profit / Loss after tax",B20="Profit / Loss after Tax and WHQ/RHQ Allocation"),$A$2,IF(OR(B20="Shipment Vol.",B20="Teu's",B20="zsp510030 Headcount: FTE Office Staff",B20="Zsp510020 Headcount: Full Time Equivalent (Overtime Hrs FTE)",B20="Zsp510010 Headcount: PC Allocation",B20="Zsp510000 Headcount: Full Time Equivalent (Std Hrs FTE)",B20="Zsp500500 Headcount: Casuals (In House)",B20="Zsp500000 Headcount: Physical (Incl. FTP & PPT)",B20="Zsp400100 Headcount: Industrial (FTE)",B20="Headcount Stats",B20="Shipment Weight",B20="Job / Shipment Count",B20="Total Depn And Amort"),$A$8,IF(OR(B20="Office Staff Expense",B20="Industrial Staff Expense",B20="Communications: General",B20="Communications: Depn/Interest/Rent",B20="EDP Expenses",B20="Hardware: Depn/Interest/Rent",B20="Building Utilities/Repairs/Taxes",B20="Land And Bldg: Depn/Interest/Rent",B20="Trucks: Operating Expenses",B20="Trucks: Depn/Interest/Rent",B20="Cars: Operating Expenses",B20="Cars: Depn/Interest/Rent",B20="Forklifts: Operating Expenses",B20="Forklifts: Depn/Interest/Rent",B20="Warehouse: Other Operating Expenses",B20="Warehouse: Depn/Interest/Rent",B20="Administration Expenses",B20="Travel Expenses",B20="Professional Fees",B20="Insurances And Taxes",B20="Expenses Of Office Facilities",B20="Office Facilities: Depn/Interest/Rent",B20="Advertising And Promotion",B20="Write Offs / Bad Debts",B20="Bank Fees And Interest",B20="Exchange Rate Gain / Loss",B20="Gain / Loss On Disposal Of Capital Assets",B20="Other Non",B20="Interest Earned / Paid"),$A$4,$A$6))))

kangbei

Why not just create an attribute on whatever your dimension is in column B, populate it with the appropriate 0,1, .. D, N, 4 value, then refer to that attribute in column A of the active form.

sf656

Hi Andy,

Would I just create a text attribute and apply a 1,2,3 against the relvant names in column B?
is the formula =ATTRS(dimension, element, attribute)?

Thanks

こ忘了隠藏ご

Hi Andy,

Never mind I worked it out using your magical tip. Thank you so much for helping me out. It was driving me crazy.

I had to think of the problem a little differently and you guided me in the right area.

Once again thanks for your help. Really appreciate it.

krc

Yes a text attribute (I tend to call mine ActiveFormat) populated with the values that match those in the Format Area, your 0, 1 etc.

Then in Column A in the Active Form you can either use:
Code: =DBRA( "<server>:<dimension>", <element, eg from your original post B20>, "ActiveFormat")
or
Code: =DBR( "<server>:}ElementAttributes_<dimension>", <element>, "ActiveFormat")
which should execute faster. (Note that this second option has to be a DBR rather than a DBRW.)