Detailed Aging Report by Row
<p>I have a report that I need to make for accounting In SSRS 2008 R2. It is an aging report that shows all owing invoices grouped by 0-30 days, 31-60 days, 61-90 days and 90 days over. I have this query that brings in all the correct info.</p>

<pre><code>SELECT DISTINCT
    T.ttfid,
    N.ttfn,
    T.T_DATE,
    T.NUM,
    T.DESCR,
    T.PAYEE,
    T.DEBIT,
    T.CREDIT,
    T.X,
    T.TTYPE,
    T.BORROWER,
    T.INVOICE_N,
    T.PAID_ON,
    T.CHECK_N,
    N.CustId,
    N.LOAN_NO,
    N.CustFn,
    B.LNAME,
    cc.CCODE
FROM TRANS T 
LEFT OUTER JOIN ttMAIN N ON (T.ttfid = N.ttfid)
LEFT OUTER JOIN BORROWER B ON
    (T.ttfid = B.ttfid) AND
    (B.BORR_NO = 1)
LEFT OUTER JOIN ttCC cc ON N.CustId = cc.cid
WHERE
    (
        (T.PAID_ON IS NULL) OR (T.PAID_ON &gt; @dtEnd)
    ) AND
    (T.DEBIT &gt; 0) AND
    (T.T_DATE BETWEEN @dtStart AND @dtEnd)
ORDER BY
    T.PAYEE,
    T.T_DATE DESC,
    T.NUM
</code></pre>

<p>In SSRS I can correctly group by CCODE and get the correct totals using a child group like</p>

<pre><code>=iif(DateDiff("d",Fields!T_DATE.Value, Parameters!dtEnd.Value)&gt;=0 and DateDiff("d",Fields!T_DATE.Value,Parameters!dtEnd.Value)&lt;=30,Fields!T_DATE.Value,nothing)
=iif(DateDiff("d",Fields!T_DATE.Value, Parameters!dtEnd.Value)&gt;=31 and DateDiff("d",Fields!T_DATE.Value,Parameters!dtEnd.Value)&lt;=60,Fields!T_DATE.Value,nothing)
=iif(DateDiff("d",Fields!T_DATE.Value, Parameters!dtEnd.Value)&gt;=61 and DateDiff("d",Fields!T_DATE.Value,Parameters!dtEnd.Value)&lt;=90,Fields!T_DATE.Value,nothing)
=iif(DateDiff("d",Fields!T_DATE.Value, Parameters!dtEnd.Value)&gt;=91 and DateDiff("d",Fields!T_DATE.Value,Parameters!dtEnd.Value)&lt;=99999999,Fields!T_DATE.Value,nothing)
</code></pre>

<p>However the details end up either not showing up or I get repeating files across groups like whats shown below.</p>

<p><a href="http://i.stack.imgur.com/dU6I0.png" rel="nofollow">If you notice the ttfn is the same across all groups but only adds to one total.</a></p>

<p>I am thinking I need to use multiple data sources or a query that will pre sort. The SQL query above is not my own. I do have a little SQL experience and have about a year writing reports. I have been throw in head first and currently the only one at work that has any knowledge of all this. If you do have an answer please stick with me as I not only want the report to work but understand why as well. Thanks</p>

<p><a href="http://i.stack.imgur.com/qxeKS.jpg" rel="nofollow">Image of the actual report showing the groups</a></p>