Avatar of SQLSearcher
SQLSearcher
 asked on

SQL SSRS run a query depending on what the parameter is set to.

Hello Experts Exchange
I have a SSRS report that I want to run different query depending on what the Parameter is set to.

The Parameter is called Shift it can be Blue, Red or Nothing(Null).

I think I want to be able to run a If statement query, but I can not get the syntax right.

What I have at the moment is this;

IF @Shift IS NULL 
                                                      SELECT     [Level 3], SUM([Total Lost Hours]) AS [Total Lost Hours]
                                                      FROM          MachineLosses_View
                                                      WHERE      ([Cost Centre] IN ('Htr01 (581)', 'Htr04 (584)', 'Htr05 (585)', 'Htr3 (583)', 'Htr6 (586)')) AND 
                                                                             [date] BETWEEN @StartDate AND @EndDate AND [Area/Line/Machine] = @Area
                                                      GROUP BY [Level 3]
                                                      ORDER BY [Total Lost Hours] DESC; 
ELSE
                                                      SELECT     [Level 3], SUM([Total Lost Hours]) AS [Total Lost Hours]
                                                      FROM         MachineLosses_View
                                                      WHERE      ([Cost Centre] IN ('Htr01 (581)', 'Htr04 (584)', 'Htr05 (585)', 'Htr3 (583)', 'Htr6 (586)')) AND 
                                                     [date] BETWEEN @StartDate AND @EndDate AND [Area/Line/Machine] = @AreaAND Shift = @Shift
                                                     GROUP BY [Level 3]
                                                    ORDER BY [Total Lost Hours] DESC; 
GO

Open in new window


I get the following error message.

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset 'Dataset1'
Incorrect syntax near 'Go'.

How do I resolve?

Regards

SQLSearcher
SSRSMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
SQLSearcher

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Lokesh B R

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SQLSearcher

ASKER
Thank you for your help.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy