pjamack

Check out these sites. http://fecplanner.com/jasperreports/docs/quick.reference.html http://ireport.sourceforge.net/cap5.html

Keep me updated.

Thanks for your help. That's how I've done it before, using SQL. The problem is I have no access to the db, so I just call a Function, pass in parameters,and get fields returned which I have to calculate, manipulate, etc. So the easy choice would be to do in it SQL. The problem I have is I can't do it in SQL and the function isn't written like that. In fact, the dates are returned as Strings as it is. So I'm trying to figure a way to do it in Jasper. And converting a string to a Date as a variable expression in Jasper seemed the most logical. But it doesn't work. So getting a natural order of Sunday, Monday, Tuesday, etc in a Jasper Crosstab has been Hard to do so far. Post Edited by pjamack at 08/23/2011 20:07 Post Edited by pjamack at 08/23/2011 20:12

I got the String to Date to work as a variable and then sort of use it in a crosstab as a column group. Right now I'm working on figuring a way to place nested ifs in a variable for days of weeks as a number.

I'm trying to do something like ... <variable name="ConvertedDayInt" class="java.lang.String"> <variableExpression><![CDATA[(($F{DAY_OF_WEEK}.equals("Sunday") ? "0" ? ($F{DAY_OF_WEEK}.equals("Monday") ? "1") : "" ) )]]> </variableExpression> </variable> And break it down even further into Monday, Tuesday, Weds, etc. It's for sorting, ordering in a crosstab. Right now if I sort it by Date, the end user doesn't like the fact that it's dynamic and the Days would change depending on when You ran the report. AKA run it today and Monday shows up first. Run it tomorrow and Tuesday, etc. They want a standard Sun, Mon, Tues, Weds, Thurs, Fri, Sat as the column header every time. The issue is if I run it normally by day of week, it'll sort the columns by alphabetical order. AKA Friday, Monday, Sat, Sun, Thurs, Tues, Weds Obvioulsy that's not good. None of the sorting or ordering can be done in the database and the date and day_of_week are returned as separate strings. So when I try to convert date string just to EEE, it throws an error. Any help ASAP would be appreciated.

I'm having problems getting two crosstabs to show up in One report in the Summary Band. The input parameters are setup as such where you call an oracle function and grab data for about 5 weeks worth of data. The first crosstab is filtered to only see data for the upcoming week. The second crosstab calls a subdataset and shows all data, but brokend down by a different level. Sometimes both crosstabs show up when I run a report. Other times the first crosstab shows up. Other times neither. Other times just the second crosstab. It's confusing that sometimes it works and sometimes it doesn't. Is this something to do with the data in the database and/or function called or am I setting something up wrong in the Crosstab and Report? Post Edited by pjamack at 08/19/2011 22:24

I got the Crosstab filter expression to work. However I have two crosstabs and only one displays data. The crosstab filter expression used is new Boolean($V{theDate}.after(org.apache.commons.lang.time.DateUtils.addDays($F{REPORT_END_DATE},-7 ))) If my inputs are for a months worth of data, I will get the last weeks worth of data subtracting 7 days from Report End Date. Works like a charm. The problem is the Second crosstab. It's supposed to show data for the past month broken up into Week 11, Week 12, etc. If I don't even use a filter or if I do user a filter expression it doesn't matter as it just comes up as a blank page. Not sure why the second crosstab isn't working or showing up. I run the report a different way, with multiple datasets, and while there are issues with that, I do get that to display the first Crosstab and the Second Crosstab. But it's two calls to the database and it's a no no where I'm at. So I need to just use one function call and filter out what is needed. Any help?

Used this.. new Boolean($P{REPORT_START_DATE}.after(org.apache.commons.lang.time.DateUtils.addDays($P{REPORT_END_DATE},-7))) and other variations and it works...but report comes back as blank... Tried with Days(), -7 etc and that works too but I'm running against a staging database and there isn't any data in it from the past few months, so new date will just return blank info.

I guess the simplest way to explain is there are two crosstabs. CrossTab #1 Filtered on data from today till next Thursday. new Boolean($P{REPORT_START_DATE}.after( new Date()-7 ) && $P{REPORT_END_DATE}.before( new Date())). but the-7 never works. Crosstab #2 filtered on last months worth of data. new Boolean($P{REPORT_START_DATE}.after( new Date()-28 ) && $P{REPORT_END_DATE}.before( new Date())) Input parameters would be Start_Date is say July 20 and End Date Aug 18. Then filter in crosstab expressions from that point. but again, just trying to filter on date range seems to be a complicated mess in Jasper. Post Edited by pjamack at 08/18/2011 19:19

I've been trying to figure another way where I just have One Main dataset, no subdatasets and then Filter each Crosstab.. BUt I'm stuck on being able to filter them properly. What I'm trying to do is the user Puts in a Date Range, lets say for a months worth of data, well CrossTab 1 will Filter that data into Todays Date plus 7 days... CrossTab #2 will filter based on Todays Date - 28 days or whatnot. That way Crosstab # 1 shows the right data for upcoming weeks data and CrossTab#2 shows data for the past month. But I'm getting errors on filtering by dates as the REPORT_START_DATE etc compared to java.util.date, sysdate, java.util.calendar.date, etc all come back with errors. Lost right now on how to filter crosstab expressions.

Is it possible to explain what you did? I'm trying to build subdatasets based on Filtering the date ranges. I have two crosstabs, one function call, but different parameters(date ranges) passed in. The problem is the database is kind of off limits. So one call to grab all the data and then create two subdatasets that filter the data into what is needed for a particular crosstab. So Main dataset calls all ranges, Subdataset filters data from main, Subdataset2 filters even further from main. Is this possible?

So how do you get Crosstab Totals to work properly? My total numbers are off or they just grab the last row/column and place that as total. No matter what I change as far as Function, it doesn't matter.

I grab data from a function which stores info in a Temp Table. The problem is when I run the same report a few times, the old data continues to accumulate and my report doesn't always match what Dates or Data I want. Is there a way in JasperReports where I can rollback or reset the temp table or connection pool? The easier way would be to write commit or rollback in the function , but I don't have access to that and they want to do everything in Jasper. Any help would be appreciated.

I have a requirement where I need to only have 7 Columns across in a Crosstab. Days of week, for example. Is there a way to set up a crosstab where The Columns can only be Sun, Mon, Tues, Weds, Thurs, Fri, Sat. RIght now, depending on how I set up the crosstab from Fields returned or Variables set from those Fields, I could get 9 Columns across or 5 or have it look like Mon, Thurs, Sat, Friday, etc.. Obviously whatever is returned for the database and how Crosstabs sorts things is a factor, but I need the columns across to always be Sun-Sat, and then have the data follow accordingly.

If you're righting a SQL Query, try this instead... WHERE $P{Usr_Rng}<>'All' Not sure about Range= this or that since if you're writing a query in a JasperReport, the Range needs to have meaning in Jasper as well, otherwise it'll throw an error.

I've used a ton of different tools and software over the years. Jasper does have it's pluses If you know java, there are a lot of options. A lot of integration. Embedding with your own tool capabilities. Pixel perfection, etc. And it's open source and has been used by a number of companies and people. What it doesn't do well though is Reporting. If you have to spend a week or two building a report that some other tool might get done quickly, all the added integration capabilities are of no use. If you have end users who work through the growing pains with you, then it's a great tool. Everybody will learn and nobody will get their head knocked off. But if you have end users who want a report to look exactly like they want it, with the sorting, calculations, etc and the database is sort of hands off don't do too much, Jasper is going to be a pain to all involved. And demanding business users dont' want to hear "the tool is limited." Other tools are Pentaho, a little nicer, but again, the forums are useless, the help is limited and it's a tool a lot of people haven't used or used here and there. I haven't used BIRT. I've heard good things about it though. THe other tools are a lot more expensive like Business Objects with Crystal Reports, Cognos, Microstrategy, Microsoft 2008 BI stack, Oracle has it's toys like OBIEE & Hyperion, and so on. Since you're going with Jasper I'm assuming the big dogs are just too costly. With them though the support is a lot better and there is tons of knowledge. While there are issues, doing reporting becomes a lot easier. But programmers don't always like those tools because it becomes more about the Analysts than programmers. It really depends on what kind of end users you have. With Jasper, you need end users who aren't that demanind and don't need everything Tomorrow. Especially if they want pivot tables, sorting, break downs, and complex reports. Because if you have developers who build work arounds for every report, and you do a ton of reports, fast forward a year and you'll have a thousand reports that sort of do the same thing and a slow system where nothing is really organized or connected. And then you upgrade to a new server and all those workarounds don't work anymore. That is the nightmare in Jasper. A lot of people built workarounds then were forced to stay with older versions because the workarounds took too much effort to make, the reports finally did what was expected, and now rebuilding all the objects, database code, functions, jsp, UI, etc just so the same reports that work can work in the new version, is a nightmare.

From my experience and those I've spoken to with years of Jasper Experience, there is a lot of possibilities and capabilities within the various tools. It can be a great tool for those with a lot of time, patience, and even more patience. The problem with Jasper is it is limited when it comes to a True Reporting solution. If you have hardcore programmers who know Java, and they are the ones assiged to Reporting, then there are a world of opportunities possible, including everything you mentioned. And a lot of what you mentioned is available within IReports. But if you want a tool that is more drag and drop and point and click and a tool where Business only users can build Reports, Jasper is a horrible tool for that. There is adhoc, domains, topics, etc, but again, non programmers hate Jasper. Many programmers complain about it themselves. Half of the 'features' seem to require more programming or workarounds. And if you assign reporting tasks to programmers who have a ton of other programming duties, it might not go over well. If they have a full plate of other duties and suddenly have a new caseload of Jasper Reporting duties, Jasper will become a pain in the butt nobody likes or wants to use. And They'll find ways to program and avoid half of Jaspers real capabilities. What's the point in having a tool, even one you pay for, where people would rather build work arounds then use the tools available? Because truth be told, Jasper is a pain in the neck. It seems to be created for programmers by programmers. Great in one sense, horrible in a Business Reporting sense. Nobody who is already busy wants to have more crap thrown on their plate. Especially if it's something that requires too much busy work that other tools seem capable of doing without such a learning curve and without programming. Throw it to the business and customer care people, and they'll jump off a cliff wanting to know why you didn't go with something better. And it's a horrible tool for support. Research the forums. Research other forums. Research the web. You will find most questions go unanswered. Even if you get the Pro version and get Jasper Support, it rarely helps as the same questions might go unanswered. Or you get programming 'do this' kind of answers not related to what the issue is at all. If you have a few programmers who aren't busy, who like learning new things, who don't find reporting as a pain in the neck already without dealing with Jasper, then why not. It's an interesting tool to learn. But as stated earlier, if you assign it to programmers who are already busy and who don't really like sizing fonts, setting up layouts, figuring out why a tool doesn't offer simple calculations or date functions, you will be sadly disappointed. And god forbid you assign Jasper to a non programmer.

Easiest way I've found is to have a Start Date and an End Date... As far as limitations on Max/Min for Date inputs, you can write it in the SQL query, otherwise within Jasper, I've yet to find a way to do this and nobody from Jasper or on the forums seems to actually know how to use Dates for calcuations/min/max/etc as parameters and variables. If you want to simply display a field with Date - 7 or Date + 7 or Date between this and that, you can find ways to do that using GregorianCalendar, etc. But Displaying a field is far different than actually using the Date to calculate something in your report.

This has worked in TextVariables, if I create a varaible as a String, etc but if I try to create a DATE Variable and use the SImpleDate, Utils.Date or whatever to convert a String to Date, it throws Cannot Cast String to Date errors. If I change the Variable to String, then I could play around and make it work. But it defeats the purpose if I'm trying to convert returned Strings to Dates and then Group the Dates into Week One, Week Two, etc. Nobody seems to have a clue how to do this. Or maybe it's just not possible in Jasper to Convert String to Date and group by Week. Because Convert String to Date and just display it doesn't do anything for Grouping or putting dates into Weeks. It's still a String.

Welcome to the Jasper World where it seems nobody really knows how to do simple reporting tasks. Many questions, very few real answers. I've tried similar things as you, not from Excel, but I call a function and get a Date stored as a string back. I've figured out how to subtract/add dates in a Parameter or Field, I've figured how to convert from String to date, but it's just at the variable or field display level. I haven't figured out how to convert string to Date, then subtract/add and group dates into Weeks, etc. At this point, after reading and asking questions, you have to assume Jasper is very limited in it's Reporting capabilities. That or nobody really knows how to use the product. Either way it's not a good thing. Support is limited. Questions never answered. Wish I could help, but all i Know is Java commons library can help you group things and break down dates, add/subtract dates, etc but only if it's a Date. Haven't been able to do it if it's a string and convert to Date. Variables converted from String to Date just use something like <variable name="theDate_1" class="java.lang.String"> <variableExpression><![CDATA[(($F{theDATE} != null && $F{theDATE}.length() == 10) ? $F{theDATE}.substring(0,2)+"/"+$F{theDATE}.substring(3,5)+"/"+$F{theDATE}.substring(6,10) : "")]]> </variableExpression> </variable> there is an easier way, but that converted my String to Date. But it's still a string. It's not really a date. So It just converts it at a display level, not a useful level.

Not sure what the issue is right now, but if I run a Report, call a Oracle function, return results and place them in the ColumnHeader and Detail bands, I get the correct results. But if I get rid of the detail band and add them to a Crosstab, the results are off. No clue where the numbers are coming from as I do NOTHING in crosstab for the metric. I even tried using a Group band and for whatever reason, Jasper is coming up with it's own numbers. Even when I don't count or sum or calculate anything, the numbers don't match the original numbers. How does JasperReports work when you use Crosstabs and Group bands because at this point, even when not doing any calculations and setting it to "NOTHING" it still returns unknown numbers. AKA APP COUNT = 50 in detail band. APP COUNT = 222 in Group or Crosstab summary. Makes little sense to me where the numbers come from or what Jasper is calculating as I didn't set it to do any of that.

Created the report with 2 subreports.. Main Report is essentially blank with just the one subreport in Detail section and the other in the Summary Section. Then both subreports are essentially Crosstabs in Summary section. Did this to have the right format/spacing/layout. The problem now is two crosstabs seem to crash the server. One works fine. BUt two, not so much.

I understand why it's showing what it is now when it shows TUESDAY TUESDAY etc. Whatever data from the Curson in the row would be the same all the way across. So if the returned values are for some date on a Tuesday, that entire row would be Tuesday. Makes sense. What I get back from the DB, put into FIELDS is .... StartDate, EndDate, DayAsDate, DayOfWeek, Group, Region, Name, Code, SalesName, AppCount So when I'm checking DOW..whatever the row has, it's what the result will be. Same for AppCount. I guess I can try to do a crosstab, but I'm not sure how to populate it at the level I'm trying to do when the data is returned in a standard way. Post Edited by pjamack at 08/05/2011 22:39

the variable expressions, as stated previously, were kind of reversed to trick it. Just so I could see what the $F{DAY_OF_WEEK} field was actually passing in. Now it makes sense why i always got null. For whatever reason the DOW didn't match what I was looking for.

the value $F{DAY} is just a date returned as a string from Oracle db.