asked on

Include empty row values in SSRS AVG total column

I have a matrix that produces like this:

As you can see the average column is not taking into account any of the years that don't have activity.

How do I get it to include those empty years?

The rows are grouped by a field called "kpart", the columns are grouped by a field called "year"

-------------

I saw a solution online like the below, but I don't know where to put the declaration of that Public Function in SSMS. My preferred solution would be a formula that goes direct into the Matrix report item directly:

Public Function MatrixCellValue(ByVal sumValue As Object, ByVal avgValue As Object, ByVal InScope As Boolean, ByVal sumFormat As String, ByVal avgFormat As String) As Object
If InScope Then
Return sumValue
Else
If Not avgValue Is Nothing Then
Return String.Format(sumFormat, CDbl(sumValue)) + vbCrLf + String.Format(avgFormat, CDbl(avgValue))
Else
Return Nothing
End If
End If
End Function

akscott

ASKER

I did create one solution, but there HAS to be an easier way! I created a field in my query that did a countdistinct on my year field; then placed that within the matrix and used a formula to divide Total Field Report Item by the Year count Report item like this: =ReportItems!Textbox9.Value / ReportItems!Textbox13.Value

Would still love to see a more elegant solution, thank you.

Mike McCracken

What if a year has no data? DO you want it included?
If so just find the max and min year Max(year) - min(year) + 1 is the count you want.

mlmcc

ASKER CERTIFIED SOLUTION

Arifhusen Ansari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

akscott

ASKER

I've not tested this solution as I had to move on to the next thing -- but I appreciate the effort in helping and I WILL test it and get back as to whether it worked.

akscott

ASKER

I will post again when I have tested for sure.