Select an element in an MDX subset whilst keeping others

[gtonkin]

I am trying to create a default subset on my month dimension that always has the current month selected so that the views using the default subset is based on this.
I have a static subset called _S-Template that contains the current month - updated either via monthly TI processes or by a Custodian.
I have an All N subset generated via MDX to give me Jan through Dec.
What I need to try and mimic is the user opening the All N subset from the Title elements for the Month dimension, clicking on the current Month e.g. Sep then clicking OK

The only thing I can think of at this stage is:

Code: Select all

UNION(
TM1SubsetToSet([RBB-Month],"_S-Template"),
TM1SubsetToSet([RBB-Month],"_S-All N"))

I specifically want to keep all N level elements, as users will be using this subset in views and websheets accessed through TM1 Web/Contributor/iPad App.
By having all elements available, users can use the dropdowns to select other months. Using only _S-Template in views and templates gives them one element. To select others, users need to try and fight their way through the TM1 Web subset interface and cannot select other months through the iPad App.

Any ideas on how to take the All N subset and select the relevant items based on the other subset so that the views/websheets are updated accordingly?
Thanks in advance.

[pandinus]

Why not have TI update your static subset so that it will contain the following:

• Current month
• Jan
• Feb
• Mar
• ...

[gtonkin]

Why not have TI update your static subset so that it will contain the following:

• Current month
• Jan
• Feb
• Mar
• ...

Thanks Pandinus-I could do that - same result as my MDX but what I would really like to emulate is having the months in order but the current month selected in the view.

Maybe another way of explaining what I want is by referring to the .vue file
Control code 374 contains the Title Dimensions/Subsets - Control code 373 contains the selected Title elements - in my case right now I have an entry 1,Sep for my RBB-Month dimension that is linked to my dynamic subset where element 1 is Sep. I would like to use a standard subset Jan,Feb,...,Dec and be able to update so that the view somehow uses 9,Sep.
I do not want to construct view as this will only address views, not web sheets etc. I would like to set the current month as selected/active/chosen.

[declanr]

Ok so surprise surprise the following is very poorly documented.

The documentation mentions that viewtitleelementset works in combination with a dimension index.

However in the versions I have tested at least (so 10.2ishes) and some older ones that I vaguely remember doing this with... if you have a subset set on a title dimension you can use viewtitleelementset but give it the subset index instead of the dimension index.

If you want purely MDX then I dont know of anyway to have MDX default to showing a specific element within the subset first as it has no way of knowing whether it will be a title, row or column.

[gtonkin]

If you want purely MDX then I dont know of anyway to have MDX default to showing a specific element within the subset first as it has no way of knowing whether it will be a title, row or column.

Thanks Declan-that is unfortunately the premise from which I started - was hoping for some magic solution or crafty workaround.

[tomok]

Is "Current Month" an actual element in your time dimension or is it an alias for the month that is currently defined as current. What I like to do is always have the month that is currently being worked on defined as current by having an alias on the time dimension and only populating that for the month in question. I have a control cube where I populate the element name of the month that is considered current and then rule that over to the alias on the Time dimension.

Code: Select all

['AltName'] = S:IF(!Time @= DB('Control', 'Current Month', 'string'), 'Current', STET);

Now, on all my active form reports I can make them point to "Current" with the SUBNM formula like this (I have a subset called "Months" with all the months of the current year in it):

Code: Select all

=SUBNM(Server&":Time","Months","Current")

All the reports will open to show whatever is defined as the "Current" month in my control cube and if you double-click on the cell it will show all the months from which to select. The caveat here is that it won't do what you want for views because you need to have an actual subset with "Current" then all the other months. What you can do is create a subset called "Report Months" and have it defined by MDX like this:

Code: Select all

{[Time].[Current]}+{[Time].[Months]}

Make sure you have the Altname alias selected in your subset. This will create a subset with the "Current" element first and then add all the elements from the 'Months" subset to it.

[gtonkin]

Thanks for the detailed reply Tom-unfortunately, Pandinus introduced "Current Month" - for all intents and puposes, I have a dimension with elements Jan, Feb,...,Dec
Instead of a control cube, I have been using a subset _S-Template as my web sheets would reference this with

Code: Select all

=SUBNM("RBB:RBB-Month","Default",1)

but have updated to use two subsets to achieve what I require:

Code: Select all

=SUBNM("RBB:RBB-Month","Default",$A$5) where $A$5 has =SUBNM("RBB:RBB-Month","_S-Template",1)

I could have nested the SUBNM's but chose not to.

Used the alias trick before but found the easiest way to maintain it was to delete the alias, recreate and update in TI - each month you would then have the relevant principal linked to current month. Thanks again for the options - I am sure your feedback will also be useful to others who find this thread.

[paulsimon]

Hi

As far as I know there is no way to select a default element in an MDX subset. I am afraid that you will need to use a slightly different approach in Views and Sheets.

In a View you can set the title element on a subset, as I am sure you already know.

In a slice I use the following

=SUBNM( "MyServer:TimeDim", "MyMDXSubset",rInitTime,"MyAlias")

I retrieve the value for rInitTime from the usual sort of Info cube that we all have in one variant or another. This has an element giving the current month

That goes into a range that is named rInitTime

This means that when the sheet first opens, the SUBNM will display the current time element, but the user can still use the SUBNM to change to a different Time element based on the given subset.

Does that help?

Regards

Paul Simon

[gtonkin]

Thanks for the feedback Paul-using both of these approaches but was hoping to find one that could be used universally. The main goal is still to be able to have all required elements available in the subset but the relevant selected-It definitely looks like a mixed approach per target environment otherwise my original MDX where the current/selected element is always the first in the subset i.e. index=1