TM1 Forum

Hello,

I have a dimension called Region with a hierarchy city, country, continent. Also I have an text attribute called "population". Every city can have an entry from one of the following: "low", "medium" or "high".

Is it possible to group by this attribute in TM1-Perspective and let TM1 sum up some figures depending on the text attribute?
So e.g. I have

Medium 1000
Low 450.000
High 900.000

Thanks a lot for your help!

Toto

You could create a dynamic subset for each population category listing the N level cities belonging to each attribute category. Then in a DBRW reference the subset name and TM1 will return the aggregate of the subset members (this is a hidden or undocumented feature, it has been discussed a bit on this forum in the past).

Or you could use TI to create hierarchies based on the attribute values.

lotsaram wrote:Then in a DBRW reference the subset name

I would use the SUBNM forumla in the report. The DBRW would then referance the result.

jim wood wrote:I would use the SUBNM forumla in the report. The DBRW would then referance the result.

What do you mean? A SUBNM pulls out element(s) in the subset, but to use a subset as a user defined consolidation what you need to reference in the DBRW is the subset name itself, not the names of the elements. Am I missing something?

Indeed so say if you have a list of elements on your vertical in an excel sheet you can you use subnm to pull the first, second etc...

This means that you can build a flexiable report that expands and contracts depending on the number of members in the subset.

You guys are talking about two different things. Lotsa, you are talking about a single DBRW pulling a consolidated value from the cube with the consolidation being the subset itself. Jim is talking about a dynamic report where the rows (or columns) are dynamic based on the number of elements in the subset. Jim has to add an Excel SUM formula at the end to get the total, not to mention dealing with all the empty rows or columns you would have to build into the structure to make it work "dynamically".

tomok wrote:not to mention dealing with all the empty rows or columns you would have to build into the structure to make it work "dynamically".

Not exactly difficult stuff. A simple IF statement within the DBRW cell takes care of it. I've done this many time.

tomok wrote:add an Excel SUM formula at the end to get the total

I was thinking of putting it at the top (rather than the end) to stop it moving around but hey that must just be me.

jim wood wrote:Not exactly difficult stuff. A simple IF statement within the DBRW cell takes care of it. I've done this many time.

No, not difficult, just a pain. I also forgot to mention you have to decide, in advance, how big the range should be and copy your DBRWs out to that range and add conditional formatting to make them invisible, if empty. Keep in mind I'm talking sans VBA. I generally don't use it because it means I have to have two versions, one for Excel and one for TM1Web.

I agree about the range. As for formatting again that can be taken care of using IF statements rather than VBA. You just need to display null when appropriate. That's the way I've done it in the past but to be fair I haven't published them via TM1 web so I'm not sure if that would work.

jim wood wrote:I agree about the range. As for formatting again that can be taken care of using IF statements rather than VBA. You just need to display null when appropriate. That's the way I've done it in the past but to be fair I haven't published them via TM1 web so I'm not sure if that would work.

When I mentioned VBA I was talking about using it to dynamically copy the DBRW formulas down and/or across. As long as you don't care about unneccesary DBRWs then it's easy to just use conditional formatting to hide unwanted formulas. This works fine in TM1Web.

Ah I get what you mean now and yes that would keep the report optimised rather than keeping a stack of unused forumlas. I guess it depends on the size of the report and how much you expect it to expand / contract.