How about loop a sub report?

[Peter Jin]

Hi,
I may need to put several sub reports in the detail section in the master report. So the sub reports will get loaded many times. Will this cause significant performance issues? Does jasper cache data source for report in this case?

My case:
User's data are scattered in 5 tables and I need to put those data together under a particular user. I don't want to use UNION because HQL doesn't support it. In addition, Union 5 tables may result in a huge SQL. I have to use one sub report for each table and iterate users in master report and for each user iterate all sub reports to display data relevant to that user.

Is this viable or any other solution?

Any comment is appreciated.

[chr15athome]

Hi Peter,

If I understand your requirements correctly I think grouping would be better than having multiple subreports.

Grouping the data on users allows you to create a seperate table for each user, you can also have a header and footer for each user and you can also perform calculations and have totals at the bottom of each group.

Hope this helps.

Chris

Peter Jin wrote:

Hi,
I may need to put several sub reports in the detail section in the master report. So the sub reports will get loaded many times. Will this cause significant performance issues? Does jasper cache data source for report in this case?

My case:
User's data are scattered in 5 tables and I need to put those data together under a particular user. I don't want to use UNION because HQL doesn't support it. In addition, Union 5 tables may result in a huge SQL. I have to use one sub report for each table and iterate users in master report and for each user iterate all sub reports to display data relevant to that user.

Is this viable or any other solution?

Any comment is appreciated.

[chr15athome]

Sorry I forgot to mention that the info I gave is based on using iReport/jasperReports, not sure what you are using now but I definately recommend iReport.

Chris


Hi Peter,

If I understand your requirements correctly I think grouping would be better than having multiple subreports.

Grouping the data on users allows you to create a seperate table for each user, you can also have a header and footer for each user and you can also perform calculations and have totals at the bottom of each group.

Hope this helps.

Chris

Peter Jin wrote:

Hi,
I may need to put several sub reports in the detail section in the master report. So the sub reports will get loaded many times. Will this cause significant performance issues? Does jasper cache data source for report in this case?

My case:
User's data are scattered in 5 tables and I need to put those data together under a particular user. I don't want to use UNION because HQL doesn't support it. In addition, Union 5 tables may result in a huge SQL. I have to use one sub report for each table and iterate users in master report and for each user iterate all sub reports to display data relevant to that user.

Is this viable or any other solution?

Any comment is appreciated.

[Peter Jin]

Hi Chris,

Thanks for your reply.
Grouping can not solve my issue because it works on one data source only. In my case, data for a user scattered in 5 tables. UNION might be a way to combine all tables to a data source, but I can not use it either (explained in the first post). any thoughts?

[chr15athome]

Its hard to give advice when I have no real idea of your data structure, I am unsure why you would have seperate tables for differents users unless you mean graphically.

If you have 5 separate tables 1 for each user then UNION seems to be the only way to do it, unless you know Java, you could put the data into beans and use a bean datasource to build your report.

I always find that subreports can normally be replaced with groups, I personally hate subreports...lol.

Chris


Sorry I forgot to mention that the info I gave is based on using iReport/jasperReports, not sure what you are using now but I definately recommend iReport.

Chris

chr15athome wrote:

Hi Peter,

If I understand your requirements correctly I think grouping would be better than having multiple subreports.

Grouping the data on users allows you to create a seperate table for each user, you can also have a header and footer for each user and you can also perform calculations and have totals at the bottom of each group.

Hope this helps.

Chris

Peter Jin wrote:

Hi,
I may need to put several sub reports in the detail section in the master report. So the sub reports will get loaded many times. Will this cause significant performance issues? Does jasper cache data source for report in this case?

My case:
User's data are scattered in 5 tables and I need to put those data together under a particular user. I don't want to use UNION because HQL doesn't support it. In addition, Union 5 tables may result in a huge SQL. I have to use one sub report for each table and iterate users in master report and for each user iterate all sub reports to display data relevant to that user.

Is this viable or any other solution?

Any comment is appreciated.

[David Bolen-2]

Peter Jin <jinyuping@...> writes:

> Grouping can not solve my issue because it works on one data source only. In
> my case, data for a user scattered in 5 tables. UNION might be a way to
> combine all tables to a data source, but I can not use it either (explained
> in the first post). any thoughts?

It's still somewhat unclear without knowing more about your table
structure, but if you just mean that you have a reasonably normalized
schema where the relevant information is stored in several tables with
appropriate foreign key relationships (this can be true even if normal
access is controlled by an ORM like Hibernate), is there a reason your
report query can't just join the relevant tables together, grouping
the result by user?

If you mean 5 completely distinct data sources (e.g., separate
databases), then another thought (if you have control of the code
rendering the report) might be to implement a custom data source that
itself performs the integration from the multiple data sources, even
if via 5 distinct queries.  From the Jasper engine's perspective it
would still be utilized as a single data source for the report.

-- David


------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
jasperreports-questions mailing list
jasperreports-questions@...
https://lists.sourceforge.net/lists/listinfo/jasperreports-questions

[Peter Jin]

We have an audit system which uses separate tables with similar structure to store different kinds of activities for a user. We want to report the number of activities per activity type per user.

David Bolen-2 wrote:

Peter Jin <[e-mail]> writes:

> Grouping can not solve my issue because it works on one data source only. In
> my case, data for a user scattered in 5 tables. UNION might be a way to
> combine all tables to a data source, but I can not use it either (explained
> in the first post). any thoughts?

It's still somewhat unclear without knowing more about your table
structure, but if you just mean that you have a reasonably normalized
schema where the relevant information is stored in several tables with
appropriate foreign key relationships (this can be true even if normal
access is controlled by an ORM like Hibernate), is there a reason your
report query can't just join the relevant tables together, grouping
the result by user?

If you mean 5 completely distinct data sources (e.g., separate
databases), then another thought (if you have control of the code
rendering the report) might be to implement a custom data source that
itself performs the integration from the multiple data sources, even
if via 5 distinct queries.  From the Jasper engine's perspective it
would still be utilized as a single data source for the report.

-- David


------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
jasperreports-questions mailing list
[e-mail]
https://lists.sourceforge.net/lists/listinfo/jasperreports-questions

[Peter Jin]

Thanks a lot for your comments. We have an audit system which uses separate tables with similar structure to store different kinds of activities for a user. We want to report the number of activities per activity type per user. I'll consider to create a view first.


Its hard to give advice when I have no real idea of your data structure, I am unsure why you would have seperate tables for differents users unless you mean graphically.

If you have 5 separate tables 1 for each user then UNION seems to be the only way to do it, unless you know Java, you could put the data into beans and use a bean datasource to build your report.

I always find that subreports can normally be replaced with groups, I personally hate subreports...lol.

Chris


Sorry I forgot to mention that the info I gave is based on using iReport/jasperReports, not sure what you are using now but I definately recommend iReport.

Chris

chr15athome wrote:

Hi Peter,

If I understand your requirements correctly I think grouping would be better than having multiple subreports.

Grouping the data on users allows you to create a seperate table for each user, you can also have a header and footer for each user and you can also perform calculations and have totals at the bottom of each group.

Hope this helps.

Chris

Peter Jin wrote:

Hi,
I may need to put several sub reports in the detail section in the master report. So the sub reports will get loaded many times. Will this cause significant performance issues? Does jasper cache data source for report in this case?

My case:
User's data are scattered in 5 tables and I need to put those data together under a particular user. I don't want to use UNION because HQL doesn't support it. In addition, Union 5 tables may result in a huge SQL. I have to use one sub report for each table and iterate users in master report and for each user iterate all sub reports to display data relevant to that user.

Is this viable or any other solution?

Any comment is appreciated.

[chr15athome]

Yes I overlooked using JOIN. Used to use JOIN for multiple tables in my old system.
If you are not up on Joins I have an example I can send.

David Bolen-2 wrote:

Peter Jin <[e-mail]> writes:

> Grouping can not solve my issue because it works on one data source only. In
> my case, data for a user scattered in 5 tables. UNION might be a way to
> combine all tables to a data source, but I can not use it either (explained
> in the first post). any thoughts?

It's still somewhat unclear without knowing more about your table
structure, but if you just mean that you have a reasonably normalized
schema where the relevant information is stored in several tables with
appropriate foreign key relationships (this can be true even if normal
access is controlled by an ORM like Hibernate), is there a reason your
report query can't just join the relevant tables together, grouping
the result by user?

If you mean 5 completely distinct data sources (e.g., separate
databases), then another thought (if you have control of the code
rendering the report) might be to implement a custom data source that
itself performs the integration from the multiple data sources, even
if via 5 distinct queries.  From the Jasper engine's perspective it
would still be utilized as a single data source for the report.

-- David


------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
jasperreports-questions mailing list
[e-mail]
https://lists.sourceforge.net/lists/listinfo/jasperreports-questions

[David Bolen-2]

Peter Jin <jinyuping@...> writes:

> We have an audit system which uses separate tables with similar structure to
> store different kinds of activities for a user. We want to report the number
> of activities per activity type per user.

Still sounds pretty standard in terms of database schema.  Can I
assume that these separate activity tables have some field or unique
key that correlates back to a user table or table containing per-user
information?  If so, then unless the tables are stored in separate
database servers, it sounds like you just want to join those tables as
part of the primary query without much hassle.

Dumb example - you have a set of users, who can get rows added to one of
three activity tables, related to three activities that have different
metrics.  Each row in an activity table is a single instance of that
user performing that activity:

   users
      id integer primary key
      name text

   activity1
      user_id integer -- foreign key references users(id)
      distance integer

   activity2
      user_id integer -- foreign key references users(id)
      hits integer

   activity3
      user_id integer -- foreign key references users(id)
      weight integer

(The join will work without the database having explicit foreign key
 references but they should be there for referential integrity if the
 activity tables do have entries for users.  For performance you should
 also ensure that users.id is indexed at a minimum).

Then, a query like the following:

    SELECT    id, name,
              count(a1.distance) as a1_cnt, sum(a1.distance) as a1_total,
              count(a2.hits) as a2_cnt, sum(a2.hits) as a2_total,
              count(a3.weight) as a3_cnt, sum(a3.weight) as a3_total
    FROM      users
    LEFT JOIN activity1 a1 on users.id = a1.user_id
    LEFT JOIN activity2 a2 on users.id = a2.user_id
    LEFT JOIN activity3 a3 on users.id = a3.user_id
    GROUP BY  id, name;

would produce a result set looking like:

    id   name          a1_cnt  a1_total  a2_cnt  a2_total  a3_cnt  a3_total
    1    User 1          ##     ####       ##     ####       ##     ####
    2    User 2          ##     ####       ##     ####       ##     ####
    ...

Feed that into a JasperReports report designed for grouping by id/user
and then you have access to all of the total information in a single
detail line for that user, to be presented in the per-user section of
that report.

Now if you're looking for individual detail lines for each activity
for each user, then I think your original thought of a UNION query was
on the right track, although you're still joining within the
individual components of the UNION to link the user to the activity.

For example:

    SELECT * from (

        SELECT    id, name, 'activity1' as activity,
                  count(a.distance) as count, sum(a.distance) as total
        FROM      users LEFT JOIN a1 a on users.id = a.user_id
        GROUP BY  id, name, activity
        UNION
        SELECT    id, name, 'activity2' as activity,
                  count(a.hits) as count, sum(a.hits) as total
        FROM      users LEFT JOIN a2 a on users.id = a.user_id
        GROUP BY  id, name, activity
        UNION
        SELECT    id, name, 'activity3' as activity,
                  count(a.weight) as count, sum(a.weight) as total
        FROM      users LEFT JOIN a3 a on users.id = a.user_id
        GROUP BY  id, name, activity

    ) as data

    ORDER by id, name, activity;


which would result in a result set like:

    id   name    activity    count    total
     1   User 1  activity1    ##       ###
     1   User 1  activity2    ##       ###
     1   User 1  activity3    ##       ###
     2   User 2  activity1    ##       ###
     2   User 2  activity2    ##       ###
     2   User 2  activity3    ##       ###

which I think would work fine for Jasper to perform nested groupings
on it, first by id/name and then by activity.

I know you mentioned HQL not supporting a UNION, but JasperReport can
make a direct SQL query to the underlying data, so I'm not sure that HQL
needs to be involved.  Also, you mentioned being concerned with the size
of the data set, but as you can see here, you'll only get one summary
row per user, per activity, so I'm not sure how the data set can be any
smaller (even via a mechanism other than UNION) and still provide you
with per-activity row data for display in the report.

Note that if the activity tables have columns for a name for the activity
you could select that in lieu of the static string, though if there's any
chance for overlap you'll then want a UNION ALL instead.

Nesting the query and applying an overall ordering helps ensure that
you get the sequence in an appropriate order for reporting, since
otherwise an engine might produce an arbitrary ordering.

Or, if the per-user section of the report is just supposed to show
a row per activity with total values (e.g., just what this query gives),
then there's no need to have a second level of grouping in the report,
just take the raw query records as your rows and use them to generate
the detail lines for the per-user information.

Similarly, if you needed access to the raw activity records for each
activity, drop the count() and sum() portions of the query in favor of
the raw fields you care about, at which point JasperReports can do the
summing itself during the grouping process.  But that would likely be
less efficient if you really only needed the per-activity totals

And of course, at some point it's probably logical to consider hiding
some or all of this logic behind some views on the database server
side, thus reducing the need to have the join complexity stored in the
report - especially if you'll have multiple reports working off of the
same sort of query.

Hope this helps.  Note that none of this is too JasperReports
specific, but is more a question on querying data from the SQL data
source.  As such, you may also find assistance in more generic
database (especially for whatever your source database system is) or
SQL forums.

-- David


------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
jasperreports-questions mailing list
jasperreports-questions@...
https://lists.sourceforge.net/lists/listinfo/jasperreports-questions

[Peter Jin]

Hi David,
Thanks for your reply. Union is ok, but seems there are issues with Join.

    SELECT    id, name,
              count(a1.distance) as a1_cnt, sum(a1.distance) as a1_total,
              count(a2.hits) as a2_cnt, sum(a2.hits) as a2_total,
              count(a3.weight) as a3_cnt, sum(a3.weight) as a3_total
    FROM      users
    LEFT JOIN activity1 a1 on users.id = a1.user_id
    LEFT JOIN activity2 a2 on users.id = a2.user_id
    LEFT JOIN activity3 a3 on users.id = a3.user_id
    GROUP BY  id, name;

Let's take a look at activity1 and activity2. If activity1 has more records than activity2, some of the records of activity2 will be joined multiple times. This is incorrect. Does it make sense?

David Bolen-2 wrote:

Peter Jin <[e-mail]> writes:

> We have an audit system which uses separate tables with similar structure to
> store different kinds of activities for a user. We want to report the number
> of activities per activity type per user.

Still sounds pretty standard in terms of database schema.  Can I
assume that these separate activity tables have some field or unique
key that correlates back to a user table or table containing per-user
information?  If so, then unless the tables are stored in separate
database servers, it sounds like you just want to join those tables as
part of the primary query without much hassle.

Dumb example - you have a set of users, who can get rows added to one of
three activity tables, related to three activities that have different
metrics.  Each row in an activity table is a single instance of that
user performing that activity:

   users
      id integer primary key
      name text

   activity1
      user_id integer -- foreign key references users(id)
      distance integer

   activity2
      user_id integer -- foreign key references users(id)
      hits integer

   activity3
      user_id integer -- foreign key references users(id)
      weight integer

(The join will work without the database having explicit foreign key
 references but they should be there for referential integrity if the
 activity tables do have entries for users.  For performance you should
 also ensure that users.id is indexed at a minimum).

Then, a query like the following:

    SELECT    id, name,
              count(a1.distance) as a1_cnt, sum(a1.distance) as a1_total,
              count(a2.hits) as a2_cnt, sum(a2.hits) as a2_total,
              count(a3.weight) as a3_cnt, sum(a3.weight) as a3_total
    FROM      users
    LEFT JOIN activity1 a1 on users.id = a1.user_id
    LEFT JOIN activity2 a2 on users.id = a2.user_id
    LEFT JOIN activity3 a3 on users.id = a3.user_id
    GROUP BY  id, name;

would produce a result set looking like:

    id   name          a1_cnt  a1_total  a2_cnt  a2_total  a3_cnt  a3_total
    1    User 1          ##     ####       ##     ####       ##     ####
    2    User 2          ##     ####       ##     ####       ##     ####
    ...

Feed that into a JasperReports report designed for grouping by id/user
and then you have access to all of the total information in a single
detail line for that user, to be presented in the per-user section of
that report.

Now if you're looking for individual detail lines for each activity
for each user, then I think your original thought of a UNION query was
on the right track, although you're still joining within the
individual components of the UNION to link the user to the activity.

For example:

    SELECT * from (

        SELECT    id, name, 'activity1' as activity,
                  count(a.distance) as count, sum(a.distance) as total
        FROM      users LEFT JOIN a1 a on users.id = a.user_id
        GROUP BY  id, name, activity
        UNION
        SELECT    id, name, 'activity2' as activity,
                  count(a.hits) as count, sum(a.hits) as total
        FROM      users LEFT JOIN a2 a on users.id = a.user_id
        GROUP BY  id, name, activity
        UNION
        SELECT    id, name, 'activity3' as activity,
                  count(a.weight) as count, sum(a.weight) as total
        FROM      users LEFT JOIN a3 a on users.id = a.user_id
        GROUP BY  id, name, activity

    ) as data

    ORDER by id, name, activity;


which would result in a result set like:

    id   name    activity    count    total
     1   User 1  activity1    ##       ###
     1   User 1  activity2    ##       ###
     1   User 1  activity3    ##       ###
     2   User 2  activity1    ##       ###
     2   User 2  activity2    ##       ###
     2   User 2  activity3    ##       ###

which I think would work fine for Jasper to perform nested groupings
on it, first by id/name and then by activity.

I know you mentioned HQL not supporting a UNION, but JasperReport can
make a direct SQL query to the underlying data, so I'm not sure that HQL
needs to be involved.  Also, you mentioned being concerned with the size
of the data set, but as you can see here, you'll only get one summary
row per user, per activity, so I'm not sure how the data set can be any
smaller (even via a mechanism other than UNION) and still provide you
with per-activity row data for display in the report.

Note that if the activity tables have columns for a name for the activity
you could select that in lieu of the static string, though if there's any
chance for overlap you'll then want a UNION ALL instead.

Nesting the query and applying an overall ordering helps ensure that
you get the sequence in an appropriate order for reporting, since
otherwise an engine might produce an arbitrary ordering.

Or, if the per-user section of the report is just supposed to show
a row per activity with total values (e.g., just what this query gives),
then there's no need to have a second level of grouping in the report,
just take the raw query records as your rows and use them to generate
the detail lines for the per-user information.

Similarly, if you needed access to the raw activity records for each
activity, drop the count() and sum() portions of the query in favor of
the raw fields you care about, at which point JasperReports can do the
summing itself during the grouping process.  But that would likely be
less efficient if you really only needed the per-activity totals

And of course, at some point it's probably logical to consider hiding
some or all of this logic behind some views on the database server
side, thus reducing the need to have the join complexity stored in the
report - especially if you'll have multiple reports working off of the
same sort of query.

Hope this helps.  Note that none of this is too JasperReports
specific, but is more a question on querying data from the SQL data
source.  As such, you may also find assistance in more generic
database (especially for whatever your source database system is) or
SQL forums.

-- David


------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
jasperreports-questions mailing list
[e-mail]
https://lists.sourceforge.net/lists/listinfo/jasperreports-questions

[David Bolen-2]

Peter Jin <jinyuping@...> writes:

> Let's take a look at activity1 and activity2. If activity1 has more records
> than activity2, some of the records of activity2 will be joined multiple
> times. This is incorrect. Does it make sense?

Drat, yes, dumb error (and obviously too quick 'n dirty test data) on
my part.  I guess I let having just aggregate functions lull me into
thinking only rows were getting joined, but obviously the aggregates
are only applied after the join.  Sorry about that.

Let's try again - here's a possible replacement query that I believe
yields the correct results:

select * from (

    SELECT    users.id, users.name,
              a_1.a1_cnt, a_1.a1_total,
              a_2.a2_cnt, a_2.a2_total,
              a_3.a3_cnt, a_3.a3_total

    FROM      users

    LEFT JOIN (
        SELECT id, name,
               count(a1.distance) as a1_cnt, sum(a1.distance) as a1_total
               FROM users JOIN activity1 a1 on users.id = a1.user_id
               GROUP BY id, name
        ) as a_1 on users.id = a_1.id

    LEFT JOIN (
        SELECT id, name,
               count(a2.hits) as a2_cnt, sum(a2.hits) as a2_total
               FROM users JOIN activity2 a2 on users.id = a2.user_id
               GROUP BY id, name
        ) as a_2 on users.id = a_2.id

    LEFT JOIN (          
        SELECT id, name,
               count(a3.weight) as a3_cnt, sum(a3.weight) as a3_total
               FROM users JOIN activity3 a3 on users.id = a3.user_id
               GROUP BY id, name
        ) as a_3 on users.id = a_3.id

) as result

ORDER BY id, name;

The nested sub-selects are using an inner join, thus the final table
will have NULLs for both *_cnt and *_total if there are no matching
records for that activity.  You could change it to a left join (like
the parent select) to get 0 in the *_cnt columns, but there would
still be NULLs in the *_total columns.  If your report code would rather
not deal with NULLs, I'd probably just coalesce() those columns as
needed in the top level select.

Depending on the sheer number of activities this could get more
complicated (though again, you can hide a lot behind views and/or
server side functions), but it still has the database server doing the
bulk of the aggregation so should be more efficient than looping over
the individual per-activity queries at the reporting level.

-- David


------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
jasperreports-questions mailing list
jasperreports-questions@...
https://lists.sourceforge.net/lists/listinfo/jasperreports-questions

[Peter Jin]

Hi, It works!
All your posts are of great help. Thanks a ton!

David Bolen-2 wrote:

Peter Jin <[e-mail]> writes:

> Let's take a look at activity1 and activity2. If activity1 has more records
> than activity2, some of the records of activity2 will be joined multiple
> times. This is incorrect. Does it make sense?

Drat, yes, dumb error (and obviously too quick 'n dirty test data) on
my part.  I guess I let having just aggregate functions lull me into
thinking only rows were getting joined, but obviously the aggregates
are only applied after the join.  Sorry about that.

Let's try again - here's a possible replacement query that I believe
yields the correct results:

select * from (

    SELECT    users.id, users.name,
              a_1.a1_cnt, a_1.a1_total,
              a_2.a2_cnt, a_2.a2_total,
              a_3.a3_cnt, a_3.a3_total

    FROM      users

    LEFT JOIN (
        SELECT id, name,
               count(a1.distance) as a1_cnt, sum(a1.distance) as a1_total
               FROM users JOIN activity1 a1 on users.id = a1.user_id
               GROUP BY id, name
        ) as a_1 on users.id = a_1.id

    LEFT JOIN (
        SELECT id, name,
               count(a2.hits) as a2_cnt, sum(a2.hits) as a2_total
               FROM users JOIN activity2 a2 on users.id = a2.user_id
               GROUP BY id, name
        ) as a_2 on users.id = a_2.id

    LEFT JOIN (          
        SELECT id, name,
               count(a3.weight) as a3_cnt, sum(a3.weight) as a3_total
               FROM users JOIN activity3 a3 on users.id = a3.user_id
               GROUP BY id, name
        ) as a_3 on users.id = a_3.id

) as result

ORDER BY id, name;

The nested sub-selects are using an inner join, thus the final table
will have NULLs for both *_cnt and *_total if there are no matching
records for that activity.  You could change it to a left join (like
the parent select) to get 0 in the *_cnt columns, but there would
still be NULLs in the *_total columns.  If your report code would rather
not deal with NULLs, I'd probably just coalesce() those columns as
needed in the top level select.

Depending on the sheer number of activities this could get more
complicated (though again, you can hide a lot behind views and/or
server side functions), but it still has the database server doing the
bulk of the aggregation so should be more efficient than looping over
the individual per-activity queries at the reporting level.

-- David


------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
jasperreports-questions mailing list
[e-mail]
https://lists.sourceforge.net/lists/listinfo/jasperreports-questions

hi ,

In my company we are using  Swings on NetBeans IDE. I need a requirement of generating sub report. here i am attaching the code. In that i have two queries. one for main report and one for sub report. but am unable to call the sub report... can you please send me the code. its urgent...
please..........
Fin_PaidReceiptRpt.jrxml
Fin_PaidReceiptRpt_subreport0.jrxml
PaidReciptReport.java
JasperReportInvoker.java