SSRS 2008 R2 Chart - Display Chart average and Group average

5
votes

I have the following requirement for a report.

I have a repeating bar chart within a Tablix region (grouped by individal). On that chart, I need to display the percentage of a value (call it sales) for each month. So a chart will have a single employee, with a bar for each month, showing the month total sales as a percentage of the grand total sales. That's the easy part: my expression shows (=Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, "EmployeeChart").

My next requirement is what has me stumped. I need to display the average Sales Percentage value for each employee as a line (or bar) next to the individual Sales Percentage value. So for each month, I need to know how the Percentage distribution compares to the group average Percentage.

The end result has one graph repeated for each employee, with a bar series showing the percentage of sales in each month, and a line series (which has the same values in each chart) showing the average sales percentage of that month for all employees.

I've tried the following, and haven't gotten the results I want.

=Avg(Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, "EmployeeChart"), "EmployeeTablix")

I've also tried various combinations of declaring scopes, none of which worked - I can't get it to give me the average of multiple group separations (e.g., SalesMonth and EmployeeTablix) .

Any suggestions?

2
reporting-serviceschartsssrs-2008
UPDATE: I have a workaround that is getting me the results, but it feels quite a lot like a hack and I would prefer a more elegant solution if anyone can offer it. What I did was add a DataSet to the report, showing the sum of sales grouped by Month. I then used a UNION statement to show the grand total of sales, with the month set as 13. To display on my chart, I used =(Lookup(Fields!Month.Value, Fields!Month.Value, Fields!TotalSales.Value, "TotalSales") / Lookup("13", Fields!Month.Value, Fields!TotalSales.Value, "TotalSales")) * 100 – ahiggins

2 Answers

0
votes

Why don't you divide the grand total by total number of months in your chart for a line chart type?

=Sum(Fields!Sales.Value, "EmployeeChart") / CountDistinct(Fields!Month.Value)

... or similar for your dataset.

0
votes

Shew this one is a bit old but I came across it when looking for solution to a related problem I am having with averages shown on a chart. I thought I can share what I have done as it might be a solution?

I am working on something similar in SSRS. I have a chart giving total volumes per day of three items for a week.

On the label of each day I have added (Appended) a total of all items on that day. And under the series (Showing the colours for each item) I have added an average for the week for each item.

Here's an example..

enter image description here

(My averages are a bit off with only the last average being correct.)

To append this information to the labels of category or series groups in SSRS you need to do the following:

  1. Click on the series group you want to edit of the chart. This will display a little "Chart Data" window listing "Values," "Category Groups" and "Series Groups".
  2. Right-click on the item under either Category or Series group that you want to work with and click on "Properties".
  3. Click on the fx button next to the "Label" field to open the Expression editor.
  4. Add the information you want to display separated by "&". For example: =Fields!Day.Value & "(Tot.: " & SUM(Fields!Matters.Value) & ")"

This example will display the Name of the day with the text " (Tot.: ", the totals for all items on that day and ended off with ")". As in "Mon (Tot.: 486)".

If you want to list the appended information below the label, then you must add vbcrlf between some &'s.