SUM function based on User-specific filter
Ok, here's the situation:
- I have a transaction module
- The transactions are children of Cost Centers
- The transactions feed into a reporting module using a SUM function
- There are certain "special" cost centers that exist across different functions\parents in the Cost Center list
- I need to be able to give the users the option to: 1 - Only show the values for those "special" cost centers 2 - Show values excluding those "special" cost centers.
I thought I had this solved. I created a attribute that identified that "special" Cost Centers, a module to allow users to choose how to filter those "special" Cost Centers (filter and exclude) and incorporated those selections in the SUM formula. It worked great until I realized that the filter selection applied to ALL users. I need it to be user-specific.
Given my situation, is using a filter and incorporating that into a SUM formula that right path? I've considered an alternate hierarchy, but looks like a bunch of maintenance and tricky to maintain with the transaction detail and I think that would require separate dashboards. If the filter and SUM are correct, how do I do that? I have set up a user-specific filter, but I don't see how I can apply that filter to my SUM formula.
THANKS
Answers
-
@OSUBlakester I think you're really close to the solution already.
You can use the "current user filter" (see link) functionality. I think this is what you're looking for.
Let me know if you want an example. Would be happy to show you.
0 -
Indeed the solution is to use the "Users" list in your modules:
- add "Users" list a module with Cost Centers in order to define the "special" Cost centers
- add "Users" list to the module where the SUM is used and the filter is applied from the previous module
1 -
I was thinking that I needed to add the users to all of the other modules. Was hoping to avoid that. I can give that a try.
0 -
Blake!!!!!
What up? I think you have an example of this that I copied into your workspace, think User Filtering. The problem with using the Users list is it doesn't have a top level, and because it doesn't, you can't use the SUM function. To get around this, you will need a "fake" users list. I am out of town this week, but very happy to set up some time with you next week (Monday??) to review.
Rob
0 -
A cell in a module can store only 1 value..and the cell is the result of the intersection of all the dimensions of the module.
Unfortunately, you cannot have different results of the same cell, varying by user.
So, every time when you want to have a different result that vary by a user you need to add to the module another dimension to differentiate the cells.
A way to solve this is by using the "Users" list.
The "Top" element of the users would create an intersection that is valid for ALL the users and would not answer the constraint to have a different result that varies by user.
ciao
Alex
1 -
Hey @rob_marshall
We're suffering the same problem albeit have got user filtering going on. How to get around having layers upon layers of calculation and model size is proving a head scratcher...0 -
0
