I have a question regarding charts in reports built in report builder.
What I want to do is combine a stacked column chart (has a series group) with a single line chart, something like this:
What I have tired so far has resulted in as many lines as there are groups in the column chart. This is not what I am after. Is it possible to combine all the group values for the line series?
Thank you for helping :)
There is a bit of a fudge you can do that will generate the output you need:
I am not aware of any way to only show the one line for your secondary axis, so what I have done instead is made sure that the different Series all have the same values so that the lines are all overlapping.
To do this, you will need to calculate the values for each of your Categories and repeat them for each row of your data that is a part of that Category. So if your January 2016 percentage needs to be 29%, make sure it is 29% for any record that is returning some data for January 2016.
You can then set up your second Values group to show the average of this value, set it to show on the Secondary Axis and not show in the Legend, as this will get crowded. If you need to state what the line is, edit your Secondary Axis Title to be descriptive:
As you can see from my design view, I have actually set the Line to be the sum of the Mon Category Value (this value obviously being the same for each row in the same Category Group) which works in my example because my dataset requires no further aggregation. If you are summing up many rows to get the values in your chart, you will need to use average.
This gives you what looks like just the one steadily increasing line on my chart, but is actually one line per Series all sitting on top of each other.
