struggling to create a table

[mastereh]

I'm trying to create a table which contains summarized data based on various functions.

How do I have multiple steps calculated in iReport? Do I have to create multiple tables and then use the designer to have them sit next to each other? or should I create multple sub-reports (1 for each column)?

First column:

SELECT
     count(Policy."PolicyNum") AS Total_Policies
FROM
     "dbo"."Policy" Policy INNER JOIN "dbo"."PolicyTerms" PolicyTerms ON Policy."PolicySK" = PolicyTerms."PolicySK"
WHERE
     PolicyTerms."TermStatus" = 'Active'
     and PolicyTerms."TermExpiry" >= $P{UV_Date}

 

Second Column :

SELECT
     count(Policy."PolicyNum") AS Total_Auto
FROM
     "dbo"."Policy" Policy INNER JOIN "dbo"."PolicyTerms" PolicyTerms ON Policy."PolicySK" = PolicyTerms."PolicySK"
WHERE
     PolicyTerms."TermStatus" = 'Active' and
     Policy."LineOfBusiness" in ('AP','AC')
     and PolicyTerms."TermExpiry" >= $P{UV_Date}

 

Third Column:

SELECT
     count(Policy."PolicyNum") AS Total_Person_Auto
FROM
     "dbo"."Policy" Policy INNER JOIN "dbo"."PolicyTerms" PolicyTerms ON Policy."PolicySK" = PolicyTerms."PolicySK"
WHERE
     PolicyTerms."TermStatus" = 'Active' and Policy."LineOfBusiness" in ('AP')
     and PolicyTerms."TermExpiry" >= $P{UV_Date}

 

Any suggestions on how to best do this would be great.

Thanks.

 

[ernestoo]

Subreports are one way..but if you put different things in the WHERE clause I don't see how you would make them line up..

 

You could use subqueries http://beginner-sql-tutorial.com/sql-subquery.htm

 

or you could use stored procedures in the database

[mastereh]

Thanks for those.

 

In the end I want my table to look something like this:

 

Total Policies   |  Total Auto                | Total Property             | Total Commercial                                                            

 5000                 |   2500                        | 2000                            | 500