Re: [jasperreports-questions] Default oracle date format in ireport
John Dunn jd...@sefas.com writes:
Thanks, but my problem relates to a date used in the SQL in my report
query
Ah, other direction - sorry.
I have tried using a value that is a java.util.date in my SQL query.
Here is the relevant bit of my SL query :
AND submitted_date = $P!{PARAM_TO_DATE}
parameter PARAM_TO_DATE is defined as a java.util.Date with the default
Value Expression set to
new java.util.Date(01/01/2050)
But when the the Report query reads the SQL is gives the error
Error : SQL problems: Missing IN or OUT parameter at index ::1
Ah - it may be your use of $P!{}.
It's my understanding that by using $P!{} you're overriding normal
parameter replacement behavior and forcing iReport/jasperreport to
insert the parameter as a raw string value into the SQL without being
able to parameterize the query to leave data conversions to the
driver. In such cases I believe it's best to ensure that the
parameter being used is a string formatted exactly as you wish for the
SQL, and also to include any necessary quoting, either as part of the
parameter or in the query.
Is there a specific reason that you need to use $P!{} at this point in
your query? It seems like a normal $P{} would be fine, and in that
case the date should transit across the JDBC connection just fine. That
should also have the advantage of being database server agnostic as
the date isn't being converted into a string by your code, but inside
the driver.
If you have to stick with $P!{}, I'm guessing your current parameter
definition is causing the query to likely be built using the result of
.toString() on your parameter, which given what I think is the default
representation for a java.util.Date probably means that internally you
end up with a query like:
and submitted_date = Sat Jan 01 00:00:00 XXX 2050
(where XXX is the local timezone of the machine the report is run on)
In order to use $P!{}, I'd suggest:
* Make your parameter a type of java.Util.String and then construct the
value so it is exactly the string representation you want of the date.
So perhaps something like:
String.format(%tY-%tm-%td, new Date(01/01/2050))
* Include any necessary quoting in your SQL, which I think would be:
and submitted_date = '$P!{PARAM_TO_DATE}'
The combination of these two should, I believe, result in a SQL
command sent to the server of:
and submitted_date = '2005-01-01'
If Oracle doesn't like that format by default, just adjust the format
string as desired.
Beyond that, if you still have problems, I would try to obtain a trace
of the actual SQL that is making it to your server. I'm not familiar
with Oracle but presume there is statement logging of some sort that
can be enabled. Alternatively, I think you can use the JDBC driver
manager to add local logging to your JDBC connection.
-- David
--
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
___
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
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
