View to show first item in subset dynamically
-
- Regular Participant
- Posts: 194
- Joined: Thu May 30, 2013 1:41 pm
- OLAP Product: Cognos
- Version: Planning Analytics 2.0.7
- Excel Version: 2010
View to show first item in subset dynamically
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
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
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: View to show first item in subset dynamically
Can't get what you are trying to achieve. If you show the MDX and some views, it would be clearer.
- Alan Kirk
- Site Admin
- Posts: 6610
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: View to show first item in 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.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.
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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Regular Participant
- Posts: 194
- Joined: Thu May 30, 2013 1:41 pm
- OLAP Product: Cognos
- Version: Planning Analytics 2.0.7
- Excel Version: 2010
Re: View to show first item in subset dynamically
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
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
Alan Kirk wrote: ↑Thu Jul 29, 2021 9:37 pmIt'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.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.
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.
-
- Regular Participant
- Posts: 194
- Joined: Thu May 30, 2013 1:41 pm
- OLAP Product: Cognos
- Version: Planning Analytics 2.0.7
- Excel Version: 2010
Re: View to show first item in subset dynamically
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
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
- gtonkin
- MVP
- Posts: 1211
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: View to show first item in subset dynamically
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
- Site Admin
- Posts: 6610
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: View to show first item in subset dynamically
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.gtonkin wrote: ↑Fri Jul 30, 2021 4:38 amTrue 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.
It's a solution worth considering, but personally I'd be reluctant to go there.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- gtonkin
- MVP
- Posts: 1211
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: View to show first item in subset dynamically
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.
When you restart the server, the view will work as described above.
-
- Regular Participant
- Posts: 359
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: View to show first item in subset dynamically
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
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
- Site Admin
- Posts: 6610
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: View to show first item in subset dynamically
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.
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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- PavoGa
- MVP
- Posts: 618
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: View to show first item in subset dynamically
Would like to see the MDX you are using to build the subset.
Ty
Cleveland, TN
Cleveland, TN
-
- Posts: 20
- Joined: Wed Feb 23, 2011 6:43 pm
- OLAP Product: TM1
- Version: Latest
- Excel Version: 2013
Re: View to show first item in subset dynamically
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.
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.
-
- Regular Participant
- Posts: 194
- Joined: Thu May 30, 2013 1:41 pm
- OLAP Product: Cognos
- Version: Planning Analytics 2.0.7
- Excel Version: 2010
Re: View to show first item in subset dynamically
This is my MDX expression in TI.
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.
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])
)
}
)
}';
2
1
3
4
5
6
with 2 being the first item.