Report Builder Total sum calculation on a column

[rnola16]

I have a column in a tablix where the calculation is performed using multiple datasets using IIF function. I now have to create a Total Sum of that column. When I use the =Sum(IIF. (IIF(IIF Dataset1) Dataset2) Dataset3))....) it throws me an error. On searching more Co-pilot says that I cannot use aggregate function when using multiple datasets. How can I achieve this ?

Thanks.

[rnola16]

Yes, its an indirect approach but this works like charm. In my tablix I got a bunch of cols all coming from different datasets and the Total on these cols doesnt work with a direct Sum func. Yes, autopilot suggested the same approach w alternative. But atleast I dont need to build an other query union. Thank you for the response.

[burakkaragoz · Accepted solution]

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.