Hi all. I am trying to make a chart that compares an individual section to the entire company for employee data.
It sounds simple but really I am trying to report on more than 100% of the numbers. I am a member of my section but I am also a member of the whole. Currently I have a working solution using 1 query that lists everyone then in another column lists if they are a member of the section. Is it possible to have 2 queries display on 1 chart? I tried this with a Union and the chart would not display. If anyone has any other suggestions I am open to them. My solution works but it is not fast.
Thanks.
Sounds like you have a Dimension... a Hierarchy, with Section being a Level below "All"
If you model your metadata dimensionally, then you can do what you are trying to do using Levels and Members of the levels, from within a dimension.
Otherwise, you might also fashion a report Query that returns the measure, filtered by your section, as well as another measure, that represents the entire company. Then use multiple series in your chart.
For the section specific measure, your calculation might be:
IF([namespace].[query].[Section] = ?pSection?)
THEN ([namespace].[query].[some measure])
ELSE (0)
For the "All" measure, just return the measure, as-is.
[namespace].[query].[some measure]
The chart will do your aggregating for you.
Thanks for the reply.
Actually the second way you listed is how I am currently doing it. I will try to dimensionally model it in FM though, that might make this quicker. It is dimensionally related data its just currently in relational format.