How to get a percent in SSRS Matrix

Question

I'm pretty new to coding in SQL and using SSRS so I don't know if this should really be fixed in my query or if there is an easy answer on the SSRS side. I have a simple query that I've put into SSRS and added a Matrix to see the data. In the final "Total" row I'm trying to throw in some extra totals like a percent based on the data in a certain column. Here's my query:

SELECT
       ADV.Account
       ADV.City
       ADV.LocationID
       NUR.QueryID
       NUR.Response
FROM ADV.Visits ADV
LEFT JOIN NUR.Responses  NUR   ON (NUR.VisitID  = ADV.VisitID AND
                                   NUR.SourceID = ADV.SourceID )
WHERE NUR.QueryID = '1' AND
NUR.DateTime BETWEEN "x" AND "y" AND
ADV.LocationID IN ('1stFloor', '2ndFloor', 'ICU')

Here is an example of my responses

AccountNumber   City    LocationID  QueryID Response
12345         JAMESTOWN    ICU          1      Y
13254         JAMESTOWN    ICU          1      N
13584         SIMCITY    1stFloor       1      Y
18789         JAMESTOWN  1stFloor       1      N
45678         JAMESTOWN  1stFloor       1      Y
56789         CITYSCAPE  1stFloor       1      Y
48971         JAMESTOWN  1stFloor       1      Y
457895        CITYSCAPE  1stFloor       1      Y

Below is what the matrix looks like in SSRS. I have it totaling based on the Y (Yes) or N (No) Responses and a full total at the bottom. I now want to (In the red box) put an expression that will give me the percentage of N Responses. I have tried this a dozen different ways including adding a separate table to do the expression, adding two different data sets, one keying on the Y responses and one on the N and putting them in separate matrices and then trying to do the arithmetic in a third table by referencing either ReportItems!TextBox.Value's or the Aggregate Values themselves but I continue to get a myriad of errors.

As I said originally, I don't know if it's my query that needs editing, the SSRS expression that I just can't get correct, or both. But ANY help would be appreciated! I am using SQLServer2008 (writing out of Microsoft SQL Server Management Studio), and ReportBuilder3.0.

Ross Bush Ross Bush · Accepted Answer · 2015-12-31T17:17:34

You can put this in your sql procedure result set and simply use =MAX(myPercentCalc!Value,"myDataset"), however, I would try to use a calc field for this. Calculated fields can be added to the dataset via the IDE where the list of dataset fields are simply add a new one and the value set as an expression so you could do a CALCNCount and set the expression to:

=IIF(myYNField!Value="N",1,0)

Then simply use the SUM(CALCNCount!Value) in your percentage calc.

How to get a percent in SSRS Matrix

1 Answers