Timestamp format in HTML5 Stacked Bar axis

[mike.conner]

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?

[Nikhilesh_1]

Hi Mike,

To resolve your issue, I need to know in what format is the "Field" that you are using as category defined ?  In ireport designer, For date fields, there is a way to specify a format string in the field properties, that might be helpful.

Is it possible to provide jrxml / data to troubleshoot the same?

 

Thanks

Nikhilesh

Helical IT Solution

[mike.conner]

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]

 

[mike.conner]

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.

[mike.conner]

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

[Nikhilesh_1]

Hi.

How to reach out to you. Can you drop me a test mail at nikhilesh (at) helicaltech (.) com

We can speak there