0

I have a data grid using Matrix that contains 3 columns (PADate, LineOfBusiness and Total). PA date is the row group and the Line of Business is the column group and I'm adding the total records in the line of Business. The line of business has multiple values (MA, PA and LC).

The goal is to exclude the total of line of business "LC" in the total, but I'm getting errors. I tried 

=IIF(Fields!LineofBusiness.value <> "LC", SUM(Fields!Total.Value, Nothing)

but I get an error.

In the screen shot below, If there's a value in L-CTRx column it should subtract that value in Totals. Instead of 21990 it should be 21998.

enter image description here

Arsee
  • 651
  • 2
  • 11
  • 36

1 Answers1

0

You need to put the IIF inside the SUM:

=SUM(IIF(Fields!LineofBusiness.value <>"LC", Fields!Total.Value, CDEC(0) )

The error was due to the incorrect syntax for the 

SUM(Fields!Total.Value, Nothing)

SUM only takes one argument. You should have the red squiggly line under to indicate that there's something wrong.

It would work if you used this but would give an incorrect total:

=IIF(Fields!LineofBusiness.value <>"LC", SUM(Fields!Total.Value), Nothing)
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • I used CDEC(0) because I am **ass**u**me**ing that `Fields!Total.Value` is a dollar amount and not an integer. If it's an **INT*, remove the CDEC. – Hannover Fist Jul 29 '16 at 22:21
  • Thanks for the reply. I need to subtract the dynamic matric column to the total column that is not part of the matrix. So if the Line of Business is not LC then add the matrix column ($$) amount in the total; otherwise, subtract the matrix column where line of business is LC to the total ($$). – Arsee Aug 01 '16 at 15:53
  • I think it should work the way you want - it will **SUM** all values (Fields!Total.Value) except if the Line of Business = **LC**. – Hannover Fist Aug 01 '16 at 17:10
  • I added =IIF(Fields!LineOfBusiness.Value <> "L-CTRx", SUM(Fields!PAReceivdCaseCount.Value),SUM(Fields!PAReceivdCaseCount.Value)-Fields!PAReceivdCaseCount.Value) as an expression in Total column, but I'm still getting 21,990 instead of 21,988. – Arsee Aug 02 '16 at 18:19
  • Your **SUM** needs to be on the **outside** of the expression otherwise it looks at the **first** LineOfBusiness, not **each** individually. `=SUM(IIF(Fields!LineOfBusiness.value <> "LC", Fields!PAReceivdCaseCount.Value, CDEC(0) )` – Hannover Fist Aug 02 '16 at 18:38
  • I modified the column TOTAL expression to =SUM(IIF(Fields!LineOfBusiness.Value <> "L-CTRx", Fields!PAReceivdCaseCount.Value,Nothing)), but I'm getting warning message "[rsAggregateOfMixedDataTypes] The Value expression for the textrun ‘Total.Paragraphs[0].TextRuns[0]’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type." Thanks – Arsee Aug 03 '16 at 22:02