Stacked column chart with single line overlay

[MustangPriMe]

I'm using SSRS 2012 and trying to build the following chart:

[ul]
[li]A stacked column chart of timesheeted hours, the series being grouped on (and therefore the columns broken down by) department.[/li]
[li]A single line overlay on a second axis showing the total staff headcount (not broken down)[/li]
[li]Both charts with month as the category group (x-axis)[/li]
[/ul]

The problem I'm coming across is that I can't get a single line with the total headcount to show. It ends up with multiple lines - one for each of the departments, the same as the stacked columns are broken down by.

Is what I'm trying to do possible in SSRS?
If so, what am I missing?

Thanks
Paul

 

[xlbo]

The problem is (well prior to 2012 anyway...I haven;t had teh chance to use 2012 yet but I think the same will apply) SSRS uses contextual aggregation. therefore if you use a grouping in teh chart, everything will be related to that grouping whether it is on 1 axis or the other

What may work is to force the contextual aggregation using the INSCOPE function

Here is 1 example of how inscope works (see the marked answer for the best example):
Link

You can think of a chart as analagous to a matrix so the same theory should apply

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question