SSRS matrix report total sum from expression

Question

I am trying to create sum() expression which will summarize rows and place results into "Total Servers in TPM" column. Looks like I am not able to provide the right feed for sum expression.

SSRS matrix report in design mode looks like:

Matrix report picture

[TPM_scan_type] expression can have 2 values: "TPM Succesfull Scan" or "TPM Failed Scan"

=Switch(Fields!ScanStatus.Value = "OK","TPM Succesfull Scan",Fields!ScanStatus.Value = "FAILED!","TPM Failed Scan")

[scan_count] expression just aggregate values for each "Primary SA"

=Switch(Fields!ScanStatus.Value="FAILED!",Count(Fields!ScanStatus.Value),Fields!ScanStatus.Value="OK", CountDistinct(Fields!ServerName.Value))

Results:

Report output picture

Data sample:

USE tempdb;
GO

IF OBJECT_ID('dbo.TPM_test') IS NOT NULL
DROP TABLE dbo.TPM_test;
GO

CREATE TABLE dbo.TPM_test
(
ServerName   varchar(30) NOT NULL,
ScanStatus   varchar(10) NOT NULL,
Primary_SA   varchar(30) NOT NULL,
HotfixID     int
);

INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('LDNSQLF700', 'OK', 'SA1', 157848);
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('LDNSQLF700', 'OK', 'SA1', 976832);
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('LDNSQLF700', 'OK', 'SA1', 234354);
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('NYSQL502', 'FAILED!', 'SA1', '');
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('PSQL1011', 'FAILED!', 'SA1', '');
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('NTQDF002', 'OK', 'SA1', 878641);

INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('AUSSQL140', 'OK', 'SA2', 537990);
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('AUSSQL140', 'OK', 'SA2', 1349605);
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('JAP543X2', 'FAILED!', 'SA2', '');
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('EU456CLX', 'FAILED!', 'SA2', '');
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('EUCTX654', 'OK', 'SA2', 5637965);
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('EUCTX654', 'OK', 'SA2', 6464367) ;
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('EUCTX654', 'OK', 'SA2', 1323123) ;
INSERT INTO dbo.TPM_test(ServerName, ScanStatus, Primary_SA, HotfixID)
VALUES('EUCTX654', 'OK', 'SA2', 1004326) ;
GO

Ian Preston Ian Preston · Accepted Answer · 2013-04-12T12:09:43

Wouldn't be easier to just use the CountRows function instead of a Switch statement for scan_count?

It looks like you're already got row/column groups set up in your Matrix, so CountRows will just be executed in whatever Scope it's called and give your desired result.

I created a simple test DataSet:

enter image description here

With this DataSet I added a calculated field TPM_scan_type based on your expression above.

Based on this I created a standard Matrix:

enter image description here

In each case the expression in the screenshot is just =CountRows().

Also note that Total Servers in TPM is outside of the column group scope.

End result:

enter image description here

This looks like what you require and seems to do it in a simpler way.

Edit after comment:

OK, I've changed the DataSet to include duplicate rows for OK ScanStatus results:

enter image description here

I've changed the Total Servers in TPM expression to the following:

=Count(IIf(Fields!ScanStatus.Value = "FAILED!", Fields!ServerName.Value, Nothing))
    + CountDistinct(IIf(Fields!ScanStatus.Value = "OK", Fields!ServerName.Value, Nothing))

Which gives the required results, I think, the same as the above report output with this new DataSet.

In fact, this can also be used for TPM_Scan_Type, too.

One last suggestion - is there any reason you can't just use =CountDistinct(Fields!ServerName.Value) for all expressions? If I understand your data this will give the required results - it works for the above DataSet, for example.

Edit after Dataset added:

I have updated the report to use your sample data:

enter image description here

Result for both expressions, i.e.

=Count(IIf(Fields!ScanStatus.Value = "FAILED!", Fields!ServerName.Value, Nothing))
    + CountDistinct(IIf(Fields!ScanStatus.Value = "OK", Fields!ServerName.Value, Nothing))

and

=CountDistinct(Fields!ServerName.Value)

enter image description here

So to me it seems you can use =CountDistinct(Fields!ServerName.Value) in all cases.

SSRS matrix report total sum from expression

1 Answers