Average of MS SSRS Tablix group totals

Question

I have a SQL Server Report Builder tablix report that evaluates sales activity over time.

Rows by company, columns are grouped by date. Something like this:

                2015   2016   2017
Company1          10     12      1
Company2           6      5      0
Company3           8     10      7

(The report also expands columns into months)

I would like to add a column, or color a background, calculated based on the average of each year's totals. For example, Company1 averages 7.6/year if I include 2017. I would like to be able to say that 2015 was 131% of average, 2016 was 157% of average, and 2017 is 13% of average. Bonus points if I can exclude the current year from the average.

The result might look something like this:

                2015          2016         2017
Company1          10 (131%)     12 (157%)     1 (7.6%)
Company2           6 (%%)        5 (%%)       0 (%%)
Company3           8 (%%)       10 (%%)       7 (%%)

Since the source data has one sale per row, and the tablix is what's creating the grouped count by date, I can't seem to just run an average, which just gives me "1", due to the fact that I'm counting on a count column. The source data looks something like this:

CompanyName  Date      SalesRep      Amt     Count
Company1,    1/1/2015, salesrepname, 50000,    1
Company1,    2/1/2015, salesrepname, 20000,    1
Company1,    3/1/2015, salesrepname, 50000,    1
Company1,    4/1/2015, salesrepname, 10000,    1
Company1,    5/1/2015, salesrepname,  5000,    1
...

How do I go about getting the average of each year?

StevenWhite StevenWhite · Accepted Answer · 2017-01-19T18:04:38

If you were just grouping on Company and Year you could override the scope of your aggregates with a group name. However, SSRS doesn't have a way to specify combinations of groups. So in your case you will need to make those sub-calculations available another way. It is usually best to do that in the SQL. You can either add a subquery to your existing query (preferred) or add an additional dataset. If you use a separate dataset you'll also have to match up the values with a Lookup function.

If you try to come up with an elaborate workaround like custom code or referencing textboxes it is going to become difficult to maintain and will be very inefficient.

Average of MS SSRS Tablix group totals

2 Answers