How to join flat and deep tables in MS SQL Reporting Services

Posted on 2011-05-05
Last Modified: 2012-05-11
I have one table of loans and another table of transactions that relate to those loans.  I am trying to figure out how to produce a report that shows each loan in the loan table, and corresponding transaction totals.

Transaction Table:  LOAN_NUM, TRANS_TYPE, AMOUNT

Desired output:

LOAN_NUM, BORROWER_NAME, FUND_DATE, SUM OF AMOUNT where trans_type = D, SUM OF AMOUNT where transtype = P

In access, I would create a couple queries and it would be done.  I can't figure out an easy way to do in MS Reporting Services.  Help!
Question by:jeffbarbacpa
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    Use a query like this as the source of your report:
    Select loan.LOAN_NUM, loan.BORROWER_NAME, loan.FUND_DATE, sum(transaction.AMOUNT) as Total
    from loan inner join transaction on loan.LOAN_NUM = transaction.LOAN_NUM
    where  transaction.trans_type = 'D'
    group by loan.LOAN_NUM, loan.BORROWER_NAME, loan.FUND_DATE

    Open in new window

    Didn't test it so it might need some tweaking.

    Author Comment

    what about the sum of 'P' transactions though?  How do I get both 'D' and 'P' in the same query?
    LVL 22

    Accepted Solution

    Sorry, didn't read you question well enough.
        sum(case when [transaction].trans_type = 'D' then [transaction].AMOUNT else 0 end) as TotalD,
        sum(case when [transaction].trans_type = 'P' then [transaction].AMOUNT else 0 end) as TotalP
        inner join [transaction] on loan.LOAN_NUM = [transaction].LOAN_NUM 
    group by 

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    SQL Pivot 7 37
    SSDT or BIDS in SQL Server 2014 4 64
    MDX in SSRS v SSAS 1 631
    VS/SSRS - Rectangle re-sizing when element hidden 2 44
    Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
    Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now