Search code examples
sqlreporting-servicescountssrs-2008ssrs-2008-r2

Count of one value per row in report

I have a requirement which needs to shown as below:

Dept | Description|Course | Overall|Reading|Pass|Fail|Notattended|Writing|Speaking

ABC | XYZ | DEF | 10 | 12 | 8 | 2 | 2 | 10 | 7

with drill down enabled on Dept.

I am able to achieve as this Reportwith below query.

 Query:

    SELECT ID, Forename, Surname, Code, Description, Grade, Dept,Course, Title, 
    CASE WHEN Code IN ('VJ028') THEN 'FUNCTIONAL SKILLS - LEVEL 1 & LEVEL 2' 
    WHEN Code IN ('VE203', '22877C') THEN 'WRITING - LEVEL 2' 
    WHEN Code IN ('VE202', 'VE201') THEN 'READING - LEVEL 2' 
    WHEN Code = 'VE201' THEN 'SPEAKING & LISTENING' 
    WHEN Code = 'RU243' THEN 'ENTRY LEVEL' 
    WHEN Code IN ('22875C') THEN 'E2 FUNCTIONAL SKILLS SPEAKING & LISTENING' 
    WHEN Code IN ('22876C', '22879C') THEN 'E2 FUNCTIONAL SKILLS READING' 
    WHEN Code = '22877C' THEN 'E2 FUNCTIONAL SKILLS WRITING' 
    WHEN Code = '22878C' THEN 'E3 FUNCTIONAL SKILLS SPEAKING & LISTENING' 
    WHEN Code = '22880C' THEN 'E3 FUNCTIONAL SKILLS WRITING' 
    WHEN Code= '22879C' THEN 'E3 FUNCTIONAL SKILLS READING' 
    WHEN Code = 'VE102' THEN 'READING-LEVEL 1' 
    WHEN Code = 'VE103' THEN 'WRITING-LEVEL 1' 
    WHEN Code = '3748-01' THEN 'FUNCTIONAL SKILLS ENGLISH' 
    WHEN Code = 'VE101' THEN 'SPEAKING & LISTENING-LEVEL 1'
    END AS SubjectCode,
    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 = '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 = 'XA' THEN 'Not attended'
    END AS Grade
        FROM Table

How can achieve counts in my report? Please suggest

Solution

  • What do you want to count. you can use count expression. =count(Fields!Data.Value)