0

I am currently using SSRS Report Builder 3.0 and need to make a stacked column chart with 2 series (side-by-side). The category grouping (x-axis) should be a month/year while the values (y-axis) should be a percentage based on:

Series 1: sum of revenue (ChargesTotal dataset column) for the month (by special grouping to make the stacked column)

Series 2: sum of revenue from marketing (MarketingChargesTotal dataset column) for the month (by special grouping to make the stacked column)

I've already tried using the 100% Stacked Column which did not work because the second series (sum of revenue from marketing by month) needs to be divided by the sum of revenue for the month (series 1 total column sum), rather than /100. And to my knowledge this cannot be done with a 100% Stacked Column chart.

I cannot figure out how to achieve the results I'm after. Ultimately, what I want is the series 1 column to sum to 100% across all months on the x-axis while series 2 should never be a 100% tall column because it will never accumulate to 100% of revenue received, but some percentage of that. So series 2 should be a variable height column across the different months.

This seems like a wordy explanation; but I don't know any other way to explain what I'm trying to do... Please get back to me with any questions and I'll do my best to be prompt with answers to assist in coming up with a solution.

Thank you in advance for any guidance!

Update

After implementing the suggested answer I have the following results (I thought it better to display the results rather than try to explain them):

enter image description here

sadmicrowave
  • 39,964
  • 34
  • 108
  • 180

1 Answers1

1

I would use the Sum function with a Scope expression to acheive this, e.g. for Series 1:

= sum (Fields!ChargesTotal.Value ) / sum (Fields!ChargesTotal.Value , "Chart1" )

And for Series 2:

= sum (Fields!MarketingChargesTotal.Value ) / sum (Fields!ChargesTotal.Value , "Chart1" )

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • I'll check it out to see if it works but I think the 'chart1' scope was what I was needing to identify the field and value I wanted to pull from. Thanks for your suggestion – sadmicrowave Jul 16 '14 at 12:54
  • checkout the image I added to my OP. I would expect the first series to be 100% after implementing your suggestion. To me, this means that the `Fields!ChargesTotal.Value, 'chart1'` value and scope are not retaining the category groupings by month and perhaps the sum function is summing all charges for the year. My axis `Maximum` value is set to `1` and the axis format type is set to `Percentage` btw. – sadmicrowave Jul 16 '14 at 14:24
  • You said: "... what I want is the series 1 column to sum to 100% across all months on the x-axis...". This appears to be the case - there are 12 bars at just under 10% each. – Mike Honey Jul 16 '14 at 22:44
  • The y-axis is already scaled accordingly I think I am seeing a small percent because my denominator is too large. Am I wrong? – sadmicrowave Jul 17 '14 at 02:52
  • Who knows? You need to define "small percent". Exactly what result are you expecting to see, and why? – Mike Honey Jul 17 '14 at 04:04