mike.conner

(Sorry, the code block didn't preserve the formatting on paste).

Nikhilesh, As I said, I'm using Jaspersoft Studio. I gather it is a bit different. If you mean the field of the query (here, FINISHED_HOUR), there doesn't appear to be a format string for that, according to http://jasperreports.sourceforge.net/xsd/jasperreport.xsd. (unless maybe it is added as a property?). If you mean the expression used for the category, it is currently: DATEFORMAT($F{FINISHED_HOUR}, "yyyyMMdd HH") I tried using simply $F{FINISHED_HOUR}, and adjust the date time label formats for the axis (Chart Properties,Chart>Format>Date Time Label Formats for ... axis), but I still get yyyy-MM-dd HH:mm:ss.S showing up on the axis.

Per Nikhilesh request, here is an the jrxml: <?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version last--><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="CompletedJobs" pageWidth="480" pageHeight="200" columnWidth="480" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="48c17d88-1d96-44da-b9d8-faa5376e00ce"> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="TMSampleReportDB"/> <property name="com.jaspersoft.studio.unit." value="pixel"/> <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/> <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/> <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/"/> <property name="ireport.jasperserver.report.resource" value="/organizations/TM_Organization/Reports/CompletedJobs_files/main_jrxml"/> <property name="ireport.jasperserver.reportUnit" value="/organizations/TM_Organization/Reports/CompletedJobs"/> <parameter name="Jobs_Start_Date" class="java.sql.Timestamp"> <defaultValueExpression><![CDATA[new java.sql.Timestamp(new SimpleDateFormat("yyyyMMdd HH:mm").parse("20140721 08:51").getTime())]]></defaultValueExpression> </parameter> <parameter name="Jobs_End_Date" class="java.sql.Timestamp"> <defaultValueExpression><![CDATA[new java.sql.Timestamp(new SimpleDateFormat("yyyyMMdd HH:mm").parse("20140721 14:51").getTime())]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[ -- lots of sql CTEs omitted, leading up to this:job_count_with_code as (select rp.report_hour as FINISHED_HOUR,-- jc.JOB_TYPE, c.JOB_CODE,-- jc.JOB_STATE, CASE jc.JOB_STATE when 0 then 'UNKNOWN' when 1 then 'PENDING' when 2 then 'ACTIVE' when 3 then 'COMPLETED' when 4 then 'FAILED' when 5 then 'CANCELED' when 5 then 'HOLD' else null end as JOB_STATE_CODE, jc.MGR_ID, jc.JOB_COUNT from report_hour rp left join job_count jc on rp.report_hour = jc.FINISHED_HOUR left join job_code c on jc.JOB_TYPE = c.job_type)select * from job_count_with_codeorder by FINISHED_HOUR, JOB_STATE_CODE, JOB_CODE, MGR_ID]]> </queryString> <field name="FINISHED_HOUR" class="java.sql.Timestamp"/> <field name="JOB_CODE" class="java.lang.String"/> <field name="JOB_STATE_CODE" class="java.lang.String"/> <field name="MGR_ID" class="java.lang.String"/> <field name="JOB_COUNT" class="java.lang.Integer"/> <summary> <band height="200" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <componentElement> <reportElement x="0" y="30" width="480" height="170" uuid="83abc378-403b-46c1-a732-71a934280d2b"> <property name="local_mesure_unitwidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.width" value="px"/> </reportElement> <hc:chart xmlns:hc="http://jaspersoft.com/highcharts" xsi:schemaLocation="http://jaspersoft.com/highcharts http://jaspersoft.com/schema/highcharts.xsd" type="StackedBar"> <hc:chartSetting name="default"> <hc:chartProperty name="_showDataPoints"> <hc:propertyExpression><![CDATA[boolean.TRUE]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="chart.borderRadius"> <hc:propertyExpression><![CDATA[new Integer(0)]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="chart.borderWidth"> <hc:propertyExpression><![CDATA[new Integer(0)]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="chart.reflow"> <hc:propertyExpression><![CDATA[boolean.TRUE]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="chart.zoomType"> <hc:propertyExpression><![CDATA["xy"]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="colors"> <hc:propertyExpression><![CDATA[java.util.Arrays.asList("#80699B","#89A54E","#FF0000","#3D96AE","#DB843D","#92A8CD","#A47D7C","#B5CA92","#4572A7")]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="credits.enabled"> <hc:propertyExpression><![CDATA[false]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="credits.href"> <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="credits.text"> <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="legend.enabled"> <hc:propertyExpression><![CDATA[boolean.TRUE]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="plotOptions.column.marker.enabled"> <hc:propertyExpression><![CDATA[boolean.TRUE]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="plotOptions.series.showCheckBox"> <hc:propertyExpression><![CDATA[boolean.TRUE]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="title.text"> <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="xAxis.labels.rotation"> <hc:propertyExpression><![CDATA[new Integer(0)]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="xAxis.labels.step"> <hc:propertyExpression><![CDATA[new Integer(1)]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="yAxis.labels.step"> <hc:propertyExpression><![CDATA[new Integer(2)]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="yAxis.title.text"> <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression> </hc:chartProperty> <hc:chartProperty name="plotOptions.bar.marker.enabled"> <hc:propertyExpression><![CDATA[boolean.TRUE]]></hc:propertyExpression> </hc:chartProperty> </hc:chartSetting> <multiAxisData> <multiAxisDataset/> <dataAxis axis="Rows"> <axisLevel name="Hour"> <labelExpression><![CDATA["Level Label expression"]]></labelExpression> <axisLevelBucket class="java.sql.Timestamp"> <bucketExpression><![CDATA[DATEFORMAT($F{FINISHED_HOUR}, "yyyyMMdd HH")]]></bucketExpression> <comparatorExpression><![CDATA[]]></comparatorExpression> <bucketProperty name="HOUR"><![CDATA[$F{FINISHED_HOUR}]]></bucketProperty> </axisLevelBucket> </axisLevel> </dataAxis> <dataAxis axis="Columns"> <axisLevel name="State"> <labelExpression><![CDATA[]]></labelExpression> <axisLevelBucket class="java.lang.String"> <bucketExpression><![CDATA[$F{JOB_STATE_CODE}]]></bucketExpression> <bucketProperty name="JOB_STATE_CODE"><![CDATA[$F{JOB_STATE_CODE}]]></bucketProperty> </axisLevelBucket> </axisLevel> </dataAxis> <multiAxisMeasure name="Jobs" class="java.lang.Integer" calculation="Sum"> <labelExpression><![CDATA[""]]></labelExpression> <valueExpression><![CDATA[$F{JOB_COUNT}]]></valueExpression> </multiAxisMeasure> </multiAxisData> <hc:series name="Jobs"> <hc:contributor name="SeriesItemHyperlink"> <hc:contributorProperty name="hyperlinkType" valueType="Constant" value="ReportExecution"/> <hc:contributorProperty name="hyperlinkTarget" valueType="Constant" value="Parent"/> <hc:contributorProperty name="hyperlinkReference" valueType="Constant" value="http://cnn.com"/> <hc:contributorProperty name="_report" valueType="Constant" value="/organizations/TM_Organization/Reports/JobsDetail"/> <hc:contributorProperty name="JOB_STATE_CODE" valueType="Bucket" value="State.JOB_STATE_CODE"/> <hc:contributorProperty name="JOB_START_DATETIME" valueType="Bucket" value="Hour.HOUR"/> </hc:contributor> </hc:series> </hc:chart> </componentElement> <textField> <reportElement x="0" y="7" width="480" height="20" uuid="8de966ad-e893-41f5-8ed2-05a0b06e4994"/> <textElement textAlignment="Center"> <font size="14" isBold="true"/> </textElement> <textFieldExpression><![CDATA["Finished Jobs"]]></textFieldExpression> </textField> </band> </summary></jasperReport>[/code]

We are evaluating Jaspersoft Studio to determine how we could use it to create some dashboards for our product. As part of that, I'm trying put together a simple report that shows activity/unit of time. For example, how many jobs were completed per hour for the last 12 hours, and within that, which completed normally, failed, were cancelled. Using the HTML5 Charts, I can create a Stacked Bar, with a Category Level of Hour, Series Level of Job State (completed, failed, ...), and Measure of Job Count. This more or less works, but in order to get the categories correct, I either need to use the full timestamp, or format it as a string to minimum of "yyyyMMdd HH" to guarantee the categories (buckets) are both unique and in the right order. So... Is there a way with HTML5 Stacked Bar to use the Timestamp for the Category Level, but display it differently (e.g. dd HH)? I see that there is a comparator expression, but I couldn't find anything on how that is intended to be used in this context. Moreover, is there a better chart to be using to visualize this data?