SSRS: Using exists condition in sql WHERE clause while passing values from SSRS Parameter

Table : incident ---------------- incident_id usr_id item_id Inc_Date 10059926 191 61006 8-22-2015 10054444 222 3232 6-7-2015 Table: act_reg -------------- act_reg_id act_type_id incident_id usr_id act_type_sc 454244 1 10059926 191 ASSIGN 471938 115 10059926 191 TRAVEL TIME 473379 40 10059926 191 FOLLOW UP 477652 115 10059926 191 TRAVEL TIME 489091 504 10059926 191 ADD_ATTCHMNTS 477653 504 10054444 222 ADD_ATTCHMNTS

Parameter:  @attach (value=1, Label=Yes & Value=0, Label=No)
 Result (While I am selecting 'Yes' in dropdown)
 ----------------------------------------------
 incident_id   usr_id    item_id  
 10059926       191      61006    
 10054444       222       3232

SELECT  incident.incident_id,incident.usr_id,incident.item_id
FROM  incident 
where exists (How i can write query here to check the  act_type_sc=ADD_ATTCHMNTS is exists  )


September 10th, 2015 12:28am

Hi Salman,

Try this

SELECT  incident.incident_id,incident.usr_id,incident.item_id
FROM  incident 
where usr_id    IN  ( SELECT usr_id    FROM act_reg WHERE act_type_sc='ADD_ATTCHMNTS')

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 12:34am

@Milan Das :  Its check only the users. I need to select all the incident With or without Attachemt when i select "Yes" or "No" in Dropdown list (Parameter)
September 10th, 2015 2:58pm

It is not clear what do you want to do with your parameter.

SELECT  i.incident_id,i.usr_id,i.item_id,
case when exists(select 1 from dbo.act_reg a
where a.incident_id = i.incident_id and a.usr_id = i.usr_id
and act_type_sc = 'ADD_ATTACHMNTS') then 1 else 0 end as [AttachmentsExist]
FROM  incident i

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 3:27pm

If you want to select only users/incidents that have attachments in case your parameter is Yes and select everyone if parameter is No, then

SELECT  i.incident_id,i.usr_id,i.item_id
FROM  incident i
where (@Param = 1 and exists(select 1 from dbo.act_reg a
where a.incident_id = i.incident_id and a.usr_id = i.usr_id
and act_type_sc = 'ADD_ATTACHMNTS')) or @Param = 0 OPTION (Recompile)
September 10th, 2015 3:31pm

Dear Naomi:

My Query is if i select Yes in Dropdown (Value=1) then it will display all data with having "ADD_ATTACHMENT' value in Act_reg table , if it "No" (Value=0) then it will display data without "ADD_ATTACHMENT' value.

I think its clear now

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 3:59pm

Then use slight modification of my query, e.g.

SELECT  i.incident_id,i.usr_id,i.item_id
FROM  incident i
where (@Param=1 and exists(select 1 from dbo.act_reg a
where a.incident_id = i.incident_id and a.usr_id = i.usr_id
and act_type_sc = 'ADD_ATTACHMNTS')) or (@Param = 0   
AND NOT EXISTS (select 1 from dbo.act_reg a
where a.incident_id = i.incident_id and a.usr_id = i.usr_id
and act_type_sc = 'ADD_ATTACHMNTS'))

OPTION (Recompile)

September 10th, 2015 5:52pm

Hi Salmanpc, 

According to your description, you want to filter the data based on the parameter value, right? 

In Reporting Services, if we want to return the different result based on the report parameters, we can add a Filter to the dataset or add @ReportParameter to the dataset query. In your scenario, if the parameter value "Yes" is selected, the report will display the records which act_type_sc field value is "ADD_ATTCHMNTS". If "No" is selected, all records will be displayed except the field value is "ADD_ATTCHMNTS".

For your requirement, we have tested in our local environment, and the filter works properly.We can use Replace() and IIf() function to get the expected result.  Please refer to the following filter: 

  1. Create a table and drag the field into the table. 
  2. Right-click the table and go to Filters tab. The filter is like below: 

The result is like below: 

If you have any other question, please feel free to ask. 

Regards, 
Shrek Li

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 1:56am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics