How to showing the data and total amount as per user setting ?

Surya.Arif

I have a list 'subportfolio' data as follow

- All Subportfolio

A14

A15

A16

A17

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)

Filter_Option_34788.png

Accepted answers

All comments

PaulRitner

Hello,

I think there are a couple ways to do this (in no particular order):

User Level Filtering: (You might already be doing some of this)
1. In the Report Option Module, add Users as a dimension. In this way, each user can select their own options and not interfere with other users.
2. In the P&L Data Module, also add User as a dimension and include IF-Then criteria to only bring in amounts for selected items (from the Report Option Module). Note, you can exclude IF-Then logic in favor of a 1 or a zero by converting the checkboxes to 1's and 0's and then simply bringing in the data as you are, but adding a little piece to the end of the formula that multiplies the result by the selection's 1 or 0. I prefer the latter because it means less IF-Then to look at later...
3. One potential issue with this approach is module/model size. It could be too big, or not scale well. However, its easy... so if its not too big, its probably ok.
User Level Filtering (alternate approach, might also work well)
1. This is similar to the above, but slightly different, and I think along the lines of where you are already headed.
2. Create the Report Option Module with Users Dimension, like above
3. Add a new module called "P&L Data - User Subtotals"
  1. Include the Users list
  2. Only include line items for the key subtotals that you need. For example: Total Rev, Total Expense, Net Income, etc.
4. In the regular P&L module:
  1. add a few line items at the bottom that mirror the line items you just created in the P&L Data - User Subtotals Module.
  2. Add the Users List to these line items (along with the other lists in the module)
  3. Insert a formula to zero out any items the user hasn't selected. (just like in the previous example)
5. Go back to the "P&L Data - User Subtotals" module and link in the subtotals... they will aggregate by themselves, so the formula is a simple point to the line items in 4(1), above.
6. Note: If you only have a few line items to begin with in P&L Data, then this 2nd option will be the effectively the same as the first...
7. Last, but not least (if needed), let's add a user filter to the P&L Data module (based on our users selections). This can be accomplished two ways:
  1. Add users as a dimension (to the P&L Data module), but only at the module level, not to the line items. then you can apply your Report Option Module (user level) boolean selections as a filter, with User being "current page" in that filter.
  2. Add a line item at the bottom of the P&L Data module named "Filter". format this line item to boolean and only include the Subportfolio list & Users. Link in the user level selections from the Report Option Module... and apply this as a filter.
  3. In both of the above cases, you'll have to add a Refresh button to the dashboard (or direct users to click the Refresh option) to reapply the filter each time selections change.

This post should be directionally correct. Let me know how it goes!

Paul

Surya.Arif

Dear Paul,

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).

Thanks

Surya Arif

Dashboard_34797.png

PaulRitner

Hi,

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

L2 Subportfolios

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.

Cheers!

Surya.Arif

Hi Paul,

Thank you very much for your advice, it worked.

Never thought that the "show ?" could also be used as a filter.

Quick Links

Can you help?

Unanswered questions

UX Filter
Hi ALL, I have a module where a line item (Assignments column) is formatted by a large ‘Projects’ list. Each record is assigned to a Project and this module is published to a UX page. When users apply a filter on the Assignments column, the filter dropdown displays the entire Projects list (since the column is…
Dimensionality Issue
Hi ALL, In my model, I have Need stored at a 'D3' RS Group level. I need to use this Need in another module at the G5 Location level. I currently have a mapping module where each G5 Location maps to a D3 RS Group. This Module uses D2 Role as dimension. but the line item I've to find the RS Group (Find D3 RS Group = 'FIND…
Formula to return Connection details...
I couldn't find any formula to do this, but I want a way to put on the page/grid the connection details. This is so I know what connection I'm in rather than going into Connections details to see it. Is there an XL3 formula to return this value?