TM1 Active Form dynamically referencing multiple dim element

[dan.kelleher]

Hello,

I am trying to create an active form with one dimension (Orders) as my row, with a Projects dimension hidden above (for arguments sake I only have 1 measure):

Orders Actuals
External Opening Order Book (N) 150
External Order Book Adjustments (N) -10
External Order Intake(+ive) (N) 50
External Sales (N) -100
External Closing Order Book (N) 90

I need to be able to enter data into the External Order Book Adjustments for the total projects (a dummy cell), but the rest of the data will come from consolidations entered at project level (from a different form) (See Projects Dimension below):

Total Projects (C) <-- This is the level to view the other Order rows
Project 1 (N)
Project 2 (N)
Project 3 (N)
Project 4 (N)
Total Projects Dummy (N) <-- This is the level to enter the Order Book Adjustments

I have set up a DBRW function to reference either Total Projects Dummy, or Total Projects depending on the row in question (driven my an indirect() formula) but when both references are not the same (i.e. one is TP and one is TPD), the Active Form does not display anything.

Has anyone else had to overcome a similar issue?

If someone could point me in the direction of a good advanced Active Forms guide (more detailed than the developer's guide), I'd be very appreciative.

Thanks,

Dan

[lotsaram]

If you could post a screenshot it would help but I am reasonably sure that your problem is because "Total Projects Dummy (N)" is not a member of the row subset root.

When the "report view" (similar to stargate view but not quite) is built server side it is constructed only for the current title elements, the row subset elements and all column elements (as per "!" reference in VIEW formula.) This is different from the VIEW formula which builds a view for all row and column elements. For a report view if you try and reference an element in the DBRW for a row dimension member that is not in the row subset no data will be returned.

You can either include the element in the row subset OR you can point the DBRW at a VIEW formula for the cube reference that has "!" reference for the projects dimension rather than the default which points the DBRW at the TM1RPTVIEW formula.

[dan.kelleher]

Thanks for your help but I don't think I've explained myself properly.

External Opening Order Book Read from cube (Total Programmes - Consolidation)
External Order Book Adjustments Write to cube (Total Programmes Child - Node)
External Order Intake(+ive) Read from cube (Total Programmes - Consolidation)
External Sales Read from cube (Total Programmes - Consolidation)
External Closing Order Book Read from cube (Total Programmes - Consolidation)

The DBRW formulae to derive/ write to the measure figures are as follows:

=DBRW(Dimension1, Dimension2, ... , External Opening Order Book, Total Programmes)
=DBRW(Dimension1, Dimension2, ... , External Order Book Adjustments, Total Programmes Child)
=DBRW(Dimension1, Dimension2, ... , External Order Intake(+ive), Total Programmes)
=DBRW(Dimension1, Dimension2, ... , External Sales, Total Programmes)
=DBRW(Dimension1, Dimension2, ... , External Closing Order Book, Total Programmes)

As the DBRW formulae bursts down from the top row during a refresh, I have used Indirect() to change the reference to the node depending on the row

I have uploaded an example of my file as well.

Thanks,

Dan

[lotsaram]

This could be pretty elementary. Your "External Order Book Adjustments" CoA is not referencing "Total Programmes Child" it is referencing "Total Programmes" as per all the other CoA nodes. what happens when you select the correct element in the SUBNM in the rngProjectInput range?

I am at a loss as to why you have used INDIRECT. Vlookup or lookup via DBRA would be a much more sound choice. As the reference to the INDIRECT is not fixed when your inter or intra order book CoA nodes get drilled down on this will presumably stuff up the INDIRECT references anyway!

From your DBRWs your cube is structured as:
CoA, Counter Party, Function, Version, Organisation, Period, Project, Tier, Year

From the TM1RPTView your cube is structured as:
Title dims: Counter Party, Function, Organisation, Period, Tier, Year
Row Dims: CoA
by deduction Column Dims should be Version and Project

I don't know the actual structure of your cube, but if you have played around with the DBRWs or TM1RPTVIEW formulas and stuffed up the dimension ordering or have missed a dimension then that would also be a possible explanation.

[dan.kelleher]

Thanks again for your help.

The report just shows zeros when total programmes child is selected in the rngProjectsInput range.

Is there a way to use the dbrw() function without having the row and column elements 'rebuild' themselves every time the report is refreshed (alt-f9)? ie. a static report in structure that is still able to read/write from and to the cube. I think this would solve the issue I am having.

Indirect is used to refer to the correct range. As the range is above the active form section, it remains static and doesn't get overwritten

Thanks,

Dan