@WendyZhang,

I got it working with the existing data and RDL! My brain was a little fried from the heat yesterday, so of course the answer popped into my head once I cooled down - you can do the required pre-aggregation in the SELECT statement out of the Excel file.

First I had to rename the Excel columns to the desired column names for the SQL statement. Then I modified the SELECT to specify the specific grouping field names and did SUMs for Net_Paid and Total_Discount. The SQL is as follows:

select Company, File_No, Claim_No, File_Year, Deductible_Amount, SUM(Net_Paid) As Sum_Net_Paid, SUM(Total_Discount) As Sum_Total_Discount

from [Sheet1$]

GROUP BY Company, File_No, Claim_No, File_Year, Deductible_Amount

Then I left the group in place in the report but deleted all the Group By fields so every group row was effectively a detail row. The group is still needed so the group totals can be calculated (i.e. it tells the paginated report that the scope for the totals row is the whole group).

Finally, I modified the detail row-level expression to get rid of the inline Sums, and then wrapped the Totals version of the expression in a Sum(). With this simpler solution design, the scope names were not needed in the expressions.

The final Totals expression looks like this (spaced out for my sanity, but the viewer is removing the indenting):

=Sum( IIf(

IIf( (Fields!Deductible_Amount.Value-Fields!Sum_Total_Discount.Value)>Fields!Sum_Net_Paid.Value,

Fields!Sum_Net_Paid.Value,

(Fields!Deductible_Amount.Value-Fields!Sum_Total_Discount.Value)

)

<=0, 0,

IIf( (Fields!Deductible_Amount.Value-Fields!Sum_Total_Discount.Value)>Fields!Sum_Net_Paid.Value,

Fields!Sum_Net_Paid.Value,

(Fields!Deductible_Amount.Value-Fields!Sum_Total_Discount.Value)

)

)

)

And the report generates the expected values for the totals:

Here are the V2 modified Excel and RDL files:

scenario.xlsx (14.1 KB)

Wendyâ€™s Test Scenario.rdl (44.3 KB)