title: SSRS sum(distinct()) equivalent
body: <p>I am currently working with an SSRS 2008 report that returns a dataset similar to the following:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Where <code>MoneyOut</code> should be equal to the total amount of <code>MoneyIn</code> for a job if the job has been balanced out correctly. </p>
  
  <p>The problem that I am running into is when displaying this in a tablix in SSRS I can return the correct <code>MoneyOut</code> value for a job by setting the field to <code>=first(Fields!MoneyOut.Value)</code> but I also need to sum the value of these by day and attempting to do <code>=sum(first(Fields!MoneyOut.Value))</code> yields an error about nesting aggregate functions.</p>
  
  <p>I've also attempted to sum the value of the textboxes using something like <code>=sum(ReportItems!MoneyOut1.Value)</code> which yields an error that you can only use aggregates on report items in the header or footer.</p>
  
  <p>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?</p>