none
Error executing query in SSRS

    Question

  • I try to use multi-value parameters in SSRS, when I try to run the report with more than 1 value with the next query:

    select TestPlanName, AreaPath, IterationPath, TestSuiteSuitePath, ResultTestCaseId,  ResultTest, ResultOutcome, BuildName, ConfigurationName  
    from 
    (select row_number() over(partition by TestCaseId
     order by ResultDate
     Desc) as ranc,* 
    from [tfs_warehouse].dbo.TestResultView (nolock) a
    where 
     ResultOutcome <> 'Never Run' and (@BuildName = BuildName or @BuildName = null) and (@TestPlanName = TestPlanName or @BuildName = null)
    and TestSuiteSuiteName is not null )
    a where ranc=1
    group by TestPlanName, AreaPath, IterationPath, TestSuiteSuitePath, ResultTestCaseId, ResultOutcome, TestRunTitle, TestRunState, ResultTest, BuildName, ConfigurationName

    I receive the next error: 

    an expression of non-boolean type specified in a context where a condition is expected near ' '

    What I'm doing wrong?



     
    Sunday, December 01, 2013 2:26 PM

Answers

  • The problem is you are using = to compare a column to multiple values. Switch it to use IN so (@BuildName = BuildName or @BuildName = null) becomes  and (BuildName IN (@BuildName) or @BuildName = null) and (@TestPlanName = TestPlanName or @BuildName = null) becomes (TestPlanName IN (@TestPlanName) or @BuildName = null). This could also be phrased

    (@BuildName = null OR (TestPlanName IN (@TestPlanName) AND BuildName IN (@BuildName))).


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, December 02, 2013 6:24 PM

All replies

  • Hi,

    Thanks for your post.

    I tried to run the query you provided above in SQL Server, but I didn't get any error.

    Would you please post all detail error message here? And please provide the version of TFS and SQL Server you are using.

    Best regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 02, 2013 7:40 AM
    Moderator
  • The problem is you are using = to compare a column to multiple values. Switch it to use IN so (@BuildName = BuildName or @BuildName = null) becomes  and (BuildName IN (@BuildName) or @BuildName = null) and (@TestPlanName = TestPlanName or @BuildName = null) becomes (TestPlanName IN (@TestPlanName) or @BuildName = null). This could also be phrased

    (@BuildName = null OR (TestPlanName IN (@TestPlanName) AND BuildName IN (@BuildName))).


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, December 02, 2013 6:24 PM
  • Hi Oren,

    Is there any update? Have you tried Tim's workaround?

    Best regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 03, 2013 6:09 AM
    Moderator