• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

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

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.

Loan table:  LOAN_NUM, BORROWER_NAME, FUND_DATE
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!
0
jeffbarbacpa
Asked:
jeffbarbacpa
  • 2
1 Solution
 
Nico BontenbalCommented:
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.
0
 
jeffbarbacpaAuthor Commented:
what about the sum of 'P' transactions though?  How do I get both 'D' and 'P' in the same query?
0
 
Nico BontenbalCommented:
Sorry, didn't read you question well enough.
Select 
    loan.LOAN_NUM, 
    loan.BORROWER_NAME, 
    loan.FUND_DATE, 
    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
from 
    loan 
    inner join [transaction] on loan.LOAN_NUM = [transaction].LOAN_NUM 
group by 
    loan.LOAN_NUM, 
    loan.BORROWER_NAME, 
    loan.FUND_DATE

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now