akscott
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
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
Last Comment
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
If so just find the max and min year Max(year) - min(year) + 1 is the count you want.
mlmcc
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
ASKER
I will post again when I have tested for sure.
SSRS
SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.
10K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
Would still love to see a more elegant solution, thank you.