if else textfield expressions

1

 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. 

 

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago

21 Answers:

0

Use this function in Ireport:

 

( <condition> ? exp1 : exp2 )

 

For example:

( $P{day_of_week}.equals("Monday") ? (do something) : (do something else) )

The question mark is the "true" condition, while the colon is the "false" condition.

 

David

 

despec's picture
124
Joined: Jan 26 2010 - 11:45am
Last seen: 5 years 7 months ago
1

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.

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
1

 So in essence, if using that basic Expression/condition

 

( $F{day_of_week}.equals("Monday") ? ($F{CountApp}) : (if not don't add) )

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

 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.  

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

I'm not sure if this will work, but try something like this.  Setup 5 variable fields (say Monday_Count to Friday_Count, and set them to evaluate at "Report Time".  In the expression editor, add this:


( $P{dotw}.equals{"Monday"} ? $F{CountAppt} : (else statment) )  for each variable.  Then place these variable fields in the summary band.  I believe this will get you your counts.

David

 

despec's picture
124
Joined: Jan 26 2010 - 11:45am
Last seen: 5 years 7 months ago
0

 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.    

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

 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>

 

 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.

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0
Two things...You need to create a "variable" to count...That is, create a variable called "MONDAY_CNT", set the Calculation to "Count" and the evaluation time to "Report"...

Try a boolean expression in variable expression field:

Boolean.valueOf ($F{DOW}.equals("TUESDAY"))

Not sure, though, this is going to work, but give it a shot.

David
despec's picture
124
Joined: Jan 26 2010 - 11:45am
Last seen: 5 years 7 months ago
0

 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 .

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

 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?

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

 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>

 

  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.

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0
A lot of it depends on where you are placing the variable object. If you're summing, you'll want to put the variables in the Summary band. If you're putting them in the detail band, you'll end up seeing just the current record value...What kind of value is in $F{COUNT_APPS}?
despec's picture
124
Joined: Jan 26 2010 - 11:45am
Last seen: 5 years 7 months ago
0

 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.

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

 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 comparision.   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>

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

 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>

 

  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

TUESDAY   TUESDAY   TUESDAY   TUESDAY   TUESDAY   TUESDAY  

TUESDAY

 

 

 

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.

WEDNESDAY WEDNESDAY 07-27-2011 WEDNESDAY WEDNESDAY WEDNESDAY

WEDNESDAY

 

 

 

 

 

 

 

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0
At this point, it might be best to post your JRXML file...Quite honestly, I'm as confused as you.

David
despec's picture
124
Joined: Jan 26 2010 - 11:45am
Last seen: 5 years 7 months ago
0

 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>

 
pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

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

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

 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.

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

 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
pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
0

 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.

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 7 years 8 months ago
Feedback
randomness