Dynamic SQL in an SSRS report's Dataset

• eheraux

  Old Hand

  Points: 393

  More actions

  December 28, 2007 at 8:10 am

  #180096

  In Reporting Svces 2005, I'm trying to adjust a WHERE clause dynamically based on a user-passed parameter. I keep getting errors with the syntax no matter what I try, and I've pulled too much of my hair out at this point, and I need another set of eyes.

  I have a total of three parameters for the user to input, @PhysUnit and @Categories, which are both multi-value parameters, and @Post, which only allows one entry and is the one that I need too look at to determine part of my dataset's WHERE clause.

  The sql I have in the dataset right now is:

  *******************************************************

  ="SELECT *

  FROM vw_RoosterFldTotals

  WHERE Cat IN (@Categories) AND Unit IN(@PhysUnit)"

  & IIF(Parameters!Post.Value="Vacant Only"," AND [Staff]='Vacant' ",IF(Parameters!Post.Value="Exclude Vacancies"," AND [Staff]<>'Vacant' "," "))

  *********************************************************

  So I'm trying to dynamically append an AND condition to my existing WHERE clause.

  All I'm getting for an error message is a .NetSQlClient Data Provider message saying "Incorrect syntax Line 1 near "=". I've tried eliminating the equals sign, and I've tried changing all quotes to single quotes. Nothing's helping me. I've even tried putting underscores at the end of each line to suggest I'm wrapping to the next line. That didn't help, either.

  And to make matters worse, Visual Studio is changing my double quotes to square brackets after giving me each error message.

  Any ideas?

  Thanks much,

  -Ed H.

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  December 31, 2007 at 5:36 am

  #763672

  I 've built a little test report with this query and got the following error:

  [rsCompilerErrorInExpression] The CommandText expression for the query ‘Test’ contains an error: [BC30201] Expressie wordt verwacht. (An expression is expected, PB)

  Changing the second IF function to IIF solved the problem.

  Peter

• infogetrana-796814

  Valued Member

  Points: 69

  More actions

  May 14, 2009 at 1:45 am

  #994305

  I am also facing same issue ...

  Has anyone solve this problem ?

  Thanks in advance.:cool:

• Siva Gurusamy

  SSCrazy

  Points: 2711

  More actions

  May 15, 2009 at 7:22 am

  #995304

  Try just what is given below. no =, no ", no nothing, just the one below.

  SELECT * FROM vw_RoosterFldTotals

  WHERE Cat IN (@Categories) AND Unit IN(@PhysUnit)

  and

  ((@Post = 'Vacant Only' and Staff = 'Vacant') or

  (@Post = 'Exclude Vacancies' and Staff 'Vacant'))

  

  "Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation