Link to home
Create AccountLog in
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
ASKER CERTIFIED SOLUTION
Avatar of Lokesh B R
Lokesh B R
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of SQLSearcher
SQLSearcher

ASKER

Thank you for your help.