Dynamic filter on dataview

Andreas Sauer

Hello,

 

I have a dataflow that contains revenue and costs for my projects.

Is it possible for users to filter dynamically on this dataview over several cubes?

For example

• one user only wants to see projects that have revenue greater than 2.000 and total cost greater than 1.500.
• another user wants to see projects that have revenue smaller than 1.000 and total cost greater than 2.000.

 

Like in Excel:

 

Thanks in advance.

 

BR, Andreas

Andrea Masiero

hello! 

the only solution i quickly see to have this behavior is to create some of cubes and a procedure.  

A couple of cubes where the user set the threshold (the min and the max for instance) 

I will then create a procedure that will fill a Filter cube that i have in the layout. 

Through this procedure you fill the filter cube accordingly to what the user set in the threshold ones. 

Hope it's clear. 

Cheers 

Andreas Sauer

Hello Andrea,

 

thanks for your help. It's totally clear and I already thought about this solution.

 

BR

Federico Cazzalini

Ciao Andreas Sauer,

consider also using the "filters"; maybe teaching to end-users to change the layout and eventualy save it in their own Board Presentation could be a good way.

Unknown

How about a 'filter' cube with an entity of 4 members - Revenue Upper Limit, Revenue Lower Limit, Cost Upper Limit, Cost Lower Limit, together with an entity that corresponds to ownership of the data (so users don't overwrite each others limits).  This cube would then go in the layout 4 times (all hidden) - once with a reference to Revenue Upper Limit, once Revenue Lower Limit etc.  On the report screen the user can enter values into this filter cube - e.g. Rev UL = 2000, Cost UL= 1500 etc.

Now you need to write one or more IF statement algorithms (all hidden in the layout) to allow for all cases of potential data entry to the filters (granted this is the most complex part of the solution, but achievable).  These would generate a 1 if the conditions are met and then the rows filtered using the method suggested by Federico Cazzalini.  Also no procedure required.

Andrea Masiero

NIIIICCCCEEEEEEEE!!!!!

Thank you very much Phil Marshall ! Love your idea! simpler, faster and elegant! 

Andreas Sauer use Phil's idea, it's guarantee it will work as you want. Furthermore, you also have the solution for the concurrency of users... 

together with an entity that corresponds to ownership of the data (so users don't overwrite each others limits)

you got the entire solution! ,

The if statement can be simple if you sum the first if on costs and the second if on revenues and you use the sum as layout filter. 

Enjoy now and keep us posted. 

 

Phil Leader of the New School! 

Unknown

Thanks Andrea Masiero - your'e too kind.  Nice idea re summing the output of the algorithms.

I would have had a go at writing them, but it's Friday afternoon and it's too hot here!

Nicolas CHIGROS

together with an entity that corresponds to ownership of the data (so users don't overwrite each others limits)

That use case is typically why I posted the following idea on why Board should out of the box handle a user dimension in some would be way cleaner.

 

user session & user stored cubes to help with user experience