Multiple Grand Totals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Multiple Grand Totals

Author

Topic

ecomajor
Starting Member

12 Posts

Posted - 2009-03-06 : 12:36:06

Want to create multiple grand totals

I have a report in Reporting Services which is using a matrix. I have a grand total at the bottom which sums up each row of the matrix and then one that creates a percentage as well. There are two grouping within the row group region and classification. THere is a total on each region as well. What I would like to do is also put in a total by class on top of the grand total. I wrote a function that allows me to put in two values in the total field but the problem is that I cant get it to know which classification its within and create the write total by class. Below is my function

Public Function MatrixCellValue(ByVal sumValue As Object, ByVal avgValue As Object, ByVal InScopeMain As Boolean, ByVal InScopeSub As Boolean, ByVal sumFormat As String, ByVal avgFormat As String) As Object
If InScopeSub or InscopeMain 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

Here is how i call the function

=Code.MatrixCellValue(

Sum(Fields!active_total.Value),

Sum(Fields!active_total.Value,"matrix1_ColumnGroup3"),
INSCOPE("matrix1_REGION"),

INSCOPE("matrix1_department"), "{0,6:N0}",
"{0,6:N0}")

I am unsure how to post an image but please send me your email address and I will email you an image if necessary.

But this is sort of what it looks like

2/2009

France MGMT 5

Pro 4

England MGMT 3

Pro 4

Grand Total 16

MGMT 8

Pro 8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-08 : 13:09:41

i dont think you need a custom function for that. what you need is a IIF expression to check if column in a particular scope and then write expression for true and false parts

ecomajor
Starting Member

12 Posts

Posted - 2009-03-09 : 09:12:52

I tried that but had no success. Any specifics on how to write the code?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 05:00:10

yup. something like
IIF (Inscope("yourgroup"),SUM(Fields!yourfield.value),SUM(Fields!yourfield.value,"YourGroup"))...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources