Crosstab Query/Bar Chart Question

[zerglicious]

I would like to display the following information in a Barchart? Is this possible? What would be the simplest implementation?

 

Code:

UNAUTHORIZED_ITEMS COUNTERFEIT_ITEMS STOLEN_ITEMS LOST_ITEMS EXPIRED_ITEMS RECALLED_ITEMS
------------------ ----------------- ------------ ---------- ------------- --------------
               172                 0            0          0             0              0

[zerglicious]

OK, I managed to figure this one out on my own but I thought I would list the solution I found in case someone else has the same issue. If you've done most of the work in your query then getting the chart to work isn't too difficult. You don't even need to do group or variable calculations or anything -- you just make categoryExpressions and valueExpressions for the bar chart part of the same seriesExpression. It is a little crude but this seems to work fine.

 

Code:

<?xml version="1.0" encoding="UTF-8"  ?>
<!-- Created with iReport - A designer for JasperReports -->
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport
		 name="counterfeit_items_chart"
		 columnCount="1"
		 printOrder="Vertical"
		 orientation="Portrait"
		 pageWidth="250"
		 pageHeight="200"
		 columnWidth="250"
		 columnSpacing="0"
		 leftMargin="0"
		 rightMargin="0"
		 topMargin="0"
		 bottomMargin="0"
		 whenNoDataType="NoPages"
		 scriptletClass="com.keys.reports.DemoScriptlet"
		 isTitleNewPage="false"
		 isSummaryNewPage="false">
	<property name="ireport.scriptlethandling" value="2" />
	<property name="ireport.encoding" value="UTF-8" />
	<import value="java.util.*" />
	<import value="net.sf.jasperreports.engine.*" />
	<import value="net.sf.jasperreports.engine.data.*" />

	<parameter name="COLUMN_VALUE" isForPrompting="false" class="java.lang.Long"/>
	<queryString><![CDATA[select SUM(DECODE(TRACKING_CODE, 'INF-1002', COUNTER, 0)) UNAUTHORIZED_ITEMS,
    SUM(DECODE(TRACKING_CODE, 'INF-1004', COUNTER, 0)) COUNTERFEIT_ITEMS,    
    SUM(DECODE(TRACKING_CODE, 'INF-1005', COUNTER, 0)) STOLEN_ITEMS,
    SUM(DECODE(TRACKING_CODE, 'INF-1006', COUNTER, 0)) LOST_ITEMS,
    SUM(DECODE(TRACKING_CODE, 'INF-1007', COUNTER, 0)) EXPIRED_ITEMS,
    SUM(DECODE(TRACKING_CODE, 'INF-1008', COUNTER, 0)) RECALLED_ITEMS
from (
    select ih.tracking_code AS TRACKING_CODE, i.key, 1 as COUNTER
    from item_history ih, item i
    where ih.item_id = i.id
    and ih.tracking_code in ('INF-1002', 'INF-1004', 'INF-1005', 'INF-1006', 'INF-1007', 'INF-1008') 
    group by ih.tracking_code, i.key 
    order by ih.tracking_code asc
)]]></queryString>

	<field name="UNAUTHORIZED_ITEMS" class="java.lang.Long"/>
	<field name="COUNTERFEIT_ITEMS" class="java.lang.Long"/>
	<field name="STOLEN_ITEMS" class="java.lang.Long"/>
	<field name="LOST_ITEMS" class="java.lang.Long"/>
	<field name="EXPIRED_ITEMS" class="java.lang.Long"/>
	<field name="RECALLED_ITEMS" class="java.lang.Long"/>

		<background>
			<band height="0"  isSplitAllowed="true" >
			</band>
		</background>
		<title>
			<band height="200"  isSplitAllowed="true" >
				<bar3DChart>
					<chart isShowLegend="false" evaluationTime="Report"  hyperlinkTarget="Self" >
					<reportElement
						x="6"
						y="11"
						width="233"
						height="174"
						key="element-1"/>
					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
					</chart>
					<categoryDataset>
						<dataset resetType="None" >
						</dataset>
						<categorySeries>
							<seriesExpression><![CDATA["ITEM_STATUS"]]></seriesExpression>
							<categoryExpression><![CDATA["UNAUTHORIZED_ITEMS"]]></categoryExpression>
							<valueExpression><![CDATA[$F{UNAUTHORIZED_ITEMS}]]></valueExpression>
				<itemHyperlink >
				</itemHyperlink>
						</categorySeries>
						<categorySeries>
							<seriesExpression><![CDATA["ITEM_STATUS"]]></seriesExpression>
							<categoryExpression><![CDATA["COUNTERFEIT_ITEMS"]]></categoryExpression>
							<valueExpression><![CDATA[$F{COUNTERFEIT_ITEMS}]]></valueExpression>
				<itemHyperlink >
				</itemHyperlink>
						</categorySeries>
						<categorySeries>
							<seriesExpression><![CDATA["ITEM_STATUS"]]></seriesExpression>
							<categoryExpression><![CDATA["STOLEN_ITEMS"]]></categoryExpression>
							<valueExpression><![CDATA[$F{STOLEN_ITEMS}]]></valueExpression>
				<itemHyperlink >
				</itemHyperlink>
						</categorySeries>
						<categorySeries>
							<seriesExpression><![CDATA["ITEM_STATUS"]]></seriesExpression>
							<categoryExpression><![CDATA["LOST_ITEMS"]]></categoryExpression>
							<valueExpression><![CDATA[$F{LOST_ITEMS}]]></valueExpression>
				<itemHyperlink >
				</itemHyperlink>
						</categorySeries>
						<categorySeries>
							<seriesExpression><![CDATA["ITEM_STATUS"]]></seriesExpression>
							<categoryExpression><![CDATA["EXPIRED_ITEMS"]]></categoryExpression>
							<valueExpression><![CDATA[$F{EXPIRED_ITEMS}]]></valueExpression>
				<itemHyperlink >
				</itemHyperlink>
						</categorySeries>
						<categorySeries>
							<seriesExpression><![CDATA["ITEM_STATUS"]]></seriesExpression>
							<categoryExpression><![CDATA["RECALLED_ITEMS"]]></categoryExpression>
							<valueExpression><![CDATA[$F{RECALLED_ITEMS}]]></valueExpression>
				<itemHyperlink >
				</itemHyperlink>
						</categorySeries>
					</categoryDataset>
					<bar3DPlot >
						<plot />
						<categoryAxisFormat>
							<axisFormat >
							</axisFormat>
						</categoryAxisFormat>
						<valueAxisFormat>
							<axisFormat >
							</axisFormat>
						</valueAxisFormat>
					</bar3DPlot>
				</bar3DChart>
			</band>
		</title>
		<pageHeader>
			<band height="0"  isSplitAllowed="true" >
			</band>
		</pageHeader>
		<columnHeader>
			<band height="0"  isSplitAllowed="true" >
			</band>
		</columnHeader>
		<detail>
			<band height="0"  isSplitAllowed="true" >
			</band>
		</detail>
		<columnFooter>
			<band height="0"  isSplitAllowed="true" >
			</band>
		</columnFooter>
		<pageFooter>
			<band height="0"  isSplitAllowed="true" >
			</band>
		</pageFooter>
		<summary>
			<band height="0"  isSplitAllowed="true" >
			</band>
		</summary>
</jasperReport>

Post edited by: zerglicious, at: 2007/10/08 18:41