squirleegirl
asked on
Error using SRS report parameter in WHERE clause with CASE/WHEN statement
Greetings! I am fairly new to SRS Reporting and am just completely confused on how to resolve this issue.
We have several reports that use parameters which pull into the query such as (= @startdate or IN @organization) without no problems. All of the parameters I have used before are queried from the database except when using dates.
However, I am trying to build some non-queried parameters such as "Previous Week", "Current Month", etc. Currently, I have them set up with the label / value information of (Previous Week, 1) and (Current Month, 2). This parameter name is start.
In my SQL, I am calling this value as @start and in the WHERE clause is a date field that my query is filtering for. Generally I have been using BETWEEN @startdate and @enddate with both parameters being datetime data types.
What I would like to do is depending on the value of the @start parameter, the SQL would use a CASE/WHEN statement to determine the dates (instead of using specific dates via @startdate and @enddate). For example, if the user chose "Previous Week", the value is 1 and the case statement would use this code for my SQL date selection:
b.StartDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), - 8) AND DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, - 8))
If the selection was "Current Month" and the value 2, then it would be a different date selection in the WHERE statement.
Here is my code:
WHERE
CASE (@start)
WHEN 1 Then b.StartDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), - 8) AND DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, - 8))
When 2 Then b.StartDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GetDate()), 0) AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GetDate()) + 1, 0))
End
The error I get is "Must declar the scalar variable @start". I have looked at it so long that I am now thoroughly confused and am uncertain whether the problem is the parameter, the SQL, or what.
If anyone has any suggestions, they are greatly appreciated.
We have several reports that use parameters which pull into the query such as (= @startdate or IN @organization) without no problems. All of the parameters I have used before are queried from the database except when using dates.
However, I am trying to build some non-queried parameters such as "Previous Week", "Current Month", etc. Currently, I have them set up with the label / value information of (Previous Week, 1) and (Current Month, 2). This parameter name is start.
In my SQL, I am calling this value as @start and in the WHERE clause is a date field that my query is filtering for. Generally I have been using BETWEEN @startdate and @enddate with both parameters being datetime data types.
What I would like to do is depending on the value of the @start parameter, the SQL would use a CASE/WHEN statement to determine the dates (instead of using specific dates via @startdate and @enddate). For example, if the user chose "Previous Week", the value is 1 and the case statement would use this code for my SQL date selection:
b.StartDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), - 8) AND DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, - 8))
If the selection was "Current Month" and the value 2, then it would be a different date selection in the WHERE statement.
Here is my code:
WHERE
CASE (@start)
WHEN 1 Then b.StartDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), - 8) AND DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, - 8))
When 2 Then b.StartDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GetDate()), 0) AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GetDate()) + 1, 0))
End
The error I get is "Must declar the scalar variable @start". I have looked at it so long that I am now thoroughly confused and am uncertain whether the problem is the parameter, the SQL, or what.
If anyone has any suggestions, they are greatly appreciated.
ASKER
I tried that, but kept getting this error.
Error in WHERE clause near 'BETWEEN'.
Error in WHERE clause near ','.
Unable to parse query text.
Error in WHERE clause near 'BETWEEN'.
Error in WHERE clause near ','.
Unable to parse query text.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That fixed it! Thank you very much for your quick, thorough answer!
Click on the ellipses (...) to the right of the dataset name
On the parameters tab, make sure there is a line for @start. Its value should be =Parameters!Start.Value