SSRS – Using a Case Statement in a SQL Where Clause

0
votes

below is my code, I wish to make a drop down list in ssrs using the case statement options but I don't know how to parametrize case statements. 

select c.EntityID, c.FirstName, c.LastName, (case ap.PlacementVerificationMethod 
when  107 then 'contact center'
when 1 then 'work number/3rd party verification'
 when 101 then 'Placement call'
 when 102  then 'walk in/Self report'
 when 103 then 'Email'
 when 104 then 'Employer Report'
 when 105 then 'In person with participant'
 when 106 then 'In person with employer'
else
'unknown' end) as 'Placement method', wh.JobTitle, ap.PlacementDate, p.ProviderName Employer, u.UserName Placementby  from 
    AssessEmploymentPlacement ap
    join
    users u
    on
 AP.PlacementBy = U.EntityID
    join
    WorkHistory wh
    on
    WH.WorkHistoryID = AP.WorkHistoryID
    join
    client c
    on
    wh.ClientID =c.EntityID
    join
    provider p
    on
    WH.ProviderID = P.EntityID
    join
    assessment a
    on
    AP.AssessmentID = A.AssessmentID
where ap.PlacementDate between @placementbegindate and @placementenddate


sample table for AssessEmployementPlacement


 @ChrisLätta  sample table for AssessEmploymentPlacement

 AssessmentID  client    placementVerificationmethod

   1234        sam           null
   4567        james         101
   2234        don           102
   5364        manny        107
   6595        jon           null
   6598        woe           104
   5496        kie            105
1
sql-serverreporting-servicesparameterscasessrs-2012
Is there a table of placement verification methods? You can base your parameter options on a query of that. – Chris Latta
Can you explain about you problem or add some sample data and expect output – Chanom First
Do you want to just use the values in your case statement as options on a parameter in SSRS? – BishNaboB
@ChrisLätta I added a sample table for assessemploymentplacement on the post. – Henry Amadi
@BishNaboB yes but my main report is a count that has all the label options and their total.so I want to be able to click on each label e.g clicking on 'call center' and it should take me to all placements that were made by call centers. – Henry Amadi

1 Answers

0
votes

If I understand correctly, there are a couple of ways to accomplish this:

You can create a parameter in SSRS with the values you wish to be able to filter by specified. This is useful if there are a small number of never changing values to use. This parameter can then be used to filter the main query in the where clause either by using

where ap.PlacementVerificationMethod = @Parameter

or 

where ap.PlacementVerificationMethod  in (@Parameter)

The second one is used for multiple selection parameters.

You can also use grouping in the main report body in a tablix. If you group by the field "Placement Method" you can then set the visibility for the subgroup (probably grouped on EntityID) to be toggled by the Placement Method text box. This allows you to expand the group to show more details when wanted.