cbarlow3

So in the previous query, you had a line in your WHERE clause that said AND CARTELLA.N_CARTELLA=2 It sounds like you instead want the user to be able to enter 2 (or whatever number they like) and have the report select based on that. In iReport, make sure you have the "Report Inspector" pane open. In that tree, you should see "Parameters" with either a + or - sign next to it. If it's a + sign, click on the + to expand that part of the tree so you can see the parameters that iReport creates for you automatically (REPORT_CONNECTION, REPORT_TIME_ZONE, etc.). Now if you right-click on "Parameters" you can "Add Parameter". iReport will automatically add a new parameter to the tree with a name of "parameter1" that is of Parameter Class java.lang.String. If the data you are comparing to is actually an Integer, for example, then you need to change the parameter class. Left click the parameter "parameter1" in the Report Inspector tree if it is not already selected. Now look in the Properties pane. Change the name of the parameter from parameter1 to n_cartella (or whatever name you like), and use the Parameter Class dropwdown to the class to java.lang.Integer, or whichever data type agrees with the data in the CARTELLA.N_CARTELLA field in your database. Leave the "Use as a prompt" checkbox checked. You can assign a Default Value Expression or not. Now, in your query and in any expressions you use, iReport has a syntax where parameter names are surrounded by $P{}. So you change the line that has a hardcoded number 2 in your WHERE clause to instead read: AND CARTELLA.N_CARTELLA=$P{n_cartella} That should be enough to get iReport to prompt you for the parameter and use it for comparison when you are running the report from the iReport preview. If your actual runtime environment is eventually JasperReports Server, then when you deploy your report to the server repository, you will also have to set a property on the report to make it prompt, and you'll have to define an Input Control that has the same name and data type as your parameter. Carl

Based on your error message, I assume that you are using Oracle rather than DB2, and Oracle doesn't yet support the WITH clause I used in my example. SQL Cookbook has an Oracle solution to the problem as well, but it uses a SYS_CONNECT_BY_PATH function, a START WITH clause, and a CONNECT BY clause, none of which I'm familiar with, and of course since I'm not running Oracle here, I couldn't test any of it to be sure I understood how to change the author's example to meet your needs. I have pasted his actual example query below (except I've changed the table and field names to unabbreviated versions so it is easier to tell what the example is about), but he also has a page and a half of explanation, which I won't copy for copyright and laziness reasons. :) I hope studying the Oracle example he gives helps get you to the next stage where you can use a similar technique in your own query. Code is pasted below. Carl Code:select deptartmentNumber, ltrim(sys_connect_by_path(employeeName,','),',') employees from (select departmentNumber, employeeName, row_number() over (partition by departmentNumber order by employeeNumber) rowNumber, count(*) over (partition by departmentNumber) counter from employee ) where level = counter start with rowNumber = 1 connect by prior departmentNumber = departmentNumber and prior rowNumber = rowNumber - 1

I normally run reports only from within JasperReports Server, but the one test I did over a year ago with running one from a Java program, here was the ksh script I used to run it: function addPath { for arg in /cr/web/apache-tomcat/webapps/jasperserver-pro/WEB-INF/lib/*.jar;do classpath="${classpath}:${arg}" done } CLASSPATH=. classpath="${classpath}:/cr/web/apache-tomcat/lib/db2jcc.jar" addPath java -classpath "${classpath}" JasperReportGenerator\ jdbc:db2://www.corelationlocal.com:50001/D0000T00\ cr0000db\ cr0000db\ test.jrxml ---- Our JasperReportGenerator.java accepts a database name, a username, a password, and the jrxml filename of the report it's supposed to run. Notice that the java command includes a classpath switch, and the classpath consists of a DB2 Universal Driver jar file (obviously, if you're using SQL Server, I presume there's a different driver jar file somewhere on your system), plus all the jar files that are in our jasperserver-pro/WEB-INF/lib/ directory. I hope you can translate this successfully to your environment. Carl

Which version of SQL are you using? As near as I can make it out, it sounds like OPERATORI is a parent record of CARTELLA, which is a parent record of INTPRE. For a specific CARTELLA (where N_CARTELLA=2), it is only associated with a single OPERATORI record, but is also associated with one or more INTPRE records, correct? I only know enough Italian to find my way around train stations and restaurants, so as soon as your table and field names are abbreviated, it all becomes completely theoretical for me! :) If I am correct so far, then I want a single result row for that CARTELLA, and I want it to list the various values of INTPRE.PRE_DES_PREST in a single text field. I don't see how I can also get back INTPRE.PRE_ANNO, since there would be more than one of these also. For now I will give you a solution that does not return PRE_ANNO until I understand how you want that handled. If you actually want a result row for each distinct value of PRE_ANNO, that's possible, but I'll start with a version that just omits PRE_ANNO. The code below is the same DB2 solution as before, but this time with your table and column names. Carl P.S.: At least in DB2, recursive common table expressions (CTE--the "WITH" clause) have lots of restrictions, like not being able to do an INNER JOIN or ORDER BY in the recursive half of the UNION ALL, for example. That's why I've used a Cartesian JOIN and a WHERE in that second half. The sorting of the PRE_DES_PREST is handled for you automatically by the ">" comparison in the WHERE clause of that second half of the CTE. Essentially, the first half lists all values of PRE_DES_PREST (and gets a count of how many there are per CARTELLA), while the second half first lists all pairs of PRE_DES_PREST where the second value is greater than the first value, then lists all triplets of PRE_DES_PREST where the third value is greater than the second value, etc. In the end, only the rows that have the same number of values in the "List" as the total number calculated for that CARTELLA in the non-recursive first half of the CTE are selected. These will be rows where the lowest value of PRE_DES_PREST for each CARTELLA is listed first, followed by the next lowest value, etc. all the way to the hightest value. Code:WITH x (PreCartella, IntPreCount, PreDesPrestList, PreDesPrest, ListLength) AS (SELECT INTPRE.PRE_CARTELLA, COUNT(*) OVER (PARTITION BY INTPRE.PRE_CARTELLA), CAST(INTPRE.PRE_DES_PREST AS VARCHAR(500)), INTPRE.PRE_DES_PREST, 1FROM SINSNT_TEST.INTPRE INTPREUNION ALLSELECT x.NCartella, x.IntPreCount, x.PreDesList || ',' || INTPRE.PRE_DES_PREST, INTPRE.PRE_DES_PREST, x.ListLength+1FROM SINSNT_TEST.INTPRE INTPRE, xWHERE INTPRE.PRE_CARTELLA=x.PreCartella AND INTPRE.PRE_DES_PREST>x.PreDesPrest)SELECT CARTELLA.COGNOME AS CARTELLA_COGNOME, CARTELLA.NOME AS CARTELLA_NOME, x.PreDesPrestList AS PRE_DES_PREST_LIST, OPERATORI.COGNOME AS OPERATORI_COGNOME, OPERATORI.NOME AS OPERATORI_NOME, OPERATORI.INDIRIZZO AS OPERATORI_INDIRIZZOFROM SINSNT_TEST.CARTELLA CARTELLA INNER JOIN x ON x.PreCartella=CARTELLA.N_CARTELLA INNER JOIN SINSNT_TEST.OPERATORI OPERATORI ON CARTELLA.COD_OPERATORE=OPERATORI.CODICEWHERE ListLength=AttributeCount AND CARTELLA.N_CARTELLA=2 Post Edited by cbarlow3 at 02/24/2012 17:50

I don't know if this will help or not, but it's a modification of an idea I use frequently: when I define a group header, I usually put real group information (like the value of the field that defines the group) at the top of the header, but at the bottom of the header band, I put the column header labels (usually in a colored Frame element) rather than having a column header band. Then for the group header, I check the box that says to repeat the header at the top of every page. Now, you don't want your subreport to run at the top of every page, but I have a possible solution for that as well (although I rarely use subreport). Because I really like the PDF bookmark capabilities that a hyperlink allows, I usually make two overlapping versions of my group label in the header. The first one I make bold and define a hyperlink. The second one I add " (continued)" to the text and I don't make bold or a hyperlink. Now the trick is to get the first version to only print when the group changes (the first time you print it per group, in other words) and use the second version at the top of all subsequent pages. This is done by using the automatic variable that is created when you create a group as part of a "Print when" expression. So...for the first version I use an expression like GroupName_COUNT==0, and on the second version I use a Print When expression like GroupName_COUNT>0. It works like a charm: I only get the bold and the hyperlink when I switch groups (followed by a column header row that is really just the bottom of my group header), but I get a more subtle version of my group header repeated at the top of every page, including again the row that is acting like a column header. I don't know if the same Print When trick will work on a subreport or not...I notice that "Print When" is one of the properties listed for a subreport...I just haven't gone so far as to try it. Carl

Well, I don't know the exact tables and fields in question, but I believe there are one or two ways to reach your goal: I just read yesterday something about a way to use a List element and have the list display results horizontally. I don't know whether they would be comma separated, and I don't know that they would look very natural in the middle of a letter. The method with which I'm more familiar is putting the list together directly in the SQL query itself. This solution comes from section 6.10 of Anthony Molinaro's SQL Cookbook, published by O'Reilly. That section is titled "Creating a Delimited List from Table Rows". He lists different solutions, depending on whethe ryou are using DB2, MySQL, Oracle, PostgreSQL, or SQL Server. I'm most familiar with DB2, so I've listed a DB2 version. I'll assume for this example that you have a table called PERSON with a primary key called SERIAL (a system-assigned sequence number) and also fields FIRST_NAME and LAST_NAME, and you're going to output a letter for each PERSON record that meets certain criteria. I'll also assume that you have a table called PERSON_ATTRIBUTE, which has primary key SERIAL, foreign key PARENT_SERIAL (pointing to a PERSON record), and DESCRIPTION, which might have values like "good hygiene", "conscientious", "punctual", "experienced", etc. Of course, I don't know if you have a finite list of these that you keep in one table and then have another linking table that lets you represent many-to-many relationships without duplicating the attribute descriptions, but the example is complicated enough without me making complicated assumptions about how you are storing the data. So...if I do a query like this: SELECT PERSON.FIRST_NAME, PERSON.LAST_NAME, ATTRIBUTE.DESCRIPTION FROM PERSON INNER JOIN ATTRIBUTE ON ATTRIBUTE.PARENT_SERIAL=PERSON.SERIAL I get a result like this: FIRST_NAME LAST_NAME DESCRIPTION Dante Alighieri poetic Dante Alighieri romantic Michelangelo Merisi artistic Michelangelo Merisi violent Michelangelo Merisi realistic ... But want to get back this instead... FIRST_NAME LAST_NAME DESCRIPTION_LIST Dante Alighieri poetic,romantic Michelangelo Merisi artistic,violent,realistic I've pasted the DB2 version of such a SQL query below. If you are using a different version of SQL, let me know and I'll attempt to convert my example to use one of the other techniques in this section of the book, which I highly recommend, by the way. Hope that helps. Carl Code:WITH x (PersonSerial, AttributeCount, DescriptionList, AttributeSerial, ListLength) AS (SELECT ATTRIBUTE.PARENT_SERIAL, COUNT(*) OVER (PARTITION BY ATTRIBUTE.PARENT_SERIAL), CAST(ATTRIBUTE.DESCRIPTION AS VARCHAR(500)), ATTRIBUTE.SERIAL, 1FROM ATTRIBUTEUNION ALLSELECT x.PersonSerial, x.AttributeCount, x.DescriptionList || ',' || ATTRIBUTE.DESCRIPTION, ATTRIBUTE.SERIAL, x.ListLength+1FROM ATTRIBUTE, xWHERE ATTRIBUTE.PARENT_SERIAL=x.PersonSerial AND ATTRIBUTE.SERIAL>x.AttributeSerial)SELECT PERSON.FIRST_NAME, PERSON.LAST_NAME, x.DescriptionList AS SHARE_LISTFROM PERSON INNER JOIN x ON x.PersonSerial=PERSON.SERIALWHERE ListLength=AttributeCount

I can't picture the problem yet...is your subreport right in the middle of a detail band that is repeated for every result row of your main query? If I understand the overall format of your report, I might have some ideas. For example, if the subreport is in your Summary band, I believe you can make the summary start on a new page and suppress/redefine page headers on the summary. Carl

I'm confused by your statement that your report only has one column (your reason that just changing the print order won't work), yet you are clearly showing that you want your output to be in what I would normally consider to be multiple columns. So why aren't you just changing the number of columns and printing horizontally? I've attached three versions of the same report that do exactly that: vertical one column, vertical six columns, and horizontal six columns. Carl

You might check my answer to what I think is a similar question posted here: http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=606. Essentially, my suggestion is to not expect to have user input make up part of your SQL statement directly. Instead, I prefer to make the default value of the parameter be null and then explictily check for that: WHERE $P{RESPONSE} IS NULL OR MyTable.Response=$P{RESPONSE}, for example. Carl

I hate all these "dynamic SQL" solutions. I see them all the time, and they just look more like "SQL injection" to me. I gather from this board that people use them all the time, but I avoid them like the plague. Why not just have a straightforward query like this: SELECT CITIES.NAME, /* I also do not like * instead of explicit field names */ CITIES.POPULATION, CITIES.REGION FROM CITIES WHERE $P{CITY} IS NULL OR CITIES.NAME=$P{CITY} That way your user is only entering a city name, not SQL (worst possibility) or something that triggers one set of SQL or another to be executed. There are several advantages. Also, if you are deploying these with JasperServer, and you have a fairly finite list of cities in a table, you could define a query and input control that would let the user pick one (or more--depends what you want to allow them to do) cities from a dropdown list instead of typing them in. You could, for example define a query called CityQuery that reads SELECT CITIES.NAME FROM CITIES ORDER BY CITIES.NAME (this assumes that city names alone are unique enough to select from...you really need to also grab state name or some such solution). You could then define an input control called CityList that is either a single select from query or multi select from query and points to CityQuery. Your main query could then have a LIST parameter called $P{CityList} (instead of a String parameter called $P{CITY}) and your main query looks like this now: SELECT CITIES.NAME, CITIES.POPULATION, CITIES.REGION FROM CITIES WHERE $X{ IN, CITIES.NAME, CityList } That last part isn't a typo: in the $X{} syntax, it's understood that the third argument is a parameter name, so you don't bother surrounding it with $P{}. One really nice feature of using the list input control is that it automatically seems to behave the way you've indicated you want it to behave: where if you don't pick ANYTHING, it's the same as picking EVERYTHING. You would actually have to code it differently to make nothing mean nothing, which is pretty convenient most of the time, since in most cases selecting nothing doesn't really make a lot of sense, does it? Another great thing about this query/list combo is that if you think it might be a pretty common need, you can define folders in the repository where you put useful queries and useful input controls and then just LINK to the input control instead of building it from scratch each time. I use this technique with almost every report I write (although not for city selection--I'm in financial software and use it to let the user filter a report by share type, loan type, branch, etc.). My data source is defined within the "organization", so any queries like this have to be in a folder that is within the organization (not in "Public"), and any input controls that use such a query also need to be in a folder in the organiation. I also have a few single or multi-select input controls defined in Public, but instead of using a query, they use a hardcoded list of values (also defined in a Public folder) that don't depend on the database information. I hope at least some of these ideas prove useful to you. Carl

I think you have a few options, depending on the desired effect. If each page should actually be a separate detail AND you want that title to appear at the top of each of them, then you should really have that logo, page number, etc. in a Page Header band, not a Title band (Title band only prints on the FIRST page, while Page Header band is repeated). You could also force a page break after each result row returned by defining a "dummy group" from some unique identifier field that comes back different for each result row. You wouldn't need a group header and group footer, but you would indicate that you want to start each group on a new page. That way you don't have to rely on the spacing being so perfect that a page break just HAPPENS to fall at the end of the content of a single detail. Then you should probably TRY to make each detail take up a page (instead of a page plus two lines, for example) by tightening up some of the spacing if necessary. On the other hand, if you really MEAN to only have that top section appear once, and you don't mind a second detail starting right after the first one ends (on the same page), then you can just define additional detail bands (at least you can in 3.7.1, which is what I'm using), and you can define what is currently a single detail band as perhaps four or five detail bands instead, which should cause the page breaks to happen between detail bands. Hope one of those two ideas helps out. Carl

A long time ago I got some good information on this forum regarding returning values from a subreport, including some tips on how to in the main report do a SUM on those returned values. I couldn't remember how to do it at all, and going back and reading my own notes is still a little confusing, but I think the last paragraph of my notes does have the answer you need, if you can just wade through it and make it make sense! I've provided the entire set of my notes on returning values, but I think only the last paragraph applies to your specific need: If you need a value from a subreport in your main report, do the following steps: · In the subreport, put the value you need to return into a variable. In my example NegSharesPerPerson.jrxml, I have a variable called $V{SubTotNegBal} that is returned every time the subreport is run in the detail band of NegSharesByBranch.jrxml. · In the main report, create a variable with the same data type (and often the same name, just for simplicity) as the variable in the subreport. In the Properties pane, make the Calculation be “System”. · Select the subreport, either in the Report Inspector or in the Design pane. Scroll to the bottom of the Properties pane, and select “Return Values” … A dialog box comes up where you can “Add” a return parameter. In the “Subreport Variable” you put the name of the variable in the subreport (unless it’s a system-supplied variable, it won’t appear in the dropdown—you’ll have to enter the name yourself, and you don’t need the $V{} around the name…it’s assumed to be a variable name. If you’ve already created your variable in the main report that you want the result to go in, you should be able to find it in the dropdown for “Local Destination Variable”. If you want the destination to just receive the actual value, choose “Nothing” from the “Calculation Type” dropdown. · Now you can drag that variable in the main report into an appropriate band on the report. If it is the same band as the subreport, choose an “Evaluation Time” of “Band”. Otherwise, “Now” or “Auto” will probably work fine. · If you want a variable that is keeping a sum of this returned variable per group or per report, do not set these variables up with the usual Calculation of “Sum” and variable expression of $V{SubTotNegBal}, because the variable that it’s totaling won’t be evaluated until all the subreports have run. Instead, set the Calculation to “System”, leave the Variable Expression blank, still set Reset type to “Group” (and set Reset group) or “Report”, etc. depending on what kind of subtotal you’re keeping. Then create additional returned values for the subreport, the same way you created the original returned value, but this time using “Sum” as the “Calculation Type” instead of “Nothing”. My main report NegSharesByBranch.jrxml defines three variables that are all returned from the subreport: SubTotNegBal, SubTotByBranch, and GrandTotal. They all have a Calculation of “System”, and in the subreport mappings, it shows that they all come from the same subreport variable, but the first one has a Calculation Type(in the subreport return values dialog) of “Nothing” and the other two have Calculation Type of “Sum”. Additionally, in the main properties for these variables, the reset type of these three variables is “None”, “Group”, and “Report” respectively.

According to the iReport Ultimate Guide (section on returning parameters back from a Subreport): "The value coming from the subreport is available only when the whole band containing the subreport is printed. If you need to print this value using a textfield placed in the same band as your subreport, set the evaluation time of the textfield to Band." Hope that helps. Carl Post Edited by cbarlow3 at 02/15/2012 15:19 Post Edited by cbarlow3 at 02/15/2012 15:19

Yes, I think so. Buried somewhere in one of my early explanations is the caveat that I don't think you can get a ReportExecution hyperlink to work in preview mode of iReport. Even if you could, you'd definitely have to change the _report parameter of that hyperlink, because "repo:" is a shortcut that JasperServer uses for the top of the Organization in the repository tree. That's also one of the reasons why I suggest focusing on getting them to work individually first (which of course you can test in iReport preview mode), and then at the end try adding the hyperlinks and deploying to JasperServer for testing. Is iReport preview mode your actual run-time environment for these reports, or are you ultimately deploying them to another environment? Carl

Unfortunately, you're probably going to have to find a "Fishbowl" forum. The actual user input isn't controlled by the iReport jrxml file. That just defines the names and data types of parameters to be filled in. HOW they're filled in depends on the run-time environment you use: if you use JasperReports server, you define "Input Controls", which can include dropdown boxes that let the user pick one (or multiple if you choose) values that are either hardcoded options or can even be the result of another query to your database (I use the latter all the time: I want this branch to be limited to only the branches/users/etc. that the end-user selects, but the possible values for that come straight out of the database itself). On the other hand, if you have a Java application that is running the JasperSoft report, then you have it create a HashMap and fill that in--any user interface is up to you to create. I found a page here: http://www.fishbowlinventory.com/wiki/IReport that discusses how to deploy an iReport to Fishbowl. About two-thirds of the way through, it starts talking about different types of parameters. I think the parameters that I normally define in an iReport would be considered "Standard Parameters", but I have to confess that I'd never heard of Fishbowl until just now, let alone used it, so how you add parameters in that product's UI is a mystery to me. It's obvious that you'll at a minimum have to define in Fishbowl the name of the parameter and data type (so Fishbowl can map it to the iReport parameters), but after that I haven't a clue. Good luck! Tell us how it goes! Carl

I have notes on how to pull an image (gif, jpg, etc., stored in Base64 format) from the database and display it in iReport (I've pasted those notes below, in case they're of help to anyone). I was hoping to use a similar technique to pull and display a PDF from our database! It seems unlikely to work, but our financial software stores receipts as PDFs, and I've written a report that lists transaction history. I was thinking how nice it would be to write a "drill-down" report, where each transaction on the history report has a hyperlink that would pass the transaction index to a second report that just pulls/displays the receipt for that transaction. I would also pass the parameters from the transaction report down to the receipt report so I can have another hyperlink at the top of the receipt report to "go back" to the history report (passing all the original parameters back to it). I also have notes on how to do the drill down and "drill up", so I think those are working corrrectly. My real problem is that the PDF isn't an "image", and I don't know if there's any support for treating it similar to an image and somehow viewing it inside a report. In fact, since it's a receipt, not a "document", I'd love to convert it to an image on the fly, but I'm just using iReport and JasperServer, so it's not as though I can program any kind of unsupported transformation within a Java application. Any ideas? Carl If you have a Base64 image field like PERSON_ID.IMAGE that you want to display on the report, here are the steps: 1. Obviously, include the field in the query itself. 2. Change the field data class of your field from java.sql.Blob to java.awt.Image. I don’t think that’s in the drop-down list in iReport, but you can go right into the XML and edit the line that looks like this: <field name="PERSON_IMAGE" class="java.sql.Blob"> 3. Drag an image element from the palette onto the design pane. When the wizard asks you to choose a file, click “Cancel”. iReport will still leave an image placeholder icon on your band. 4. Set the properties for the image. Set the size and position as normal. Change the Expression Class from java.lang.String to java.awt.Image (this should be available in the dropdown). Change the Image Expression from blank to the field you’ve gotten in your query, e.g. $F{PERSON_IMAGE}

I confess that I didn't actually read the code, but if your table, chart, etc. summarizes the information across multiple detail records, then you want that table/chart/whatever in a summary that follows the detail. That might be the summary band or it could be a group footer band, for example. Not only did you see the table "repeated" (appearing multiple times) when you put it in the detail band, but I suspect that the data in that table was changing with each detail, until finally you had one that represented the total information with the last detail. So you did the right thing creating in the summary band instead. Next problem: the blank pages. I suspect this is happening because you still have a detail band defined but have nothing displayed in that detail band. Your obvious choices are to either (a) drag some detail information into the detail band so you have pages of data leading up to a summary table, or (b) delete the detail band (right click on it in the tree and select "Delete band"--you can always change your mind later and create an empty one again) so you have just the Title and Summary bands and don't have a detail band (blank or otherwise) that prints for every row returned from your query. The thing that tricks people at the beginning (particularly if their first report is a summary, not detail) is that even though the design area makes it look like the detail band is a single row on a report, it's actually just the design for a row that will be repeated for every result row from your query. Hope that helps. Carl Post Edited by cbarlow3 at 02/10/2012 18:00

Sorry, I suggested that you take out or summarize everything that might change per PO.NUM (which is true, you should do this if you want to use GROUP BY), but you also have to include in your GROUP BY everything else, even though you don't expect it to change. So looking at your original query,even though you select fields from six different tables (VENDOR, PO, POITEM, PAYMENTTERMS, QBCLASS, and OBCLASS), it looks to me like fields in the PO record uniquely determine every field you select except POITEM.TOTALCOST. So I would SUM() that one field and then include all the other fields in the GROUP BY clause as shown below. Carl Another P.S.: in your case, your question is more about SQL than about iReport. There are LOTS of great places (online is probably sufficient) to learn more about SQL. After you have learned and practiced the basics, I highly recommend SQL Cookbook by Anthony Molinaro. For an interesting nuance on how to use GROUP BY, I came across the following link: http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx, where the author basically says that the first example (which looks just like my recommended query below) is inefficient and is "simply wrong". I think that's a little harsh. What he means is that there's a more efficient way to organize your query. I take his point that it's better to group by a single value rather than by a long list of values that came along for the ride or complicated expressions rather than just the values they depend on. Code: Post Edited by cbarlow3 at 02/09/2012 18:59

Can you post your SQL query? Can you post the error? I don't even know the data type of ID or what syntax you used in your WHERE clause, but if I assume for the moment that id is an integer data type, I would think either of the following would work: SELECT id, field2, field3 FROM recordName WHERE id=1 OR id=2 OR id=11 or SELECT id, field2, field3 FROM recordName WHERE id IN (1, 2, 11) Hope that helps. Carl

You'll probably do better re-posting (and/or searching) in the JasperReports Server forum. Not everyone on this board uses JasperReports Server. Carl

I think that the approach depends on whether you want to see the individual POITEM.TOTALCOST values and then see a total for the entire PO, or just see the total alone. If you just want the total, then the best way to do that is probably with your SQL query itself: you can add GROUP BY PO.NUM right above your ORDER BY PO.NUM, but then anything that doesn't remain constant within a PO.NUM in your SELECT should either be removed or summarized. For example, instead of selecting POITEM.TOTALCOST, you would select SUM(POITEM.TOTALCOST) AS PO_TOTAL_COST. You wouldn't need to define a group in iReport and use group headers and footers, because you would only get one detail line returned per PO, thanks to the way you altered the SQL query. On the other hand, if you actually want a separate row returned for each individual PO_ITEM, but have some PO-level information appear once at the beginning and a total cost for the whole PO appear after the last PO_ITEM is displayed, then your SQL query is probably fine as is. In that case, what you do is within iReport, right click on the top level of the tree and select "Add a group". Call it POGroup or some such thing, and make it be based off of $F{PO_NUM}. Say yes to a group header and trailer (you can always get rid of them later if you change your mind). Now you probably want to drag the PO_NUM and other PO-level fields into the header band, drag PO_ITEM-level fields (like $F{POITEM_TOTALCOST}) into the detail band, but then also drag the PO_NUM into the group footer band, change the value from $F{PO_NUM} to "Total for "+$F{PO_NUM}+": " or something descriptive like that. Then drag the $F{POITEM_TOTALCOST} from the field part of the tree into the group footer. You'll get a prompt about whether you're trying to display the actual value or a SUM of the values. Choose SUM. iReport will automatically creaet a new variable for you (you could have created it manually) that is the total of the $F{POITEM_TOTALCOST} values and that resets every time you start a new group (PO). You can even click on the new variable in the Variables part of the tree and look at the properties to see how iReport defined it. You could similarly drag that $F{POITEM_TOTALCOST} into the Summary band, choose "Sum", and it will create another variable that shows the sum for this field and doesn't reset for the entire report. So you can show a grand total for all PO_ITEMS/PO's dispayed on the report in your summary band. Both techniques are useful...just depends on which effect you're trying to accomplish. Hope that helps. Carl P.S.: I forgot to mention that a somewhat common error is to define a group within iReport without including the appropriate ORDER BY clause in your main SQL query. There are legitimate scenarios where you may want to define a group based on something other than the value that is used to sort the order that the rows are being returned, but it's much more common that you want to first ORDER them in SQL and then create an iReport GROUP on that same value. It's tempting to think that creating a group in iReport automatically sorts or re-sorts the data for you, but that's not the case...and that's actually a good thing, because it offers the flexibility to handle those unusual cases as well. Post Edited by cbarlow3 at 02/09/2012 17:12

No. So far, we haven't actually written any code to try to do any of this through web services anyway. Instead, some time after I've manually set up a new client (credit unions in our case) with a "live" organization and have sent up a standard library of queries, input controls, and reports in that organization (as well as a handful of datatypes and input controls in the public folder: "StartDate", "EndDate" and a few others that are reused constantly and don't require a database query), at some point the credit union realizes that they also want a copy of all that in an organization that uses the "test" database as a data source or their "month end backup" database as a data source. When that happens, I have the most luck (manually, through the JasperServer plug-in in iReport) with the following: I first create the new organization, naturally. I then copy all the folders from the live organization and paste to the new destination organization. Then I modify the new copy of the datasource so it points to the correct database. Then I double check my folder of organization-specific queries to make sure they're all pointing to the right database. Sometimes it seems like I get lucky, and the copy/paste actually changes the queries automatically to point to the local copy, but sometimes it doesn't. Then I go to my folder of organization-specific input controls (which use the queries I just modified). I check to see if those are pointing to the local copy of the queries or not. Finally, I look at the reports. I don't think I've ever found that they automatically change to look at the new data source, so I have to change each one individually. I also have to check every input control link (but not the local input controls that are unique to the report). If the input control link points to an input control in Public/ (like StartDate), I'm fine. But if it points to an input control that is organization-specific, I have another problem: I don't know of a way to change the input control LINK to point to a different copy of the input control. Instead, the interface shows me the values that define the input control itself. This is very dangerous for me. It's showing me the path to the LIVE query that the LIVE input control needs. If I change that to point to the test/monthend copy of the query, I've just accidentally re-defined the LIVE input control that all my LIVE reports are using to use a query that is based on the TEST/MONTHEND data source! About the only good news here is that users will get a permission problem and be unable to run the report, so it doesn't go unnoticed for long. For these input controls, I actually have to delete the input control link from the report and create a new input control link that points to the local copy of the input control. I think I've actually gotten lucky ONE time and had these change over for me automatically...I don't know what I did different in the copy/paste...it may be that there's an optimal order to copy/paste these folders in, but I haven't really found the complete answer to why it sometimes requires less manual work than other times. I hope this method of working your way out from the data source to the queries to the input controls to the reports helps organize the process for you, but as for automating it--I haven't found any help. JasperSoft really thinks of multi-tenancy as being only about serving multiple clients on a single instance of JasperServer, but the notion of test/backup databases for a single client is not as common an idea as I had hoped. Carl

I hope this helps: I haven't had exactly the same experience (hidden rows and columns), but I have had too many rows and columns defined, many of them very small, and I've also had spreadsheets where the end user was unable to re-sort based on a column, and here are the things I've done to help out: 1. I am sometimes tasked with writing about 100 reports, often starting out from very brief descriptions and without easy access to the end user to interview directly with questions. I have had times where I go through the extra effort of formatting a report so that it is optimally used as a pdf export: special fonts, colors, groups, subtotals, PDF bookmarks, etc.--only to discover that the end user really wants primarily an Excel export. It doesn't usually take me a lot of work to convert it, but it does mean that much of the work I already did was wasted. On the flip side, I sometimes find that the end user thinks they want an Excel spreadsheet, only to find out that the reason they are used to an xls is because the report I'm replacing didn't output the data in the order they wanted, so they are used to exporting it and re-sorting it one time only before interpreting/acting on the report. Sometimes it's hard to get the end user to really explain what the kernel of what they want and need instead of what they used to have, etc. But I digress... 2. Back to what I think your problem might be: I find that most of my xls export woes stem from not having fields in various bands lined up exactly. I usually strip out everything except a TITLE band and a single DETAIL band. I remove the margins, and I keep making the form ridiculously wide until I'm sure I've fit all my fields in, and then I resize to exactly fit the total width of the fields combined. I make my fields wider than I normally would if wrapping were acceptable, as it often is on a "normal" report. I do less formatting with the "pattern" property (I don't use the currency pattern, as I usually do on normal reports, for example). And I make my column headers be the only thing in the TITLE band. On normal reports, I usually put the column headers in a COLUMN HEADER band or else at the bottom of a repeated group header band, but in an xls, they probably don't want the column header to ever repeat. I don't output subtotals or totals, since they're going to want to manage that themselves in Excel. But most important, I don't rely on visually placing the fields: I make sure the TOP of every field is 0, the HEIGHT of every field is 20 (the height of my TITLE and DETAIL bands), I use nice round numbers for the widths so that I can quickly tell if Left(field A) + Width(field A) = Left(field B), etc. I also double check the Left and Width properties of every field as compared to the Left and Width properties of the column header that pertains to that field. If they are ONE pixel/unit off, the Excel export will define an extra row or column to make up the difference. And the closer you are to being exact without hitting it, the smaller those rows and columns will be, but they're still annoying and disruptive to the end user, as you've discovered. There are probably some nice shortcuts that I could use, particularly in the Formatting Tools Window, which I largely ignore. I've seen someone select several haphazardly placed fields and then click "Organize As Table", and it worked some magic that I haven't been able to reproduce very successfully, and it may be that the relatively new "Table" element itself would be the way to go, but I'm still using 3.7.1 Professional, so I don't have access to the Table element yet. Hope something in those suggestions helps out! Carl

I think you have two main problems: the first is that I think the expression you listed is going to interpret the "then" part of your tertiary ?: operator as a string, not as a SQL statement to be executed. I don't think these expressions have direct access to the database like that--only to returned fields (from a main query, a list, a table, etc., variables, and parameters. The second problem is that I think the condition you're testing, the value of client3 is not "()" as you assume it is. I don't know if that's because of the order you have your parameters defined or the data type or something else, but if it's jumping to the "else" part, something about the actual comparison isn't what you're expecting it to be. Otherwise, assuming that you're actually printing out the value of client3_null somewhere, I believe that instead of "( )" or blank or null or whatever is currently printing, you would see it print the text "(select c.client_id from clients c)". Now, maybe I misunderstood: maybe that's what you want that paramater to have in it (just the text, not the result of an SQL query), because maybe you're then going to use that text in your main query. If so, then ignore what I'm calling "your first problem". For the second problem, I would maybe do a few things: (1) verify that your paramaters are defined in the correct order if the value of one depends on the value of another (as they do here), (2) make sure the data types agree with each other (it looks like both ${Pclient3} and $P{client3_null} are supposed to be strings) and check what the default values are, (3) simplify your expression until you get the ?: working: $P{client3}=="()" ? $P{client3}+": true" : $P{client3}+": false" --I actually already included my fourth suggestion in that example, which is (4) make sure you're printing the value of $P{client3} to be sure you know what the value really was. In my example, I'm making sure that I'm recording what the value of $P{client3} was at the time I evaluated $P{client3_null}. I would drag a copy of $P{client3_null} onto the TITLE band of my report and see what happens. Once I get to where I understand what value is really in $P{client3} at that time and have it working predictably and always picking true (empty parens) vs. false (anything else) correctly, then I would change the "then" and "else" values back to what you really want them to be. Hope that helps. Carl

Several months ago I got some great suggestions on how to write the jrxml in such a way that it works in preview mode AND when it's deployed to JasperReports Server so you don't have to keep changing the code each time you change environments. My notes may be out of date (I see that I added further notes to my original notes, etc.), but for what they're worth... Source: djsiders on JasperForge.net 11/23/2010 Something I have found useful to have to keep from having to flip back and forth between the repository name and the local file path when working in iReports and the deployed server version of a report. Create a Parameter IS_RUNNING_LOCAL - type Boolean - default value false - use for prompt Create another Parameter SUB_REP_LOCATION - type String - do not use for prompt - set default to code below: $P{IS_RUNNING_LOCAL}.booleanValue() ? "SubReportName.jasper" : "repo:SubRepReposName" Then set your SubReport Expression property to: $P{SUB_REP_LOCATION} Of course change the two report names to match your sub report names. When deploying to the server do not use either of these params as input, just the boolean when running iReports. When you run in iReports, answer true to the IS_RUNNING_LOCAL prompt. This will cause iReports to use the local name/path. When running on the server with no prompt, it will default to false and use the repository name for the subreport. If you are doing a lot of dev work, you can temporarily change the default value to true so you can use the default of the prompt. An added benefit is that iReport will then find and compile the sub-report (at least in later iReport versions) as well. (Carl: I have more luck ending the repo: version of the name with the .jrxml extension. Also, when I run a preview of the main report in iReport, I get warnings during the compilation that the compiler is ‘Unable to locate the subreport with expression: “$P{SUB_REP_LOCATION}”’ Not to worry. That’s just because that parameter is filled in at run-time. As soon as you get the prompt and answer “true”, it should run fine. If you have multiple subreports, just create multiple parameters with different extentions: $P{SUB_REP_LOCATION_ASSETS}, $P{SUB_REP_LOCATION_LIABILITIES}, etc.) (Carl: rumor has it that the default behavior when deploying a main report to JasperServer changed after V3.7.1. One person recommends deploying Main report first, skip automatic procedure for subreports, then deploy subreports in resource folder as separate .jrxml files (or you can deploy them as their own report units for stand-alone capability). Then in the main report, set the subreport expression to the FULL pathname (apparently regardless of whether or not you’ve deployed as a resource), without an extension. It should be something like “repo:/Reports/MainReportName/subreportName”. To find actual full pathname, right click after the subreport in the repository navigator and select “Properties”.) --Carl P.S.: Thanks for posting the solution you found as well...sometimes people who find the answers to their own questions don't post them, and inquiring minds want to know! :) Post Edited by cbarlow3 at 02/02/2012 17:28