abalakrishnan95 Posted August 30, 2018 Share 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 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