TM1 Forum

Hi All,

I'm fairly new to TM1, so apologise if this is standard stuff!

I have a cube that contains one row dimension, several title dimensions and one measures column dimension. The measures dimension contains multiple values, some of which are strings, others are numbers. I would like to add an "All" element to each of the titles dimensions so that users can filter the data or show all the data, depending on their requirements. Whatever their selection, the values in all of the measures must be shown.

If I add a consolidation element to the dimension and place all other elements as children of that, when the cube is refreshed, whilst I do get the All option, the string values in the measures dimension are not displayed with only the numeric values being shown.

I have tried adding ALL as an additional element at the same level as the existing ones in a dimension. Whilst this did work (I got the "All" option AND the measures data), I will have to manually create cells for every combination of actual value and ALL value for each element in the main row dimension to ensure that, no matter what the user selects, the data is displayed. Given that I will possibly have 13 titles dimensions, that will mean hundreds of lines of code to create cells for all possible combinations, that wouldn't be maintainable, especially if I need to add or remove dimensions.

What is the best way to include an "All" option for a dimension AND ensure that all the string measures are displayed at all times no matter what is selected?

Thanks in advance

Andy

When you created your "ALL" elements all you actually did was create a consolidation that adds all the children up so that TM1 can display the total in the cube. it doesn't mean "show me all the elements", it means "show me the TOTAL of all the elements". That is a huge difference. Since string values do not consolidate you can't ask for your ALL element and expect to see all the children string values. What you need is to create a subset in the appropriate dimensions and use those subsets in your rows and/or columns. That's the only way you are going to see the comments along with the data.

It would be really helpful to post a screenshot of the VIEW you are talking about and also the complete dimensionality of the cube to which the view belongs.

As Tomok said strings don't be definition consolidate, values do. Therefore "All whatever" in a title dimension will return the TOTAL of the whatever dimension in all numeric measure cells of the grid but the string cells will be data enterable (unless defined by a rule) since a string can be entered regardless of the leaf/consolidated status of other dimensions. Also it would be really helpful to know what exactly is being stored in the string measures, particularly is the same granularity/dimensionality needed for the string data vs. the numeric data? (I would suspect not). If a single piece of string data should apply for all members of a dimension (in other words it isn't relevant for storage to that dimension) then you are much better off holding this in an appropriately dimensioned cube and bringing in to the other cube with a rule which would apply to a large area and be easy to maintain.

Thanks Tomok - That was why I tried creating an element at the same level as the actual values in the dimension and copied the data into that during the process - so that each "record" appeared twice, once as normal and once using ALL in place of a value.

Tomok/Lotsaram.. I can't post a screenshot as the actual data is sensitive. However, imagine a list of invoices, where the row dimension is the invoice number and the measures dimension holds the other data, eg date, office, salesperson, addressee and address, net, VAT and gross etc. My aim here is to have a title dimension for, say, the office. A user may want to see all offices or just one but in either case they need to see the full measures data. Similar to how an Excel spreadsheet would show drop-downs for Data/Filter/Autofilter, if you like. I appreciate that, normally, TM1 would show you the total net, VAT and gross for your selection, however, the requirement I have is to show a filterable list (for want of a better expression) that still includes the date, office, salesperson etc.

Thanks for your help

Andy

ATD wrote:I can't post a screenshot as the actual data is sensitive. However, imagine a list of invoices, where the row dimension is the invoice number and the measures dimension holds the other data, eg date, office, salesperson, addressee and address, net, VAT and gross etc. ...

Ever heard of the "blur" feature in Snagit?

You are talking then about what is actually a flat invoice table as opposed to a cube. All the string measures are really just attributes of invoice. For good slice and dice reporting yes you can turn this into a multi-dimensional cube by making department, purchaser, vendor, etc. into dimensions. In that case I would have a separate 2D flat cube with just the invoice number and string measures and store the strings there and have an open ended rule to retrieve the strings that relate to specific invoices in the reporting cube - you don't even need feeders for this as there is no requirement to feed the string cells (just restrict zero suppression to rows only so the measures display.)

Also to do what you want could be mimicked using a 2D cube if you used an active form as the GUI as you could filter the attributes in the "titles" and parse the selections to an MDX statement for the rowset which could filter out the invoices returned.

Apologies for the delay - I've been trying the various methods to get something to work. Nothing worked, so I've agreed with the users that they are ok to slice the cube to a spreadsheet and use Excel filtering to get what they need.

I can't see anything that allows me to flag this post as "Closed" but I'll consider it closed anyway - thanks for all the suggestions

Andy