Hi Carrie
One design option would be to use form variables. This design option would require you to guesstimate a max number of departments for the report, lets say 50 for example. Then perform the following steps:
- In a hidden sheet, map a dynamic row range that returns all of the departments you want to use for this report.
- in the visible sheet, set up 50 department columns with their respective sales % column.
- Map the 50 department columns. For the department dimension use a form variable.
- in each of the form variables, use an index function referencing the dynamic row range to retrieve the corresponding department name.
- Add logic to dynamically hide department columns that are not needed.
This design option will dynamically add new departments to your report up to 50 departments. Once you hit the 50 you can always update the report with a higher maximum.
Note: One thing to keep in mind is that this design option trades formatting flexibility with template performance as you will always have the max number of departments mapped on the report.
------------------------------
Mike Liu
Manager
Vena
------------------------------
Original Message:
Sent: 02-20-2024 15:19
From: Carrie Self
Subject: Horizontal Cascade with Percentages
Hi! Crowdsourcing here.... Has anyone found a good way to cascade columns while skipping every other column to allow for percentages of sales? I'm working with a department level side by side P&L where we add departments fairly consistently. Because we're showing the account balance with the percentage of sales next to it, I have to manually add/map each new department which means I'm having to remap this report (and two others similarly laid out) almost every period.
Open to any ideas! Thanks!! Screenshot of the report in question below for reference.
------------------------------
Carrie Self
Sr. Dir. Business Intelligence
Tacala | Southern Brew
------------------------------