pjamack

Main Report : <?xml version="1.0" encoding="UTF-8"?><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Appointment Reports - AByDay" pageWidth="612" pageHeight="792" orientation="Landscape" columnWidth="552" leftMargin="30" rightMargin="30" topMargin="20" bottomMargin="20"> <property name="ireport.scriptlethandling" value="0"/> <property name="ireport.encoding" value="UTF-8"/> <property name="ireport.jasperserver.reportUnit" value="/XTIME1/REPORTS/DEV_REPORTS/AppByDay"/> <property name="ireport.jasperserver.url" value="http://something.../jasperserver-pro/services/repository"/> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <import value="net.sf.jasperreports.engine.*"/> <import value="org.apache.commons.lang.time.*"/> <import value="java.util.*"/> <import value="net.sf.jasperreports.engine.data.*"/> <style name="Crosstab Data Text" hAlign="Center"/> <parameter name="REPORT_START_DATE" class="java.util.Date"/> <parameter name="REPORT_END_DATE" class="java.util.Date"/> <parameter name="PARAM_TYPE" class="java.lang.String"/> <parameter name="PARAM_VALUE" class="java.lang.String"/> <parameter name="DEALER_CODE_LIST" class="java.lang.String"/> <parameter name="SUMMARY_LEVEL" class="java.lang.String"/> <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false"> <defaultValueExpression> <![CDATA["C:\\Users\\abc\\Desktop\\JasperReports\\Templates\\"]]> </defaultValueExpression> </parameter> <queryString language="plsql"> <![CDATA[{?= call theFunctions.APP_REPORT( $P{REPORT_START_DATE}, $P{REPORT_END_DATE}, $P{PARAM_TYPE}, $P{PARAM_VALUE}, $P{APP_LIST}, $P{SUMMARY_LEVEL})}]]> </queryString> <field name="REPORT_START_DATE" class="java.util.Date"/> <field name="REPORT_END_DATE" class="java.util.Date"/> <field name="DAY" class="java.lang.String"/> <field name="DAY_OF_WEEK" class="java.lang.String"/> <field name="APP_GROUP" class="java.lang.String"/> <field name="REGION" class="java.lang.String"/> <field name="APP_NAME" class="java.lang.String"/> <field name="APP_CODE" class="java.lang.String"/> <field name="SALES_NAME" class="java.lang.String"/> <field name="APP_COUNT" class="java.lang.Number"/> <variable name="SUN_APT_COUNT" class="java.lang.Number" calculation="Sum"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("SUNDAY") ? $F{APPOINTMENT_COUNT} : null]]> </variableExpression> </variable> <variable name="MON_APT_COUNT" class="java.lang.Number" calculation="Sum"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("MONDAY")? $F{APPOINTMENT_COUNT} : null]]> </variableExpression> </variable> <variable name="TUES_APT_COUNT" class="java.lang.Number" calculation="Sum"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("TUESDAY")? $F{APPOINTMENT_COUNT} : null]]> </variableExpression> </variable> <variable name="WEDS_APT_COUNT" class="java.lang.Number" calculation="Sum"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("WEDNESDAY")? $F{APPOINTMENT_COUNT} : null]]> </variableExpression> </variable> <variable name="THURS_APT_COUNT" class="java.lang.Number" calculation="Sum"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("THURSDAY")? $F{APPOINTMENT_COUNT} : null]]> </variableExpression> </variable> <variable name="FRI_APT_COUNT" class="java.lang.Number" calculation="Sum"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("FRIDAY")? $F{APPOINTMENT_COUNT} : null]]> </variableExpression> </variable> <variable name="SAT_APT_COUNT" class="java.lang.Number" calculation="Sum"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("SATURDAY")? $F{APPOINTMENT_COUNT} : null]]> </variableExpression> </variable> <variable name="MONDAY_DOW" class="java.lang.String" resetType="None"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("MONDAY") ? $F{DAY} : $F{DAY_OF_WEEK}]]> </variableExpression> </variable> <variable name="TUES_DOW" class="java.lang.String" resetType="None"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("TUESDAY")? $F{DAY} : $F{DAY_OF_WEEK}]]> </variableExpression> </variable> <variable name="WEDS_DOW" class="java.lang.String" resetType="None"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("WEDNESDAY")? $F{DAY} : $F{DAY_OF_WEEK}]]> </variableExpression> </variable> <variable name="THURS_DOW" class="java.lang.String" resetType="None"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("THURSDAY")? $F{DAY} : $F{DAY_OF_WEEK}]]> </variableExpression> </variable> <variable name="FRIDAY_DOW" class="java.lang.String" resetType="None"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("FRIDAY")? $F{DAY} : $F{DAY_OF_WEEK}]]> </variableExpression> </variable> <variable name="SAT_DOW" class="java.lang.String" resetType="None"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("SATURDAY")? $F{DAY} : $F{DAY_OF_WEEK}]]> </variableExpression> </variable> <variable name="SUN_DOW" class="java.lang.String" resetType="None"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("SUNDAY")? $F{DAY} : $F{DAY_OF_WEEK}]]> </variableExpression> </variable> <background> <band/> </background> <title> <band height="84"> <staticText> <reportElement key="staticText-1" x="4" y="6" width="546" height="40"/> <box> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="0.0" lineColor="#000000"/> <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center"> <font size="28" isBold="true" pdfFontName="Helvetica-Bold"/> </textElement> <text> <![CDATA[Appointment Report]]> </text> </staticText> <line> <reportElement key="line-2" x="0" y="2" width="550" height="1" forecolor="#000000"/> <graphicElement> <pen lineWidth="2.0" lineStyle="Solid"/> </graphicElement> </line> <textField isBlankWhenNull="false"> <reportElement key="textField-4" x="4" y="46" width="144" height="17"/> <box> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="0.0" lineColor="#000000"/> <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement/> <textFieldExpression class="java.util.Date"> <![CDATA[$F{REPORT_START_DATE}]]> </textFieldExpression> </textField> <textField isBlankWhenNull="false"> <reportElement key="textField-4" x="148" y="46" width="144" height="17"/> <box> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="0.0" lineColor="#000000"/> <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement/> <textFieldExpression class="java.util.Date"> <![CDATA[$F{REPORT_END_DATE}]]> </textFieldExpression> </textField> <textField> <reportElement x="330" y="46" width="20" height="17"/> <textElement/> <textFieldExpression class="java.util.Date"> <![CDATA[org.apache.commons.lang.time.DateUtils.addDays($P{REPORT_START_DATE},-28)]]> </textFieldExpression> </textField> <textField> <reportElement isPrintRepeatedValues="false" x="422" y="6" width="130" height="40"/> <textElement textAlignment="Center"> <font size="14" isBold="true"/> </textElement> <textFieldExpression class="java.lang.String"> <![CDATA[$F{APP_GROUP}]]> </textFieldExpression> </textField> </band> </title> <columnHeader> <band height="35"> <staticText> <reportElement x="1" y="15" width="62" height="20"/> <textElement/> <text> <![CDATA[Week Starting]]> </text> </staticText> <textField evaluationTime="Band"> <reportElement isPrintRepeatedValues="false" x="63" y="15" width="70" height="20"/> <textElement/> <textFieldExpression class="java.lang.String"> <![CDATA[$V{MONDAY_DOW}]]> </textFieldExpression> </textField> <textField evaluationTime="Band"> <reportElement isPrintRepeatedValues="false" x="483" y="15" width="70" height="20"/> <textElement/> <textFieldExpression class="java.lang.String"> <![CDATA[$V{SAT_DOW}]]> </textFieldExpression> </textField> <textField evaluationTime="Band"> <reportElement isPrintRepeatedValues="false" x="273" y="15" width="70" height="20"/> <textElement/> <textFieldExpression class="java.lang.String"> <![CDATA[$V{THURS_DOW}]]> </textFieldExpression> </textField> <textField evaluationTime="Band"> <reportElement isPrintRepeatedValues="false" x="133" y="15" width="70" height="20"/> <textElement/> <textFieldExpression class="java.lang.String"> <![CDATA[$V{TUES_DOW}]]> </textFieldExpression> </textField> <textField evaluationTime="Band"> <reportElement isPrintRepeatedValues="false" x="413" y="15" width="70" height="20"/> <textElement/> <textFieldExpression class="java.lang.String"> <![CDATA[$V{SAT_DOW}]]> </textFieldExpression> </textField> <textField evaluationTime="Band"> <reportElement isPrintRepeatedValues="false" x="203" y="15" width="70" height="20"/> <textElement/> <textFieldExpression class="java.lang.String"> <![CDATA[$V{WEDS_DOW}]]> </textFieldExpression> </textField> <textField evaluationTime="Band"> <reportElement isPrintRepeatedValues="false" x="343" y="15" width="70" height="20"/> <textElement/> <textFieldExpression class="java.lang.String"> <![CDATA[$V{FRIDAY_DOW}]]> </textFieldExpression> </textField> </band> </columnHeader> <detail> <band height="100"> <textField evaluationTime="Band" pattern="###0"> <reportElement isPrintRepeatedValues="false" x="413" y="0" width="70" height="20" isRemoveLineWhenBlank="true"/> <textElement/> <textFieldExpression class="java.lang.Number"> <![CDATA[$V{FRI_APT_COUNT}]]> </textFieldExpression> </textField> <textField evaluationTime="Band" pattern="###0"> <reportElement isPrintRepeatedValues="false" x="343" y="0" width="70" height="20" isRemoveLineWhenBlank="true"/> <textElement/> <textFieldExpression class="java.lang.Number"> <![CDATA[$V{FRI_APT_COUNT}]]> </textFieldExpression> </textField> <textField evaluationTime="Band" pattern="###0"> <reportElement isPrintRepeatedValues="false" x="133" y="0" width="70" height="20" isRemoveLineWhenBlank="true"/> <textElement/> <textFieldExpression class="java.lang.Number"> <![CDATA[$V{TUES_APT_COUNT}]]> </textFieldExpression> </textField> <textField evaluationTime="Band" pattern="###0"> <reportElement isPrintRepeatedValues="false" x="483" y="0" width="70" height="20" isRemoveLineWhenBlank="true"/> <textElement/> <textFieldExpression class="java.lang.Number"> <![CDATA[$V{SUN_APT_COUNT}]]> </textFieldExpression> </textField> <textField evaluationTime="Band" pattern="###0"> <reportElement isPrintRepeatedValues="false" x="203" y="0" width="70" height="20" isRemoveLineWhenBlank="true"/> <textElement/> <textFieldExpression class="java.lang.Number"> <![CDATA[$V{WEDS_APT_COUNT}]]> </textFieldExpression> </textField> <textField evaluationTime="Band" pattern="###0"> <reportElement isPrintRepeatedValues="false" x="273" y="0" width="70" height="20" isRemoveLineWhenBlank="true"/> <textElement/> <textFieldExpression class="java.lang.Number"> <![CDATA[$V{THURS_APT_COUNT}]]> </textFieldExpression> </textField> <textField evaluationTime="Band" pattern="###0"> <reportElement isPrintRepeatedValues="false" x="63" y="0" width="70" height="20" isRemoveLineWhenBlank="true"/> <textElement/> <textFieldExpression class="java.lang.Number"> <![CDATA[$V{MON_APT_COUNT}]]> </textFieldExpression> </textField> <textField> <reportElement isPrintRepeatedValues="false" x="1" y="0" width="62" height="20"/> <textElement/> <textFieldExpression class="java.lang.String"> <![CDATA[$F{DAY}]]> </textFieldExpression> </textField> </band> </detail> <columnFooter> <band/> </columnFooter> <pageFooter> <band height="27"> <textField pattern="" isBlankWhenNull="false"> <reportElement key="textField" x="340" y="4" width="170" height="19"/> <box> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="0.0" lineColor="#000000"/> <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Right"> <font fontName="Helvetica" size="10"/> </textElement> <textFieldExpression class="java.lang.String"> <![CDATA["Page " + $V{PAGE_NUMBER} + " of "]]> </textFieldExpression> </textField> <textField evaluationTime="Report" pattern="" isBlankWhenNull="false"> <reportElement key="textField" x="514" y="4" width="36" height="19" forecolor="#000000" backcolor="#FFFFFF"/> <box> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="0.0" lineColor="#000000"/> <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Left" verticalAlignment="Top" rotation="None" lineSpacing="Single"> <font fontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="CP1252" isPdfEmbedded="false"/> </textElement> <textFieldExpression class="java.lang.String"> <![CDATA["" + $V{PAGE_NUMBER}]]> </textFieldExpression> </textField> <line> <reportElement key="line" x="0" y="1" width="550" height="1" forecolor="#000000"/> <graphicElement> <pen lineWidth="2.0" lineStyle="Solid"/> </graphicElement> </line> <textField pattern="" isBlankWhenNull="false"> <reportElement key="textField" x="1" y="6" width="209" height="19"/> <box> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="0.0" lineColor="#000000"/> <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement> <font fontName="Times-Roman" size="10"/> </textElement> <textFieldExpression class="java.util.Date"> <![CDATA[new Date()]]> </textFieldExpression> </textField> </band> </pageFooter> <summary> <band height="300"> <subreport isUsingCache="false" runToBottom="true"> <reportElement stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" x="4" y="0" width="546" height="300"/> <subreportParameter name="REPORT_START_DATE"/> <subreportParameter name="REPORT_END_DATE"/> <subreportParameter name="PARAM_TYPE"/> <subreportParameter name="PARAM_VALUE"/> <subreportParameter name="APP_LIST"/> <subreportParameter name="SUMMARY_LEVEL"/> <connectionExpression> <![CDATA[$P{REPORT_CONNECTION}]]> </connectionExpression> <subreportExpression class="java.lang.String"> <![CDATA["repo:Group_by_Day.jrxml"]]> </subreportExpression> </subreport> </band> </summary></jasperReport>[/code]

Tried something different. Not sure why it's doing this but like I said, i kept it to simply $F{DOW} , which is a string and has "Monday", "tuesday", etc. Because I never got any returns, I kind of tricked it to show me what that field is actually showing. <variable name="TUES_DOW" class="java.lang.String" resetType="None"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("MONDAY")? $F{theDate} : $F{DAY_OF_WEEK}]]> </variableExpression></variable>[/code]So since it doesn't equal "MONDAY", it won't show me theDate, it'll actually show me the day of week it holds. The other variables would be "WEDS_DOW", etc. Here are the results TUESDAYTUESDAYTUESDAYTUESDAYTUESDAYTUESDAYTUESDAY then go to page two of Report and it'll show.. So you see that one day it does find "TUESDAY" but the rest it shows a different day. So it's confusing to me. Is this the way it's coming back from the database or is there some way I changed this up in JasperReports? For this scenario, it's not a crosstab. Just in the Detail Band. WEDNESDAYWEDNESDAY07-27-2011WEDNESDAYWEDNESDAYWEDNESDAYWEDNESDAY

The oddest thing is the simple $V(DOW) variable. It's a string that looks to see if it equals "MONDAY", "TUESDAY" etc coming from the database results. There is a field returned called DAY that shows what day of week it is. So to test that simple string, I made the Column Headers the $V{DOW}.equals("MONDAY") and so on. Most of the time they come back as null. Once in a while I'll see WEDNESDAY as a column header, but not always. I'm not sure if it's the data that's being returned or if it's the variable. But to me it's a simple comparison. I've tried both numeric and string. Doesn't matter. This simple == 'string' either returns null and places that in the column header or it'll show WEDNESDAY once in a while. And going back to the AppCount variable, the only time it'll show up is under WEDNESDAY. Not always, but sometimes. That's the odd part that's confusing. <variable name="SUN_DOW" class="java.lang.String" resetType="None"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals(7)? $F{DAY_OF_WEEK} : null]]> </variableExpression></variable><variable name="SUN_DOW" class="java.lang.String" resetType="None"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("SUNDAY")? $F{DAY_OF_WEEK} : null]]> </variableExpression></variable><variable name="SUN_APP_COUNT" class="java.lang.Number" calculation="Sum"> <variableExpression> <![CDATA[$F{DAY_OF_WEEK}.equals("SUNDAY")? $F{APPOINTMENT_COUNT} : null]]> </variableExpression></variable>[/code]

it's numeric and 'sum'. I have been moving things around from Detail band to Summary band, etc. I got one aspect of the report,(a subreport) to work and that was a crosstab placed in the Summary band. The break down by day and AppCount by week right now is in the detail band. If I placed it in the Summary band with the Crosstab, the formatting and layout is all screwed up and the report results will show Crosstab first, then AppCount piece, then Crosstab results and the formatting is all over the place and ugly.

Right now the expression is set up as... <variable name="WEDS_APT_COUNT" class="java.lang.Number" calculation="Sum"> <variableExpression> <![CDATA[$F{DOW}.equals("WEDNESDAY") ? $F{COUNT_APPS} : null]]> </variableExpression</variable>[/code]Every other variable is set the same as I just copy and pasted it and changed WEDS to THURS, MON, etc. But the weird thing is sometimes I'll get NULL, other times I'll get WEDNESDAY(FOR DOW HEADER) and it'll have the sum of APPS under it. But I never see any other day. MON, TUES, THURS, FRI, SAT, SUN are always null or just blank. But if I just run $F{DOW} I'll get data back..Something isn't working right in Jasper or in the way the data is looked at. Doesn't make sense why WEDS would be seen, the APPS COUNT would be summed and listed sometimes, but nothing else is ever listed. Only Null or Blank. Really at a loss now.

I got the textfield expression to work, add days, subtract days etc. What I'm having issues with is passing a parameter using this expression. Say $P{StartDate}-7 days. Always comes back with errors regardless of what I do. The other issue seems to be textfields works but variables and parameters don't work the same way.... If I want to set up July 1 - July 7 as Week MON TUES WEDS THURS FRI WeekONE 27 33 44 55 66 WeekTWO 8 5 5 6 7 WeekTHREE 4 44 66 22 11 WeekFOUR 4 11 1 2 2 TOTAL 43 93 You get the point. All I get back from the function is a Date, count#, etc. From that point I need to convert StartDate and EndDate into Weeks, convert Date into Day of Week, and then populate the Days of the week numbers in the appropriate column and appropriate Week. Then Total those days up at the bottom. So far converting, calculating, and counting all this stuff in Jasper is one big pain. And doesn't exactly work. Post Edited by pjamack at 08/04/2011 23:08

The example report/file on that page is corrupt and doesn't open... Nevermind got it to open with Notepad. Post Edited by pjamack at 08/04/2011 22:57

So without adding jar files or scriptlets, what is the easiest way to break things up into weeks, month, etc. If I have input controls for StartDate and EndDate and pass in say July 1 to July 31 2011 into a function that returns fields in a cursor, how would I go about translating July 1 - 7 as WeekOne, July 8-15 as WeekTwo and so on. And within those date ranges, I also have to calculate values/counts for certain columns. It's really not just about displaying a date range but also computing the values in fields for those date ranges. To me it would be much easier to do the calculations and computations in Oracle, but not able to do that. So in Jasper, what is the best way to go about converting a StartDate, EndDate into WeekOne, WeekTwo , etc.

I'm using JasperReports 4.0.0 and there seems to be an issue with old report data keeps showing up. I call an Oracle function that returns results in a cursor from a temp table. Even when I delete everything from the temp table, this data keeps showing up. The input controls are rather simple. You input StartDate, EndDate, then a couple of values and the reoport should return. But no matter what dates I enter, it seems I continue to get data from something I ran hours ago. For example if StartDate is July 1 and EndDate is July 31, I'll get my reports in a crosstab(summary band) and everything looks fine. 30 minutes later I might run the same report and enter StartDate as Aug 1 and EndDate as Aug31 (to see a count of issues coming this month).... or I could enter Jan 1 to Jan 31, doesn't matter. I'll get the results of my new report, but after the results from the July 1 to July 31 data is showed. I'm not understanding why the old data even shows up if I've deleted the temp_table, ran a new report, logged out of they server, shut down my browser, etc. Does Jasper store data or is there some kind of connection pooling I'm not aware of set up between Jasper and Oracle and my queries and calls are always stored until flushed or reset?

Has anybody figured out how to set up this without writing a scriptlet or java code? For a report I'm working on, somebody enters StartDate, EndDate and lets say that's from July 1 to Aug 1 2011. Is there an easy way in Jasper to break this down into Weekly Ranges? AKA July 1 - July 7 is WEEK ONE and so on. ANd along with that, having the ability to show the data and rows within those date ranges and then total them at the end?

Check to make sure you have the right Database name, right table names, right column names. Check to see if you have the right datasource properties set up, right JDBC Oracle driver, etc. Sometimes you might need to reference domain, server name,etc. FOr example Your database is called TOYS and resides on ABC server. You might need to reference it as ABC.TOYS.tablename and so on.

Another quick question is there a way to run a subreport, call a function, and have different input values? Say a report has 5 standard Inputs. StartDate, EndDate, ValueX, ValueYBasedOffX, LevelOfReport The Main report has a date range say from Today till next Thursday. The Main Report Level is at the highest level..Lets say By State or Country. The SubReport has a date range from Today minus 30 days. (then in the report broken down by week) The SubReport's Level is broken down by City and County. Both reports call the same function, but with only One Input for the main report, how would I get those values to pass in and run again?

Thanks for all your help. I will try this. What's weird is that I've tried various things and nothing really works except for Wednesday. Whether I just want a Column Header to say WEDS or if I want a Field under the Column WEDS to show AptCounts, it came back with results. For Example : MON TUES WEDS THURS FRI SAT SUN null null 500 null null null null So I tried to go even more basic and got rid of the AptCount and just tried to see if I could get any results from the Cursor for the Day of Week. Figuring maybe it wasn't reading the number or string right or something. I tried to add the Day from the Cursor. AKA $F{DOW}.equals("TUESDAY") and show that day in the column header. Again, everything came back either blank or null. No column headers. Except for WEDNESDAY. So I looked at each variable and then just tried to hardcode the expressions in the text field. I'm not sure what the issue is. I do get back DOW if I just use the $F{DOW} most of the time.(Depends on what parameters are passed) but if I try and use the expression or variable, the only day that ever seems to return is WEDNESDAY. And I copy and pasted the same things over and over to make sure every variable was the same. Maybe something in the database or the cursor is sending back upper case/lower case or mix strings, I don't know. I even changed the cases and still nothing. If nothing worked, then I'd think I was doing something wrong. But the fact Wednesday came back with results, was weird. This would be a lot easier if I could just write up the function/procedure and just pass back $APT_COUNT_SUNDAY and populate my Report field with that. But I have no access to the code and am not allowed to change any of that. They want everything done in JasperReports. Not sure why, as I always thought a lot of calculations and changes should be done on database server and not reportServer. But it is what it is and I'm stuck building a rather simple in a complicated way report and it's taking forever because people don't want to add anything to DB .

For whatever reason this doesn't work. It loads fine, runs, but I'm not getting any results. It's always NULL. So I tried to just use DOW instead of Count# accumulation to see if anything works. Again, nothing. <variable name="TUES_DOW" class="java.lang.String"> <variableExpression><![CDATA[$F{DOW}.equals("TUESDAY")? $F{DOW} : null]]></variableExpression></variable>[/code]Maybe it's not reading the string right, I don't know. But If I use $F{DOW} I will Get TUESDAY in the rows.. If I use the Variable, I don't get anything. At a loss right now as to why it's not working. Getting this to work will at least move me in the right direction to get the Count# working.

Thanks. I will try that today. I've been playing around with other things and getting some things to work, but it's a complicated report. This is one piece of it. There is a crosstab, other groupings, etc. They want like 3-4 reports in one and I'm just trying to figure out a piece at a time. Another hard part is the Input Date. A user will input say Today, check for all counts till next week(and the fields will be represented by when they clicked on the report). So the Days are always hard coded in a way. Mon, Tues, Weds, Thurs, Fri, Sat, Sun are always the column header. But the dates are different. So if a person ran a report today till next Tuesday, it would be today's date and count under Tues, weds, thurs, etc. And then Mon, which is before Tues in the header, shows next Mondays date and count. Weird setup but it is what it is and what is wanted. That's really not the hardest issue, if I can get the columns and data setup , I'll figure that part out. It's the input dates. What IT wants is somebody to click on todays date,and the end date is only 7 days forward so they can't crash the system, but for the totals on the bottom of the report, I have to subtract up to a month ago for the date range from the start date and group down by week for say a 4 week range. So week 1 ends on, week 2 ends on. All this in the report. The SP in the function package is standard. I figured a way to subtract and add dates to the dates in my fields. What I haven't figure is how to pass that into a parameter. Otherwise the only dates going in will be say today till next tuesday and not today till next tuesday, minus 28 days worth of data.

The only thing that worked was PrintWhenExpression $F{DOW} == "Monday" but that doesn't help with accumulating Count Numbers by day. If i do the conditional expressions it comes back with Incompatible conditional operand types Number and int value = (java.lang.String). So if I turn the AptCount to a String, it kind of defeats the purpose of adding them and totaling them. At a loss right now.

So in essence, if using that basic Expression/condition ( $F{day_of_week}.equals("Monday") ? ($F{CountApp}) : (if not don't add) )

I tried that with conditions and since "Monday" wasn't an object, it came back with errors. So I tried $F{DOW}=="Monday" and that seems to work, at least as far as no errors. But now I need to figure out how to count each appointment based on the day of week. AKA Monday has 13 appointments, Tuesday 12, etc. I don't get those from the DB. All I get is a cursor which returns general fields. I have to do the calculations and break up in Jasper.

Trying to figure a simple way to grab data based on certain conditions. All the data was returned from a cursor in a function and now I need to set up the report. Example .. Week end Mon Tues Weds Thurs Fri 01/17/11 5 10 11 4 3 01/10/11 4 12 5 3 6 ---------------------------------------------------------------- total 9 22 16 7 9 So essentially I need to take a particular returned field, make sure it's only For Monday, add that up, then total it, etc for each week. Unfortunately none of this was done in the function.. Just pass in date paremeters, return cursor and figure a way to set everything up in the report. What' the best way to do some kind of IF DAY=MONDAY, THEN +1 for COUNT, etc.

I've been toying around with building a Crosstab report and it's been a trying process. I've done the crosstab in group & Summary idea. I've done the create a subreport and sub dataset idea. Nothing seems to work. I call a Oracle Function, pass in parameters from the input prompt, and return variable fields in the Main report. If I don't use a Crosstab or Subreport, I can get data. Once I add the Crosstab, I'm returning Blank empty reports. No errors, just empty reports. If I run another entirely different report that uses the temp table where the function stores the data first, and then run this Report, I do return results but it's results based off of what's already in the temp table. NOT what was supposed to be input and retunred from the Crosstab report. So what's the best solution to get Crosstab reports to actually work and run a Oracle Function that passes in parameters and returns variable fields?

Is this the only solution? I'm calling a function that creates a temp table, returns a cursor, etc and it seems i can't pass fields in the crosstab query. So I tried a workaround where I just queried the temp table, but for whatever reason, the main report doesn't seem to call the function when I create a subreport. If i run another report on the server that stores data in that temp table, (it's not auto commit), my crosstab is fine and works. But it only returns data that the other report ran. Any new inputs or variables are ignored. Trying to figure a way to workaround the crosstab returning blank reports or old data.

It's reporting. If you read half the forums, many people have similar problems that go unresovled or unanswered. If you start talking to people who actually have heard of JasperReports and those who use it, there are very few people who actually give it good remarks. It seems many people create work arounds for it's many limitations. I've used many products over the years and in the end, Reporting isn't about the technology. It's about getting the right reports to the right people with the right information. From my experience and from many people I've spoken to recently, JasperReports can't do the simple Reporting job very well. So yes, if you need a screwdriver, you should find a screwdriver. But if the screwdriver doesn't do it's job very well, that's a problem.

After using this product for a short time, it's obvious JasperReports isn't ready for 'prime time' usage. The forums are rather useless as most questions go unanswered or unresolved. I can't tell you how many times I've searched on google and found unanswered questions. Either people don't care, are rude, or nobody really knows. Many posts link to long gone pages or something posted from 2006. And if it were something different, I'd say you could work around it, but it's reporting. I won't even go into the other products. Reporting isn't about technology. It's not about spending months figuring out how to configure some radio list or how to work around the lack of dynamic columns. Rather simple reporting tasks just aren't that simple in Jasper. Even migrating reports from old systems to new ones is a major hassle and undertaking. At the end of the day, reporting is about getting results to business users. If it takes you soo long figuring out that "arial" isn't supported or you have to write some code to work around the lack of this or that, it's just not worth it. The joke I'm hearing is people write code to avoid dealing with JasperReports lack of common reporting functionalities. People have written their own reporting engines to get around simple tasks Jasper can't seem to do. It really should be as easy as 1-2-3 to build reports, get them out there, and move on to the next issue. Reporting should never be about the technology. It seems with Jasper, it's all about the technology and figuring out this fix or this problem or creating a work around. If you work in the corporate world or any world for that matter, nobody cares about a lack of dynamic columns unless you write some code. It's a report. It shouldn't be this complicated. And so far, JasperReports is just not a very good tool for most companies. Especially companies where developers don't have access to the server functions and report developers aren't the programmers and don't have access to eclipse, etc. Yeah play around with it at home, but I wouldn't use it in the corporate world. It' s just not that good. If I have to waste time writing code to work around an issue instead of working on a report or working on business code, it kind of is a waste of time. Post Edited by pjamack at 07/20/2011 22:29

Everything works fine in a package with a bunch of functions in it. StoredProcs are just a pain and don't really work. Stick with Functions.

I'm migrating all reports fromJasperServer & JasperReports version 2.7 to version 4.0 and am coming across serious problems in getting them to work. What works on 2 doesn't work on 4. I'm calling a function in Oracle, made sure all the input parameters were exactly the same as the old version and yet, the exact same report, which works on version 2, comes back with 'Can't execute SQL...ORA Missing Expression Errors, etc. on Version 4. Confused at this point as to why the exact same report works on Version 2 and after Migrating and adding inputs on IReport, it can't run the PL/SQL function call. Like I said, it works on the older version, no issues. Is there something I have to do to migrate all the reports over to the new version?