locked
Use Iif function for underlaying row RRS feed

  • Question

  • Hey Guys,

    I have a table, that has three groups:

    1. Invoice Method
      2. Customer
    3. Job

    Now, on row three, I only want to sum the budget for jobs, where the Job task's field 'Charge' is yes (boolean). 

    A job has for example 5 rows. Each Jobtask row has a budget of 100. Total is € 500. Only three rows have Charge=yes.
    Now if I filter for Charge=Yes, I only have € 300. 

    For as far as I can see, I can choose to put Charge Field in my Query...

    How can i do this? For as far as I can imagine, I am seeing the Job lines here, not the jobtasks.

    Please help me, I am stuck... :-(


    • Edited by THensbroek Friday, April 27, 2012 7:56 AM
    Friday, April 27, 2012 7:51 AM

Answers

  • Hi there

    You can expression like this.

    =sum(iif(Fields!Active.Value="yes",Fields!totalval.Value,0))

    I am putting screenshot for your help

    Many thanks

    Syed Qazafi

    Friday, April 27, 2012 8:29 AM

All replies

  • Hi there

    You can expression like this.

    =sum(iif(Fields!Active.Value="yes",Fields!totalval.Value,0))

    I am putting screenshot for your help

    Many thanks

    Syed Qazafi

    Friday, April 27, 2012 8:29 AM
  • Again, thanks. I have one more question.

    I now have the sum in my rows as I wanted, but it sometimes give an #Error.

    It seems that sometimes this 'Charge' field is not 'true' of 'false' but sometimes it is 'Unknown'.

    Any idea how to have a workaround for this?

    My expressions are now as following:

    =Sum(Iif(Fields!Charge.Value <> true,Fields!Cost_Budget.Value,0))

    and

    =Sum(Iif(Fields!Charge.Value <> false,Fields!Cost_Budget.Value,0))

    Friday, April 27, 2012 10:43 AM
  •  

    Hi there

    Please Switch it to other way around instead of putting <>  please put = sign in this way it will ignore the unknown part. I hope this will help

    Many thanks

    Syed QAzafi

    so expression might look like this

    =Sum(Iif(Fields!Charge.Value = true,Fields!Cost_Budget.Value,0))

    and

    =Sum(Iif(Fields!Charge.Value = false,Fields!Cost_Budget.Value,0))

    Saturday, April 28, 2012 2:14 AM