Report Design problem

[prasunkanti]

Dear sir,

I am totally a new user of ireport.   I have four tables. The Tables are given below.

 

 

 

1. inv_profile:

accnt_no	ac_name1
23	Prasun Kanti
45	Babu

2. psdr_cds:(Use for Buy)

accnt_no	no_shares	trans_dt	comp_cd
23	40	1-jan-2006	101
45	70	11-dec-2011	101
23	20	1-nov-2011	101

3. swr_cds(Use for Sale)

accnt_no	no_shares	trans_dt	comp_cd
23	20	1-jan-2007	101
45	20	12-dec-2011	101
23	30	15-nov-2011	101

4. comp

comp_cd	comp_nm
101	AB BANK

 

Now i need a Report same :

 

Accnt_no       Name                       Total Buy                  Total Sale         Balance

 

23                  Prasun Kanti             60                              50                     10

45                   Babu                        70                               20                   50

 

I am try to solve this problem but i am unable to do this. Please help me about this report.

Thanks

Post Edited by prasunkanti at 12/13/2011 02:41

Post Edited by prasunkanti at 12/13/2011 02:42

[cbarlow3]

Your first hurdle is to get comfortable with SQL (assuming that's the query language your database uses).  Luckily, SQL is even more readily documented than iReport.  There are many good books and online resources.  I normally recommend learning the basics from some online tutorials, but for a good reference book, I recommend O'Reilly's SQL Cookbook by Anthony Molinaro, because it shows examples of how to solve useful problems, pointing out the slight dialect differences between DB2, Oracle, MySQL, SQL Server, and PostgreSQL.

There are several ways to organize the query you need.  Here's one way:

SELECT
  Accnt_no,
  Name,
  SUM(Buy) AS TotalBuy,
  SUM(Sale) AS TotalSale,
  SUM(Buy) - SUM(Sale) AS Balance
FROM
(
SELECT
  inv_profile.accnt_no AS Accnt_no,
  inv_profile.ac_name1 AS Name,
  COALESCE(psdr_cds.no_shares, 0) AS Buy,
  0 AS Sale
FROM
  test.inv_profile AS inv_profile LEFT OUTER JOIN
  test.psdr_cds AS psdr_cds ON
    psdr_cds.accnt_no=inv_profile.accnt_no
UNION ALL
SELECT
  inv_profile.accnt_no AS Accnt_no,
  inv_profile.ac_name1 AS Name,
  0 AS Buy,
  COALESCE(swr_cds.no_shares, 0) AS Sale
FROM
  test.inv_profile AS inv_profile LEFT OUTER JOIN
  test.swr_cds AS swr_cds ON
    swr_cds.accnt_no=inv_profile.accnt_no
) a
GROUP BY
  Accnt_no,
  Name
ORDER BY
  Accnt_no

I prefer to use INNER JOIN when I can, but in this case I used LEFT OUTER JOIN, because I assume that it's possible to have an entry in inv_profile that has no buy and/or sell entries, and presumably you would still want them to show up on the report.  If you wanted to skip an entry that had neither a buy or sell, you could add this clause just before "ORDER BY"

HAVING
  SUM(Buy)<>0 OR SUM(Sale)<>0

 Hope that helps!

Carl