Saving and Reusing user settings

[schlemiel29]

Hi,
I know, that this is no new topic, but I found no solution.
Given the scenario of a user using a report. He selects a product and a customer, then he switches to another report. There these selections should be set already.
I know, that I can pass them by variables using "Addtional options" (or similar, I'm using german language version). But in this case I have to call the new report from the old one to pass this data.
But when he restarts his work the next day, this information is lost.

I know, I can save these selections in a specific Cube. I used a one using "}Clients" and "}Dimensions" dimensions, so I can store an element for every user and every dimension. Using a SUBNM function, I can take the selected element and write it to the cube, but how can I get it back?
Whenever I use the function SUBNM, the selected element will be placed as a fixed string within the formula like:

=@SUBNM($E$14;"";"T113";"Caption_Default")

(BTW: How get i rid of the @ symbol?)

So when I open the report directly without passing parameters, how do I get the information from my special cube into the SUBNM formula?
BR
Dirk

[Steve Rowe]

If you want to knit your reports / front ends together so that user selections follow the user around the sheets, environments and across sessions, do something like the following.

1. Build a Front End Selection cube, dimensioned by }Clients and "Selection" which contains the selection references (like "Version" or "Period" or "Year", whatever really).
2. Use TM1 Picklists to populated the selections in the cube.
3. Replace subnms with references to the Front End Selection cube.

Once you have this ground work in place you can become increasingly sophisticated and you'll find yourself using it more and more often.

For example using it in PAW to drive selections and then referencing the selections in MDX sets that are in the data visualisations.
Cascading picklists.
Updating the selections via TI across a number of users when some event happens.

[schlemiel29]

Wow, that sounds great. I don't have any experience with Picklists. So first I have to do some research for that. Thanks a lot!

So I think, the picklist is set to the dimension "}Dimensions", because every dimension needs different selections, correct? Or is it placed in an additional mesaure dim for the "Front End Selection cube" (FESC)

Maybe you can give me one example for a Dimension "Year", which is a list of all year beginning with the actual year?
Thanks in advance
Dirk

[Steve Rowe]

Hi,

This is the section of the help on picklists

https://www.ibm.com/docs/ru/planning-an ... pick-lists

It's normal (for me) to use a subset (https://www.ibm.com/docs/ru/planning-an ... pick-lists) driven picklist.

Normally the set that I want users to pick from will be driven by MDX so I can easily control the contents of the set.

So I have a FES measure "Reporting Period"
it's Picklist attribute would be something like "subset:Period:FE-Available Reporting Periods"
the set "FE-Available Reporting Periods" would have MDX against it, something like this

Code: Select all

{FILTER(TM1SubsetToSet([Period].[Period], "Default", "public"), ([Period].[Period].CURRENTMEMBER.PROPERTIES("FE-Available Reporting Periods") = "1"))}

Changing the population of the attribute FE-Available Reporting Periods against the period, changes the set contents which modifies the front end for all users.

If I want to have another selector for Period, say "Pick the Period of Data to load from the Ledger", then I set-up a new set and new attribute to run this bit of the front end.

[schlemiel29]

Hi Steve,
I think I got it.

I created a Cube

Code: Select all

DB( '}TM1Config_UserSelection', !}Clients, !}TM1Config_UserSelectionDimElement )

where "}TM1Config_UserSelectionDimElement" includes all dimension names like "}Dimension". But in "}TM1Config_UserSelectionDimElement" I could add an attribute named "Picklist". (Maybe this was not neccessary!?)

I also created a "Picklist Cube" (or similar naming, bc I use german version?) for "}TM1Config_UserSelection", automatically named "}Picklist_}TM1Config_UserSelection".

There I added a simple rule:

Code: Select all

[]=S:'Dimension:' | !}TM1Config_UserSelectionDimElement;

So every Picklist value gets the text : "Dimension:dimension name". So I can control, that in "}TM1Config_UserSelection" only elements of the elements corresponding to "}TM1Config_UserSelectionDimElement" element could be stored.
I included a DBR formula (not DBRW!!!) to cube "}TM1Config_UserSelection" in my Excel sheet and could select the appropriate element from a simple list like in Excel's build-in Data validation function.

So if anybody else is facing the same problem, here is a hint to the solution.
Thanks
Dirk

[Steve Rowe]

Sounds like a good first step.

You could have put your rule against the Picklist attribute rather than in the picklist cube. You only tend to need picklist cubes with much more complex logic against a data cube rather than the simple 2d list we are working with here. (i.e. it's the picklist cube you don't need, not the picklist attribute but you have a working solution so no point changing now)

I suspect you will want to increase the complexity at some point and run from sets rather than the full dimension list as this will allow you to refine and control the user experience more explicitly.

[schlemiel29]

You are totally right!
I have the solution for an hour, and even now I want to improve it.
And because this should be the base of a development for many reports, I like to get a very flexible solution before using it many times in many reports.

So next step will be to restrict it to subsets, so I have to get rid of the rule and import all settings from a table into the attribute "Picklist".
Then I like to show alias instead of IDs, but store IDs. This seems to be solved some years ago by IBM (lucky me!). Unfortunatly there are no examples, only the same syntax is repeated every time.

And I also want to get it as lean as possible, so I will remove the Cube as advised.

[Steve Rowe]

SHowing the alias should be a property of the set not the picklist definition? i.e. Save the set with the alias set. I'm not aware of any bugs in current releases in this area

[Wim Gielis]

What is unfortunate, is that subsets for the picklists all have to exist.
It is not possible to inject an MDX for a subset (combining several inputs in a string for the MDX) and have the server execute it at run-time. That would be much neater.
You can work around with a button and a simple TI process but that's another step that users in general will want to avoid.

[Steve Rowe]

There is an RFE here
https://ibm-data-and-ai.ideas.ibm.com/ideas/PAOC-I-318

that would allow picklists to defined by MDX only without the need for a set in between, I see it's been marked as not for consideration but allegedly even a closed RFE can be reopened if it get's enough votes!

[Wim Gielis]

Very sad

[schlemiel29]

Additional question:
Using Picklists in special cubes only offers me a simple dropdown list. How can I use the subset editor shown by SUBNM, which also has these filter functions for level, pattern and also hierarchical selection? In large dimensions a simple flat list will be terrible.

[Wim Gielis]

Actually not

[Steve Rowe]

There's no way of doing what you want at this time*, i.e. configure a picklist such that it opens the set editor dialogue. I can see that it would be useful and would give the best of both worlds where we get the richness of the set editor but also the ability to store and reuse the users selections.

I'd be happy to vote for an RFE in this space if you want to raise one.

You could set up cascading pick lists to help the user navigate to what you want them to pick. For example
Selection 1 MDX Returns the Old Style Level 1 parents of a dimensions
Selection 2 MDX Returns the Old Style Level 0 children of the parent selected in Selection 1.

or whatever supports your use case.

*AFAIK

[schlemiel29]

Good to know, so I don't need to search for it. Thank you very much.