cbarlow3

I think you'd be better off leaving the pointer a single color, but defining colors for the ranges on the dial (maybe "Low" is colored red and represents values 0 to 35, "Medium" is yellow and represents 36 to 70, while "High" is green and represents 71 to 100, for example). Not only is the gauge widget already set up to be able to handle such range color definitions, but you'd be giving the user more info that just whether or not they're on track. They would be able to see what the cutoff is for "on track" and how close to that edge they really are. As you probably know, gauges are very wasteful when it comes to the amount of space they take up just to represent a single value. There are other options that take up less room and can show more information, such as Stephen Few's bullet chart or even a linear gauge instead of a round one. If they really need to know the value being displayed with any accuracy, you'll probably have to actually PRINT the value itself, in addition to the gauge chart. You ALWAYS see gauges in demos of dashboard software, and pretty much anyone who asks for a dashboard assumes it's going to look like a 747 cockpit, but they're really pretty awful to use. If the "good vs. bad" coloring is important, you also need to worry about the approximately 8% of males who are color-blind. They can't tell green from red (in the most common form of color-blindness), yet using other colors that we as a society haven't been conditioned to automatically associate with anything could be a problem as well. If you insist on using a round gauge, then my recommendation is to define colored ranges instead of a dynamically colored needle, but personally, I've sworn off of gauges, pie charts, 3-D charts, and anything else that takes up too much space for too little (or even misleading) information. Carl

I don't know if there's a standard way to mark a thread as resolved, but if you're the one who started it, I think you can edit the subject of your original question to add "- resolved". Of course, when I'm scanning through subjects, I'm just as interested (or maybe more interested) in the questions that have a resolution as those that don't yet. The only thing I don't like is when someone figures out the answer for themselves but doesn't bother to post the answer to their own question! :) Carl

I'm not sure exactly what the problem is you're referring to, but just in case it helps, I'll tell you two things I've found when trying to create reports specifically with Excel export in mind: 1. If you have multiple detail bands or you combine a detail band with a title band, etc., you need to be VERY careful about making sure that the "Left" and "Width" properties for the fields in one band match up exactly with the fields above/below them. Just having them almost match up visibily is not good enough. As an example, if your design pane looks like this: Title Band: AccountNumber Name Balance Detail Band: $F{ACCT} $F{NAME} $F{BALANCE} you need to make sure that those three fields in the detail band line up exactly with the three headers in the title band. 2. Also, if you are combining two pieces of information into what you hope Excel will consider a single column, you can't drag two fields into that section of the report, or it will make two columns. You need to instead combine the data into a single field: Title Band: AccountNumber Name Balance Detail Band: $F{ACCT} $F{FIRSTNAME}+" "+$F{LASTNAME} $F{BALANCE} Hope that helps out. Carl

The example in the book (which I highly recommend) assumes that you originally have a result set with columns DEPTNO and EMPS that originally might look like: DEPTNO EMPS ------ ----------- 10 CLARK 10 KING 10 MILLER 20 SMITH 20 ADAMS ...etc. and you want to transform the output to this... DEPTNO EMPS ------ ------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD ...etc. You can doubtless modify the solution to meet your specify (possibly simpler) need. Here's his MySQL solution (you're in luck--the MySQL version happens to be the simplest solution): Use the built-in function GROUP_CONCAT to build the delimited list: select deptno, group_concat(ename order by empno separator ',') as emps from emp group by deptno Personally, I use DB2 way more than MySQL, so I don't know the particulars of how this function works, but just knowing the name of the function you need should help if you Google "MySQL GROUP_CONCAT". Here's one article I found: http://www.dougboude.com/blog/1/2009/12/A-SWEET-Little-MySQL-Function-GroupConcat.cfm Hope that helps. Carl

I don't think there's a free reference for iReport. On the plus side, that helps keep this forum pretty active! :) I started keeping my own personal documentation that's a mix of my personal experience and good answers I've gotten on this forum, and I've documented that when I want to do my own date formatting (usually for the same reason you cited: so I can combine it with other string data in a single field), I do this: new StringBuilder(new SimpleDateFormat("yyyy-MM-dd").format($P{EnterStartingDate}))+" to " + new StringBuilder(new SimpleDateFormat("yyyy-MM-dd").format($P{EnterEndDate})) Hope that helps. Carl P.S.: you can also use "MM/dd/yyyy" (or other variations) instead of "yyyy-MM-dd", if you're so inclined. Look up SimpleDateFormat in Java documentation to get all the options. Post Edited by cbarlow3 at 01/31/2012 18:01

I would need to see the jrxml probably (you could attach it), but my first guess is that your output rows aren't sorted by the criteria that you're considering "repeated". If your query returns values of A, B, C, D, A, A, D, B (in that order) and you set print repeated values for this field to false, all it would do is suppress the last occurrence of "A", because it immediately follows a return row that had "A". Also, you still get a detail band if you are printing more than one field...it just suppresses the printing of the actual field you have set that property on. It's so you can do things like the following: SELECT ACCOUNT_OWNER.NAME, ACCOUNT.NUMBER FROM ACCOUNT INNER JOIN ACCOUNT_OWNER ON ACCOUNT.OWNER_SERIAL=ACCOUNT_OWNER.SERIAL ORDER BY ACCOUNT_OWNER.NAME and output it like this: James Brown 12345 40837 78113 Marvin Gaye 10337 22215 Ray Charles 10053 etc. In this example, I order by the name and then I set the name field in my detail band to not print repeats (meaning "don't print if you're exactly the same value as the detail band immediately prior to this one). Hope that helps. Carl

iReport as such doesn't support check boxes or radio buttons (or dropdown boxes, date pickers, etc.). It just supports input parameters and then when you run in preview mode, it has its own way of prompting for the values. BUT...you are probably not expecting your end user to run these reports in iReport. You are presumably either writing some Java code to call report code (in which case you're in charge of prompting the user with whatever controls you like to fill in the values of the parameters) or else you are using the JasperReports Server to host your report. If you're deploying your report to JasperReports Server, then part of that process usually involves defining input controls within JasperReports Server repository that have the same name as your input paramater(s) and that get the appropriate data type. These can be locally defined input controls, or in some cases you may find that there are a few very common input controls that you decide to define like StartDate and EndDate, for example. If you define some of those ahead of time in a repository folder like Public | Input Controls, then you can link to the existing input control instead of defining a new one each time. If you have questions on how to create them, you might check the JasperSoft Server forum (different from iReport forum) or the JasperSoft Server documentation. Carl

I think you probably want to do that transformation within SQL rather than in iReport. Anthony Molinaro's SQL Cookbook (c. 2006 O'Reilly) shows techniques for this in his section 6.10 "Creating a Delimited List from Table Rows". In this particular case, the solution is more SQL-flavor-specific than usual, depending on whether you are using DB2, MySQL, Oracle, PostgreSQL, or SQL Server (the author give solutions for every "recipe"/problem in all these variations). What kind of database are you using (and what version number, if you happen to know that too)? Carl

Someone asked a question in a previous thread regarding calculating the number of business days between two dates. I've attached a report that lets you enter a StartDate and EndDate, and outputs all dates in that range. For each date, it outputs the date, the day of the week, and a "category", where the category is either "Saturday", "Sunday", the name of a holiday (sometimes with "(observed)" if the actual holiday is on a fixed date but falls on a Saturday or Sunday, e.g. since New Year's Day (Jan 1) fell on Sunday in 2012, we in the United States took off Monday Jan 2 instead as a work holiday), or a category of "Working Day". Naturally, the methods used could be used as part of a calculation (e.g., find all days in a date range that have a category of "Working Day", and then count how many there are). A few notes about the methods used--first, you must of course decide which holidays you really want to include as non-working days, depending on your locale, etc. Secondly, my SQL query takes advantage of the WITH clause (also known as common table entries or CTE), and furthermore, it uses recursion to generate all the dates between $P{StartDate} and $P{EndDate}. If your flavor of SQL doesn't support recursion, you can use a really large table of VALUES (1, 2, 3, 4, ...) up to whatever the max days you'll support as a range between the two dates. If your version of SQL doesn't support CTE...well, your version will look a lot messier than mine with all the nested queries. Once you have a temporary table that has each row returning a date in the date range, I am using four techniques to categorize each date: 1. DAYOFWEEK(date): The version of DAYOFWEEK I'm using returns 1 for Sunday and 7 for Saturday. 2. Fixed date holidays (like Jan 1 New Years, Dec 25 Christmas, etc.). Obviously those are easy to find using MONTH(date) and DAY(date), although I also check to see if it's a Friday and the day before or Monday and the day after to catch the "observed" holidays. 3. Holidays that move and are of the "Third Monday in January" format. These are simpler than most people make them out to be. You can just see if this is January, Monday, and a day from 15 to 21 in this example. 4. Movable feasts. Ah, "computus". I'm using essentially a Gaussian formula from Calendrical Calculations by Dershowitz and Reingold to calculate Easter. Since my report considers Saturday and Sunday to be a higher precedence category than any holiday, I chose to display "Good Friday" (Friday two days before Easter) instead of Easter itself, which still shows up as category "Sunday". I'm using the standard Western definition of Easter--first Sunday AFTER the Paschal Moon, which is the first (calculated) full moon on or after March 21. I've also made sure that I only calculate Easter one time for each year in the date range, rather than once for each date in the range that I want to categorize. Also note that I chose to format my output dates in mm/dd/yyyy format, just because that's what we're most used to in the U.S. That's just done with the "Pattern" property in iReport and has no bearing on the actual calculation in the SQL query. I hope that having all this logic in one place will be a useful starting point for many of you who have to deal with holidays vs. working days without necessarily having to resort to using a limited table of holidays that needs to be refreshed periodically (usually manually). I've attached the full jrxml, but I've also pasted the main SQL query below. Carl Code: Post Edited by cbarlow3 at 01/31/2012 21:25

Actually, calendrical calculations are something of a hobby for me, so I decided to also add the code for calculating Easter (by the current Western definition, which is "First Sunday AFTER the Paschal Moon, which is the first (calculated) full moon on or after March 21". Because I made my CASE statement treat Saturday and Sunday as their own category, more important than any holidays, I decided that although I calculate Easter (on which several other religiouis holidays are based), I would use "Good Friday" (Friday before Easter) as my example holiday to display. Easter still shows up as category "Sunday". Of course, if in the U.K. you normally get the Monday after Easter off, it would be a simple change. Or if you decided that you wanted Easter to be more important than category "Sunday", you would just include a CASE clause for Easter earlier in the CASE statement than the "Sunday" test. Notice, I only calculate the Easters for the years in the date range one time and then use that small number of dates in the main query rather than trying to calculate Easter each time I want to categorize a date. I think I'll also post this under a different subject in case it's helpful to others as well. Carl

I don't have screenshots, but here are some fairly thorough notes I made the first time I successfully navigated the drill-down report waters--I hope they help: 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. --Carl

I think there are two main ways to go about this: if you want the user to SEE a default value in an input control, the SQL query isn't the place that controls that. You can create a Groovy/Java/Javascript expression for the default parameter value in the Properties pane for that parameter, but how that works depends on whether you've written the user interface or whether you're using JasperServer. I frequently find myself writing reports (that run in JasperReports Server) that prompt for a StartDate and EndDate but where I also want to have default logic for those dates (specifically, I make it either be month-to-date or else if the current date is within the first ten days of the month, I use all of the previous month). The way I do this is to NOT use the parameter values in my main query. Instead, I put the following code at the top of my main query, and then in my main query, I refer to DefaultDates.StartDate and DefaultDates.EndDate instead of $P{StartDate} and $P{EndDate}: WITH DefaultDates (StartDate, EndDate) AS ( SELECT COALESCE($P{StartDate}, x.EndDate - DAY(x.EndDate) Days + 1 Days) AS STARTDATE, x.EndDate AS ENDDATE FROM ( SELECT CASE WHEN $P{EndDate} IS NOT NULL THEN $P{EndDate} WHEN DAY(ENV.POSTING_DATE)>10 THEN ENV.POSTING_DATE ELSE ENV.POSTING_DATE - DAY(ENV.POSTING_DATE) Days END AS EndDate FROM CORE.ENV AS ENV ) x ) Note, this will only work for you if your version of SQL supports the "WITH" statement (also known as "Common Table Entries" or CTE). In our database, the CORE schema has a one-row table called ENV (environment) that has a POSTING_DATE field that represent's today's date. Hope that helps! Carl

Okay, I'm attaching my jrxml code for a report where you input startdate and enddate, and it lists every date in that range, plus what day of the week it is and a "Category", where "Category" is either "Saturday", "Sunday", the name of a holiday (or in some cases I put the holiday name and "(observed)" when the normal holiday date falls on a Saturday or Sunday), or...if the day isn't any of those categories, I put "Working Day". Obviously, instead of printing all these dates, it would be very easy to just select the ones that are "Working Day" and count them. A few things to watch out for: my version not only uses the CTE (WITH) method of first building a temporary, named table that can be reused, it also uses recursion. If your version of SQL doesn't support recursion, you'll probably need to instead define a table of VALUES from 1to whatever your maximum number of days is between startdate and enddate that you'll support. Other things to watch out for: my current code uses U.S. Federal Holidays, not U.K., although I've shown the two most common situations you need to deal with: a fixed date that requires special handling when it falls on a Saturday or Sunday, and a holiday that is defined as the xth y-day of month-Z. I don't have Easter or any other religious holidays that are based on full moons, etc. ...it can be done, but it's a significant jump in complexity. I also output my dates in mm/dd/yyyy format...we yanks are pretty illogical that way. Good luck! Carl Post Edited by cbarlow3 at 01/29/2012 05:20

Yes, that's possible. How you do it depends on what flavor of SQL you're using. I think the first trick is to just GENERATE all dates from a starting date to an ending date (which of course could be useful for many things anyway), and then figuring out which ones you want to filter out based on day of week, holidays, etc. Then if you just want to COUNT what you get back, that's easy enough. It's going to be a little time-consuing, so if your flavor of SQL supports common table entries (CTE--WITH statements, essentially) and you are going to be doing a lot of these calculations in a single query, then you should probably first build a temporary table of all workdates in the full range you need (1/1/2000 to Today + 30 days or whatever the case might be). Then for each calculation, you would just select all workdates from that temporary table that are between a given startdate and enddate, where you've made sure that each startdate and enddate you want to do the calculation for fall within the original startdate and enddate range you used to generate the temporary table. Based on your question, I wrote a version of this this morning for DB2 SQL. So far, it will take a startdate and enddate and generate all dates in that range. Then it selects all those dates that are not Saturday, are not Sunday, are not January 1, are not a Friday Dec 31 (which is when you would presumably have New Year's Day off if Jan 1 fell on a Saturday), are not Monday Jan 2 (again, an observed date). This weekend I'll add an example of a holiday that follows the "Third Monday in May" type rule so you can see how that's done as well. Sorry to tantalize you with only the DESCRIPTION of a solution, but I won't have time to wrap it up until tomorrow. Carl

Svenn is correct--it's really a two-part question. The iReport of the answer is that you need to create parameters for the values you plan to input and use those parameters in your query...e.g.: SELECT po.id, po,description, po.datecreated FROM purchase_order as po WHERE po.date_created BETWEEN $P{StartDate} AND $P{EndDate} While testing in the preview mode of iReport, this is in fact enough to make it prompt you for the two dates. The second part of the answer depends on how your end user runs the report. Are you using JasperReports Server or do you have a Java program that is running your report just using JasperReport Library, or...? If your end user is using JasperReports Server, then you need to: 1. Deploy your report to the repository in JasperServer 2. Add input controls for each parameter that you want to prompt for 3. Change the property of the report so that it always prompts (this isn't actually necessary--you could have the report automatically run once with the default values and then rely on the user to click on the oh-so-subtle (at least in Version 3.7) "Report options" icon to enter new values. But in most cases, I find it's better to always prompt first. Each of these three steps is relatively easy if you have the repository navigator plug-in and are used to finding your way around the repository. I've even defined standard input controls called StartDate and EndDate in the public folder of the repository so I can just link to them instead of creating new, local input controls each time. If you have problems with the input controls, you'll probably want to post on the JasperReports Server forum rather than iReport. If your end user is running some application you've written that in turn runs the report through JasperReports Library, then you will of course have to do your own prompting. How you pass that value along to the report depends on what you're doing, but if it's a Java application, you probably use: HashMap jasperParameter = new HashMap(); jasperParameter.put("StartDate", startDate); JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, jasperParameter, connection); You can then also use the various exoprters to create PDF, etc. If you have questions about THAT process, you'll probably want to post on the JasperReports Library forum. Carl

It sounds to me like what you're saying is that you get back the right information, but you get it repeated when your input parameters result in more than one row being returned. It seems to me that there are two logical ways to organize your report, depending on what you're trying to do: 1. Don't use a subreport at all...I haven't seen anything in your explanation that tells me why you need it...use the SELECT you've built as your main query, and for each row that meets the criteria based on your input parameters (and your potential interpertation of wild cards), output the information for that returned row in the detail record. 2. Use your main query to determine which customer ID's (assuming that thisis the unique key) match the criteria based on the input parameters, and for each one, call a subreport, passing only that customer ID, and then in the subreport you have some completely different query that drills down into new details or whatever based solely on the customer ID, not based on the original input parameters, which were only used in the main query to qualify this customer to appear on the report It sounds to me like maybe you're combining the two ideas: using your main query to identify which customers match the input criteria, and then within a detail record calling a subreport that uses the same query. So if five customers match the input parameters, you end up with a list of those five in a subreport that is in your first detail record of the main query...but is also repeated in the other four detail records you get returned. If the main query and the subquery are doing exactly the same thing, you probably don't need that subreport at all...certainly not in the detail section. I have used a List element with a dataset that had essentially the same query as my main query (the List dataset works very similarly to a subreport), but I put it in the summary section, and it's typcially to redundantly repeat subtotals that I've already displayed at the end of each group. I wish that were a standard feature, by the way--some way to repeat all your group footers in a combined List/Table at the end of the report without having to run an additional query. Carl

Actually, when you think about it, it makes perfect sense: the chart/map is trying to summarize the information from all the previous detail bands. If you want ONE chart, put it in the ONE summary band. Although the detail band looks like it is also a single band in the design pane, it's obviously not really a single band in the final report, as that detail band is repeated for every row returned in your query. So if you want a separate chart for each query row returned, put the chart/map in the detail band (that would be much less common). If you get to where you are defining "groups", you may find that a chart also makes sense in a group footer record, just to summarize the detail information that came back for that specific group. That would result in multiple charts/maps per report, but not one per detail band. Hope that helps clarify why charts are most commonly found in the Summary band rather than the detail band. Carl

What are you trying to print on the report? The "organization" name in the sense that the JasperSoft repository uses it, or do you mean the name that the company puts at the top of their letterhead? If the former, I think Matt's answer is correct, and it's useful new information for me, because we usually set up one of our clients with multiple "organizations", each being nearly identical except that they each have a different Data Source (pointing to either the live database or an older backup/test copy), and so the reports and queries in each organization point to the local version of Data Source. After reading Matt's solution (and testing it), I'm now tempted to add the "organization" name to the top of most of my reports so our clients will know for sure whether the report they're looking at (if it was printed or exported) was run in the "live" organization (d0023liv might be our organization name) or in one of their "test" organizations (d0023t00, for example). Our organization names aren't super user-friendly, but I could make them discrete. In fact, instead of at the top, I think I'll put them on the left side of my page footers (the right side has "Page x of y"). If you instead mean the company letterhead name, I can still see how that might be useful if either (a) you have it in several reports and you don't want to have to change all those reports if the company name changes, or (b) you use the same reports for multiple companies and would like to not have to modify the code for each. These end up being effectively the same problem. Is it possible that the name you seek is already in the main database you're querying? If so, I think that would be the ideal place to look, as it seems to resolve both the scenarios I mentioned: no customization necesarry as you make copies of the report specification available to multiple customers who use the same schema, and no report modifications necessary if the customer changes their business name, assuming they have ready access to change their own business name within their database in some kind of single row environment table, for example. Carl Post Edited by cbarlow3 at 12/14/2011 23:30

Now that I've mentioned two ways to do it (manually add two parameters or add the XML text), I see there's an even simpler third and fourth way: 3. In the Palette under "Tools", drag "Domain Parameters" onto the design view. That brings up a list of Domain Parameters you can add. Try adding "LoggedInUserFullName". It looks like it's a simple java.lang.String, but it seems to work. 4. Or, just manually add a String parameter called LoggedInUserFullName. Seems to be a reserved parameter name, because it worked just fine without me having to use the Domain Parameters tool. It still has the limitation that the value is null when you're in iReport preview mode. Carl P.S.: While reading through some of these questions, I found one where I knew Matt Dahlman had posted a good solution, and while I was looking for that, I found someone's similar question of how to access the organization/tenant as a system parameter. I'm looking at the different getter and setter methods available in the User, Tenant, TenantQualified, TenantImpl, etc. classes to see if I can figure out the answer to that one. In our business, we set up one organization for a client that accesses their LIVE database, but we typically will make another organization or two that is nearly an exact copy of the first organization, except that the reports and queries all point to a different Data Source (a test / backup / development copy of the client's live database). So it might be helpful for me to start making it be a standard to output the organization at the top of each report so the client knows for sure whether the report was run in test or live. Similarly, for multi-tenancy implementations where multiple customers share a repository, you might still want the same report to be installed for multiple customers without having to customize the customer name in each. Anyway, I haven't figure out how to do that yet. P.P.S.: I just found out how to get the Tenant name (at least if you use the Professional version): Create a parameter called $P{LoggedInUserTenantId}. That seems to work for my purpose, since our application has a user log onto a specific "organization" based on which database they're interested in accessing. C.B. Post Edited by cbarlow3 at 12/14/2011 23:13

I use DB2 all the time in my iReports, and I just use the name of the schema(s) in my FROM clause, e.g. here I am using the CORE schema: SELECT ACCOUNT.ACCOUNT_NUMBER || ' L ' || LOAN.ID AS ACCT, COALESCE(PERSON.LAST_NAME, '') || COALESCE(',' || PERSON.FIRST_NAME, '') AS PERSON_LAST_FIRST, LN_TYPE.DESCRIPTION AS LOAN_TYPE, LOAN.BALANCE AS LOAN_BALANCE, FROM CORE.ACCOUNT AS ACCOUNT INNER JOIN CORE.PERSON AS PERSON ON ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON.SERIAL INNER JOIN CORE.LOAN AS LOAN ON LOAN.PARENT_SERIAL=ACCOUNT.SERIAL INNER JOIN CORE.LN_TYPE AS LN_TYPE ON LOAN.TYPE_SERIAL=LN_TYPE.SERIAL WHERE LOAN.CLOSE_DATE IS NULL AND LOAN.CHARGE_OFF_DATE IS NULL AND $X{ IN, LN_TYPE.DESCRIPTION, LoanTypeList } ORDER BY ACCT Of course, the schema(s) you use have to be defined in the Data Source that your report points to. Hope that helps. Carl

Assuming you are using JasperServer and that your database also has a table where one of the columns is the same JasperServer username (the username they log onto JasperServer with), I found and tested the following solution over in the JasperReports Server forum: In iReport, add the following two parameters in this order: Name: LoggedInUser Class: com.jaspersoft.jasperserver.api.metadata.user.doman.User Use as a prompt: (No--leave the box unchecked) Default Value Expression: (leave blank) Name: LoggedInUserName Class: java.lang.String Use as a prompt: (No--leave the box unchecked) Default Value Expression: $P{LoggedInUser}.getUsername() If you prefer to add these directly to the XML, you could insert the following text right before <queryString>: <parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User" isForPrompting="false"/> <parameter name="LoggedInUsername" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA[$P{LoggedInUser}.getUsername()]]></defaultValueExpression> </parameter> Now the main query of your report needs to look something like this: SELECT customer.name, customer.phone_number FROM customer WHERE customer.repName=$P{LoggedInUserName} Only drawback is that I think this will only work directly in JasperServer, not in preview mode of iReport. Hope that helps. Carl

iamoat, I've been using multi-select input controls for almost a year now, and was I happy when I figured out how to do it! Specifically, I was not looking forward to hardcoding the choices the user had, when I knew that those choices actually came from the database itself. You don't say below whether in addition to figuring out the syntax in the report itself you also figured out how to define the input control. I find that I end up using the same input controls on a regular basis (BranchList, LoanTypeList, etc.), so I created a folder in my organization (not under Public, because these input controls need queries from a data source that is also inside the organization, not Public) for Queries and another folder for Input Controls. Sometimes I define local, one-off input controls, but for the ones I know I'm going to probably re-use, it's great to have the query and the input control already in the repository so I can just Link to the existing input control the next time I need it again for another report. Carl

I think maybe you are confusing the use of the term "columns". From your screenshot, it looks like you are organizing your report into two columns just to save room on the page. It means that you define what you want to appear on the report on the left side of the design pane and then your report will output two rows of detail per returned row (because you have defined Detail1 and Detail2), and when it gets to the bottom of the page, it will start displaying additional returned rows on the right side of the page (or there's also an option to go left-to-right and then work down the page). But how many fields (also frequently thought of as "columns") you output in each detail row is up to you. You can for example output Name, street address, city, and state (four fields or columns) in Detail1 and then output only E-mail address in Detail2, for example. It's true that multiple detail records may make the report harder to read, as it reduces the effectiveness of your Column Header band, but it can be done. On the other hand, if you require two detail bands to comfortably fit the data you want to output for each record, you might want to consider not splitting your report up into two columns in the first place. Or changing your page layout from portrait to landscape (wider report, in other words). If you decide to continue having your report organized in two report columns and two detail bands, the output might look something like this: Name1 Street1 City1 State1 Name48 Street48 City48 State48 Email1 Email48 Name2 Street2 City2 State Name49 Street49 City49 State49 Email2 Email49 ...etc. In the above example, you drag four fields into the left side of the Design view into Detail1 and one field into the left side of the Design view into Detail2. When the report is actually rendered, you see the report is organized into two overall report columns, but that each report column shows all the fields you selected. Hope that helps. Carl Post Edited by cbarlow3 at 12/14/2011 17:43 Post Edited by cbarlow3 at 12/14/2011 17:44

Your first hurdle is to get comfortable with SQL (assuming that's the query language your database uses). Luckily, SQL is even more readily documented than iReport. There are many good books and online resources. I normally recommend learning the basics from some online tutorials, but for a good reference book, I recommend O'Reilly's SQL Cookbook by Anthony Molinaro, because it shows examples of how to solve useful problems, pointing out the slight dialect differences between DB2, Oracle, MySQL, SQL Server, and PostgreSQL. There are several ways to organize the query you need. Here's one way: SELECT Accnt_no, Name, SUM(Buy) AS TotalBuy, SUM(Sale) AS TotalSale, SUM(Buy) - SUM(Sale) AS Balance FROM ( SELECT inv_profile.accnt_no AS Accnt_no, inv_profile.ac_name1 AS Name, COALESCE(psdr_cds.no_shares, 0) AS Buy, 0 AS Sale FROM test.inv_profile AS inv_profile LEFT OUTER JOIN test.psdr_cds AS psdr_cds ON psdr_cds.accnt_no=inv_profile.accnt_no UNION ALL SELECT inv_profile.accnt_no AS Accnt_no, inv_profile.ac_name1 AS Name, 0 AS Buy, COALESCE(swr_cds.no_shares, 0) AS Sale FROM test.inv_profile AS inv_profile LEFT OUTER JOIN test.swr_cds AS swr_cds ON swr_cds.accnt_no=inv_profile.accnt_no ) a GROUP BY Accnt_no, Name ORDER BY Accnt_no I prefer to use INNER JOIN when I can, but in this case I used LEFT OUTER JOIN, because I assume that it's possible to have an entry in inv_profile that has no buy and/or sell entries, and presumably you would still want them to show up on the report. If you wanted to skip an entry that had neither a buy or sell, you could add this clause just before "ORDER BY" HAVING SUM(Buy)<>0 OR SUM(Sale)<>0 Hope that helps! Carl

Kristen, For some reason, my post posted twice, but when I noticed, I couldn't figure out how to delete this one. The details of the error and how I eventually solved the problem are in the other post, which is at http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=94056#94134. Carl