Hi Declan,
Thank you for your reply. I did actually try the UNION approach before posting the question to the community but could not get it working. Now I wonder if I am missing something because you say it should work.
This is how the selection widget behaves with the union function:
1. The top element "Alla" is selected (as a default for all clients the element "Alla" is prefilled in the cube which contains the user-specific selections)
2. The user selects the element S2031.
- Element S2031 shows upp as the first element in the subset as expected with the UNION function.
- Issue: The hierarchy broke but that can be fixed by adding the "ALL" argument to the UNION statement in the MDX so that duplicates are kept. This just looks a bit strange when the top element is selected because it shows twice then as element 1 and 2. Maybe something the users could live with.
3. I close the book and open it again.
Unfortunately the element "Alla" is shown and not S2031 although it is the first element in the subset. It seems like the selection widgets always defaults to the element the book was initially saved with.
Or am I missing something?
Regards,
Mario
------------------------------
Mario Hasler
------------------------------
Original Message:
Sent: Thu June 17, 2021 11:48 AM
From: Declan Rodger
Subject: PAW - How to make the selection widget show user-specific selections stored in a parameter cube
HI Mario,
We have some solutions that work via a combination of the 2 methods you have described.
The "trick" is to have the selection widgets be based off an MDX subset. That MDX subset is a union of the element that the user last selected (retrieved from a cube containing the }client dimension) and the existing hierarchy that you would have had in there anyway to make selections from. Just make sure that the union results in the first element in the subset being the one which they selected last time.
I should also add that where we have this approach we are also using hidden websheets on the PAW book that are synchronized to take the selection widget selections, show them in a SUBNM - which is then sent into a cell against the }Clients dimension. I don't see any reason that you couldn't do this without the convoluted hidden websheet method though... we just use that because it helped us achieve a few other requirements in those particular applications.
Thanks,
Declan
------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
Original Message:
Sent: Thu June 17, 2021 11:09 AM
From: Mario Hasler
Subject: PAW - How to make the selection widget show user-specific selections stored in a parameter cube
Hi Mark,
Thank you for your answer. Appreciate it! I have used the solution you suggested in other circumstances and it worked fine. The reason why I don't want to use it in this use case is that the organization dimension is quite big with several thousand elements and a hierarchy with several levels. Users do want to have possibility to "navigate" through the hierarchy in order to find the desired element (see screenshot below). With the "cell and picklist"-solution you only get a flat list of elements. So the users pretty much have to know exactly which element they want to select so they can search for it.
With the new button synchronize functionality which was launched I was really enthusiastic being able to use the selection widgets instead of cells and picklists. It is just this issue which I described which is frustrating because I already know users will (rightfully so) complain about that.
SUBNM functionality would be great but ultimately it would be great if the user-specific selections in the selection widgets could be used in MDX on-the-fly without the need to store the selection in a cube or attribute.
Regards,
Mario
------------------------------
Mario Hasler
Original Message:
Sent: Thu June 17, 2021 05:58 AM
From: Mark Wragg
Subject: PAW - How to make the selection widget show user-specific selections stored in a parameter cube
Hi Mario,
My usual workaround for things like this is to create an attribute against the client, lets call it "Client Selected Org", make that a picklist re a subset of the Organisation dimension, and then have the MDX reference this attribute. Then instead of the Dimension selector you actually make the selector the attribute cube intersection.
This means it will always show the last value entered by the user. Of course you don't have to use the attribute cube, you could create a specific cube for this purpose and have the client as one of the dimensions.
Having said this, it can come with certain anomalies, meaning that we really need the SUBNM type functionality you are asking for!
I would say in general, how the subsets are updated in PAW needs some work. Often times they are not automatically recalculated/refreshed, which as you say is potentially confusing for end users.
regards,
Mark
------------------------------
Mark Wragg
Original Message:
Sent: Tue June 15, 2021 10:59 AM
From: Mario Hasler
Subject: PAW - How to make the selection widget show user-specific selections stored in a parameter cube
Hi,
I have a selection widget for the organization dimension (4 levels) and a crosstab which should always show the zero level (cost centres). Meaning if I select a level 2 element in the organization dimension selection widget, the table should show all cost centres (leaf level) which belong to the selected level 2 element.
I solved this with a selection widget and a TI process which stores the selection for each user in a parameter cube (using the new synchronize feature in action buttons). In the crosstab, a MDX is applied to the organization dimension which gets the selected element for the current user and shows all leaf elements.
In the screenshot below I can select element N48 in the selection widget, click on run and the table updates and shows all leaf elements which belong to element N48. So far so good.
------------------------------
Mario Hasler
------------------------------
#PlanningAnalyticswithWatson