Detailed Aging Report by Row

Question

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.

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 > @dtEnd)
    ) AND
    (T.DEBIT > 0) AND
    (T.T_DATE BETWEEN @dtStart AND @dtEnd)
ORDER BY
    T.PAYEE,
    T.T_DATE DESC,
    T.NUM

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

=iif(DateDiff("d",Fields!T_DATE.Value, Parameters!dtEnd.Value)>=0 and DateDiff("d",Fields!T_DATE.Value,Parameters!dtEnd.Value)<=30,Fields!T_DATE.Value,nothing)
=iif(DateDiff("d",Fields!T_DATE.Value, Parameters!dtEnd.Value)>=31 and DateDiff("d",Fields!T_DATE.Value,Parameters!dtEnd.Value)<=60,Fields!T_DATE.Value,nothing)
=iif(DateDiff("d",Fields!T_DATE.Value, Parameters!dtEnd.Value)>=61 and DateDiff("d",Fields!T_DATE.Value,Parameters!dtEnd.Value)<=90,Fields!T_DATE.Value,nothing)
=iif(DateDiff("d",Fields!T_DATE.Value, Parameters!dtEnd.Value)>=91 and DateDiff("d",Fields!T_DATE.Value,Parameters!dtEnd.Value)<=99999999,Fields!T_DATE.Value,nothing)

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

If you notice the ttfn is the same across all groups but only adds to one total.

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

Image of the actual report showing the groups

Does `BORROWER` have more than one row with `BORR_NO` equal to 1? What about `ttMain` Also the expression you showed for aging groups is only for the 31 to 60 day range. Where's the rest? — shawnt00, Jan 12 '16 at 18:15
I have updated my question to show all the groups. Yes the `Borrower` has more than one row with `BORR_NO` equal to 1. I am not quite sure what you want me to look for in `ttMain` — Brett Cooper, Jan 12 '16 at 18:40
I'm looking for relationships in the query that cause the transactions to replicate. Are there 4 borrowers? Also I don't understand how those 4 expressions are being used separately to create the report groups. Usually you would nest those expressions in a single alculation. Is there a fifth that combines those somehow? — shawnt00, Jan 12 '16 at 18:44
Those expressions are all under one group CCode. Let me add an image of the SSRS report to help. — Brett Cooper, Jan 12 '16 at 18:50
The `ttfid` is unique in `ttMain` but not in `TRANS` or `BORROWER` — Brett Cooper, Jan 12 '16 at 18:59

shawnt00 · Accepted Answer · 2016-01-12T19:29:21.380

Add a formula using the expression below. Remove the four groups you have and replace with one that uses the new formula.

=iif(
    DateDiff("d", Fields!T_DATE.Value,Parameters!dtEnd.Value) <= 30, "0-30 Day",
    iif(
        DateDiff("d", Fields!T_DATE.Value,Parameters!dtEnd.Value) <= 60, "31-60 Day",
        iif(
            DateDiff("d", Fields!T_DATE.Value,Parameters!dtEnd.Value) <= 90, "61-90 Day",
            "91 Day and over"
        )
    )
 )

I'm not entirely sure this logic is fully correct for the intended result but it is based on what you've provided here so far. I don't know the meaning of the two start and end parameters which is one of the reasons I add that caveat.

Note: That expression is easiliy re-written as a switch() as well:

=switch(
    DateDiff("d", Fields!T_DATE.Value,Parameters!dtEnd.Value) <= 30, "0-30 Day",
    DateDiff("d", Fields!T_DATE.Value,Parameters!dtEnd.Value) <= 60, "31-60 Day",
    DateDiff("d", Fields!T_DATE.Value,Parameters!dtEnd.Value) <= 90, "61-90 Day",
    true, "91 Day and over"
 )

This formula only designates which group the rows belong to. There's more going on in the report if things are disappearing. Any idea why the previous expressions returned `T_Date` as their value? — shawnt00, Jan 12 '16 at 19:33
Originally didnt work. I deleted the table and recreated from scratch. Verifying right now but it is looking good thanks @shawnt00 — Brett Cooper, Jan 12 '16 at 19:34

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

I had a simular type of question, which was answered here. SSRS Category Grouping

I think you'll need to use the switch function to make this work.

edited May 23 '17 at 12:15

Community

1
1

answered Jan 12 '16 at 19:22

BIReportGuy

799
3
13
36

Detailed Aging Report by Row

2 Answers2