How to sum only one of repeated values from joined data in RDLC

Question

I'm not sure if SSRS is dumb, or I am (I'm leaning towards both).

I have a dataset that (as a result of joins etc) has some columns with the same values duplicated across every row (fairly standard database stuff):

rid    cnt    bid   flg1   flg2
-------------------------------
4      2882   1     17     3
5      2784   1     17     3
6      1293   1     17     3
18     9288   2     4      9
20     762    2     4      9

Reporting based on cnt is straightforward enough. I can also make a tablix that shows the following:

bid    flg1   flg2
------------------
1      17     3
2      4      9

(Where the tablix is grouped by Fields!bid.Value and the columns are just Fields!flg1.Value and Fields!flg2.Value respectively.)

What I can't figure out is how to display the sum of these values -- specifically I want to show that the sum of flg1 is 21 and the sum of flg2 is 12 -- not the sum of every row in the dataset (counting each value more than once).

(Note that I'm not looking for a sum of distinct values, as they may not be unique. I want a sum of one value from each bid group, because it's from a table join so they will always have the same value.)

If possible, I'd also like to be able to do a similar calculation at the top level of the report (not in any tablix); although I'd settle for hiding the detail row if that's the only way.

Obviously, Sum(Fields!flg1.Value) isn't the answer, as this either returns 51 (if on the first row inside the group) or 59 (if outside it). I also tried Sum(Fields!flg1.Value, "bid") but this wasn't considered a valid scope. I also tried Sum(First(Fields!flg1.Value, "bid")) but apparently you're not allowed to sum first values for some weird reason (and may have had the same scope problem anyway).

Using Sum(Max(Fields!flg1.Value, "bid")) does work, but feels wrong. Is there a better way to do this?

(Related: is there a good way to save the result of that calculation so that I can later also show a Sum of those totals without an even hairier expression?)

Answer 1

There are two basic ways to do this.

1. Do what you have already done (Sum(Max(Fields!flg1.Value, "bid")))

2. Sum the rendered values. To do this check the name of the cell containing the data you want (check it's properties) and then use something like =SUM(ReportItems!flg1.Value) where flg1 is the name of the textbox, which is not necessarily always the same name as the field.