Tuesday 31 March 2015

Tip : Removing Meta data of exported or scheduled or e-mail bursted jasper report : Or Changing the report pdf export meta data for Adobe Reader application

This post will give you the quick information on removing or user given pdf application name for jasper reports when open in applications like "Adobe reader".

Generally when you export the report output and open the same pdf file(s) using applications like "Adobe Reader" you can find the application name of the pdf by going "File-> Properties".

By default  the application name for the exported report in pdf format is : Jasper Reports Library 6.0

Now, the question is how to remove this or supply user name ? ( can be a developer name or email sender name)

I must say thanks to  Carlos ( A community guy) for asking me to explore and work this in Jasper. (I love doing experimenting and find something as like you).

This is as simple as one can do very easily in 2 to 3 steps and explained below( but for the 1st attempt one might get conflicts & here is the helping hand for you).

Software used : Jasper Studio 6.x and Jasper Server 6.x, Postgre SQL for simple report testing.

CASE-1 : With a Name supplied for the report 
Step 1: 
Open the report in studio and go to the properties of report
Outline -> Click on Report Name -> Find the properties of the Reports -> Find for the properties in the list and click on Add properties.

Now, add this property
Property Name: net.sf.jasperreports.export.pdf.metadata.creator
Value : Carlos Natividade

Now save the report and run (publish/schedule/email burst) and export it to pdf.

Test 1 : Exporting from studio and checking in Adobe reader properties (application name)
Test 2 : Exporting from Jasper Server and checking the file from Adobe reader properties (application name)
Test 3 : Scheduling the report in the JasperServer - output of scheduled report format is pdf and test by downloading it and checking the file from Adobe reader properties (application name)
Test 4: Email bursted report in pdf format - download the report and check the file from Adobe reader properties (application name)

In all the cases we get the application name with Carlos Natividade.

For your quick reference find the below sample output opened in "Adobe Reader Application"


CASE-II : Don't want to print any name for exported pdf when open in application like "Adobe"
Repeat the CASE-I procedure but simply ignore the value for the property.
Sample output image :


Looking for similar kind of properties to apply, please refer below reference link and play around with the properties. 

A sample JRXML which runs on postgresql foodmart database: (Looking for ready available JRXML - Copy paste below entire code in a opened Jasper Report and test it)

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.1.final using JasperReports Library version 6.0.0  -->
<!-- 2015-03-31T01:45:40 -->
<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="&quot;&quot;" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="6d836bdc-d774-45c5-8e2c-5431bdf1f892">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart_db"/>
<property name="net.sf.jasperreports.export.pdf.metadata.creator" value="Carlos Natividade"/>
<property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/"/>
<property name="ireport.jasperserver.user" value="superuser"/>
<property name="ireport.jasperserver.report.resource" value="/Explore/RemovalOfMetaData_files/main_jrxml"/>
<property name="ireport.jasperserver.reportUnit" value="/Explore/RemovalOfMetaData"/>
<queryString language="SQL">
<![CDATA[SELECT * FROM customer LIMIT 10]]>
</queryString>
<field name="customer_id" class="java.lang.Integer"/>
<field name="account_num" class="java.lang.Long"/>
<field name="lname" class="java.lang.String"/>
<field name="fname" class="java.lang.String"/>
<field name="mi" class="java.lang.String"/>
<field name="address1" class="java.lang.String"/>
<field name="address2" class="java.lang.String"/>
<field name="address3" class="java.lang.String"/>
<field name="address4" class="java.lang.String"/>
<field name="city" class="java.lang.String"/>
<field name="state_province" class="java.lang.String"/>
<field name="postal_code" class="java.lang.String"/>
<field name="country" class="java.lang.String"/>
<field name="customer_region_id" class="java.lang.Integer"/>
<field name="phone1" class="java.lang.String"/>
<field name="phone2" class="java.lang.String"/>
<field name="birthdate" class="java.sql.Date"/>
<field name="marital_status" class="java.lang.String"/>
<field name="yearly_income" class="java.lang.String"/>
<field name="gender" class="java.lang.String"/>
<field name="total_children" class="java.lang.Integer"/>
<field name="num_children_at_home" class="java.lang.Integer"/>
<field name="education" class="java.lang.String"/>
<field name="date_accnt_opened" class="java.sql.Date"/>
<field name="member_card" class="java.lang.String"/>
<field name="occupation" class="java.lang.String"/>
<field name="houseowner" class="java.lang.String"/>
<field name="num_cars_owned" class="java.lang.Integer"/>
<field name="fullname" class="java.lang.String"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="28" splitType="Stretch"/>
</title>
<columnHeader>
<band height="33" splitType="Stretch">
<textField>
<reportElement x="0" y="0" width="141" height="30" uuid="fee430bb-49ac-4209-8e03-9e4e38b40c0f">
<property name="local_mesure_unity" value="pixel"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
</reportElement>
<textFieldExpression><![CDATA["Customer ID"]]></textFieldExpression>
</textField>
<textField>
<reportElement x="141" y="0" width="141" height="30" uuid="35f79e2f-6669-454d-995a-e4697edbabae">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
</reportElement>
<textFieldExpression><![CDATA["Account Number"]]></textFieldExpression>
</textField>
<textField>
<reportElement x="282" y="0" width="141" height="30" uuid="ad4be3cf-c3cd-489f-8bef-275cefe87e14">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
</reportElement>
<textFieldExpression><![CDATA["Last Name"]]></textFieldExpression>
</textField>
<textField>
<reportElement x="430" y="0" width="124" height="30" uuid="67609fe5-b3bf-48e8-b1de-6e807e5759f2">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
</reportElement>
<textFieldExpression><![CDATA["First Name"]]></textFieldExpression>
</textField>
</band>
</columnHeader>
<detail>
<band height="32" splitType="Stretch">
<textField>
<reportElement x="0" y="0" width="141" height="30" uuid="40ea0517-5729-4d45-bbca-5ba4b9f727d4"/>
<textFieldExpression><![CDATA[$F{customer_id}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="141" y="0" width="138" height="30" uuid="95c30bfc-3933-4d41-b748-8d1bae7f894a"/>
<textFieldExpression><![CDATA[$F{account_num}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="279" y="0" width="138" height="30" uuid="3bf1e187-5105-41fa-a95b-5abcb66fe4b4"/>
<textFieldExpression><![CDATA[$F{lname}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="417" y="0" width="138" height="30" uuid="17271741-98ff-4e32-ad8a-6792020574de"/>
<textFieldExpression><![CDATA[$F{fname}]]></textFieldExpression>
</textField>
</band>
</detail>

</jasperReport>


Reference:

http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.pdf.metadata.creator

Monday 30 March 2015

Quick Learn : Report Scheduling & Bursting in Jasper Server 6.x with an example scenario

There are numerous posts on scheduling and report bursting in jasper. Even though there were, this article is an outcome of my research & hope will help you to do the same very faster with an end to end example. 

Setup : 

1) Jasper Reports Server Professional 6.0
2) A sample demo Report ( 07g.RevenueDetailReport) - Available only in professional Jasper Server.
3) Email Server : Gmail
(User Name : testjasperreport@gmail.com Password : jasperreports) - these are open for the community people who reads this page.

Aim of the post : 

Schedule a Report and send the output of report to email with attachment & also send success or failure notification email.

Step - 1

To work with email notifications & report bursting we need to work with 2 files.
They are
i)js.quartz.properties
Location of the file :
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF

Configure the mail server

report.scheduler.web.deployment.uri=http://127.0.0.1:8080/jasperserver-pro/

report.scheduler.mail.sender.host=smtp.gmail.com
report.scheduler.mail.sender.username=testjasperreport@gmail.com
report.scheduler.mail.sender.password=jasperreports
report.scheduler.mail.sender.from=testjasperreport@gmail.com
report.scheduler.mail.sender.protocol=smtp
report.scheduler.mail.sender.port=587


If your mail server requires authentication, change below property from false to true as shown in below.

ii) applicationContext-report-scheduling
Location of the file :
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF

 <bean id="reportSchedulerMailSender" class="org.springframework.mail.javamail.JavaMailSenderImpl">
        <property name="host" value="${report.scheduler.mail.sender.host}"/>
        <property name="username" value="${report.scheduler.mail.sender.username}"/>
        <property name="password" value="${report.scheduler.mail.sender.password}"/>
        <property name="protocol" value="${report.scheduler.mail.sender.protocol}"/>
        <property name="port" value="${report.scheduler.mail.sender.port}"/>
        <property name="javaMailProperties">
            <props>
                        <prop key="mail.smtp.auth">true</prop>
        <prop key="mail.smtp.starttls.enable">true</prop>
            </props>
        </property>
    </bean>

NOTE : Restart jasper server once the changes done if it is already running

Step - 2 

Now, lets work with a report and test.
* Professional server is having sample built in reports. Pick one of the report , for instance  pick "07g.RevenueDetailReport"

* Locate this report in the server, right click it find "schedule" option and click on it


Step - 3

* After clicking Schedule option, it will take us to "Schedule Job" jasper page. We call scheduling procedure as job and term it as scheduling jobs for reports.

* Click on "Create Schedule" button  and find the four tabs i) Schedule ii) Parameters iii) Output Options and iv) Notifications

i)  Schedule Tab:

As shown in below image set
a) the date and time of execution
b) Recurrence ( See documentation for the various recurrence in reference
c) Move to Parameters tab.



ii) Parameters Tab :
select the parameters of the report here .. we will get the output in pdf format for the selected input controls
* Fill all the required properties in all of the sections and save the job.

iii)  Output Options 
find the options in the image

iv) Notifications :
Find the information in below image


Click on Save button and give a name to scheduler (job)
Find the all scheduled  jobs ..

OUTPUT : Check it out in email

login to the gamil with below credentials and check the output.
testjasperreport@gmail.com /jasperreports


Opening the 2nd mail : Attachments one

Opening the 1st mail : Notifications mail


Check the output in CC mail :




Solution-2 :  (smtps)

Change below at 1st location. 
file name & location : <Jasper Installed Home>apache-tomcat/webapps/jasperserver/WEB-INF/js.quartz.properties

report.scheduler.web.deployment.uri=http://127.0.0.1:8080/jasperserver-pro/

report.scheduler.mail.sender.host=smtp.gmail.com
report.scheduler.mail.sender.username=testjasperreport@gmail.com
report.scheduler.mail.sender.password=jasperreports
report.scheduler.mail.sender.from=testjasperreport@gmail.com
report.scheduler.mail.sender.protocol=smtps
report.scheduler.mail.sender.port=465

Change below in 2nd location

 <bean id="reportSchedulerMailSender" class="org.springframework.mail.javamail.JavaMailSenderImpl">
        <property name="host" value="${report.scheduler.mail.sender.host}"/>
        <property name="username" value="${report.scheduler.mail.sender.username}"/>
        <property name="password" value="${report.scheduler.mail.sender.password}"/>
        <property name="protocol" value="${report.scheduler.mail.sender.protocol}"/>
        <property name="port" value="${report.scheduler.mail.sender.port}"/>
        <property name="javaMailProperties">
            <props>
                        <prop key="mail.smtps.auth">true</prop>
        <prop key="mail.smtps.starttls.enable">true</prop>
            </props>
        </property>
    </bean>

and the remaining procedure of setting mail is same

NOTE (Additional Notes): 
If we use Microsoft Exchange mail Services, then we need to add one more line in the applicationContext-report-scheduling.xml

      <props>
            <prop key="mail.smtp.auth">true</prop>
            <prop key="mail.smtp.starttls.enable">true</prop>
            <prop key="mail.smtp.starttls.required">true</prop>
        </props>

Resources 

Find More information at below sites : 

1)  http://stackoverflow.com/questions/9683679/email-not-sent-in-jasper-report-scheduling
2) https://mdahlman.wordpress.com/2011/09/28/emailing-reports-from-jasperreports-server/
3) http://community.jaspersoft.com/wiki/configuring-report-scheduler
4) https://community.jaspersoft.com/documentation/tibco-jasperreports-server-installation-guide-community-edition/v60/configuring-report

Tuesday 24 March 2015

Conditional colors to the bars of HTML-5 Bar Chart - Applicable on other chart types like Line Chart.

This post is a re-blogging from Jasper WIKI pages on conditional coloring of bars for HTML-5 Bar Chart.


https://community.jaspersoft.com/wiki/dynamically-updating-bar-colors-html5-bar-charts

Quick Output Image :

JRXML: Run this JRXML on foodmart database.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.final using JasperReports Library version 6.0.0  -->
<!-- 2015-03-24T10:39:11 -->
<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="HTML5BarChart-CustomiseColors" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="a146894c-9f63-42a0-bd25-21cce326e316">
    <property name="com.jaspersoft.studio.data.sql.tables" value="dGltZV9ieV9kYXkgQVMgLDI1LDc1O3NhbGVzX2ZhY3RfMTk5NyBBUyAsMTg0LDc5Ow=="/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
    <queryString language="SQL">
        <![CDATA[SELECT sum(sales_fact_1997.store_sales) as sales,
    time_by_day.the_month
FROM sales_fact_1997
    INNER JOIN time_by_day ON
     time_by_day.time_id = sales_fact_1997.time_id
     GROUP BY the_month, month_of_year
     ORDER BY month_of_year]]>
    </queryString>
    <field name="sales" class="java.math.BigDecimal"/>
    <field name="the_month" class="java.lang.String"/>
    <variable name="Variable_1" class="java.lang.String">
        <variableExpression><![CDATA[DATEFORMAT(new Date(),"MMMMM")]]></variableExpression>
    </variable>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <summary>
        <band height="358" splitType="Stretch">
            <componentElement>
                <reportElement x="0" y="0" width="555" height="238" uuid="58498ba5-f781-4a59-8db0-70e30d626b77"/>
                <hc:chart xmlns:hc="http://jaspersoft.com/highcharts" xsi:schemaLocation="http://jaspersoft.com/highcharts http://jaspersoft.com/schema/highcharts.xsd" type="Column">
                    <hc:chartSetting name="default">
                        <hc:chartProperty name="chart.zoomType">
                            <hc:propertyExpression><![CDATA["xy"]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="credits.enabled">
                            <hc:propertyExpression><![CDATA[false]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="credits.href">
                            <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="credits.text">
                            <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="title.text">
                            <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="yAxis.title.text">
                            <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression>
                        </hc:chartProperty>
                    </hc:chartSetting>
                    <multiAxisData>
                        <multiAxisDataset/>
                        <dataAxis axis="Rows">
                            <axisLevel name="Month">
                                <labelExpression><![CDATA["Level Label expression"]]></labelExpression>
                                <axisLevelBucket order="None" class="java.lang.String">
                                    <bucketExpression><![CDATA[$F{the_month}]]></bucketExpression>
                                    <bucketProperty name="color"><![CDATA[IF($F{the_month}.toString().matches(DATEFORMAT(new Date(),"MMMMM")),"red",IF($F{sales}.intValue() >= 50000,"green","blue"))]]></bucketProperty>
                                </axisLevelBucket>
                            </axisLevel>
                        </dataAxis>
                        <dataAxis axis="Columns"/>
                        <multiAxisMeasure name="Sales" class="java.math.BigDecimal" calculation="Nothing">
                            <labelExpression><![CDATA["Sales"]]></labelExpression>
                            <valueExpression><![CDATA[$F{sales}]]></valueExpression>
                        </multiAxisMeasure>
                    </multiAxisData>
                    <hc:series name="Sales">
                        <hc:contributor name="SeriesItemProperty">
                            <hc:contributorProperty name="color" valueType="Bucket" value="Month.color"/>
                        </hc:contributor>
                    </hc:series>

                </hc:chart>
            </componentElement>
        </band>
    </summary>
</jasperReport>

:-)

Friday 20 March 2015

JFree Bar Chart Customization in Jasper iReport - Decrease the size of bars & space between bars

This post will give you quick solution on customizing JFree Bar Chart .
Re-collected the year and half back workout again & here we go.

Software : 

1) Jasper iReport 5.6.1 community
2) Eclipse Luna

Problem Statement : Image-1

1) Decrease the width of the bars
2) Decrease the space b/w bars for a series bar Chart


Solution Image :Image-2


How to do ?

1) Develop your chart & have a preview which looks like image-1.

2) You need to customize the chart using JFree Java code written in Eclipse & get the output in Jar file.

3) Open Eclipse & create a project and then a class. You should import 3 jar files to your project.
   Those are : 1) jasper-reports-<version>.jar 2)jfreechart-<version>.jar & 3)jcommon-<version>.jar

  You can right click the project name and go Build path section and then Add jar files -> click on "Add external jar files " from the location where your jars are avaialbe.

How to get the jar files ?
You can get all the jar files in below jasper server installed location. (i.e., you should be installed jasper server in your local machine).

C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF\lib

Alternatively, you can type the names of the jars and download directly from web.



4) Run the code & export to  a jar file. (for exporting Project Name -> Right click - > Export)

 5) Now the downloaded jar file code has to apply on the chart customization.
For that in iReport, Go to Tools-> Options and select the jar file from your system location.


6) Now select the chart to get it's properties on right side properties window.
   search for Customizer Class property. Provide the class name of the Java code along with it's package name.
i.e., for example : com.sadakar.jfree.JFreeBarChartCustomization1

That's all.. Save the report and have a preview. you will get the output as shown in Image-1

NOTE :
When you publish the report to the server you will not get the output as expected. B'z your server doesn't know about the changes you done to the report. For this you need to load your jar file in lib folder of jasper instance. i.e., place the jar file at
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF\lib 
and restart your server.

Exploring the same with Studio 6 community & professional and update this line :-)

 Interesting...!!! Have a question or suggestion ? Drop your feedback in comment box and help community to share knowledge... :-)

Cheers..!!!




Wednesday 11 March 2015

Tips : Playing with variables in Jasper Studio to find percentages, to get the two digits after decimal point & to get the only numeric value from decimal by rounding the value

Software : 
Jasper Design Studio : 6.0.1 Pro
Jasper Server : 6.0.1 Pro

1) Calculating Percentage from java.lang.BigDecimal Types

By default all the numeric fields in report takes java.math.BigDecimal & you can not perform arithmetic  operations like division for calculating percentages. (Alternatively you need to work with the methods from BigDecimal class to get % kind of things)

Find percentage : 
1) Create a variable  of type java.lang.Double
2) Calculation = Nothing
3) Expression :
      $F{Field1}.doubleValue()/$F{Filed2}.doubleValue())*100  

    NOTE : $F{Field1} and $F{Filed2} are of java.math.BigDecimal types .
4) Increment type : None
5) Reset group :  If you are creating variables in any data set & using it in table component give your GROUP Name

6) Drag the variable on to the design area of table component.
7) Double click the variable to open it's  expression
     a) By default you can see it's expression as $V{Variable_1}
     b) Change the variable expression  to
              new DecimalFormat("#.##").format(Double.valueOf($V{Variable_1}))+"%"

2) Two decimal places after floating point for java.math.BigDecimal type values

  $V{Variable_2}.setScale(2, java.math.RoundingMode.HALF_UP)+"%"
This is on dragged variable. Not in the variable directly. The variable initially $V{Variable_2}. Double click it to get it's expression editor

Example: 95.83%

NOTE : 
a) Create a variable of double type for finding %'s .
                   i.e.,
                       Variable Name : VAR_PER_1
                      Expression :  (a.doubleValue()/b.doubleValue()) *100
                      Example : 95.834444123

b)  Now convert the above variable to Decimal type by creating a new variable of type Decimal
                 i.e., Variable Name : VAR_PER_2
                 BigDecimal.valueOf($V{VAR_PER_1})    [Remember it VAR_PER_1]

c) Now get 2 decimal places  by applying   $V{VAR_PER_2}.setScale(2, java.math.RoundingMode.HALF_UP)+"%"


3) Get only  Numeric value as percentage by rounding the decimal places of Double value

Assume you have java.lang.Double type having 45.45 and want to get 46 from it. Then the below expression will help full to you.

Variable_1=45.45                                              of java.lang.Double type
Variable_2= (int) ($V{Variable_1} + 0.5)        of java.lang.Integer type

[i.e., you need to create another variable of integer type and in its expression type cast the double variable to integer]

There could be another ways to solve the same kind of solutions using java expressions but I hope somehow the above tips help full.

Thanks for reading this page & suggestions and improvements to this page are welcome in comments box :-)

:-) SADAKAR POCHAMPALLI :-)
BI DEVELOPER, HYDERABAD, INDIA.

Friday 6 March 2015

Conditional colors to Fields in Jasper Studio 6.0.1 - Example sceenshot and JRXML

1) Style
2) Condition-1
3) Condition-2
4) On the Text Field
5) Output
6) JRXML

1) Style

2) Condition-1
3) Condition-2
4)  On the Text Field

5) Output
6) JRXML

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.final using JasperReports Library version 6.0.0  -->
<!-- 2015-03-06T13:13:06 -->
<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="Blank_A4_22" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ab524a87-197a-4dac-b294-ba81b7cb1409">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
    <style name="Style1">
        <conditionalStyle>
            <conditionExpression><![CDATA[$F{unitsales}.doubleValue()>=0 && $F{unitsales}.doubleValue()<=1000]]></conditionExpression>
            <style backcolor="#7679B0"/>
        </conditionalStyle>
        <conditionalStyle>
            <conditionExpression><![CDATA[$F{unitsales}.doubleValue()>1000 && $F{unitsales}.doubleValue()<=3000]]></conditionExpression>
            <style backcolor="#EBEDA4"/>
        </conditionalStyle>
    </style>
    <queryString>
        <![CDATA[SELECT c.city, SUM(unit_sales) UnitSales FROM customer c, sales_fact_1997 sf7 WHERE
c.customer_id=sf7.customer_id GROUP BY c.city]]>
    </queryString>
    <field name="city" class="java.lang.String"/>
    <field name="unitsales" class="java.math.BigDecimal"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="32" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="36" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="278" height="30" uuid="621ecd38-7026-4d55-9afe-6a1fa2c37613"/>
                <text><![CDATA[city]]></text>
            </staticText>
            <staticText>
                <reportElement x="278" y="0" width="277" height="30" uuid="2c237fac-2804-4031-a064-a666b37fd164"/>
                <text><![CDATA[unitsales]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="46" splitType="Stretch">
            <textField>
                <reportElement x="0" y="13" width="278" height="30" uuid="a448fd0f-d6b7-45ef-8915-3250c06d178a"/>
                <textFieldExpression><![CDATA[$F{city}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement style="Style1" mode="Opaque" x="278" y="13" width="277" height="30" uuid="2040c97e-9bd0-4c05-b515-a06f60b315af"/>
                <textFieldExpression><![CDATA[$F{unitsales}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>