TM1 Forum

Hi Guys,

Anyone had any experience with making a either a row or column dimension auto expand including all of the latest elements per the consolidation.. For example in our Customer dimension we have customer groups and one such group is other. In the P&L by customer report we list the customer groups down the left hand side and like to show the Other Customers consolidation expanding with all of the customers within. I currently have a VBA macro that refreshes this list by using ELCOMPN and ELCOMP commands in a loop. However it would be nice if there was something native that I could use instead of needing run this VBA each time we need to refresh the sheets.

For example lets say in the customer dimension we have:

Customer Group A (C level)
Customer Group B (C Level)
Customer Group Other (C level)
- Other 1 (N level)
- Other 2 (N Level)
- Other 3 (N level)

I have simply create this row structure in my Excel spreadsheet either via directly or via Active Forms. However if we add more customers to this group, the report will not automatically be reflecting this.. Therefore I need a way of recreating this in a way that the new customers will also be listed under the consolidation. I am just wondering if there is anyway of doing this in TM1 Web? Or do I need to keep my VBA script and continuing running this each time we use these sheets.

Thanks,
Damien

If you are using active forms then this should be able to automatically update the rowset without any need for VBA - just use the MDX argument of TM1RptRow or refer to a dynamic subset in the subset argument.
This will also work in TM1 web.

(You would still be stuck for automatically expanding or contracting columns though with your VBA loop as active forms only have dynamic rows, but could you just redesign the report so that only rows need to be dynamic?)

Yes, thanks for that suggestion, its a shame the TM1RptRow functionality does not work for columns as well. I will see what I can do to get around that.

Thanks,
Damien