View to show first item in subset dynamically

[kenship]

Hi,

I have a dimension that is called "Stage" and have simple elements 1,2,3,4,5,6.

In our planning process we roll from Stage 1 to 2 then on. I have control cube to define current stage.

Now in data cube I have a cube view showing current stage as a context dimension.

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.

Thanks.

Kenneth

[macsir]

Can't get what you are trying to achieve. If you show the MDX and some views, it would be clearer.

[Alan Kirk]

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.

[kenship]

Thanks for your suggestion.

Since we have rollover process to go from one stage to another, your suggestion could work!

It's a shame that it couldn't be done in a straight way.

Kenneth

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.

[kenship]

Thanks for your reply.

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.

I would be following Alan's advice to refresh the view every time there's a change of stage.

Kenneth

Can't get what you are trying to achieve. If you show the MDX and some views, it would be clearer.

[gtonkin]

...
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.

[Alan Kirk]

...
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.

The thought of something along those lines did drift across my mind, and it's not a bad idea, BUT... the only reason I didn't bring it up is that there is a small risk - probably very small, but Murphy's Law and all that - that someone may open the view while (in this case) the dummy 99 element is in place. Cue wailing, rending of garments and gnashing of teeth as their inputs go into the digital ether.

It's a solution worth considering, but personally I'd be reluctant to go there.

[gtonkin]

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]

Hi,

This is a general problem that I wish could be addressed somehow, more control how subsets behave would be great.

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!

Maren

[Alan Kirk]

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.

I'm not sure why, but I rend to regard any process which involves a server restart as being juuuuuuust a tad to "high maintenance" for my tastebuds.

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!

Taking that one step further, the subset could be made to consist of only the current element if the view is exclusively for input. If it's not then the users will all need to be trained to use the "All" button to see the other elements, but if it is I would imagine that it would be the only one being entered to and they therefore wouldn't need the others in the view's drop-down anyway.

[PavoGa]

Would like to see the MDX you are using to build the subset.

[ndivine]

If your dimension is in the context (or Title) area, you can use ViewTitleElementSet, no changes to the subset needed.
https://www.ibm.com/docs/en/planning-an ... elementset
ViewTitleElementSet(CubeName, ViewName, DimName, Index);

A curious note to this function - if a subset is also defined for that dimension, then you need to use the subset index for the Index argument. If no subset is defined, then you use the dimension index.

[kenship]

This is my MDX expression in TI.

Code: Select all

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])
					 			)
				}
			)
		}';

So in the above, if the current stage according the control cube is 2, then the subset will show like this:

2
1
3
4
5
6

with 2 being the first item.

Would like to see the MDX you are using to build the subset.