TM1 Forum

kenship wrote: ↑Thu Jul 29, 2021 7:54 pm
To make slicing easier I edited the MDX statement to show the current stage as first item then other ones excluding the current stage. For example, if current stage is 2 then the the subset will look like this:

2
1
3
4
5
6

However, I found out the view will save the selected element. So if my current stage becomes 3, even though 3 will show as the first item the cube view will still display data based on the stage selection when I save it, which is 2.

So my question is: is there any way to have a cube view to show the top listed element in a subset dynamically.

Alan Kirk wrote: ↑Thu Jul 29, 2021 9:37 pm

kenship wrote: ↑Thu Jul 29, 2021 7:54 pm
To make slicing easier I edited the MDX statement to show the current stage as first item then other ones excluding the current stage. For example, if current stage is 2 then the the subset will look like this:

2
1
3
4
5
6

However, I found out the view will save the selected element. So if my current stage becomes 3, even though 3 will show as the first item the cube view will still display data based on the stage selection when I save it, which is 2.

So my question is: is there any way to have a cube view to show the top listed element in a subset dynamically.

It's one of the more annoying limitations of a cube view. In a websheet / activeform etc you can use the SubNm() function, which at least allows the title dimension selection to be made by either name or index. If you use the Index, the selected element will update when the subset does.

A cube view doesn't work that way. Even if you use a named, saved subset in the title area, and later change that subset, then the original element will still be visible. (Assuming that it's still in the subset. If it's not the cube view will just use the first element again.)

The only way I can think of around this is to delete the view and recreate it with the modified subset, which will make the new top element the default one. There are TI commands which can rebuild both subsets and views so I'd probably be inclined to script it out rather than do it manually.

macsir wrote: ↑Thu Jul 29, 2021 9:05 pm Can't get what you are trying to achieve. If you show the MDX and some views, it would be clearer.

kenship wrote: ↑Thu Jul 29, 2021 9:45 pm ...
Unfortunately it has nothing to do with the MDX statement but everything to do with how a cube view stores dimension elements and what's being selected in the context dimension...

gtonkin wrote: ↑Fri Jul 30, 2021 4:38 am

kenship wrote: ↑Thu Jul 29, 2021 9:45 pm ...
Unfortunately it has nothing to do with the MDX statement but everything to do with how a cube view stores dimension elements and what's being selected in the context dimension...

True but that does not mean you cannot achieve what you are trying to.

I have a cube view where the period dimension is in the context area and shows the last 10 days.
If I update the cube view for whatever reason, when opening the cube view again, the view defaults to the day I saved the view on.
However, after 11 days, the element being referred to in the .vue file is no longer in the subset and TM1 then defaults to the first element in the subset.
In my case, this is yesterday.

So, add a 99 to your stages dimension so that it appears in your MDX somewhere.
Save your view with 99 selected.
Remove 99 from your dimension and see if your view now returns the first element from your subset.

gtonkin wrote: ↑Fri Jul 30, 2021 5:08 am As an alternative that may leave Alan (and others) and , you could just edit the .vue file, replace the element being addressed with one that does not exist and save.
When you restart the server, the view will work as described above.

MarenC wrote: ↑Fri Jul 30, 2021 7:42 am One option, short of tinkering with the view itself, is to make the current stage something like 2 - current stage.

So a subset would look as follows:

1
3
2 - Current Stage
4
5

Low tech but it helps the users!

vMDX = '{
		TM1FilterByPattern  (TM1SubsetAll([Stage]),   [CTRL]. ([ctrl_param].[Stage],[Ctrl_m].[svalue])
				)
		}
		+
		EXCEPT({[Stage].[1],[Stage].[2],[Stage].[3],[Stage].[4],[Stage].[5],[Stage].[6]},
				{TM1FilterByPattern(TM1SubsetAll([Stage]),   [CTRL]. ([ctrl_param].[Stage],[cbm_ctrl_m].[svalue])
					 			)
				}
			)
		}';

PavoGa wrote: ↑Sat Jul 31, 2021 7:52 pm Would like to see the MDX you are using to build the subset.