abalakrishnan95 Posted August 21, 2018 Share Posted August 21, 2018 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_cntQuarter 1some url1some role1154Quarter 1some url2some role211Quarter 2some url1some role212Quarter 3some url2some role1110But every iteration I have tried has failed so I tried another way to get this tableqtrqtrurl_txturl_txtuser_role_txtrole1_pagerolerole2_pageQuarter 1qtr 1some url 1some url1some role115415411Quarter 1qtr 2some url2some url1001112Quarter 2qtr 1some url1some url2some role201211Quarter 3 qtr3some url2some url2some role111011012something 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_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] Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now