SSRS sum(distinct()) equivalent

Question

I am currently working with an SSRS 2008 report that returns a dataset similar to the following:

Job# ClientId MoneyIn MoneyOut
------------------------------

1    ABC123    10      25
1    ABC123    10      25
1    ABC123    5       25
2    XYZ123    25      50
2    XYZ123    25      50
3    XYZ123    15      15

Where MoneyOut should be equal to the total amount of MoneyIn for a job if the job has been balanced out correctly.

The problem that I am running into is when displaying this in a tablix in SSRS I can return the correct MoneyOut value for a job by setting the field to =first(Fields!MoneyOut.Value) but I also need to sum the value of these by day and attempting to do =sum(first(Fields!MoneyOut.Value)) yields an error about nesting aggregate functions.

I've also attempted to sum the value of the textboxes using something like =sum(ReportItems!MoneyOut1.Value) which yields an error that you can only use aggregates on report items in the header or footer.

So my question is, is there some way to duplicate the functionality of distinct() in SSRS reports or is there some way to just total up the values of text fields that I am unaware of?

Solution 1

Why do you need an equivalent of the SUM(DISTINCT ...) function when you have it in SQL Server?

Sounds like you need to change the query supplying the data to your report, rather than trying to do it in the report itself.

Solution 2

if I understand your question truely, you must use group for grouping data and sum your favorite data in each group.

Comments

[HurnsMobile]

Marking this as the accepted answer, it seems like I was just in the wrong frame of mind yesterday. I added some partitions to the query in the view and its working correctly now.

[Jeff Walker Code Ranger]

Could we get some more detail on what the solution is? This answer isn't helpful as is.

[Tony]

@Jeff Walker Code Ranger - HurnsMobile was asking for SSRS to have the functionality of the DISTINCT key word, all I suggested was that he should go back to the source of the data and perform the operations there and use SSRS to display it. He was focussing on manipulating the data in the report when it can be done more easily in SQL Server. Are you are having difficulty with some SQL or Report in a similar way? Post a new question and I'll try to help out.