Barchart not combining categories to single chart

0

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>


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

abalakrishnan95's picture
Joined: Feb 28 2018 - 6:53am
Last seen: 11 months 3 weeks ago

0 Answers:

No answers yet
Feedback