CODEWITHSUNDEEP
sqlreporting-servicescountssrs-2008ssrs-2008-r2
Aruna Raghunam
Aruna Raghunam

Reputation: 921

Counts repeating for groups in ssrs report

I need to calculate count of Pass, Fail or Non-attended - for 3 groups in my report as below.

Please look here:

Report -> Result_Groups

Query:

CASE WHEN Description = 'FUNCTIONAL SKILLS - ENGLISH (LEVEL 1 & LEVEL  2)' THEN 'Overall' 
WHEN Description = 'ENGLISH (ENTRY LEVEL)' THEN 'Overall'
WHEN Description = 'FUNCTIONAL SKILLS ENGLISH' THEN 'Overall' 
WHEN Description = 'READING -ENGLISH LEVEL 2' THEN 'Reading' 
WHEN Description = 'READING- ENGLISH (LEVEL 1 )' THEN 'Reading' 
WHEN Description = 'E2  ENGLISH FUNTIONAL SKILLS READING' THEN 'Reading' 
WHEN Description ='E2 FUNCTIONAL SKILLS READING' THEN 'Reading' 
WHEN Description = 'E3 ENGLISH FUNTIONAL SKILLS READING' THEN 'Reading' 
WHEN Description= 'WRITING- ENGLISH (LEVEL 1 )' THEN 'Writing' 
WHEN Description = 'WRITING- ENGLISH LEVEL  2' THEN 'Writing' 
WHEN Description = 'E2 ENGLISH FUNCTIONAL SKILLS WRITING' THEN 'Writing' 
WHEN Description = 'E3 ENGLISH FUNCTIONAL SKILLS WRITING' THEN 'Writing' 
WHEN Description = 'SPEAKING & LISTENING- ENGLISH LEVEL 2' THEN 'Speaking & Listening' 
WHEN Description = 'SPEAKING & LISTENING- ENGLISH (LEVEL 1)' THEN 'Speaking & Listening' 
WHEN Description = 'E3 ENGLISH FUNCTIONAL SKILLS SPEAKING AND LISTENING' THEN 'Speaking & Listening' 
WHEN Description = 'E2 ENGLISH FUNCTIONAL SKILLS SPEAKING AND LISTENING' THEN 'Speaking & Listening' END AS CourseType,
CASE WHEN Grade = 'PA' THEN 'Pass' 
WHEN Grade = 'FL' THEN 'Fail' 
WHEN Grade = 'X' THEN 'Not Attended' END AS Grade

Dataset:

SELECT DISTINCT 
STEN_Student_ID, STUD_Forename_1, STUD_Surname,  COUNT(Grade) AS Count_Grade, Course, Section, CourseType, Grade
FROM FS_Exams_English
GROUP BY  STEN_Student_ID, STUD_Forename_1, STUD_Surname,  Grade, Course,  Section, CourseType
ORDER BY STEN_Student_ID

Let me know how can I do this.

Upvotes: 1

Views: 256

Answers (1)

StevenWhite
StevenWhite

Reputation: 6034

Right now you're displaying the same value in all 3 columns. In order to differentiate them you could use calculated fields in your dataset for each column.

  1. Right-click on your dataset and select "Add Calculated Field..."

  2. Name the first one something like "Pass_Value" and use an expression like this:

    =IIf(<pass expression>, 1, 0)

Note: You will need to reference the column that identifies a passing grade here.

  1. Repeat the last step for the "Fail" and "Non-Attended" values.
  2. In your table, refer to your 3 calculated fields to get the desired output.

EDIT:

Thank you for posting the query. The calculated field expressions would look like this:

=IIf(Fields!Grade.Value = "Pass", 1, 0)

Then in the table, you can reference it like this:

=Sum(Fields!Pass_Calculated_Field.Value)

Upvotes: 1

Related Questions