Two tables from one SQL run

[christrueman]

I'm trying to run a report showing two tables/lists from one run of SQL and I'm not sure whether this is possible or the correct procedure to use.  I would appreciate any help.

SQL

Select column1, column2, count(1) as nr_of_records from table group by column1, column 2

I would like one report to show column1 and sum(nr_of_records) and the other to show column2 and sum(nr_of_records)

Can I sum all the records and display

 

column1	nr_of_records		column2
15/06/2009	2		books	12
16/06/2009	6		postcards	3
17/06/2009	4		newspapers	13
18/06/2009	8		Total	28
19/06/2009	1
20/06/2009	3
21/06/2009	4
Total	28

I can run two seperate SQL statements  and subreports but this would not be very efficient

Hope someone can help

Chris

[mdahlman]

Chris,

 

You are right that you could do this with subreports. But that's indeed overkill for what you need.

 

But your suggested SQL query would not return the data that you are looking for. It would return data grouped by both columns. So for example you would get the group "June 15, books, 1", "June 15, postcards, 1", "June 16, books, 2", etc.

 

You genuinely want 2 different queries where each is grouped on a single field. The queries are both hitting the same data. So it might be possible to create some variables to tease out the data you want in a single query. But conceptually they are two different queries, and I think you'll find the report a lot easier to create and maintain if you think of it that way.

 

So your basic steps are:

1. Write the 2 queries (this should be easy).
2. Create a report with 2 datasets
3. Add 2 List components to the title section of the report (or the summary). Each List uses one of the datasets. (But each uses the same connection.)

 

Regards,
Matt

[christrueman]

Many thanks, I was thinking that my SQL would return 1 book and 1 newspaper on the 15th, 5 newspapers and 1 postcard on the 16th etc therefore I could sum nr_of_records in relation to column1 to get sales by date and sum nr_of_records in relation to column2 to get sales by type. Is this what you mean by creating some variables to tease out the data? If so could you point me in the right direction?

 

Whilst the example seems simple the actual data means a long run time and I would expect to face this challenge many times if I was to take on Jaspersoft

Thanks again for the reply

[mdahlman]

Chris,

 

Populating the report you describe using only a single query is fundamentally very difficult. You can get the raw data simply enough with some variation on SELECT * FROM mytable. But then you want to group and subtotal based on distinct but overlapping groups.To get the first list you order by and group by date. To get the second list you order by and group by media.

 

The two different orderings are mutually exclusive. So to get around it you would need to resort to things like hard-coded variables. Rather than having one variable called "media" that counts and resets for each media type (books, postcards, newspages), instead you might create 3 variables: one for each media. In principle you could make this work. But it's begging for trouble.

 

Creating a report with hard-coded variables like this is going to be 10 times harder to build and 100 times harder to maintain. OK, I just pulled those estimates out of thin air... but it really would be a magnitude of order more difficult.

 

Use one query for each list, and life should be good. That should apply not just to JasperReports/iReport, but also to other reporting systems.

 

Regards,
Matt

[christrueman]

OK, thanks again for the replies.

 

I come from a background of Business Objects where to do this sort of thing is very simple.  Business objects understand the relationships of the returned data and automatically accumulates measure objects whilst grouping dimension objects.  Simply copying the report table and deleting column 2 would give the first table (vice versa for the second).  I'll just have to change my way of thinking!

 

I have successfuly created the report based on your previous post.  Even made it look good!  If I get some time I'll try the variable approach, after the one of investment I may be able to re-use the code aganist other reports.

 

Life is good

Chris