Jump to content
Changes to the Jaspersoft community edition download ×

Bar graph not reporting correct data


abalakrishnan95

Recommended Posts

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

 

qtrurl_txtuser_role_txtpage_cnt
Quarter 1some url1some role1154
Quarter 1some url2some role211
Quarter 2some url1some role212
Quarter 3some url2some role1110

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

qtrqtrurl_txturl_txtuser_role_txtrole1_pagerolerole2_page
Quarter 1qtr 1some url 1some url1some role115415411
Quarter 1qtr 2some url2some url1001112
Quarter 2qtr 1some url1some url2some role201211
Quarter 3 qtr3some url2some url2some 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

index1_2.png.aef079545d1bfbac5084a3447426b6e1.png 

 

 

Here is how it is now

index2_3.png.8a4c32a84079679a5faf5682dc8b057c.png

 

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_pagefrom(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 cmsfrom db.tablewhere acs_ts::date >= $P{Q1START}AND acs_ts::date <= $P{Q1END}AND $X{IN,url_txt,SCREENS}UNION ALLSELECT'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 cmsfrom db.tablewhere acs_ts::date >= $P{Q2START}AND acs_ts::date <= $P{Q2END}AND $X{IN,url_txt,SCREENS}UNION ALLSELECT'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 cmsfrom db.tablewhere acs_ts::date >= $P{Q3START}AND acs_ts::date <= $P{Q3END}AND $X{IN,url_txt,SCREENS}UNION ALLSELECT'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 cmsfrom db.tablewhere acs_ts::date >= $P{Q4START}AND acs_ts::date <= $P{Q4END}AND $X{IN,url_txt,SCREENS}) as tgroup 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>[/code]

 

index1_2.png.cad975e1de2aa0f7eef419e6f676b6d7.png

index2_3.png.fb0425a26fa1244b2abd84d01105f6f5.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...