Bar graph not reporting correct data

0

Hello,

I am fairly new to creating charts in jasper but i'm at my wits end with this issue.

 

I have a data set that is returning proper data that is set up like so

 

qtr url_txt user_role_txt page_cnt
Quarter 1 some url1 some role1 154
Quarter 1 some url2 some role2 11
Quarter 2 some url1 some role2 12
Quarter 3 some url2 some role1 110

But every iteration I have tried has failed so I tried another way to get this table

qtrqtr url_txturl_txt user_role_txtrole1_page rolerole2_page
Quarter 1qtr 1 some url 1some url1 some role1154 15411
Quarter 1qtr 2 some url2some url1 00 1112
Quarter 2qtr 1 some url1some url2 some role20 1211
Quarter 3 qtr3 some url2some url2 some role1110

11012

something similar and this got closer with getting the correct numbers but not the right quarters showing on the chart

 

Here is how it was

 

 

 

Here is how it is now

 

In both instances, only 2 quarters tend to show up no matter the date range I choose (bottom one is one month off from the first picture)

it is usally quarter 1 and quarter 4 but there is obviously data in quarter 2 and 3 from when I run the query in the sql.

 

Here is the jrxml of the latest version

 

Any help would be amazing

<?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-21T14:23:03 -->
<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="usg_rpt_bar"
              pageWidth="576" pageHeight="792"
              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 DISTINCT ON (url_txt)
t.qtr as qtr,
t.url_txt as url_txt,
sum(t.pub) as pub_page,
sum(t.itm) as itm_page,
sum(t.cms) as cms_page,
sum(t.gst) as gst_page,
sum(t.lib) as lib_page
from
(
SELECT
'Quarter 1' 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,
CASE when user_role_txt = 'CMS' then 1 else 0 end as cms
 
from db.table
 
where acs_ts::date >= $P{Q1START}
AND acs_ts::date <= $P{Q1END}
AND $X{IN,url_txt,SCREENS}
 
UNION ALL
 
SELECT
'Quarter 2' 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,
CASE when user_role_txt = 'CMS' then 1 else 0 end as cms
 
from db.table
 
where acs_ts::date >= $P{Q2START}
AND acs_ts::date <= $P{Q2END}
AND $X{IN,url_txt,SCREENS}
 
UNION ALL
 
SELECT
'Quarter 3' 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,
CASE when user_role_txt = 'CMS' then 1 else 0 end as cms
 
from db.table
 
where acs_ts::date >= $P{Q3START}
AND acs_ts::date <= $P{Q3END}
AND $X{IN,url_txt,SCREENS}
 
UNION ALL
 
SELECT
'Quarter 4' 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,
CASE when user_role_txt = 'CMS' then 1 else 0 end as cms
 
from db.table
 
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]]>
 
    </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="cms_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>
            <image>
                <reportElement x="0" y="0" width="150" height="56"
                               uuid="5c21389e-0147-4bab-8b2b-b2462883c4e4">
                    <property name="com.jaspersoft.studio.unit.width"
                              value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.height"
                              value="pixel"/>
                </reportElement>
                <imageExpression>
                    <![CDATA[$P{CMSLOGO_DIR} + "CMS_logo.jpg"]]>
                </imageExpression>
                <hyperlinkTooltipExpression>
                    <![CDATA["Centers for Medicare & Medicaid Services"]]>
                </hyperlinkTooltipExpression>
            </image>
            <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="0" y="40" width="532" height="245"
                                   uuid="090b7b3c-b745-45bd-af08-3b79a027f3c6">
                        <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["CMS"]]></seriesExpression>
                        <categoryExpression><![CDATA[$F{qtr}]]></categoryExpression>
                        <valueExpression><![CDATA[$F{cms_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"/>
                    <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>
    </detail>
</jasperReport>

abalakrishnan95's picture
Joined: Feb 28 2018 - 6:53am
Last seen: 1 year 2 months ago

0 Answers:

No answers yet
Feedback
randomness