Search code examples
reporting-servicesssrs-2008ssrs-2012ssrs-tablix

How to use multiple conditions in IIF expressions in ssrs to return a value

I am a beginner in SSRS and maybe this may sound easy, I have a dataset like below.

enter image description here

What I want to achieve is:

enter image description here

For column region and country I used a Row Groups. For column New lead I used this expression =IIF(Fields!TextA.Value=Fields!TextB.Value AND Fields!Subject.Value = "New Lead" AND NOT(Fields!Category.Value = "Closed"), Fields!Total.Value + 1, nothing) to get Total value for each Subject and just change the harcoded value for Fields!Subject.Value for Column Contract, Qualify but the value is not populated. I Checked there is no typo for hardcoded value. I can achieved this by using matrix but the columns arrangement for new Lead, Contract and Qualify is not like I wanted.

Solution

  • It sounds like you're thinking programmatically and adding the total one by one. 

    Fields!Total.Value + 1

    You want to SUM the Totals if it matches the given Subject (and A = B and Not Closed). 

    =SUM(
    IIF(Fields!TextA.Value = Fields!TextB.Value AND Fields!Subject.Value = "New Lead" AND NOT(Fields!Category.Value = "Closed")
        , Fields!Total.Value, 0))

    Are TextA and TextB always the same? If so, you can remove that part.

    You're probably also going to need to Group By your Area to make it look like your mock-up - otherwise Thailand would sum up Bangkok and Phuket.