abalakrishnan95 Posted August 30, 2018 Posted August 30, 2018 This is a follow-up to a previous question I asked herehttps://stackoverflow.com/questions/51968001/bar-chart-not-showing-all-categories-jaspersoft-studioand herehttps://stackoverflow.com/questions/51900998/jasper-creating-a-bar-chart-for-each-series-variableUpdate: 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=1I 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 sectionThe 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
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