TM1 Forum

Hi,

When I use the 'SUBNM' function in an active form and double click on it, it takes me to the subset editor but there is no roll up functionality.
I want the users to change their own date range and roll up the results.

At the moment the date range is set up by a TI process which is working ok, the only problem is when multiple users are trying to run the same report at the same time. As far as I understand TM1 doesnt like running processes in parallel when they impact the same cube or metadata which is the case in this example.

How can set this up without running a TI process?

Any help is appreciated.

Cheers Niels

Roll Up is a cube viewer only function, it cannot be used in TM1web or Excel. You must create consolidations within the dimension structures if you wish to report multiple elements and that means they cannot be user defined.

You could use TI to create consolidations but it'd be messy and could cause problems. Or depending on how many items a user wants to consolidate, maybe give them rows / columns to select elements and get Excel to add the numbers up for them.

I remember logging this issue as an enhancement request about 5 years ago. I can't see a reason for the absence of the roll up functionality in the different UIs. Am I missing something (apart from the required functionality of course?).

Hi Steve,

Thank you for you reply.
At the moment I am using a TI process to generate a date range which the user defined in the active form.
A user can run a daily sales report based on two days they enter and the TI process creates a consolidation will all the dates in between the chosen dates. It works ok but I am worried that when multiple users run the same report at the same time it creates issues as they are changing meta data at the same time, something TM1 doesnt cope very well with apparently.

I have had a few application freezes already and I suspect it is because of that.

Does anyone know of any other way to overcome this issue?

Cheers Niels

Hi Niels

You could create a numeric attribute against your days dimension which stores the date index (Excel version of it, ideally loaded).
With this you can create a MDX query for your Active Form like

=”{FILTER({FILTER( {TM1SUBSETALL( [days] )}, [}Elementattributes_Days].([}Elementattributes_Days].[day_index])>”&X&”)},[}Elementattributes_Days].([}Elementattributes_Days].[day_index])<”&Y&”)}”

X and Y being references to cells which have the DATEVALUEs of the two dates the user enters in the Active Form.
This query you can use in the TM1RPTROW function (it should not create a lock either).

Cheers

Hi Gregor,

Thanks heaps for that. I am not familiar with mdx so I have to look into this but it looks good!

So just to be clear: everywhere you use 'days' that is the name of the dimension right?
And I need to add the excel date as an attribute to all the values in the 'days' dimension so when the user types in a day (like 2011-01-01) Excel converts that to 40544 and run the report based on that day, or when they enter an interval based on all the days in between the dates they enter?!

I will give it a go and let you know how it goes!

Thanks again.

Cheers

Hi,
You are spot on.
Cheers

Hi Niels,
If you are purely interested in the 'Total' for a range of dates rather than showing the individuals dates the solution I suggested might not be right for you. Obviously in the described way the Total would be a SUM in Excel.
So the solution you have might even be the best one and you should confirm whether it actually is the cause for your freezes.

On the other hand, if the dates the users enter are not random but are based on an underlying logic, which I guess there is, you might be able to pre create all the necessary consolidations (overnight).

Cheers

Hi Gregor,

Thanks for your reply. Unfortunately I do need a total for all those days so your solutions might not work then.
Having a sum in Excel can work obviously but it does mean I need to have the formulas on a different worksheet.

The date interval can be any combination of two dates in the last 3 years so creating consolitations without TI is not really an option.

I am not 100% sure if running the TI process by more than one person is the cause of the application freeze but it does contribute I think.

Maybe I have to leave it as it is for now and focus on more critical processes.

Thanks again foe your help.

Cheers Niels