0

I did not hide the entire rows. I hid just the duplicated values in one column and I need to sum that same column, excluding the hidden values. Thank you very much.

The field having duplicated values that I wanted to hide is SpaceArea This is how I hid the duplicated values from rows on the SpaceArea column: 

=IIF(Fields!SpaceID.Value = Previous(Fields!SpaceID.Value),True, False)

Then I need to SUM all the SpaceArea excluding the hidden values.

Once I tried to use the same Show/Hide logic to the Sum expression as per another post of yours, I got an error message. This is what I tried:

=Sum(IIF(Fields!SpaceID.Value = Previous(Fields!SpaceID.Value), Nothing, Fields!SpaceArea.Value))

==> then I got this error message:

previous functions cannot be specified as nested aggregates.

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
Tubi
  • 23
  • 8

1 Answers1

1

You can use row_number() in your SQL to highlight the duplicates.

row_number() over (partition by SpaceID order by (select null)) as [RowNumber]

This will give you an arbitrary numbering to the duplicate rows.

Then in SSRS you can use..

sum(iif(Fields!RowNumber.Value = 1, Fields!SpaceArea.Value, Nothing)) to only sum the first instance of each duplicate.

BishNaboB
  • 1,047
  • 1
  • 12
  • 25
  • Hi, thank you so much for taking time to respond. I did try your suggestion, but I got an error message at the (select null), the error message said "ORA-00923: FROM keyword not found where expected" – Tubi May 04 '17 at 03:52
  • Actually I think I got it. It worked. Thank you so very much for your help :) – Tubi May 04 '17 at 04:01