Home
Analytics
Date Formatting
James0816
I am trying to format a data field for a specific date format. I have tried using both the data and text controls with expression builder and neither one is giving what I am looking for.
I pull in the data field into the report and I want the value to look like this:
Date: 31-Oct-2012 12:00 PM
very simple right? not working out for me. If someone could kindly lend some helpful advise, I would greatly appreciate it.
Thanks,
James
Find more posts tagged with
Comments
kclark
After you add the date/time to your report select it and from the property editor > Format Date Time. You can select a format from the drop down or create your own custom format.
Tubal
Make sure your data field that is holding your date is set to a 'Date' type (this may be your problem), and then you can use a custom format, like kclark mentioned.
The formatting would be: d-MMM-yyyy h:mm a
Thanks.
James0816
The custom format is where I'm failing. Where/how do I code that in the expression builder? It doesn't like using format(). Compiles fine just doesn't run.
Also, the "Date: " must be in front of the date value.
I tried using this:
"Date: " + dataSetRow["rdate"]
That works just doesn't give the formatting I'm looking for.
Thanks again
kclark
If you want to use the expression builder instead of setting it in the property editor you could use formatter.format()<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
var mydtf1 = Formatter.format( params["date1"].value, "MMM d, yyyy");
var mydtf2 = Formatter.format( params["date2"].value, "MMM d, yyyy");
"Start Date: "+mydtf1 + " End Date " + mydtf2 + BirtDateTime.diffDay(params["date2"], params["date1"]) +" Days";
</pre>
<br />
<img src='
http://1.bp.blogspot.com/-n9vaSGWFvQk/TV7dReUP3oI/AAAAAAAAAWc/yzoQPZuoqGs/s1600/importdata.png'
alt='Posted Image' class='bbc_img' />
James0816
I ran across that example once before. Tried and I get an error stating that formatter is not defined.
kclark
Did you import java.text?<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage( Packages.java.text );
</pre>
James0816
importPackage( Packages.java.text );
var mydtf1 = Formatter.format(dataSetRow["rdate"].value, "d-MMM-yyyy h:mm a");
"Start Date: "+mydtf1 ;
kclark
If you want a custom date/time format you could go to the format datetime in the property editor like above then select custom from the format as. You can then scroll to the bottom and put your format code in there.
MMMM dd,yy
or
MMM d,y
gauglerb
<blockquote class='ipsBlockquote' data-author="'James0816'" data-cid="111106" data-time="1351777128" data-date="01 November 2012 - 06:38 AM"><p>
importPackage( Packages.java.text );<br />
var mydtf1 = Formatter.format(dataSetRow["rdate"].value, "d-MMM-yyyy h:mm a"); <br />
"Start Date: "+mydtf1 ;<br /></p></blockquote>
<br />
importPackage did not help me, also having this problem.<br />
I recently started using 4.2.2 whereas I never had this problem in 4.2.0. I get a null pointer on Formatter.format<br />
<br />
<br />
I am doing the same format function that worked properly in 4.2.0<br />
(int, "000") as computed column type String.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
Caused by: java.lang.NullPointerException
at org.eclipse.birt.core.script.function.bre.Formatter.getNumberFormatter(Formatter.java:100)
at org.eclipse.birt.core.script.function.bre.Formatter$Function_Format.execute(Formatter.java:138)
at org.eclipse.birt.core.script.function.bre.Formatter.execute(Formatter.java:83)
at org.eclipse.birt.core.script.functionservice.impl.ScriptFunction.execute(ScriptFunction.ja...
</pre>
Tubal
Do you have a sample of the data you are trying to format and the format string you are using?<br />
<br />
<blockquote class='ipsBlockquote' data-author="'gauglerb'" data-cid="116898" data-time="1368808735" data-date="17 May 2013 - 09:38 AM"><p>
importPackage did not help me, also having this problem.<br />
I recently started using 4.2.2 whereas I never had this problem in 4.2.0. I get a null pointer on Formatter.format<br />
<br />
<br />
I am doing the same format function that worked properly in 4.2.0<br />
(int, "000") as computed column type String.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
Caused by: java.lang.NullPointerException
at org.eclipse.birt.core.script.function.bre.Formatter.getNumberFormatter(Formatter.java:100)
at org.eclipse.birt.core.script.function.bre.Formatter$Function_Format.execute(Formatter.java:138)
at org.eclipse.birt.core.script.function.bre.Formatter.execute(Formatter.java:83)
at org.eclipse.birt.core.script.functionservice.impl.ScriptFunction.execute(ScriptFunction.ja...
</pre></p></blockquote>
gauglerb
<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="116900" data-time="1368810237" data-date="17 May 2013 - 10:03 AM"><p>
Do you have a sample of the data you are trying to format and the format string you are using?<br /></p></blockquote>
<br />
Formatter.format(row["CERT_ROST"], "000");<br />
<br />
where the value of CERT_ROST is COALESCE(col, 0) as CERT_ROST to ensure it is coming from the DB as an integer without null values possible.<br />
<br />
<br />
I recently got a new computer, and I'm trying to get up and running using my previous report objects. I got 4.2.2 whereas these objects were made in 4.2.0. I'm going to switch back to the all in one 4.2.0 and see if it resolves my issues, unless someone knows whats going on?<br />
<br />
EDIT: I've reverted back to 4.2.0 and still same error. Must have something to do with my new computer. Weird though. <br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
data.engine.CompCol.FailRetrieveValueComputedColumn ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Fail to compute value for computed column "NUMBER_FORMATTED".
A BIRT exception occurred. See next exception for more information.
There are errors evaluating script "Formatter.format(row["CERT_ROST"], "000")":
null (Element ID:2382)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1237)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1216)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:96)
at org.eclipse.birt.report.engine.executor.GridItemExecutor.execute(GridItemExecutor.java:65)
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:180)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:77)
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:937)
at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:973)
at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(BirtGetPageAllActionHandler.java:131)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(BirtDocumentProcessor.java:183)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)
at sun.reflect.GeneratedMethodAccessor79.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:755)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180)
at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:128)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:384)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:598)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:486)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1065)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:413)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:192)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:999)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:111)
at org.eclipse.jetty.server.Server.handle(Server.java:350)
at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:454)
at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:900)
at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:954)
at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:851)
at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:230)
at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:77)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:606)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:46)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:603)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:538)
at java.lang.Thread.run(Unknown Source)
Caused by: org.eclipse.birt.data.engine.core.DataException: Fail to compute value for computed column "NUMBER_FORMATTED".
A BIRT exception occurred. See next exception for more information.
There are errors evaluating script "Formatter.format(row["CERT_ROST"], "000")":
null
at org.eclipse.birt.data.engine.impl.ComputedColumnHelper$ComputedColumnHelperInstance.process(ComputedColumnHelper.java:532)
at org.eclipse.birt.data.engine.impl.ComputedColumnHelper.process(ComputedColumnHelper.java:137)
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.processFetchEvent(RowResultSet.java:160)
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.doNext(RowResultSet.java:121)
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.next(RowResultSet.java:91)
at org.eclipse.birt.data.engine.executor.cache.ExpandableRowResultSet.next(ExpandableRowResultSet.java:63)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.populateData(SmartCacheHelper.java:316)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.initInstance(SmartCacheHelper.java:285)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.initOdaResult(SmartCacheHelper.java:154)
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.getResultSetCache(SmartCacheHelper.java:79)
at org.eclipse.birt.data.engine.executor.cache.SmartCache.<init>(SmartCache.java:57)
at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.populateOdiResultSet(PassUtil.java:99)
at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.pass(PassUtil.java:62)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.doSinglePass(PassManager.java:170)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.prepareDataSetResultSet(PassManager.java:94)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.pass(PassManager.java:134)
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateResultSet(PassManager.java:74)
at org.eclipse.birt.data.engine.executor.transform.ResultSetPopulator.populateResultSet(ResultSetPopulator.java:198)
at org.eclipse.birt.data.engine.executor.transform.CachedResultSet.<init>(CachedResultSet.java:97)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:1027)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:428)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1142)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:177)
at org.eclipse.birt.report.engine.data.dte.QueryResultSet.<init>(QueryResultSet.java:98)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:168)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:267)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1939)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)
... 66 more
Caused by: org.eclipse.birt.data.engine.core.DataException: A BIRT exception occurred. See next exception for more information.
There are errors evaluating script "Formatter.format(row["CERT_ROST"], "000")":
null
at org.eclipse.birt.data.engine.core.DataException.wrap(DataException.java:123)
at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evalExpr(ScriptEvalUtil.java:946)
at org.eclipse.birt.data.engine.impl.ComputedColumnHelper$ComputedColumnHelperInstance.process(ComputedColumnHelper.java:493)
... 94 more
Caused by: org.eclipse.birt.core.exception.CoreException: There are errors evaluating script "Formatter.format(row["CERT_ROST"], "000")":
null
at org.eclipse.birt.report.engine.javascript.JavascriptEngine.evaluate(JavascriptEngine.java:295)
at org.eclipse.birt.core.script.ScriptContext.evaluate(ScriptContext.java:154)
at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evalExpr(ScriptEvalUtil.java:918)
... 95 more
Caused by: java.lang.NullPointerException
at org.eclipse.birt.core.script.function.bre.Formatter.getNumberFormatter(Formatter.java:100)
at org.eclipse.birt.core.script.function.bre.Formatter$Function_Format.execute(Formatter.java:138)
at org.eclipse.birt.core.script.function.bre.Formatter.execute(Formatter.java:83)
at org.eclipse.birt.core.script.functionservice.impl.ScriptFunction.execute(ScriptFunction.java:146)
at org.eclipse.birt.core.script.functionservice.impl.CategoryWrapper$1.call(CategoryWrapper.java:67)
at org.mozilla.javascript.optimizer.OptRuntime.call2(OptRuntime.java:76)
at org.mozilla.javascript.gen.c147._c0(unnamed script:0)
at org.mozilla.javascript.gen.c147.call(unnamed script)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065)
at org.mozilla.javascript.gen.c147.call(unnamed script)
at org.mozilla.javascript.gen.c147.exec(unnamed script)
at org.eclipse.birt.report.engine.javascript.JavascriptEngine.evaluate(JavascriptEngine.java:290)
... 97 more
</pre>
Tubal
I get the same error.<br />
<br />
If all you're doing is converting an integer to a string, you wouldn't need to use a formatter though.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>row["CERT_ROST"] + '';</pre> would convert it to a string for you.<br />
<br />
You could also set your datatype to a String in your data element or your computed column.
gauglerb
<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="116905" data-time="1368814467" data-date="17 May 2013 - 11:14 AM"><p>
I get the same error.<br />
<br />
If all you're doing is converting an integer to a string, you wouldn't need to use a formatter though.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>row["CERT_ROST"] + '';</pre> would convert it to a string for you.<br />
<br />
You could also set your datatype to a String in your data element or your computed column.<br /></p></blockquote>
<br />
The intention was to convert to string with the leading 0's.<br />
12 = 012<br />
123 = 123<br />
1 = 001<br />
<br />
I suppose i could do a lot more logic, to obtain the same result without Formatter, but I'd really like to know why it's no longer functioning. I clearly didn't do something on my new computer, because I downloaded the same exact all-in-one I was using on my old computer, and using the same report objects that used to work. Hmm....
Tubal
<blockquote class='ipsBlockquote' data-author="'gauglerb'" data-cid="116906" data-time="1368814737" data-date="17 May 2013 - 11:18 AM"><p>
The intention was to convert to string with the leading 0's.<br />
12 = 012<br />
123 = 123<br />
1 = 001<br />
<br />
I suppose i could do a lot more logic, to obtain the same result without Formatter, but I'd really like to know why it's no longer functioning. I clearly didn't do something on my new computer, because I downloaded the same exact all-in-one I was using on my old computer, and using the same report objects that used to work. Hmm....<br /></p></blockquote>
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>var n = "000" + row["CERT_ROST"];
String(n).slice(-3);</pre>
<br />
Would do the same thing (assuming you always wanted a 3 digit number)
gauglerb
I figured it out. I don't know in what version this changed or what you need to do to use the function the old way:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>Formatter.format(val, "pattern");</pre>
<br />
but using the latest all-in-one BIRT package, the following works:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
var formatter = new Packages.org.eclipse.birt.core.format.NumberFormatter("pattern");
formatter.format(val);
</pre>
