TM1 Forum

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

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

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

Elessar wrote: ↑Thu Jun 04, 2020 10:32 am you can use TM1ELLIST function for this:

Didn’t know that one. Thanks for sharing.