[jasperreports-questions] How about loop a sub report?
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. -- View this message in context: http://www.nabble.com/How-about-loop-a-sub-report--tp23847692p23847692.html Sent from the jasperreports-questions mailing list archive at Nabble.com. -- Crystal Reports - New Free Runtime and 30 Day Trial Check out the new simplified licensing option that enables unlimited royalty-free distribution of the report engine for externally facing server and web deployment. http://p.sf.net/sfu/businessobjects ___ jasperreports-questions mailing list jasperreports-questions@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
Re: [jasperreports-questions] How about loop a sub report?
Hi, It works! All your posts are of great help. Thanks a ton! David Bolen-2 wrote: Peter Jin jinyup...@gmail.com 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 ( SELECTusers.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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions -- View this message in context: http://www.nabble.com/How-about-loop-a-sub-report--tp23847733p23899241.html Sent from the jasperreports-questions mailing list archive at Nabble.com. -- 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
Re: [jasperreports-questions] How about loop a sub report?
Hi David, Thanks for your reply. Union is ok, but seems there are issues with Join. SELECTid, 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 jinyup...@gmail.com 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: SELECTid, 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 1User 1 ## ## ## 2User 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 ( SELECTid, 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 SELECTid, 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 SELECTid, 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 nameactivitycounttotal 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
Re: [jasperreports-questions] How about loop a sub report?
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 jinyup...@gmail.com 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions -- View this message in context: http://www.nabble.com/How-about-loop-a-sub-report--tp23847733p23865240.html Sent from the jasperreports-questions mailing list archive at Nabble.com. -- 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
Re: [jasperreports-questions] How about loop a sub report?
Peter Jin jinyup...@gmail.com 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: SELECTid, 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 1User 1 ## ## ## 2User 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 ( SELECTid, 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 SELECTid, 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 SELECTid, 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 nameactivitycounttotal 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
[jasperreports-questions] How about loop a sub report?
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. -- View this message in context: http://www.nabble.com/How-about-loop-a-sub-report--tp23847733p23847733.html Sent from the jasperreports-questions mailing list archive at Nabble.com. -- 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
Re: [jasperreports-questions] How about loop a sub report?
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. -- View this message in context: http://www.nabble.com/How-about-loop-a-sub-report--tp23847733p23848126.html Sent from the jasperreports-questions mailing list archive at Nabble.com. -- 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
Re: [jasperreports-questions] How about loop a sub report?
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. -- View this message in context: http://www.nabble.com/How-about-loop-a-sub-report--tp23847733p23848170.html Sent from the jasperreports-questions mailing list archive at Nabble.com. -- 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
Re: [jasperreports-questions] How about loop a sub report?
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? -- View this message in context: http://www.nabble.com/How-about-loop-a-sub-report--tp23847733p23848221.html Sent from the jasperreports-questions mailing list archive at Nabble.com. -- 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
Re: [jasperreports-questions] How about loop a sub report?
Peter Jin jinyup...@gmail.com 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
