I'm hoping to modify a widget that is already built using both the Seats and Tickets and Finance cubes.
Currently, the widget displays total revenue per performance for all Productions containing "Ceramics":
My end goal is to group all production seasons containing "All Levels Wheel" and "20a" which (there are multiple production seasons that fall into this category) and then see the total revenue for this set, as well as total number of students for this set. Each All Levels Wheel production season is a package containing multiple-day classes, and we have about 50 groups of packages of which All Levels wheel is an example. "A" stands for our spring Session, and all production seasons within our Spring Season contain 20A. 2020-20A971 is an example of one production season in our Spring 2020 lineup.
Eventually I will need to report on each Production Season broken down by Spring, Summer, and Fall for 2020 and 2021. All Production Seasons in Summer 2020 contain 20B, and for Fall contain 20C. I have about 50 class groupings and then 6 sessions for the two years 2020-2021.
Currently the only way I can figure out how to do this is create multiple Values in a Bar Chart, but the problem with this is eventually.I will need to create ~300 such value with the formula:
(sum([Ticket Paid Amount]),[Production Season])
The problem with this is eventually I'll have to create 300 such formulas: three for each of our 50 groups of production seasons, one for each spring, summer, and fall season, for both 2021 and 2022.
Here is what I have for 5 such instances:
I would guess there's an easier way to group all of the classes without having to create 300 values in various widgets, whether that's through inserting a case statement somewhere or whatever it may be.
Any insight anyone can provide would be much appreciated!
Just in case it's helpful to offer another way of building this out, this is how I report on our different types of classes. I have a different widget for each class type. This is Story Drama, but you might have one for All Levels Wheel. And instead of filtering on the value level with a formula, I filter the widget by performance name like Story Drama. And then break it out by season or fiscal year depending on how granular we want to be.
I also report on class age level, which was a bit trickier, but I figured out that the show codes followed a naming convention where each age group had a different number within it corresponding to the age group. For example below, when the second number in the show code is 3 that means it's for 9-11 year olds. This widget below is on a dashboard that is only reporting on our 2022 summer camps, so it doesn't show year over year.
Good luck!