cbarlow3

I have a user who wants a page subtotal at the bottom of each page, and when I drag the field I want totalled into the Page Footer, it asks if I want the actual field value or an aggregate function. I pick aggregate function "Sum", and it automatically creates a new variable for me with the name INCOME_1 with the same variable class (data type) as the original field, a Calculation of "Sum", a Reset type of "Page", an Increment type of "None" and a variable expression of $F{INCOME}. So far, so good, and that shortcut is faster than creating the variable myself manually and setting those properties. I frequently create such variables for a group footer or for a Total band...I think this is the first report I've done where I use a page reset. It should work the same, right? BUT...my end user had me define SIX detail bands, and I've tried to set up a dummy group around each one with a "Minimum Height to Start New Page" set large enough to display the entire set of detail bands plus a little, and I have "Keep Together" checked. So I might have only four sets of six detail lines display on a page, but it never breaks up one of these groups of six to be part on one page and part on another. Here (finally) is the strange part: the total at the bottom of the page seems to always include the first item on the next page and not include the first item on the current page (except on the first page, where the first item is part of the total, because it wasn't already considered part of the page subtotal of the previous page). So every subtotal at the bottom of the page is off as compared with what is actually printed on that page. It's as though it considers printing a detail, adds the income field to the page subtotal, then realizes that it doesn't have enough room to print the entire "group" together and jumps to a new page--AFTER it already added the amount in question to the old page subtotal! Can it really be working that way? Has anyone else experienced this, and if so, do you know a workaround? Carl P.S.: I'm on Professional 3.7.1.

Well, there are two things that don't sound like they fit very well with JasperSoft: 1. Using parameter input to filter the results of a query is very normal, and that input can be a user entering text, a date, selecting hardcoded values from a dropdown, selecting values returned from a query, etc. But in most cases, the main purpose of this parameter input is to control filtering or MAYBE sorting of the data. The actual column definition is typically hardcoded in the query (I'm going to return the company name, the city, the state, and the annual sales for that company, but I will let the user select what companies they want to include on the report or let them pick a date range for those sales, etc.). It sounds like you want the user to be able to actually MODIFY the query right down to what tables are accessed. I know that I've seen examples where people actually have parts of the SQL modified at run-time, but I avoid it because I feel that it's kludgy and potentially dangerous. It sounds like you're thinking of having the parameter get filled in with a tablename (never mind for the moment what the UI part looks like to the user) and then have your query just dump fields from that table, like: SELECT * FROM $P!{UserSelectedTableName}. That sounds like a horrible idea to me. I DO actually have a standard report that produces a PDF of all the tables and column names in our database (but none of the data), but I'm creating that based on a query of two of our database tables actually called TABLES and COLUMNS that store metadata about our database definition. I find this report helpful to remind me what the tablenames, fieldnames, and data types are, as well as the relationship between various tables. But I don't have a generic report that takes the place of a sort of database explorer portal. 2. The other thing that sounds strange to me is that you would want to hardcode the list that the user can select from. This certainly IS possible. The main difference between this and my previous notes is that instead of defining a QUERY first in JasperReports Server, you instead define a "List of Values" (and this can even be in the Public folder), where you define what the prompts will be and what will actually be filled in for the parameter. You then define an input control that uses that List of Values instead of an input control that uses a query. You then create a link from the Input Controls folder of your individual report(s) to the actual input control you created. In our database, I have for example a "List of Values" called "CheckStatusValues", where the options are hardcoded to Pending/P, Issued/I, Voided/V, Stop Payment/S, Reconciled/R, Escheated/E. The text before the slash is what I've defined as the "Label" that the user will see in the dropdown box, and then I associate each with a "Value", which in this case corresponds to the single letter abbreviation that we actually use in our database to represent a check status. Then I have a "multi-select from list of values" input control called CheckStatusList that points to that list of values. Now whenever I'm writing a check-related report and I want the user to be able to select what check statuses to include, I create a List parameter called $P{CheckStatusList}, and I link to that CheckStatusList input control wtihin JasperReports Server. Naturally, I also make sure my main query has a WHERE clause that includes AND $X{ IN, CHECK.STATUS, CheckStatusList } I hope some of this information is useful. If you're looking for a tool that will just let an end user browse through the database (I'd like to take a peek in this table, see what the fields are, look at some sample data, etc.), I don't think that's the purpose or purview of JasperSoft. The maker of the database itself (DB2, SQL Server, Oracle, etc.) would presumably provide such tools themselves. Carl I forgot to absorb your mention of JAVA. That probably makes all my discussion of JasperReports Server (including creating what it calls "input controls" moot. If you're using Java to make calls to JasperReports, then I think you have to create all your own user interface anyway...everything the user sees, you're creating as part of your Java program. Maybe I am misunderstanding your anticipated runtime environment. In fact, I still don't think I really understand how your end user will run your report or what your report is trying to do, so I think I'm going to stop providing any additional advice, as I'm probalby confusing you as much as you're confusing me. Sorry I couldn't be more help. Post Edited by cbarlow3 at 03/26/2012 18:53

I think I'm having a hard time understanding your scenario. The technique I describe lets the end user be prompted at runtime with a dropdown list that is generated in real time based on a query (like a list of company names) and the user is allowed to pick one of them (or pick multiple ones, depending on how you set up the input control), and the main report parameter is filled in with some key value(s), based again on how you set up the query and input control. So, your parameter in your main report might accept a company name as a String. Or it might accept a LIST of company IDs (primary key for a company table, for example), etc. Your main report would use the value or list in that parameter to filter out results in a WHERE clause. So...every time you run the main report, you first select a few company names from an alphabetical dropdown list, and then the report is generated, selecting only the companys you picked. I can't tell from your last comment if you're trying to affect the PROMPT (instead of the resulting parameter) or what you mean when you say that something you're doing affects prompts "later". Certainly I am building input controls from either database queries or hard-coded "list of values" at one point in time and then linking them to various report specifications later in time as I create them, but the actual USE of the input control happens mere seconds before the main query runs, so I just am not sure I understand whether you are looking for the same thing I described already or something completely different. If you can walk me through a chronological example of what you want the end user to experience, maybe I can help. Carl P.S.: I should say that we also need to verify whether your end user is running these reports in JasperReports Server or some other environment, since the way input parameters are filled is very different depending on whether you are running reports from within a Java program of your own making, preview mode of iReport, from within JasperReports Server, or some other environment. My advice thus far has all been under the assumption that you are using JasperReports Server, but this is really the iReport forum, so I figured I'd stop assuming and ask again. P.P.S.: I just now noticed that the questions were from two different posters. Sorry, I thought for a minute that the recent posting was a restatement of the original question from the original poster. Post Edited by cbarlow3 at 03/26/2012 14:42 Post Edited by cbarlow3 at 03/26/2012 14:45

This is a very important technique to know, in my opinion. 1. In your report, create a new parameter called $P{companyId} if you are only going to allow them to select a single company, but just so I can show you how to handle supporting a LIST, let's call the parameter $P{companyList}, and make the data type of that list be java.util.List. 2. In the WHERE part of your query, you now want to add either AND CompanyTable.Id=$P{companyId}, or if you are using a list, you use the following special JasperSoft syntax: $X{ IN, CompanyTable.Id, companyList } The word "IN" is a keyword for the $X{} syntax and should not be surrounded by quotation marks, the second argument is the value you are trying to see if it's in a list or not, and the third value is understood to always be a parameter, so you don't include the $P{} syntax. 3. This is all you need in theory to get a selection list working for iReport preview mode, but you still have to enter the different values you want, and like you said, who remembers the internal Id values, are you supposed to surround the text you enter with quotation marks or not, how do you use commas, etc. An interesting thing to note while you're testing in iReport is that if you just choose the default (null) value for this list parameter, the report will actually include all companies in your WHERE--any company value it finds is considered to be "IN" null. That's a little counter-intuitive, but handy, when you think about it. In most cases I run into, selecting "none" intentionally would always result in an empty report, so it seems that the default behavior is more handy. You could always code special logic if you didn't want that to be the default behavior: AND $P{companyList} IS NOT NULL AND $X{IN, CompanyTable.Id, companyList}, for example. 4. Now when you deploy to JasperReports Server (I'm making an assumption that's how your end user is running the report), you're going to want to add an input control, but you first need to create a query. If this is an input control that you will probably need for other reports, I recommend making a special folder in your Organization called Queries and another called InputControls. From the Repository Navigator pane in iReport, right click on your new Queries folder and choose Add | Query to bring up a Query dialog box. In the General tab, let's specify a name of companyQuery and a label of "Company Query" (without the quotation marks). In the Query tab, set the Query Language to SQL and set your Query to something like the following (I obviously don't know your database): SELECT CompanyTable.Id AS ID, CompanyTable.Description AS Description FROM CompanyTable WHERE CompanyTable.Status='Active' ORDER BY CompanyTable.Description. In this query, I'm making an assumption that although internally we'll be selecting a list of primary keys called Id, I'm hoping that the more user-friendly field that the user would like to see is called Description and that it is also forced to be unique within the table. On the Data Source tab, choose your data source from the repository (presumably the same one that your report uses), and click "Save". 5. Now right click on your new InputControls folder and choose Add | Input Controls to bring up the Input Controls Dialog. In the General tab, let's give a name of companyList (this must agree with the name of your parameter exactly) and a label of "Company List" (without the quotation marks). In the Input Control Details tab, scroll down and choose Multi Select Query (if you want to also define an input control called companyId, you might instead choose Single Select Query), and keep the "Visible" checkbox checked. When you choose "Multi-Select Query", it automatically adds a new section to the dialog box that has a "Query Resource" tab and a "Value and Visible Columns" tab. For Query Resource, select the "From the Repository" radio button and then browse for the query that you just created in step 4. In the "Value and Visible Columns" tab, you want the Value to be the thing that your main report is going to be comparing against--in this case, it's the ID column returned from your query, so just put "ID" (without quotes) in the "Value Column" box. For "Visible Query Columns", you can actually list multiple columns from your query that the user will see when making their selection(s) from a dropdown box, but since the ID isn't likely to mean much to the end user, we'll just add a single visible query column: click the "Add" button and type DESCRIPTION. The description values that appear will be in the order that our query puts them, which is why I put ORDER BY CompanyTable.Description. Click "Save". 6. Back to your report...the next time you need a companyList input control, you won't have to do steps 4 and 5 again...they're already done. So, deploy your report to JasperServer, then expand it in the Repository Navigator pane so you can see the Input controls folder that is local to that report. Right click on "Input controls" and choose "Link to an existing input control". Now browser your way to the companyList input control that is in your (fairly) new Organization | Input Controls folder, and click the "Open Resource" button. I will usually also right click on the report node itself in the tree and choose "Properties". The dialog that comes up has a General tab, a "Main Report and Data Source" tab, and an "Other" tab. I go into the "Other" tab and check the "Always prompt" checkbox. But of course, I do that any time my report has an input control to fill a parameter, not just when that input control is a list based on a query. 7. If all has gone well so far, then when you run the report in JasperReports server, it should prompt for "Company List" and provide a dropdown list of company descriptions in alphabetical order. You can select just one, you can hold down the CTRL key and select/de-select as many as you like, or you can not select any (which is the shortcut for selecting all, unless you've specfiically programmed something special to handle this case). Even though the user is seeing the description field, the parameter will instead contain a list of the ID values that go with the descriptions they selected. The special $X{IN} syntax in your WHERE clause will help your query determine whether or not a specific companyTable.Id value should or should not be selected based on whether or not it is contained in that list in $P{companyList}. I've tried to be pretty detailed in my explanation. Once you've created the query and the input control in a place where they can be re-used, it is of course much faster than I'm making it out to be. You just remember that you already have a multi-select input control called $P{companyList} and you remember that it prompts by description but returns a list of IDs, and you're set! Hope that helps. Carl Post Edited by cbarlow3 at 03/21/2012 15:40

I only found out you could exclude a band for specific export types a week or two ago (and it's already been very useful information!). How to add content conditional upon the export type? I think that depends on two things: how the report is "normally" deployed (under what conditions do you want the user to see the bar chart?) and is the information in a band that can be repeated (like Detail_1 vs. Detail_2, etc.). If your report is deployed to JasperServer and the internal view is where you want to exclude text and include the chart, I'm not sure how you could exclude the text. Also, if the text/chart is in the Summary (a non-repeatable band, as far as I know), I'm not sure how you could pull it off. If you're extremely lucky, maybe your end-user doesn't use the internal view much, but instead they normally export to either format 1 (PDF, HTML, etc.) that supports a chart or format 2 (CSV, let's say) that doesn't, and let's say that the chart/text is in a repeatable band like group footer. Then I think you could just make two group footers, one with text and one with the graph, and you could specify that you want to suppress the graph group footer band if you export to format 2, and you want to suppress the text if you export to format 1. I don't think the internal view is considered an "export", so I don't think you can use the exclude property on it. Carl

Oldest code troubleshooting technique in the book: print the thing that you think is causing a problem. Output the value of $P{Delivery_Date} in your Title band to see if it has the value you think it should have. Don't forget to format the date with a Pattern property. Carl

You get that kind of functionality in the DESIGN view of a cross tab report, but I don't think you get it during runtime in JasperReports Server. I think you can get it in whatever they're calling JasperAnalysis now. Carl

Well, the fact that it's trying to run the second report is a good start. Here are the things I would troubleshoot: 1. The second report itself. Have you verified that you can run the second report directly from the repository (not from the report 1 hyperlink) without error? Obviously, if you can't run it, the link can't run it. 2. There could be a mismatch between the report resource name and what the hyperlink is trying to execute. I don't think that would give you the error you got (I would expect something more like "resource not found" or something.) You could actually change the report name in the link to something you know is wrong, just to see if you get the same error or a different one. If you get a different error (and that's the only change you've made), then you've ruled out a resource name mismatch, and you can set it back the way it was before. If you suspect a mismatch problem, remember that the link should contain the path to the resource from the repository, and it actually should contain quotation marks. In my example, the resource name is “repo:/Reports/SingleCity”, because I keep my custom reports in a folder called "Reports", but your repository might be organized differently. Also, you want the resource name at the end of that to be the resource name, not the display name. 3. There could be a problem where you're not having the first report pass parameters correctly to the second report (if your second report has parameters). If this is your first time doing a drill-down report, you might consider actually substituting a simpler report that has no parameters as report 2 just to see if you can get any report to run from the hyperlink. Assuming you have local copies of these jrxml files, just replace report 2 with the newer, simpler report, and delete the input controls you built on the repository. I guess all of my suggestions boil down to: get the newest thing you're trying (hyperlink run of a second report) to run something as simple as possible (a report that already works when you run it directly and that has no parameters) and then one-by-one start changing the scenario to be more like the one you need to work in production and re-test to see where you're having a problem. I could write a few paragraphs on the way to pass parameters to the second report, but what's the point if you're not sure if that's your problem. Divide the problem until you find the specific area that seems to be breaking the functionality. Hope that helps! Carl

Okay, as I said, I don't use subreports much, but I have played around with this exclude capability, and it looks to me like when you use these two lines: <property name="net.sf.jasperreports.export.xls.exclude.origin.band.2" value="pageFooter"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.report.2" value="footer_landscape_A4"/> I think you're just saying to exclude the pageFooter from the subreport called "footer_landscape_A4". If what you're trying to do is to exclude the pageFooter from your main report, you don't specify a .report extension. You would just have the first line and not the second line. On the other hand, it's possible that the report won't let you suppress a band unless you also suppress every band of every subReport in that band??? If so, it would be a pain, but I guess it would look something like this, depending on what bands you use in the subReport: <property name="net.sf.jasperreports.export.xls.exclude.origin.band.2" value="title"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.report.2" value="footer_landscape_A4"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.3" value="pageHeader"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.report.3" value="footer_landscape_A4"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.4" value="columnHeader"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.report.4" value="footer_landscape_A4"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.5" value="detail"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.report.5" value="footer_landscape_A4"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.6" value="columnFooter"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.report.6" value="footer_landscape_A4"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.7" value="pageFooter"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.report.7" value="footer_landscape_A4"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.8" value="summary"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.report.8" value="footer_landscape_A4"/> <property name="net.sf.jasperreports.export.xls.exclude.origin.band.9" value="pageFooter"/> Of course, if your subReport has groups or contains subReports of it's own, then it would be even more complicated. I haven't tested trying to exclude a band that has a subReport, but thanks to you, I WAS successful in writing a report that has a title, columnHeader, detail, pageFooter, and summary, and automatically suppressing all but the detail when I export to CSV, XLS, or XLSX (which seems to follow whatever directions you give the xls exporter). Thank you. Carl

It sounds like you have some success in supressing certain elements of a report when you export to xls, and I'd never heard that was possible before. I'm very interested, and I wasn't sure if you were putting the property specifications you listed directly into the jrxml of the specific report or if there is a place in iReport designer view where you can set this or if you had to go in and set something special in a configuration file for the exporter (which would affect all files you export to xls) or... I like to help respond to questions when I know the answer, but in this case your question tells me that you already know more about this than I do, and I was trying to at least get caught up to where you are, because I can see how useful it would be to be able to write a report that looks one way in JasperReports Server or in PDF but has some of the "extras" automatically stripped out if you go to export to CSV or XLS. I mostly stay away from SubReports, so I may not be able to help with your specific problem, but I'd love to start experimenting with these properties you're working with, and maybe I'll be able to help someone with a question about it in the future. Carl

You just blew my mind. I don't know the answer to your question, but I'm having to do more reports that are ultimately designed for export to CSV, and I really don't like having to create a report that has NO title or column headers, etc. just so it can be exported. I would LOVE to be able to have the report still have a few extras while you are viewing in JasperReports Server or if you export to PDF, but then have those bands automatically be ignored when exporting to CSV specifically. Even though you're having problems with part of it, it sounds like you've had some success. Can this be done on a report-by-report basis, or how are you accomplishing this? Carl

Yes, I do this all the time, because I want my column headers to be right before the data in the detail record that it's actually labelling, so what I do whenever I use groups is the following: 1. For each group, I select the "Reprint headers" checkbox so the current group header(s) repeat at the top of every page. If I have multiple levels of group headers, I might make the top level one also start on a new page. 2. For the lowest-level group header (the one that will always print just before the first detail record), I make sure the band height is enough to contain a description of what group I'm in and some column header information. Usually for me this means I make the lowest-level group header have a band height of 40. I usually start my column-style reports with a template that has a blue Frame element already in the Column Header band, so I usually just cut and paste that from the Column Header band to my lowest level of Group Header, and then I drag it so the top of the Frame is at 20. Then I delete the actual Column Header band, because I no longer have any need for it. I'll also set the "Min Height to Startt New Page" property for the group header to 80 or something like that to try to make sure that at least the group header (40) and one or two detail records (20 each) will fit on the page, otherwise I jump to a new page to start the group. 3. I like to make a distinction between when a group changes vs. when the header is re-printing at the top of the page, so I usually create two overlapping text fields at the top left of each group header band. They both contain the description of the group like $F{DepartmentName}, but one of them I put in bold and give a Print When condition of $V{DeptGroup_COUNT}==0 (or whatever the name is of the count variable that iReport automatically creates for you when you create a group). The second version I usually add +" (continued)" to the Text Field Expression, I don't make it bold, and I give a Print When condition of $V{DeptGroup_COUNT}>0. Finally, I'm a big proponent of PDF bookmarks, so I usually right click the first version of the group description (the one I'm printing in bold when the group count is 0) and select "Hyperlink", set the "Anchor Name Expression" to $F{DepartmentName}, set Bookmark Level to 1 (if this is the top level group header--use 2 for the next lower level, etc.) and I leave the other settings alone. I usually also create a Text Field in my Summary section that says "Total" and create a level 1 hyperlink on that as well so it's easy to jump right to the summary. It's nice that the overlapping fields with mutually exclusive Print When conditions that I originally created so that one version could be in bold while the other version could say " (continued)" on the end actually also proved to be the key to making sure I could create a PDF bookmark that only comes into play when the group changes and not when the group header is being repeated. I think the answer to your question is really just the part of a sentence I put in bold, but I hope the other techniques prove useful to you and others as well. I really like the consumer of the report to always know at the top of every page where they are in the report, but I don't like to separate column header labels from the data they are labelling, and I like to help a PDF user navigate the report easily. All these goals are met using these techniques. Carl

If the 1, 2, 3, and 4 in the left column indicate that your example is for four different result rows being returned from your main query, then I think you're going about it the wrong way. If you had a single detail band, that band would be repeated for each row returned from the main query. Adding a second detail band just lets you specify additional things that will print for each row returned, but as you've discovered, the order is first print every detail band for the first result row, then print every detail band for the second result row. Your required output makes it look to me like you first want to print information on every result row. Then when that is done (maybe even taking multiple pages), you then want to revisit every result row and print a second set of information (or same information in a different format, etc.). I can think of a few ways to do this, but I think the main thing is to not try to trick iReport into thinking a single set of result rows is two sets. Instead, actually return all the results twice in your main query, adding a contrived field as a breadcrumb of which pass you are on, pass 1 or pass 2. Here's an example: Change your query that looks like this: SELECT a, b, c FROM MyTable ORDER BY a to instead look like this: SELECT Pass, a, b, c FROM ( SELECT 1 AS Pass, a, b, c FROM MyTable UNION ALL SELECT 2 AS Pass, a, b, c FROM MyTable ) x ORDER BY Pass, a I normally space my queries a little differently, but I'm trying to save room! :) Then you will essentially run through all your original results two times, once with PASS having a value of 1, and once with it having a value of 2. Then you can even group by Pass in iReport if you want to have headers and footers for each section, keep some subtotals for each section, automatically split to a new page when you go from section 1 to section 2, etc. Whether you do that or not, you can use the value in $F{Pass} to determine how you want your detail to look. The easiest way is to probably define a Detail1 and a Detail2 and to have each detail have a Print When condition that is mutually exclusive: For Detail1 make the Print When expression be $F{Pass}==1, and for Detail2, make it be $F{Pass}==2. Then you can do what I think you originally did: treat Detail1 as your specification for how you want to present the data the first time through, and Detail2 as your specification for how it will print the second time through. Hope that helps. Carl Post Edited by cbarlow3 at 03/07/2012 16:36

I continue to recommend Anthony Molinaro's excellent SQL Cookbook, published by O'Reilly. This problem is discussed under section 9.10 "Filling in Missing Dates". The syntax of the solution depends on what flavor of SQL you're using. The book always gives sample solutions for DB2, Oracle, PostreSQL, MySQL, and SQL Server. Personally, I'm using DB2 on a daily basis, and the solution is to use a recursive common table expression (CTE) to generate a temporary table of all dates within the range, and then you can use a LEFT JOIN to link that table to your actual data. I've pasted an example of how that's done below. The DatesInRange CTE has a non-recursive section first that refers to the parameters, and then a recursive section that lets it generates all the other dates. Then the main query links this list of dates to your actual data and groups, counts, and sorts it. I had to use CORE.ENV in my non-recursive FROM section. In my database, that's just a table that has a single row ("ENV" standing for "Environment"). Your syntax might allow you "FROM NULL", which is really more correct. Hope that helps out. You can see an interesting example of how to use such a temporary table of dates in another posting of mine titled "Example of calculating holiday vs. business days in a date range". Carl Code:WITH DatesInRange(SD, ED) AS(SELECT $P{StartDate}, $P{EndDate}FROM CORE.ENV /* This is a table that only has a single row--I needed to put something here */UNION ALLSELECT SD +1 day, EDFROM DatesInRangeWHERE SD < ED),SELECT DatesInRange.SD, COUNT(MyData.PostingDate) AS DateCountFROM DatesInRange LEFT OUTER JOIN MyData ON MyData.PostingDate=DatesInRange.SDGROUP BY DatesInRange.SDORDER BY DatesInRange.SD

It depends on your runtime environment. In production, do your users actually run the report from within iReport? Probably not. Do you deploy the report to JasperReports Server repository and they run it from there? If so, I have to agree that I know of no way to with the current exporter button to have it automatically pop up multiple PDFs or to automatically save them with some sort of sequence number or something like that. In either of those cases, I think your best bet--assuming that each record output can fit on a single page--is the solution provided before: generate a single report and then run it through a burst program that will split a single PDF into multiple PDFs. On the other hand, if your reports are being run from a Java program, then you clearly have more control: you could from within the program run a query that just gives you an appropriate key for each detail record, and then in a loop you run your report once for each of these keys. Naturally, you would need to modify the report slightly so that it accepts an input parameter that will tell it which detail record to select, because when you run it this way, you want the report to only produce output for a single record. This way your Java program is producing one PDF for each iteration of the loop and deciding what to do with it (what name it gets, where it is stored, etc.). If bursting is still the best solution, I've used the suite of products from www.pdf995.com, which you can try out the free version. Carl

The attached report has a simple query that generates twelve different detail records with a field called $F{PAGE_NUMBER} that varies from 1 to 12. I deleted all bands except a detail band, I created a "dummy" group based on this field and specified that I wanted each group to start on a new page. Then I defined twelve detail records, all with mutually exclusive "Print when" conditions. Then, for each detail record, I put some static text on each. I made sure that my fake static text includes the page number, just so you don't lose your place, since you'll presumably want to replace my static text with your own static information. Good luck. Carl

I also start with a standard landscape or portrait template that already includes alternating color bands in the detail record, a few fields from our one-row ENV (environment) table in a list in the header, specific background colors for the alternating bands and for the column header, etc. I've attached a copy of my landscape template. I also have a very specific set of procedures I use whenever I add groups to this so that the header is repeated at the top of every page, the column header labels are moved to just above the detail band (moved to the bottom of the innermost group header band, in other words), PDF bookmarks are added at the beginning of a new group (but not for the continuation repeat of group headers), etc. Those do not appear in the template. You might also try contacting Dave Jarvis, who is sometimes on these boards. Just over a year ago he wrote a book called Indispensable: iReport, JasperReports, and R--Efficiency, Maintainability, and Prediction. I remember it has some sections with recommendations on how to come up with standards, although at least half of the book is on the use of the R language, so it's not 100% JasperSoft related. You can probably purchase his book from his web site: http://whitemagicsoftware.com/books.shtml. Carl

I can think of a few solutions, but it depends how you're using the variable. For example, if the value is a counter or some value that only increases, you could define three variables: one that resets by Group, one that resets by Page, and one that doesn't reset at all, but the variable expression is the lesser of the two values of the other two variables (of course, you don't even need that third variable...you could just display whichever one is less if the point is to display this variable on the report). A brute force solution is to only reset the variable at the page, but define the group so that it always starts on a new page! :) Carl

I'm not familiar with TRUNC, but assuming it returns a Date value, why not just return a date and a time, like this: SELECT trunc(TIMESTAMP) as DATEFIELD, TIMESTAMP as TIMESTAMP FROM TABLE WHERE TIMESTAMP BETWEEN $P{FromDate} AND $P{ToDate} ORDER BY TIMESTAMP By the way, I took out the GROUP BY clause in this query, because you didn't have any aggregate functions like COUNT() or SUM() that necessitate it. Now, if you want to use iReport Groups to add headers and or footers (with subotals, for example) per date, then you can now define your iReport group based on the new field $F{DATEFIELD}, but you still have access to the TIMESTAMP field to use in the Detail band. I added ORDER BY TIMESTAMP, which accomplishes two things: first, it's intuitive that if you group by something in iReport, you normally want it to agree with the sort order...I could accomplish that by ordering off of TIMESTAMP or off of trunc(TIMESTAMP). The second thing this accomplishes is that because I chose to order by TIMESTAMP instead of the date, it means that the details within each date will be in order by time as well, which is probably a good thing. Carl

I'm using 3.7.1 Professional, and it's working with just db2jcc.jar. After you've installed iReport and then used Help | LicenseManager | Install License to install the license, here are what I think are the relevant steps from my installation notes (your mileage may vary, depending on how different your environment is): 1. Register the IBM DB2 Universal driver through the "Services" window of iReport: Window | Services Select Databases node Select Drivers note, right-click it, and select New Driver Add IBM DB2 Universal Driver and locate the driver file (db2jcc.jar)2. Services: Then, for each database, add a new connection: Windows | Services Select Databases node, right-click it, and select New Connection to bring up the "New Database Connection" dialog. Choose basic setting tab Name: change dropdown from default of "JDBC-ODBC Bridge" to "IBM DB2 Universal Driver" For "Data Input Mode" radio button, select "Direct URL Entry" User Name: (need an operating system username that has read access to the database) Password: enter the password for this user, and optionally check the "Remember password" checkbox JDBC URL: jdbc:db2://yourHostNameOrIPAddressHere:50000/DBName Click OK, and in the next dialog, confirm the Schema (on our system, it always seems to default to the one we use...obviously, if it doesn't for you, then set it to the correct schema), and click OK.3. For each database, add a new datasource (use the "Report Datasources" icon in the main iReport toolbar. It is located to the right of the Undo and Redo arrows and to the left of the datasource dropdown box): In the Connections/Datasources dialog, click the "New" button In the "Datasource" dialog, it asks you to "Select the datasource type". Select the "NetBeans Database JDBC connection" and click "Next" In the "NetBeans Database JDBC connection dialog, enter a "Name" for the data source Be sure to choose the appropriate connection from the cdropdown box, particularly if you created multiple, similar looking connections in the earlier step. Click "Test" to test the connection, and if it is successful, click "Save" Click "Close" to close the Connections/Datasources dialog box.I hope most of those instructions are applicable to your environment as well. Carl

I don't understand the problem, but I understood the solution! :) I was just writing up some internal documentation yesterday and included this kind of example where you want to use a parameter from the main data set while you're inside a subdataset for a List, Table, etc. Yes, you either have to add that jrxml code manually or else you can right click on the List and choose "Edit list datasource" (the same area where you tell the list which Sub dataset to use and tell it to use connection $P{REPORT_CONNECTION}), and choose the Parameters tab. Each time you add a parameter (and it's easier if you've already defined the parameter in your subdataset, because you can just select it instead of typing in the name you plan on giving it), you also specify an expression from the main dataset that should be passed to that subdataset parameter--sometimes it's a main parameter, sometimes it's a field, variable, function, or some combination of those. Carl

Well, I don't think it works with PDF--as far as I know, if you export a report to PDF, normal level 1, 2, 3... bookmarks are just used for navigation within the PDF. If you want a hyperlink to run another report, I think that only works from within JasperReports server internal view of the report, not from an export. You didn't mention whether your reports are deployed on JasperReports Server or not. If they are, I've pasted below my notes on how to get "drill-down" or "drill-up" functionality to work with special Hyperlinks that are set to type "ReportExecution". I hope they help. Carl If you have a report that produces details, and you would like to make each detail be a hyperlink that can call another report, potentially passing one or more fields as parameters to the second report, that is called a “drill-down” report. You can have one report drill down to another and have that report drill down to another (multiple levels). You could also create a link on a report that returns to the link from which it was called (a “drill-up”?). If the calling report had input parameters, the called report would have to pass those back again, whether the called report actually used them or not, so the calling report has to pass any input parameters to a called report if the called report is going to have “drill-up” functionality. As an example, let us say that we have a report called SingleState that accepts a state as an input parameter ${TargetState} and generates details that include a field called $F{ADDRESS_CITY}. Furthermore, let us say that we have a separate report called SingleCity that accepts a state and a city as input parameters $P{TargetState} and $P{TargetCity} and generates details with items from the database that relate to that state and city combination. Once both reports are working correctly, you can make the first report drill-down to the second report as follows: 1. In the Report Inspector, right click on the field in the detail record that you want to turn into a hyperlink for the drill-down. Select “Hyperlink” to bring up the Hyperlink dialog box. 2. You can leave “Anchor Name Expression” blank, leave “Bookmark Level” at 0, and ignore the “Reference”, “Anchor”, “Page”, and “Tooltip” tabs. Set “Hyperlink target” to “Self”, and set “Hyperlink type” to “ReportExecution” (from the dropdown control). 3. In the “Link Parameters” tab, you will need to add each parameter you wish to pass to the subreport, but you will always need to create a parameter with a Paramater name of “_report” (but without the quotation marks), and the Expression will be a repository path like “repo:/Reports/SingleCity” (with quotation marks). For this example, we also add parameters TargetState and TargetCity (these are the names the report that your are calling uses…and you don’t need $P{} or quotation marks around the names…they’re assumed to be parameter names) and the expression for each of these is $P{TargetState} and $F{ADDRESS_CITY} respectively. You also need to make sure that the Parameter Class Name (java.lang.String, etc.) agrees with both the expression and the parameter you’ll be supplying. 4. Notice that iReport preview will not support the hyperlink functionality. To test the hyperlink functionality, you need to deploy the reports to the repository first and test them there (from JasperServer, typically). It is best to first test the reports individually once deployed, to be sure they are working correctly independently. For example, if a report requires input parameters be passed to it, that report requires that Input Controls be added as resources (I believe each Input Control has to have the same name as the parameter it’s supposed to fill), even if you expect that the report will not normally be run as a standalone. That seems very odd to me, but I’ve tested my drill-down reports with and without input controls, and the parameters do not seem to be filled correctly by the hyperlink unless you have input controls defined. It is as though the hyperlink is handing values off to presumed input controls instead of to parameters. I haven’t noticed this same problem with subreports. 5. Normally, that’s all there is to it! If you would like to make the SingleCity report have a link in the page header or column header that reads “Click here to see list of cities”, you have to use a Text Field, not a Static Text. You would then use steps 1 through 3 above, except for the _report parameter, you would specify the SingleState report as the expression, and you wouldn’t have to pass back the city. You would pass back the TargetState parameter to the SingleState report, passing it the same $P{TargetState} that you received, since at the beginning I said that the SingleState report takes a $P{TargetState} as input. If you don’t pass this back, I believe you’ll be prompted for a state again, which is probably not what you want. You want the link at the top of the page to seem like a “Back” button. In my case, my SingleState report is itself a drill-down from a report that has a fusion map of the United States. I have a link at the top of my SingleState report that gets me back to that Fusion Map, and it doesn’t have to pass a state or city parameter back to that report.

I know of no way to create and use a variable that knows what the last page number is so you can use that as part of an evaluation (if I'm on the last page, do this otherwise do that). "Page x of y" works because when you drag that onto the design pane, you can define when the "of y" part is evaluated. In fact, the only different between the "Page x" element and the "of y" element is the evaluation time. The "of y" isn't actually added to the report until the very end. In your case, I think there is a solution, however. If you look at the Report Inspector tree, notice that there is a "Last Page Footer" band that is normally grayed out by default. I believe that if you right click that and choose "Add band", you can define the page footer on the final page to look the same or different form the previous pages or even to be empty. Hope that helps out. Carl

Absolutely...actually, I use subdatasets all the time and almost never use subreports, all though many of the concepts are similar. In this case, your subdataset is being used by a Table element. By working with the subdataset, you can define the subdataset query and define parameters, fields, and variables for the subdataset, but that's not where you define the interaction between the main dataset and the subdata set. THAT is done in the Table (or List) element itself. In the tree, right click on "Table" and choose "Edit table datasource". On the first tab of that dialog box, it looks like you've already correctly linked the table to MySubDataset and said to "Use connection expression" $P{REPORT_CONNECTION}. If you click on the Parameters tab in that same dialog box, you will see that you do not yet have any definition set up for how to pass values from the main report to the subdataset parameters. Click Add. Since you already have the parameter defined in your subdataset, you can just choose that parameter from the dropdown list for "Dataset parameter name". Remember, this is the name of the parameter in your subdataset. Then click the expression editor icon for the "Value expression" text box. Like most expressions, you can enter anything you want here, but if you feel like just clicking instead of typing in this case, you are looking at parameters, fields, and variables that are currently defined in your main dataset. Select Parameters, and you should see in the middle column that your main dataset also (in this example) has a parameter called MyMainParam. Select that and click the various OK buttons. Carl Post Edited by cbarlow3 at 02/27/2012 17:12

I think that the two questions I see on this board the most are how to pass a value from a report to a subreport and how to pass a value back. In your case, you want the subreport to use the same parameter $P{MyMainParam} that the main report has access to. Since you've already defined the parameter in the subreport, the next step is to pass the value: go into your main report in iReport, click on the subreport element and scroll to the bottom of the Properties pane. Where you see the "Parameters" property, click on the "..." button and it pulls up a Parameters dialog box. Click the "Add" button to add a new parameter value to pass to the subreport. For Subreport parameter name, I don't believe you need to use the $P{} syntax, since this dialog presumes that the first text box is the name of a parameter. For the "Value expression" text box, you can enter any combination of field, parameter, variable, or function from the main report. In this case, it sounds like you have a parameter with exactly the same name in the main report, so you would use $P{MyMainParam} for the Value expression (since the value expression is not assumed to just be another parameter (even though it is in our example), you would need to use the $P{} syntax for this second text box. Hope that helps! Carl