CODEWITHSUNDEEP
sql-servert-sqlif-statementreporting-services
Anthony Sims
Anthony Sims

Reputation: 171

Implementing if/else logic in SSRS reports

Im in the process of creating an SSRS report (which Im fairly new at), and my query is written in t-sql using SQL Server 2008.

The query is an IF EXISTS/ELSE statement that basically reads - 

--Declare @variable varchar(10) = 'value'
IF EXISTS (SELECT col1 
           FROM t1 
           WHERE t1.col1 = @variable
          )
SELECT col1 
FROM t1 
WHERE t1.col1 = @variable

ELSE

SELECT col1, col2
FROM t2
WHERE t2.col1 = @variable

This runs fine in Mgmt studio, but when I try to make a tablix in SSRS, columns from t2 are left out of the dataset - which makes sense, because the entire query has to run to determine which result set is returned, and that can't happen in ssrs until the end user assigns a value to @variable, so the dataset [from the first result set] is shown by default.

That being said, how can I implement this type of logic in a report?

Upvotes: 1

Views: 1212

Answers (1)

Lucky
Lucky

Reputation: 4493

You're going to want to add a parameter to your report. In the left bar at the top left is the parameter folder. You can right click that an add a parameter.

Your parameter is passed into your query just like @variable. Replace @variable with @yourparametername. When you execute the query in the query designer, a dialog will pop up and allow you to enter a parameter so the query can execute. This will allow SSRS to read the columns and show them in the dataset.

Upvotes: 2

Related Questions