Recurring Calculations per RPTROW Value

[AlvinPresley]

Hello there!

I'm new to TM1 and I have been working on this particular report on TM1 Perspectives where in I have to display calculations based on the DBRW below each RPTRow output. For Example,

Bucket Item Jan Feb Mar April May June July...Dec
(First Rptrow Value) 1. Apple 10 20 30 20 5 40 12 ...50
(Static Calculation) Rate: 10 10 20 30 20 15 30 ...5
(Static Calculation) Total Cost: 100 200 500 600 100 600 360 250

(Second Rptrow Value) 2. Bananas 5 6 7 8 45 20 40 ....50
(Static Calculation) Rate: 10 10 20 30 20 15 30 ...5
(Static Calculation) Total Cost: 50 60 140 240 900 300 1200 250

(Third RptRow Value) 3. Pineapple
(Static Calculation) Rate:
(Static Calculation) Total Cost:

and So on


Here the Rptrow gives the fruit names and the Rate and total cost I have to put for each Rptrow value.Apart from that i have constraints as well. I cannot alter the Cube or the dimensions used in the cube (Business purposes). I cannot add a process. I can create a new dimension where in I can add attributes and the business requirement is similar the Example I have shown.

Kindly Help!

[AlvinPresley]

Also I cannot add VBA code to it because it doesnt work on TM1 web.Tm1 web is the place where i have to disply the report. Thanks again!

[David Usherwood]

Don't think this is possible, since a TM1 active form 'owns' the report area and grows/shrinks it as users change selections. You can add custom columns though.

[Steve Rowe]

As David says I don't think an active form will work for you, I can think of a workaround but it will be clumsy (and I don't know if it will work in the web..), pretty strange and annoying to be given requirements to deliver something but not be allowed to use any of the tools that would help you deliver it.

Head Gardner : I'd like you to dig a hole but you can't use the spade in the shed, instead get a fork from the kitchen drawer.

My workaround relies on using the mod function to decide which type of row you are on and then flipping the behaviour depending on the result.

Something like this

On the row header where the element name would be put

=If(Mod(row(),3)=0 , dimnm( dimname , Mod(row(),3) ) , If (Mod(row(),3)=1 , "Rate" , "Total Cost"))

On the data cells
=If(Mod(row(),3)=0 , dbrw ( references) , If (Mod(row(),3)=1 ,rate calc , cost calc))

Copy this down as far as the max number of rows you need in the report.

Totally untested but hopefully you get the idea...it's nasty and probably won't run so quick.

If Mod doesn't work in the web then you could just number the rows in 0,1,2 in a hidden column, that would probably calculate faster too.

HTH!

Cheers,

[AlvinPresley]

Thank you David and Steve. Steve,I'll get back to you on that after testing it.

Cheers!

[declanr]

A relatively easy way to do this (especially easy if you only have 1 rptrow formula in place) is to just set you subset its based on to bring each element back 3 times.

Then set the active form to only have 1 actual column that bring back the value from your cube; hide that column - in your case it would most likely be a consolidation of the 12 months.)

In the next 12 columns have a formula that says if the rptrow element is not the same as the element above it - return the dbrw for the month. Otherwise if it IS the same as the 1 that is 2 places above it do the calc you want in the third row otherwise do what you want in the middle row.

Fairly simple approach, that definitly works in TM1 web and is easy for subsequent developers to understand should they pick up your work from you at a later date.

[AlvinPresley]

Hey Thank you very much declanr!! That trick definitely worked for me!

Cheers!