Jump to content
Changes to the Jaspersoft community edition download ×

Barchart not combining categories to single chart


abalakrishnan95

Recommended Posts

This is a follow-up to a previous question I asked here
https://stackoverflow.com/questions/51968001/bar-chart-not-showing-all-categories-jaspersoft-studio
and here
https://stackoverflow.com/questions/51900998/jasper-creating-a-bar-chart-for-each-series-variable

Update: 10:06 AM 08/30/2018, I have added an update here https://stackoverflow.com/questions/52084723/jaspersoft-barchart-seperating-charts-by-category?noredirect=1&lq=1

I am at the point where I am able to see all the series on the same chart but now it is creating a chart for each quarter per title. However it should be creating a chart per title with all four quarters in the categories section

The data coming back from the query looks like this

    +-----+---------+-----------+-----------+----------+------------+
    | qtr | url_txt | pub_page  | itm_page  | cms_page |  gst_page  |
    +-----+---------+-----------+-----------+----------+------------+
    | Q1  | url1    |       123 |         5 |                 1 |               0 |
    | Q1  | url2    |        10 |         8 |                 10 |         12 |
    | Q2  | url1    |       129 |        20 |               39 |          1 |
    | Q3  | url2    |       129 |       128 |            371 |         83 |
    +-----+---------+-----------+-----------+----------+------------+

The JRXML is as so 

<?xml version="1.0" encoding="UTF-8"?>  <!-- Created with Jaspersoft Studio version 6.3.1.final using JasperReports Library version 6.3.1  -->  <!-- 2018-08-29T14:10:32 --><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="DEL_sys_usg_rpt_bar"              pageWidth="792" pageHeight="576"              whenNoDataType="AllSectionsNoDetail"              columnWidth="536"              leftMargin="20" rightMargin="20"              topMargin="20" bottomMargin="20"              isSummaryWithPageHeaderAndFooter="true"              uuid="c90df0e9-ca70-4aa8-88ab-3cc66cad2ade">       <property name="com.jaspersoft.studio.data.defaultdataadapter"              value="New Data Adapter (2)"/>       <property name="com.jaspersoft.studio.data.sql.tables"              value=""/>        <property name="com.jaspersoft.studio.unit."              value="inch"/>      <property name="net.sf.jasperreports.print.create.bookmarks"              value="false"/>      <subDataset name="Dataset1"                uuid="7d5280b5-f2f1-4d32-9729-2bb222a23169">           <property name="com.jaspersoft.studio.data.defaultdataadapter"                  value="One Empty Record"/>         <queryString><![CDATA[]]></queryString>        </subDataset>    <parameter name="START_DATE" class="java.util.Date"/>        <parameter name="END_DATE"       class="java.util.Date"/>        <parameter name="START_DATE_RPT" class="java.lang.String"/>    <parameter name="END_DATE_RPT"   class="java.lang.String"/>    <parameter name="SCREENS"        class="java.util.Collection"/>     <parameter name="Q1START"  class="java.util.Date"/>       <parameter name="Q1END"    class="java.util.Date"/>      <parameter name="Q2START"  class="java.util.Date"/>     <parameter name="Q2END"    class="java.util.Date"/>      <parameter name="Q3START"  class="java.util.Date"/>     <parameter name="Q3END"    class="java.util.Date"/>      <parameter name="Q4START"  class="java.util.Date"/>     <parameter name="Q4END"    class="java.util.Date"/>     <queryString>        <![CDATA[select *  from ( SELECT t.qtr,                   t.url_txt as url_txt,                 sum(t.pub) as pub_page,                 sum(t.itm) as itm_page,                 sum(t.gst) as gst_page,                  sum(t.lib) as lib_page            from ( SELECT 'Q1' as qtr,                           url_txt,                         CASE when user_role_txt = 'Item Developers' then 1 else 0 end as itm,                            CASE when user_role_txt = 'Public'          then 1 else 0 end as pub,                              CASE when user_role_txt = 'Librarian'       then 1 else 0 end as lib,                             CASE when user_role_txt = 'GST'             then 1 else 0 end as gst                             from del_sys.sys_usg                                    where acs_ts::date >= $P{Q1START}                          AND acs_ts::date <= $P{Q1END}                      AND $X{IN,url_txt,SCREENS}                  UNION ALL                   SELECT 'Q2' as qtr,                                 url_txt,                                CASE when user_role_txt = 'Item Developers' then 1 else 0 end as itm,                         CASE when user_role_txt = 'Public'          then 1 else 0 end as pub,                         CASE when user_role_txt = 'Librarian'       then 1 else 0 end as lib,                         CASE when user_role_txt = 'GST'             then 1 else 0 end as gst                    from del_sys.sys_usg                   where acs_ts::date >= $P{Q2START}                     AND acs_ts::date <= $P{Q2END}                     AND $X{IN,url_txt,SCREENS}                  UNION ALL                   SELECT 'Q3' as qtr,                          url_txt,                          CASE when user_role_txt = 'Item Developers' then 1 else 0 end as itm                         CASE when user_role_txt = 'Public'          then 1 else 0 end as pub,                         CASE when user_role_txt = 'Librarian'       then 1 else 0 end as lib,                         CASE when user_role_txt = 'GST'             then 1 else 0 end as gst                     from del_sys.sys_usg                   where acs_ts::date >= $P{Q3START}                     AND acs_ts::date <= $P{Q3END}                     AND $X{IN,url_txt,SCREENS}                   UNION ALL                   SELECT 'Q4' as qtr,                         url_txt,                          CASE when user_role_txt = 'Item Developers' then 1 else 0 end as itm,                         CASE when user_role_txt = 'Public'          then 1 else 0 end as pub,                          CASE when user_role_txt = 'Librarian'       then 1 else 0 end as lib,                          CASE when user_role_txt = 'GST'             then 1 else 0 end as gst                    from del_sys.sys_usg                     where acs_ts::date >= $P{Q4START}                     AND acs_ts::date <= $P{Q4END}                     AND $X{IN,url_txt,SCREENS}                ) as t          group by t.qtr, t.url_txt       ) as f]]>    </queryString>        <field name="url_txt"  class="java.lang.String"/>      <field name="qtr"      class="java.lang.String"/>    <field name="itm_page" class="java.lang.Integer"/>    <field name="gst_page" class="java.lang.Integer"/>    <field name="pub_page" class="java.lang.Integer"/>    <field name="lib_page" class="java.lang.Integer"/>    <sortField name="qtr"/>     <group name="Group1" keepTogether="true">        <groupExpression>            <![CDATA[$F{url_txt}]]>        </groupExpression>     </group>        <pageHeader>         <band height="98" splitType="Stretch">            <staticText>                 <reportElement key="t_4" mode="Opaque"                               x="130" y="-1"                               width="320" height="15"                               isRemoveLineWhenBlank="true"                               forecolor="#000000"                               backcolor="#FFFFFF"                               uuid="70d07b08-a98f-462f-912c-86e8ca3cffa5">                    <property name="net.sf.jasperreports.export.pdf.tag.h1"                              value="full"/>                    <property name="com.jaspersoft.studio.unit.x"                              value="pixel"/>                </reportElement>                <box>                    <topPen    lineWidth="0.0" lineColor="#000000"/>                    <leftPen   lineWidth="0.0" lineColor="#000000"/>                     <bottomPen lineWidth="0.0" lineColor="#000000"/>                     <rightPen  lineWidth="0.0" lineColor="#000000"/>                </box>                <textElement textAlignment="Center"                             verticalAlignment="Top">                    <font fontName="Arial" size="12" isBold="true"/>                    <paragraph lineSpacing="Single"/>                </textElement>                <text><![CDATA[DEL Report]]></text>            </staticText>             <textField pattern="([GENERAL])" isBlankWhenNull="true">                <reportElement key="COMPUTE_5" mode="Opaque"                               x="140" y="15" width="300" height="15"                               forecolor="#000000" backcolor="#FFFFFF"                               uuid="fa243ebd-82b3-426a-8693-995a1d029d8c">                     <property name="net.sf.jasperreports.export.pdf.tag.h2"/>                    <property name="net.sf.jasperreports.export.pdf.tag.h1"                              value="full"/>                </reportElement>                <box>                     <topPen    lineWidth="0.0" lineColor="#000000"/>                    <leftPen   lineWidth="0.0" lineColor="#000000"/>                     <bottomPen lineWidth="0.0" lineColor="#000000"/>                     <rightPen  lineWidth="0.0" lineColor="#000000"/>                 </box>                 <textElement textAlignment="Center"                             verticalAlignment="Top">                    <font fontName="Arial" size="12" isBold="true"/>                    <paragraph lineSpacing="Single"/>                 </textElement>                 <textFieldExpression>                    <![CDATA["Data Element Library Usage Report"]]>                </textFieldExpression>            </textField>                   <textField pattern="" isBlankWhenNull="true">                <reportElement key="DATE_1" mode="Opaque"                               x="430" y="0" width="102" height="12"                               forecolor="#000000" backcolor="#FFFFFF"                               uuid="8c30e5fd-4921-4b61-a07d-fec225f73dca">                            <property name="net.sf.jasperreports.export.pdf.tag.h2"                              value="full"/>                             <property name="net.sf.jasperreports.export.pdf.tag.h3"/>                        </reportElement>                        <box>                             <topPen    lineWidth="0.0" lineColor="#000000"/>                             <leftPen   lineWidth="0.0" lineColor="#000000"/>                             <bottomPen lineWidth="0.0" lineColor="#000000"/>                             <rightPen  lineWidth="0.0" lineColor="#000000"/>                        </box>                        <textElement verticalAlignment="Top">                             <font fontName="Arial" size="8"/>                             <paragraph lineSpacing="Single"/>                        </textElement>                        <textFieldExpression>                    <![CDATA["Run Date:  " + new SimpleDateFormat("MM/dd/yyyy").format(new Date())]]>                </textFieldExpression>                   </textField>                   <textField pattern="" isBlankWhenNull="true">                        <reportElement key="PAGE_1" mode="Opaque"                               x="430" y="12" width="40" height="12"                               forecolor="#000000" backcolor="#FFFFFF"                               uuid="d3278171-f2e1-4241-b57f-5e4f3fc2ee09">                             <property name="net.sf.jasperreports.export.pdf.tag.h2"                              value="start"/>                             <property name="net.sf.jasperreports.export.pdf.tag.h3"/>                        </reportElement>                        <box>                             <topPen    lineWidth="0.0" lineColor="#000000"/>                             <leftPen   lineWidth="0.0" lineColor="#000000"/>                            <bottomPen lineWidth="0.0" lineColor="#000000"/>                            <rightPen  lineWidth="0.0" lineColor="#000000"/>                        </box>                        <textElement verticalAlignment="Top">                             <font fontName="Arial" size="8"/>                             <paragraph lineSpacing="Single"/>                        </textElement>                        <textFieldExpression>                    <![CDATA["Page " + $V{PAGE_NUMBER} + " of "]]>                </textFieldExpression>                   </textField>                   <textField evaluationTime="Report"                       pattern=""                       isBlankWhenNull="true">                        <reportElement key="PAGE_1" mode="Transparent"                               x="470" y="12" width="30" height="12"                               forecolor="#000000" backcolor="#FFFFFF"                               uuid="f12aa9ee-4dbb-4569-bad4-1994dcfc045c">                             <property name="net.sf.jasperreports.export.pdf.tag.h3"/>                             <property name="net.sf.jasperreports.export.pdf.tag.h2"                              value="end"/>                       </reportElement>                        <box>                             <topPen    lineWidth="0.0" lineColor="#000000"/>                             <leftPen   lineWidth="0.0" lineColor="#000000"/>                             <bottomPen lineWidth="0.0" lineColor="#000000"/>                            <rightPen  lineWidth="0.0" lineColor="#000000"/>                        </box>                        <textElement verticalAlignment="Top">                             <font fontName="Arial" size="8"/>                            <paragraph lineSpacing="Single"/>                        </textElement>                        <textFieldExpression>                    <![CDATA[$V{PAGE_NUMBER}]]>                </textFieldExpression>                   </textField>                         <staticText>                        <reportElement x="0" y="70"                               width="280" height="12"                               uuid="9f93ebb5-6ca9-4f7f-be7c-d06093207859">                             <property name="net.sf.jasperreports.export.pdf.tag.h3"                              value="start"/>                             <property name="com.jaspersoft.studio.unit.height"                              value="pixel"/>                        </reportElement>                        <textElement verticalAlignment="Top">                             <font fontName="Arial" isBold="false"/>                       </textElement>                        <text><![CDATA[Note: * indicates an empty value.]]></text>                   </staticText>                   <textField>                        <reportElement x="140" y="30"                               width="300" height="20"                               uuid="c2b0287c-ce1e-461c-aabb-1c6e28b0b30d"/>                        <textElement textAlignment="Center"/>                        <textFieldExpression>                    <![CDATA["From "+$P{START_DATE_RPT}+" to "+$P{END_DATE_RPT}]]>                </textFieldExpression>                   </textField>             </band>        </pageHeader>         <detail>              <band height="285">                  <barChart>                        <chart isShowLegend="true">                            <reportElement key="" x="-20" y="40"                                   width="760" height="245"                                   isPrintWhenDetailOverflows="true"                                   uuid="3d590b3c-9ccb-4a25-a7a7-cf8bd486947a">                                  <property name="com.jaspersoft.studio.unit.y" value="inch"/>                            </reportElement>                             <chartTitle position="Top">                                 <titleExpression>                            <![CDATA[$F{url_txt}]]>                        </titleExpression>                             </chartTitle>                             <chartSubtitle/>                            <chartLegend position="Right"/>                        </chart>                       <categoryDataset>                            <dataset resetType="Page"/>                            <categorySeries>                                 <seriesExpression><![CDATA["Public"]]></seriesExpression>                                  <categoryExpression><![CDATA[$F{qtr}]]></categoryExpression>                                <valueExpression><![CDATA[$F{pub_page}]]></valueExpression>                           </categorySeries>                                     <categorySeries>                                  <seriesExpression><![CDATA["GST"]]></seriesExpression>                                 <categoryExpression><![CDATA[$F{qtr}]]></categoryExpression>                                 <valueExpression><![CDATA[$F{gst_page}]]></valueExpression>                            </categorySeries>                             <categorySeries>                                  <seriesExpression><![CDATA["Librarian"]]></seriesExpression>                                  <categoryExpression><![CDATA[$F{qtr}]]></categoryExpression>                                  <valueExpression><![CDATA[$F{lib_page}]]></valueExpression>                             </categorySeries>                             <categorySeries>                                  <seriesExpression><![CDATA["Item Developers"]]></seriesExpression>                                  <categoryExpression><![CDATA[$F{qtr}]]></categoryExpression>                                  <valueExpression><![CDATA[$F{itm_page}]]></valueExpression>                           </categorySeries>                        </categoryDataset>                       <barPlot>                             <plot backgroundAlpha="1.0">                                  <seriesColor seriesOrder="0" color="#BD0009"/>                                  <seriesColor seriesOrder="1" color="#90F000"/>                                  <seriesColor seriesOrder="2" color="#204182"/>                                  <seriesColor seriesOrder="3" color="#8F6900"/>                                  <seriesColor seriesOrder="4" color="#C8EE8A"/>                             </plot>                            <itemLabel/>                             <categoryAxisLabelExpression>                        <![CDATA["Quarter"]]>                    </categoryAxisLabelExpression>                             <categoryAxisFormat>                                 <axisFormat labelColor="#000000"                                    tickLabelColor="#000000"                                    axisLineColor="#000000"/>                            </categoryAxisFormat>                            <valueAxisLabelExpression>                        <![CDATA["Page Count"]]>                    </valueAxisLabelExpression>                             <valueAxisFormat>                                  <axisFormat labelColor="#000000"                                    tickLabelColor="#000000"                                    axisLineColor="#000000"/>                            </valueAxisFormat>                       </barPlot>                   </barChart>              </band>              <band height="34"/>         </detail>   </jasperReport>[/code]


It should show all quarters on the bottom with the series and it appropriate value for each quarter for the url title. each chart would be a separate url title.

I believe it is because it is going row by row and creating a chart based on that but I am stumped on how to fix it.
Here is how it currently is showing

595ds.png.ec9d51ced65c0b794f6f6fedf15f597f.png

595ds.png.b32e0df33b3f2b19d8535eeb4462bb28.png

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Posted Images

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...