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?
 
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
 
SQLSearcherAuthor Commented:
Thank you for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.