How to save the current selection (to file/db/cube/...)?

Unknown

Hello,

 

I'd like to save the user's current selection via a procedure (click on button to save), in order to be able to restore them later (click on button to retrieve and set). This way user's would have their own saved queries (I intend to limit the amount to 5 and have 5 Buttons).

 

What I've tried doing is storing the value of "@Selection_Ex" see Substitution formulas in a text cube with a dataflow. But that does not work as there is no value.

 

Any ideas / input?

 

BR, Ray

Stein Menkerud

Hey Ray,

 

Here is one way of doing this.

You can use an integer cube to store the User screen selections. Your selector cube would be dimensioned something like this.

1. Entity 1, User
2. Entity 2, Selection Id (You wanted 5 different saved selections per user)
3. Entity 3, For example Month
4. Entity 4, For example Product
5. Entity 5, For example Customer

To save the current screen selection:

1. Make sure the screen is setup with a selection on the user.
2. Select the Selection Id in a pager
3. Write a 1 into the cube via a procedure.

To retrieve the selection later:

Create a procedure that selects the entities based on the selector cube and then applies the selection to the screen.

For this to work, it is important to:

• Setup the User entity correctly by using the Select User = @user in the database security setup and create a user entity.  (That part is explained elsewhere in the community.)
• Use a pager to select which one of the five selections you want to retrieve. You may be able to trigger the procedure right off the pager as you make a new selection. 

 

I hope this will put you in the right direction.

Thanks,

Stein

Etienne CAUSSE

Hi Ray Martens

I have the exact same question for a different purpose, which is a cost allocation process.

The solution by Stein Menkerud works if the dimensions are always the same.

However in our case, we have 4 basic dimensions, but each granular entity has dozens of parent entities that can be used for selections, and they need to be remembered precisely (i.e. I need to remember the selected parent, not the list of children at that time, just as displayed in a @Selection_Ex formula).

Then I would need to reload this selection in a process...

Any Idea ?

Previous Member

Hi Etienne CAUSSE,

I had a nice idea of how to implement it - here's how:

 

1. have one cube in board per entity to be able to use for all of them a dynamic filter. All those cubes are dense on the entity to be filtered + a user-entity and a number-entity. The number entity will allow for more than one saved query per user.
2. save the information from @Selection_Ex to a ROLAP text cube. That cube is dense on the user-entity and the number-entity
3. parse the text cube in SQL and assign to values to the filter cubes according to the content.
4. Now on the screen I filter everything dynamically. If I want to change the saved-query number, I change the selection of the number entity. E.g. via a pager.

 

Now all that would have been great if it wasn't for...

...well I didn't manage to save @Selection_Ex to a text cube and I don't know if that's a feature or just me missing something.

 

I'm back to start, so if I come up with something, I'll let you know.

 

BR, Ray

Stein Menkerud

Ray,

 

I have tested writing the @Selection_Ex to a text cube and it does not work in my version of Board (10.1.4) either.

 

After reading the updated solution you are trying to create I believe you can accomplish the same thing with the solution I submitted without using the @Selection_Ex.  I am still assuming you are working on a solution for saving and retrieving user screen selections.  Using dynamic selections is a good option here, and you can also do that using the selector cube I described. So instead of using the “select by” action in the procedure, you can use the dynamic selection in the screen selection as you described.

 

The good thing about this solution is that you only need one OLAP cube and you don’t need to use any ROLAP cubes or SQL, and you can implement it immediately.

 

If I am misunderstanding something, then let me know.

 

Thanks,

Stein

Previous Member

Hi Stein Menkerud,

thanks for your input! So my problem is a bit like Etienne CAUSSE's. Let's say I have a product entity tree with a product group. I want to have the possibility to filter a couple of products or one product group. Let's try with something concrete such a clothing. My product groups would be shirts, socks, trousers. A Product example could be yellow shirt with long sleeves. Now using the cubes I would need two cubes for the filtering one for the product group and one for the product.

The problem starts when I try to find out if the selection has been done on one level or the other. If the user selects a product group shirts, all of the products therein become relevant so also the yellow shirt with long sleeves. If at a later point a new product is added to the product group - now there's a blue shirt with long sleeves, then my current filter combination won't find it. Although that was the intention of the person making the report.

 

TLDR: entity trees cause problems if selection on many levels should be possible.

 

The @Selection_Ex gives me the full information what the user actually wanted, and no inferred selections.

 

Right now I'm thinking about using multiple interactive selections in a dataflow but that looks very cumbersome.

 

BR, Ray