SSRS - exclude hidden values from sum - No SQL To

问题:

hope use can help me with this:

My dataset uses a pre-built module therefore I cannot use SQL adding a row_number() for the field.

I need to be able to exclude hidden values from SUM as follows:

1. In the field I used this expression:
   IIF(Fields!SpaceID.Value = Previous(Fields!SpaceID.Value), 0, Fields!SpaceArea.Value)

2. In the Textbox Property for SpaceArea -> Visibility -> show or hide of the field, I also used this expression for hidden: =IIF(Fields!SpaceID.Value = Previous(Fields!SpaceID.Value),True, False)

3. And the SUM:
   =Sum(IIF(Fields!SpaceID.Value = Previous(Fields!SpaceID.Value), Fields!SpaceArea.Value, Nothing))

Then I got this error message:

previous functions cannot be specified as nested aggregates.

Thank you very much in advance

EDIT 1:

     SpaceID    SpaceArea   RowNumber 
     A          123         1
     A          123         2
     A          123         3
     B          12          1
     B          12          2
     C          1           1

Notes:

1. SpaceID was grouped as SpaceID_Group.
2. The Expected Sum for SpaceArea = (123 + 12 +1).
3. Cannot use row_number() over (partition by ... ) because there is no SQL. The query was designed with XML, not SQL.

I managed to create another column in the tablix with the row value
=RowNumber("SpaceID_Group").
But I do not know how to refer it to my Sum. which, if I had SQL , it will look like:
sum(iif(Fields!RowNumber.Value = 1, Fields!SpaceArea.Value, Nothing))

回答1:

1. Add the following custom code to the report (the return value is for debugging)

Public Shared MyTotal As Integer Function AddTotal(ByVal t As Integer) As Integer MyTotal = MyTotal + t Return Mytotal End Function

2. Group on the SpaceId (as you already did) and hide it.

3. Add the expression Code.AddTotal(Fields!SpaceArea.Value) to the SpaceId group

4. Add the expression Code.MyTotal to field summary. In some versions like SSRS 2005, due to execution order you may notice that the calculation returns zero. In this case add a field with the expression bellow the table