I have a list 'subportfolio' data as follow
- All Subportfolio
And a module 'P&L Data' with subportfolio and line item (Rev, Exp, Total)
Now the user want to see the P&L data as per their settings,
i.e Grade A consists of A14 & A15
Grade B consists of A16 & A17
One way to do this is by making a 'Grade A' subsets and 'Grade B' subsets. But this approach is hard to maintain because the user setting is very dynamic, they can change or add another criteria easily.
My option is use a boolean/list filter module, so i make :
1. Report Option Module, in this module user can choose the filter from list 'filter option'
2. Option filter module, in this module user choose which subportfolio that they wanted to see
3. I combine it with 'P&L Data' by adding 2 line item, 'list filter?' and 'modifier' to check if selected then all the value will be multiply by 1, else by 0. (IF List Filter? THEN Value ELSE 0).
This approach already work, but only for the totals, the totals already correct but the other non selected still shown.
I can make the other data disappear using the filter and by select the list module.
But this take 2 step, and there's a chance that the user select different filter for both case.
My question, is there a way to synchronize the filter that is set in the 'Report Option Module' with the filter in the data filter ?
Or is there a better way to show data and total (parent) as per user setting ? (without using list subsets)
Solved! Go to Solution.
I think there are a couple ways to do this (in no particular order):
This post should be directionally correct. Let me know how it goes!
First off, thank you very much for your answer and suggestion.
Though not precisely as you suggested, mostly I already done it.
If you can refer to my picture so I can give a more clear explanation about my case.
1. The Red circle one, is the user define selection "Subportfolio Filter" module, in here user can select as they like what data to be shown
2. The Green Circle one is the actual setting "Report Option" of which filter will be used. Your suggestion about adding user into this Module is a very good one, I just realize that several user could be using the dashboard at the same time and it could result in a confusing result.
3. The Blue Circle one is the P&L data, already combined with the modifier and also the result as you can see at the last few line items already multiply by the modifier
4. The Orange Square one is actually my problem. As you can see the formula actually already working fine. No result there only 0, so the sum in the all subportfolio already correct. But how can I hide the data in the Orange Square so my user will only see A14, A15, and all subportfolio.
Actually it can be done but the user have to manually change the filter every time he/she change the Report Option,
more over the filter that can be choose is only "Subportfolio Filter" so there is a higher chance of user select different filter than the one that set in "Report Option"
As for you last suggestion I already tried to apply, by adding user as dimension to both my P&L Module and Report Option, the result in the data filter of the dashboard there is another Tab "User", when I add filter there is a "report option" selection but when i choose it, it immediately show another list box consisting of subportfolio filter list.
I still have to choose which one.
My goal is to have user select the filter they want in the report option (green circle) and the P&L shown the data and total for that filter (without the orange square).
Having a standard set of report options is easier to manage.
Based on where you are so far, leveraging the "Show ?" line item as a filter to hide/show subportfolios (and adding a refresh button) should work fine.
If your subportfolio combinations tend to remain static, then another possible approach would be to use an alternate hierarchy.
The alternate hierarchy would have two lists:
L1 Report Options
L1 would be your Report Options list as it is today
L2 would be a numbered list, where you add children under the report options. Each child represents a Subportfolio... because we use a numbered list, we can have duplicates (I like to define these kinds of children in a module... and then reference the module as the display by & mapping).
With this approach, you would have a 2nd P&L Module based on the L2 list, that synchronizes to the user's L1 selection... complete with subtotals. This 2nd module would have simple lookup formulas back to the primary P&L. This would be dynamic and wouldn't require a Refresh button to refresh the hide/show columns filter.
This approach should scale well.. You can, in theory, let a subject matter expert (or even end-users), manage their own Report Options through a dashboard... or, if the report options don't change very often, you can manage them yourself through a simpler dashboard.
I like alternate hierarchies because they are simpler (for me) to understand & troubleshoot... but they only work well in environments where the output requirements are slower moving.
Thank you very much for your advice, it worked.
Never thought that the "show ?" could also be used as a filter.