prehlb

I have a challenge for those using the $X{} clause in SQL environments. The definitive guide (Third Edition) has the following definition for the $X{} clause: JasperReports has built-in support for two clause functions: IN and NOTIN. Both functions expect two parameters: The SQL column or expression to be used as the left side in the IN/NOT IN clause. The name of the report parameter that will supply the values list. The value of this parameter can either be a java.util.Collection instance or an object or primitive Java array. Please note the bolded text: "or expression". I bring this up because I have an interest in having the actual SQL column name be wrapped in a function for checking against the collection for matches. For example, say we have courses with course ID's. One column name is COURSE_ID. But, some of the course ID's begin with certain characters, say "T", "K", "U" and "A" to define say a type of course. The report on courses might need to break them into two subreports - one for types T and K and the other subreport for U and A. Now, the SQL query would normally look like: 1. SELECT COURSE_ID, COURSE_NAME FROM COURSE WHERE $X{IN,COURSE_ID, CoursePrefixes} The parameter $P{CoursePrefixes} is defined as a java.util.Collection and contains ['T','K'], which are the first characters to match against in the COURSE_ID field. The problem above is that it is not looking at just the first character of the COURSE_ID field, it's looking at all the characters which is not what I need it to do. I need to have the SQL IN Clause resolve to: WHERE LEFT(COURSE_ID,1) IN ('T','K') which means the query should look like: 2. SELECT COURSE_ID, COURSE_NAME FROM COURSE WHERE $X{IN,LEFT(COURSE_ID,1), CoursePrefixes} But query 2 fails with syntax issues around the "1)". So, my question is how would you specify an "expression" for a columnName in the $X{} clause such as above? I have come up with work-arounds such as using MS SQL CTE's and such, but I wanted to know if there's any documentation for how to formulate an expression for the columnName. Thank you, Bill

Lucian, Yes, you are correct my example was wrong but also right, I was meaning to show that the comma needed to be enclosed in double-quotes, but I got carried away. Let me review my issue: When trying to create a JR that will be used exclusively for exporting to CSV, part of the requirement for the import specification (by the software that will be receiving the data) is that string fields must be enclosed in a set of double-quotes, e.g., "Bill Prehl, Jr" versus 3950 which is a numeric and does not require the double-quotes. Therefore, in the text expression field I would put: "\\""+$F{First Name}+"\\"" to make sure that the first name is surrounded by double quotes. What the JR CSV exporter does is enclose my double quotes with another set of double quotes (as you pointed out should be done via the RFC in #6). The resulting text in the CSV file is: """Bill""", """Prehl""", ... instead of "Bill", "Prehl", ... I could not find a way to have JR encase the fields in a set of double quotes without it enclosing the double quotes with another set of double quotes (like #6 in the RFC points out should be done). Does this make sense now? Thanks for the response. Bill

Lucian, I looked over the RFC4180 and found #5: (http://rfc.net/rfc4180.html#s5) which clearly talks about the issue I have at hand with enclosing the fields with a single double-quote. "Each field may or may not be enclosed in double quotes" The #6 item you are referring to is if I have a piece of text, say: John Smith, Jr <-- where the comma needs to be kept as part of the field and is not part of the CSV delimiter. Then it would need to be: AAA, John Smith"," Jr, BBB Because JR does not provide an option to enclose a field in double-quotes, then I am forced to append the double-quote to either side of my string. But, JR interprets my double-quote as a required quote as part of the original string, but in actuality, I\'m trying to force the #5 format to work with other programs that simply require a single double-quote around each non-numeric field. Furthermore, #7 (http://rfc.net/rfc4180.html#s7) covers the concept, again, of enclosing the field in double-quotes and how to "insert" or escape a double-quote inside the field as part of the original text. Lastly, I really appreciate your help on this because I had no idea there was an RFC standard for CSV. This gives us something to work from so Thank you for pointing this out. Bill

Teodor, Can you point me to documentation in 1.3.4 that can explain the comment in the changes.txt referring to the recursive expansion of $P!{} parameter references? I found you clearly explained the $X{} which is a fabulous addition to JR features! But I could not find a discussion in the forum on the recursive expansion of $P!{} parameter references. I've been using the $P!{} extensively to build my WHERE and ORDER BY clauses and I'm curious to know if you've helped make something easier in this regard. Thanks. Bill Prehl Post edited by: prehlb, at: 2007/06/19 14:21

I believe I can, but I don't want to send it with the query statements still embedded. I'll leave the entire template intact without the clauses. Would that be OK? Email me at prehlb at k12system dot com so I can send you the template. Bill

I'm using 1.3.3 and I believe it's a JasperReports issue, not an iReport issue. I'm experiencing the exact same issue - the group increment is incrementing on every record (detail section) so I'm not getting the desired string result which should be three strings concatenating instead of 10 strings. I'm going to also post this in the JasperReports forum but in case anyone else looks on this forum they will find that this issue has not been addressed. Bill Prehl

Hey thanks for the reply. We did figure out that we could build our own parser into a scriptlet and it works like a charm. So, you're theory was on the same route and it does work. Thanks for the ideas! Bill

Hi All, We've moved into the stage of developing reports after developing our rendering and job management engine that will work well for our customers. We came across a situation where we need to create letters. These letters need to contain various wording. Instead of writing one report for each wording variation we are looking to replace the text with styled text that the user can predefine as a parameter before submitting the request for the report to be generated. We're planning on allowing the user to use the FCKEditor to create the wording. But, in addition to the Bold, Italic and Underline feature we would give them the ability to place references to query fields for the report such as LAST_NAME or FIRST_NAME fields. This means we need to allow them to specify references like $F{LAST_NAME} but that won't word because JR is treating the string in the REPORT_PARAMETERS map as a plain string and not a JRExpression such as done with the Default Expression of a parameter. Does anyone have an idea of how we could pass an expression to a parameter instead of as string to a parameter? Therefore, we would pass a string that's to be interpreted as a string that would have strings embedded. Just as we can do with the Default Expression of a parameter but this time the information passed inside the REPORT_PARAMETERS map would be treated as an expression? I was thinking of trying to specify the class of the parameter as a JRExpression. Thanks in advance for any suggestions or knowledge. Bill

Scotty, You had it nailed! MSSQL doesn't know LIMIT command but my lead programmer had already created a table for counting. It's just a table that holds the numbers 0 to 8000 for various counting purposes so we called it _staticarray since it doesn't change. The name of the field is NUMBER (real ingenious). So, my final query format was just about what you said: select 0 as student_id, '' as first_name, '' as last_name, '' as grade_level, '' as phone_no, '' as address_1, '' as address_2, '' as address_city, '' as address_state, '' as address_zip, '' as address_zip_ext from _staticarray where number < $P{Skip} UNION ALL select sd.student_id, sd.first_name, sd.last_name, se.grade_level, sd.phone_no, sd.address_1, sd.address_2, sd.address_city, sd.address_state, sd.address_zip, sd.address_zip_ext ... So I wanted to thank you for the suggestion. It ended up working out nicely. The solution above is also portable so it's not locked into MSSQL. Thank you and have a great day! Bill

Hi everyone, I've searched the forum but it appears pretty sparse on mailing label solutions so I'm not sure if anyone can help. I have the mailing labels setup and working for Avery 5160 - that's not the hard part. The difficult part is to see if we can "skip" the first X labels to allow our users to reuse a partially used sheet of labels since the 5160 is a sheet of 30 labels. I'm just not sure if I need to create a "dummy" recordset (or subdataset) which is applied first or if I need to create a view on the SQL server side to append empty records at the beginning of the result. I'm even considering a scriptlet but I'm just not sure how to control "skipping" a label unless I use the Print Expression field. Thanks in advance for any thoughts and suggestions. Bill