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;
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER