SSRS Totals Calculation in Tablix

Kinetic ERP
1

I’m having trouble getting the total on a table that’s using row groups and displaying minimum values from a dataset: Min(Fields!QuoteHed_QuoteAmt.Value).

Sample data:
Record | Group | Value
1 | A | 100
2 | A | 50
3 | A | 95
4 | B | 0
5 | B | 25
6 | B | 30

Table I’m trying to create:
Group | Min Value
A | 50
B | 0
Total: 50

In report builder, when I right click → add totals to the table, it adds another Min(Fields!QuoteHed_QuoteAmt.Value) expression and the total comes out to 0. Sum(Min(Fields!QuoteHed_QuoteAmt.Value)) produces 0 as well. I understand what’s going on but don’t know how to fix it.

2

what is the field you are grouping on?

3

It’s a UD field on QuoteHed: Fields!QuoteHed_OpportunityID_c.Value

4

Sum the report field itself.

Sum(ReportItems!Textbox1.Value) or something like that

1 Like
5

When I try that, the report will not save and gives me this error:

The Value expression for the textrun ‘QuoteHed_QuoteAmt5.Paragraphs[0].TextRuns[0]’ uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

Here’s the total expression: =sum(ReportItems!MinAmount.Value)
And where I named that report field/textbox:
image

6

Can you show me your group names?

image
image1598×113 3.15 KB

7

TOTAL Field Expression: =Sum(Min(Fields!QuoteHed_QuoteAmt.Value,“YOUR GROUP NAME HERE”))

JOBNUM1 is my group name in my picture above. Your group name is whatever your detail group is in the Row Groups section of your report.

1 Like
8

That was it!! Thank so much, Utah!