Mixed (grouped/ungrouped) content on the X-axis for column chart
-
Good Day Everyone,
One of my customers came to me with the following request to be dealt with using SSRS: presenting on the same column chart:
a.) aggregated data for the current year, broken down per month
b.) consolidated data for the last four full years.
Here is a representation of the actual goal, created with MS-Excel (cf. image attached)
Has anyone come across such a request? I suspect this would not be achievable as mixing grouped and ungrouped data on the X-axis seems nowhere to be found.
Thank you for your feedbacks folks !
-
You might want to try using a group expression that groups by Month if the Year is current year Else group by Year. Your dataset would have a Month Column and a Year column.
-
im_lanie (5/10/2012)
You might want to try using a group expression that groups by Month if the Year is current year Else group by Year. Your dataset would have a Month Column and a Year column.Nice! Never thought to do that. Sheesh.
[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font] -
What an awesome answer, thank you very much ! Here is what a raw attempt looks like following your tip.
-
I would probably approach this from the dataset side. I would consolidate my prior years into a single row in the dataset so that you ended up with data something like this, then the graph problem solves itself
TimePeriod, value
2008, 98%
2009, 94%
2010, 92%
2011, 97%
Jan2012, 99%
Feb2012, 97%
Mar2012, 98%
-
Thank you for your answer Daniel, this is another suitable answer, even though I personally prefer to deal with 'consistent' datasets.
-
donbuz (5/10/2012)
Good Day Everyone,One of my customers came to me with the following request to be dealt with using SSRS: presenting on the same column chart:
a.) aggregated data for the current year, broken down per month
b.) consolidated data for the last four full years.
Here is a representation of the actual goal, created with MS-Excel (cf. image attached)
Has anyone come across such a request? I suspect this would not be achievable as mixing grouped and ungrouped data on the X-axis seems nowhere to be found.
Thank you for your feedbacks folks !
I have done this for a few clients and found (like Daniel) that the best way is to work on the dataset. Main reasons for this rather doing the work in SSRS are performance (SQL tuning for group by etc) and allowing multiple data sources (such as historic and current counts).
The query below is a mock up of the original results required by you, as a dataset in SSRS. I know it could be tidied up but the first set is the yearly values and the second are the monthly values. The OrderGroup and Subgroup are used for sorting on the horizontal axis.
select * from
(
select 1 as OrderGroup, 1 as SubOrderGroup, '2008' as XAxis, 0.95 as Year,NULL as Month,NULL as Target, 'Blue' as BlockColour,'White' as LabelColour
union
select 1 as OrderGroup, 2 as SubOrderGroup, '2009' as XAxis, 0.94 as Year,NULL as Month,NULL as Target, 'Blue' as BlockColour,'White' as LabelColour
union
select 1 as OrderGroup, 3 as SubOrderGroup, '2010' as XAxis, 0.96 as Year,NULL as Month,NULL as Target, 'Blue' as BlockColour,'White' as LabelColour
union
select 1 as OrderGroup, 4 as SubOrderGroup, '2011' as XAxis, 0.99 as Year,NULL as Month,NULL as Target, 'Blue' as BlockColour,'White' as LabelColour
) as YearValues
UNION
select * from
(
select 2 as OrderGroup, 1 as SubOrderGroup, 'Jan' as XAxis, NULL as Year,0.89 as Month,0.95 as Target, 'White' as BlockColour,'Blue' as LabelColour
union
select 2 as OrderGroup, 2 as SubOrderGroup, 'Feb' as XAxis, NULL as Year,0.90 as Month,0.95 as Target, 'White' as BlockColour,'Blue' as LabelColour
union
select 2 as OrderGroup, 3 as SubOrderGroup, 'Mar' as XAxis, NULL as Year,NULL as Month,0.95 as Target, 'White' as BlockColour,'Blue' as LabelColour
union
select 2 as OrderGroup, 4 as SubOrderGroup, 'Apr' as XAxis, NULL as Year,NULL as Month,0.95 as Target, 'White' as BlockColour,'Blue' as LabelColour
union
select 2 as OrderGroup, 5 as SubOrderGroup, 'May' as XAxis, NULL as Year,NULL as Month,0.95 as Target, 'White' as BlockColour,'Blue' as LabelColour
union
select 2 as OrderGroup, 6 as SubOrderGroup, 'June' as XAxis, NULL as Year,NULL as Month,0.95 as Target, 'White' as BlockColour,'Blue' as LabelColour
union
select 2 as OrderGroup, 7 as SubOrderGroup, 'July' as XAxis, NULL as Year,NULL as Month,0.95 as Target, 'White' as BlockColour,'Blue' as LabelColour
) as MonthValues
Order by OrderGroup,SubOrderGroup
The resulting dataset has 3 series (year, month and target). You can easily add more. For the yearly the month and target series are NULL. For the monthly the year series is NULL. The BlockColour and LabelColour are used to allow for presentation control i.e. highlighting best/worst/current month.
To make the columns show as originally required make the Year and Month series as stacked bars (one will hide in all cases) and make the target a line chart. Change the fill of the bars to the BlockColour, and the Label font colour to the LabelColour.
Picture of the resulting chart and the RDL of the demo attached (.txt needs renaming to .rdl).
Fitz
-
Mark, thank you for your great post.
Both you and Daniel finally convinced me that the 'dataset' approach is way better adapted for my needs.
Regards,
don
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply