Report Builder Total sum calculation on a column

Hi rnola16 ,

You can't sum across multiple datasets in Report Builder - it's a known pain point that Microsoft never bothered to fix properly.

Real solutions that actually work:

Fix it at the source: Combine your datasets into one query with UNION or JOIN. Then you can use normal SUM() without the headache.

Custom code hack: Add this to Report Properties → Code:

Dim total As Decimal = 0
Public Function AddUp(val As Decimal) As Decimal
    total = total + val
    Return val
End Function
Public Function GetTotal() As Decimal
    Return total
End Function

In your cells: =Code.AddUp(your IIF mess) In total: =Code.GetTotal()

It's ugly but works.

Why this happens: Report Builder gets confused when you try to aggregate expressions that pull from different datasets. Each dataset has its own scope and they don't play nice together.

Honestly, the multi-dataset approach in Report Builder is more trouble than it's worth. If you can restructure to use one dataset, do it. You'll save yourself tons of headaches.

What's your actual data scenario? Maybe there's a simpler way to structure this.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.