SSRS IIF expression based on condition

• mcfarlandparkway

  SSCertifiable

  Points: 7699

  More actions

  February 6, 2014 at 8:33 am

  #304088

  I have a table in SSRS where I filter the table with type id 34,35,36 -and I will get sum amount of all these three id's

  I have a requirement in report that one field should be in form of percentage I can go to genral and make number as percentage that's fine

  But expression I am looking in the field is if typeid = 35 I have to take that amount(sum(invamnt field)) which is coming from database.which is divided by total amount sum amount of all the id's (34+35+36)

  any ways I will get sum amount by filtering table

  Note:In datatbase with typeid = 35 we may have many number of records so that's why I am using sum.(invamnt)field

  My question is how can I get expression where typeid = 35 amount at the top/Sumamount*100

  Any help..I am new to SSRS.

  We can use IIF condition but not sure how to put in a right format.

• lynns

  Say Hey Kid

  Points: 695

  More actions

  February 6, 2014 at 9:08 am

  #1686657

  I have had success including the grand total in the dataset definition.

  SELECT typeid

  , invamt

  , (SELECT SUM(invamt) from SomeTable) AS GrandTotal

  FROM SomeTable

  In this case the expression would be

  =IIF(Fields!invamt.Value = 35, SUM(Fields!invamt.Value)/max(Fields!GrandTotal.Value) * 100, Desired result if not type 35)

  I am not sure it is the most efficient way, but I haven't found another solution yet.