Best Practice for Active Form with dynamic columns?

[Mark2007]

Hi there,

I'd like to built an active form in which not only the rows are dynamic, but also the columns.

Some time ago I built something like this by inserting a second active form in a hidden sheet that generated a list of rows that I transferred (via Index) in the column-headers in the main sheet. But this was a little bit circuitous. Does anyone have a better idea?

Regards
Mark

[Elessar]

Hi,

you can use TM1ELLIST function for this:

• Generate MDX for your columns dimension

• Use this MDX in TM1ELLIST

• Create a date range with this TM1ELLIST

• Use INDEX in columns

PS sorry, I've noticed later that you have TM1 10.2 installed. If so, TM1ELLIST is not your option as it was added in PA2

[LanceTylor]

Another way is to build a cube with the following dims:
Column # (sequence of numbers that match the excel columnns)
Column list (contains the different lists of column values - dynamic aspect)
Measures (the actual values you want in your active form report columns)
Clients (optional - control dimension for users to filter specific list for different users

In the active form:
- use DBR formula to pull the column value
--- column # matchs the excel column, use spaces if necessary. Needs to be precise
--- column list in the row
- use a validation list to select the column list to make it dynamic
- apply formatting to columns across the whole length of the report. You need to ensure that it looks like a blank column.

Hope that helps

Lance

[scrumthing]

you can use TM1ELLIST function for this:

Didn’t know that one. Thanks for sharing.