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.

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

PFrogCommented:
Look in the parameters of the query.

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
0
squirleegirlAuthor Commented:
I tried that, but kept getting this error.

Error in WHERE clause near 'BETWEEN'.
Error in WHERE clause near ','.
Unable to parse query text.
0
PFrogCommented:
Ok, that's fixed the parameter problem, now we just need to restructure the clause to remove the CASE statement. It can't actually be used the way you want. Your best option is to replace it with something like this

WHERE (@start = 1
            AND b.StartDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), - 8) AND DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, - 8))
       ) OR (
      @Start = 2
            AND b.StartDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GetDate()), 0) AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GetDate()) + 1, 0))
      )
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
squirleegirlAuthor Commented:
That fixed it!  Thank you very much for your quick, thorough answer!
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
Microsoft SQL Server

From novice to tech pro — start learning today.