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
SQLSearcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
Hi,

try this

IF @Shift IS NULL 

BEGIN
                                                      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;

END

ELSE

BEGIN
                                                      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; 

END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSearcherAuthor Commented:
Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.