Calculations in SSRS
-
i have created a table that has the following two fields:
Pass_Fail Exam_Score
P 128
P 130
P 132
P 126
P 115
F 101
F 105
F 99
i need to do several calculations from these 8 rows in this test db - should be easy, right? i can use either 'Pass_Fail' or 'Exam_Score' to query but i can't seem to get the calculations to work after countless attempts.
=Count(Fields!Pass_Fail.Value = "P") does not work...it simply counts all the rows instead of only the rows with "P".
Same result with =Count(Fields!Exam_Score >= 107).
I realize now there is something wrong with my = or >= statements because even if i change the values i get the same results. Does anyone have any ideas??
Dana
"Drats! Foiled again!" -
i can't seem to get the calculations to work after countless attempts.
=Count(Fields!Pass_Fail.Value = "P") does not work...it simply counts all the rows instead of only the rows with "P".
It seems like it is doing exactly what you told it to do. The count method counts the amount of roles passed to it. In your case the amount of rows where Pass_Fail is P.
What kind of calculations are you trying to do?
Joie Andrew
"Since 1982" -
Hi Joie,
I need it to count the "P" only - result should be 5 but i'm getting 8, which is the total of all rows in this small test db.
i also tried to use the Exam_Score field where 5 of the results are over 107 (the pass/fail cut off rate), and i get a total of 8 again. I should be getting 5 for either scenario, so i'm not sure why it's counting all rows. there's something that is causing it to ignore my ="P" or >=107 conditions.
Dana
"Drats! Foiled again!" -
Try this:
=Count(Fields!Pass_Fail.Value, "P")
Count Function (Report Builder 3.0 and SSRS)
Joie Andrew
"Since 1982" -
unfortunately this didn't work either. 🙁
Dana
"Drats! Foiled again!" -
Count Function (Report Builder 3.0 and SSRS)
Returns a count of non-null values specified by the expression, evaluated in the context of the given scope.
scope
(String) The name of a dataset, group, or data region that contains the report items to which to apply the aggregate function. If scope is not specified, the current scope is used.
-
Just had a chance to look at this in an environment. Have you tried modifying the query in BIDS? That may be more effective.
Joie Andrew
"Since 1982" -
You can't do this directly.
You would need to add a text box (name it Outcome) into say a table / matrix on the report which shows the records and the additional text box would contain an expression:
=iif(Fields!Pass_Fail.Value = "P",1,0)
From there in the header or footer of the report add another text box with the following expression
=Count(ReportItems!Outcome.Value)
This will count up all the Passes and present on the report.
Another option would be to edit the source query and put an extra column in there using a Case Statement which would either give a 1 or 0 and from that you could do a simple sum to give the answer you want.
-
CREATE TABLE #ExamResults(
Pass_Fail CHAR(1) ,
ExamScore INT,
)
INSERT INTO #ExamResults
( Pass_Fail, ExamScore )
VALUES ( 'P', 128 ),
( 'P', 130 ),
( 'P', 132 ),
( 'P', 126 ),
( 'P', 115 ),
( 'F', 101 ),
( 'F', 105 ),
( 'F', 99 )
SELECT Pass_Fail ,
ExamScore ,
CASE Pass_Fail
WHEN 'P' THEN 1
ELSE 0
END AS PassCount
FROM #ExamResults
DROP TABLE #ExamResults
-
hello all..thanks for your help. part of my problem was there was an extra character in the Pass_Fail values. ltrim and rtrim took care of that, then Count(iif...) worked. 🙂
Dana
"Drats! Foiled again!"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply