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.