[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Error using SRS report parameter in WHERE clause with CASE/WHEN statement

Posted on 2008-02-05
4
Medium Priority
?
1,196 Views
Last Modified: 2010-04-21
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.

0
Comment
Question by:squirleegirl
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:PFrog
ID: 20825346
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
 

Author Comment

by:squirleegirl
ID: 20825496
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
 
LVL 18

Accepted Solution

by:
PFrog earned 200 total points
ID: 20825582
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
 

Author Closing Comment

by:squirleegirl
ID: 31428191
That fixed it!  Thank you very much for your quick, thorough answer!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Viewers will learn how the fundamental information of how to create a table.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question