Post reply

Dynamic SQL in an SSRS report's Dataset

  • 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.

  • 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

  • May 14, 2009 at 1:45 am

    #994305

    I am also facing same issue ...

    Has anyone solve this problem ?

    Thanks in advance.:cool:

  • 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'))

    View Siva Gurusamy's profile on LinkedIn

    "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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply