I created a very simplified version of what we are trying to do on a larger scale.
I started with a data module that has information we need by product. Product is a list in the model and it has leaf levels and parents all in the same list.
Next, I created a user input module where each user will select what product they want to see. Some users may choose to see a rollup (i.e. "Product Group 1") while others want to just see a single leaf-level product (i.e. "Product A")
As expected, when I try to use just a straight LOOKUP formula, the results aren't right (0s are returned when a product rollup was selected by the user)
So, we did some research and found the community article I linked above and got the idea to use a SUM formula instead. I created a simple mapping line item that returns "refresh" for products that should be added up based on the users' selection. Refresh is the only member of the list "refresh list" which we already had available in the model for other uses.
Then, my thought was to create a line item that included "refresh list" in the applies to and do a simple SUM formula. However, we are getting the following error:
Any ideas on getting this to work in a way that won't create a lot of space drain? We are purposely not dimensioning this module by product due to space considerations - the real module has several additional dimensions but trying to simplify to get down to the root of the problem that we are having.
If the data is already in a module, i would enable the users to go to the report itself, and search for the relevant Product or Product Group. We're on a slippery slope when we start to build reports with slightly different and specific requirements.
I also noticed that you combined Product Group and Product into 1 list. I prefer splitting it into 2 lists connected via the 'Parent Hierarchy'. There might be a future scenario where i have to enter values at the Parent Group hierarchy.
Having said that, please see if the following fulfills what you're looking for. I have minimized the 'users' column to protect the identity of the users.
Please note that you cannot choose 'Product' in the PG line item; nor can you choose 'Product Group' in the Product line item.
Line item 'Valid?' ensures only PG or Product is chosen.
We do allow users to go to the report, but it doesn't provide the variance analysis that our users need. The source module is already large enough and adding the necessary variance analysis is not possible. So, our thought was to create a separate report that can do the variance anlaysis based on user-selected time periods, scenarios, and products (and thus not require any of these three lists to be dimensions of the new report).
We were trying to avoid creating yet another product list (we have several and are trying to be careful of the maintenance requirements we are creating), but that is probably our best backup plan. What you have outlined makes sense and I think we could make that work. I was just hoping to avoid the new product list issue.
Thank you both for the ideas and workarounds. I think that I'm going to put in an item on the idea exchange to allow us to do a SUM like I attempted to do (where the source was not dimensioned by "users" but the rule and target line items are). I think that would be a very cool feature - although I still don't really know why it doesn't work that way now.
I agree that compositie list is preferable, but in this case not ideal. We ultimately determined, after some further discussion with the requesting end-users, that a shorter flat-list will work and we will be able to dimension the module by that list instead of making it a user selection at all.