CODEWITHSUNDEEP
sqlsql-serverreporting-servicesparameterscalculated-columns
NonProgrammer
NonProgrammer

Reputation: 1387

How to use calculated column values as a parameter for SSRS report

I might have asked this question wrong, but here's what I am trying to do:

I have following select statement in my SP. I will be using this statement in SSRS for users to pass on a value via parameter. However, my problem is that the only parameter I can think of is a Status parameter. I made a calculated column using a case statement.

I want the users in SSRS to pick a value: Posted, Not Posted or All.

How would I do that with a calculated column and statuses?

Note: SIS.cs_id_number and PC.css_id is a Integer value.

BEGIN

SELECT PC.css_id as [CSS ID]
 , convert(VARCHAR, PC.birth_date, 101) AS DOB
 , PC.first_name as [First Name]
 , PC.last_name as [Last Name]
 , ssn as [SSN]
 , substring(PC.css_record, CHARINDEX('<AddressLine>', PC.css_record) + 22,CHARINDEX('</AddressLine>', PC.css_record)- CHARINDEX('<AddressLine>', PC.css_record) - 25) as [Street]
 , City
 , substring(PC.css_record, CHARINDEX('<StateProvinceCode>', PC.css_record) + 19 , 2 ) + ' - ' + substring(PC.css_record, CHARINDEX('<PostalCode>', PC.css_record) + 12 , 5 ) AS [State & ZipCode]
 , **[Status] = CASE WHEN SIS.cs_id_number = PC.css_id THEN 'POSTED' ELSE 'Not Posted: Student may not be in PF Database yet or there might be a mismatch' end**


FROM
profile_convert PC
left JOIN say_im_stu SIS
    ON SIS.cs_id_number = PC.css_id
        order by css_id

End

Upvotes: 2

Views: 2153

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

Based on your description, you can try adding a WHERE clause similar to this:

...
where (@Status = 'All' -- All rows regardless of status
  or (@Status = 'Posted' and SIS.cs_id_number = PC.css_id)
  or (@Status = 'Not Posted' and (SIS.cs_id_number <> PC.css_id or SIS.cs_id_number is null)))

I've assumed you're passing a parameter @Status to the report, which can be one of the following values:

  • Posted
  • Not Posted
  • All

Upvotes: 2

Related Questions