Search code examples
reporting-servicescasereportparameter

Report Parameter in combination with CASE

I'm trying to query employees and their departments. Imployees have a department key, but do not fall under that department per sé. For example secrataries that work for the director should not fall under her own department(ADVIES & BEHEER) but under the director(Directie)

But when I choose the department with report parameter(@Afdeling).

AND ORG.Afdeling = @Afdeling

I only want the choosen department. But it returns all eployees and their CASE assigned departments because of the departmentkey. When I CASE the department key It still returns the employees under CASE assigned department. But I only want the choosen department. For example:

enter image description here

Can anyone help?

Thank you

SELECT CASE ORG.Afdeling 
        WHEN 'ADVIES & BEHEER' 
        THEN 
            CASE mdw.Naam  
            WHEN 'Verhaag-Vanmaris, APM' 
            THEN 'Directie'
            WHEN 'Bouten, MJC' 
            THEN 'Directie' 
            WHEN 'Vallen-Vullers, GMS' 
            THEN 'Burgemeester en wethouders'
            END
        ELSE UPPER(LEFT(ORG.Afdeling, 1)) + LOWER(RIGHT(ORG.Afdeling, LEN(ORG.Afdeling) - 1)) 
        END AS Afdeling
FROM x
WHERE YEAR(CTE.Datum) = @Jaar
AND ORG.Afdeling = @Afdeling
Solution

  • The solution basically came down to:

    WITH CTE AS(
SELECT X.SUM(CALLS), D.Dep, EMPL.empl
FROM X
LEFT JOIN EMPL
ON X.DESPHONE = EMPL.PHONE
LEFT JOIN DEPT AS D
ON EMPL.EMP_ID = D.EMP_ID
WHERE D.Dep = @Department
GROUP BY D.Dep, EMPL.empl
)

SELECT *
FROM CTE