TM1 Forum

Hi,

I need to set up an Excel Report that (Simplified) needs to look like something like this:

ITEM VALUE-1 VALUE-2
Item001 20 10000
Item002 15 20000
Total Brand-01 35 30000

There are a number of Items linked to each Brand. The report should show the Items that contain values within the Brand, as well as the Total for that Brand. This can differ every month and Items with no values must not be shown. New Items and or Brands can be added in any month and needs to be shown as well.

What's the best way to set up a report like this ? Keeping in mind that I cannot use Macros. I tried figuring out a way to incorporate the DNEXT() function, but it's still too many lines and Items with no values are also being shown.

It works when you try using the In-Spreadsheet browser, but that unfortunately doesn't allow for much formatting. (Each time you recalc, you loose the formatting).

Anybody know of a sort of best-practice way of doing this ?

Thanks,
Michael

I think what you are really after is an active form report. This lets you do all the zero suppression and filtering that you can do in the cube viewer but with nice Excel formatting.

You can actually do some reasonable formatting with the ISB too (but it is mind-numbingly tedious to set up), right-click the ISB anchor cell and select "Styles" and have a play around.

Hi

You do not indicate which version of TM1 you're using, but I too would vote for the Active forms option.

By the way, in the TM1RPTROW function that TM1 creates when you ask for an Active form, you can insert MDX statements (making it quite powerful). But only the rows are "dynamic".

Wim

If you're not on 9.4 with active forms you can try using conditional formatting. Hide columns A&B and use them to retrieve your subset name and the total number of elements it contains. Use subnum formulas to retrieve the next element in the subset that is the driver for your report. Set your conditional formatting to use the font color that matches your background color once the number of elements retrieved has exceeded the number of elements in the subset. An example of a use for this technique would be a report of purchase order activity by vendor where the PO dimension has Vendors as parents and dynamic subsets driven by a Text attribute. We have a websheet formatted for over 1000 lines and it views nicely in the browser.

Wes

Thanks for the replies!

We're using TM1 9.1 SP3. So unfortunately no Active forms.

I'll play around with the proposals and see if I can figure it out.