SSRS sum(distinct()) equivalent

I am currently working with an SSRS 2008 r开发者_运维知识库eport 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?

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.

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

Replace your 'MoneyOut' field with the following:

CASE 
    WHEN ROW_NUMBER() OVER (PARTITION BY MoneyOut ORDER BY ClientId) = 1
    THEN MoneyOut
    ELSE NULL
    END AS MoneyOut