ReportServer Forum

Patryx

Member

Registered: 2019-03-25

Presentation of Postgresql column INTERVAL in ReportServer

Hi,
I chose a different language than english in gui.
I want to present INTERVAL column (Postgresql) - here named: "czas trwania".
In ReportServer is type as OTHER. I get always such a format in English (hours, days etc)

Can I at least get this value in chosen language? Maybe can O influence on format for presentation in this case?

Last edited by Patryx (2019-06-07 09:53:45)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi Patryx,

what do you get when you make a simple select * from table from an external tool e.g. pgadmin ?

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi Eduardo,
Result of question SELECT * FROM my_table (column interval is "czas trwania"):

Last edited by Patryx (2019-06-07 10:18:58)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi Patryx,

I raised RS-3490 for this. We will take a look.

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi,
Ok, It can be presented as you have but with current language, please (so hours, mins etc translated into current locale).
If not maybe format as postgresql used is a solution (it doesn't matter of language - no key words )

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi Patryx,

postgres intervals are not being translated into any language. ReportServer shows exactly what it gets. In my case, if I perform a select * from my_table in pgadmin, I get exactly what I see in reportserver. Maybe you configured your pgadmin to only show the values?
Anyway, reportserver does not try to change/translate anything in this case.

For formatting, you can use postgres specific functions, e.g.:

select *, to_char(interval, 'HH24:MI:SS') as converted_to_text from test_interval

or similar.

Some more information here:
https://www.postgresql.org/docs/9.1/fun … tting.html
https://stackoverflow.com/questions/309 … f-interval

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi,
Changing query helps but I have also a case where I use aggregation as SUM on interval column, so I cannot change it on query because of SUM. That's why I ask about format of presentation.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi Patryx,

as I mentioned, we present interval values as they arrive from the database. We don't change anything here, they are shown exactly as they arrive reportserver.

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi,
I understand I don't have possibility to change the format.
Thank you for your explaination.

Patryx

Member

Registered: 2019-03-25

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi,
My colleague found a solution how to format result of SUM of interval fields in Postgresql.
Maybe somebody will need it in future so I will describe it.
We noticed that by ${value} we have PGInterval object for Postgresql database:
https://jdbc.postgresql.org/development … erval.html.
Based on it we found a way to influence on the format.
Steps:
1. In Configure list tab, click button Edit format.
2. Choose Format: Template and as template we are possible to write, for example:

${value.getDays()==0&&value.getHours()<24?"":(value.getDays()+(value.getHours()/24).intValue()).toString().concat("d ")}${"".format("%02d", value.getHours()%24)}:${"".format("%02d", value.getMinutes())}:${"".format("%02d", value.getSeconds().intValue())}

Very briefly, this one calculate sum of interval in Postgresql to present it as days and time (in our case we know there won't be months nor years).
As example:
Old format:     

0 years 0 mons 0 days 115 hours 24 mins 36.390416 secs

New format:

4d 19:24:36

I have two questions to administrators:
1. As ${value} I can read object of current column. Is there any possibility to use in template object of different column from my report?
2. In our example when we wanted to use function format of string we were forced to use "".format [ ${"".format("%02d", value.getMinutes())} ] because String class wasn't visible, so we couldn't use static method "format" of String. Is there any way to write sth like String.format(...) in column template?

Last edited by Patryx (2019-06-14 11:18:12)

Patryx

Member

Registered: 2019-03-25

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi,
Unfortunately I found a problem with chars <> and &. When I save such a variant with such a format, it cause a problem with this caracter because there are changed.
When after saving variant I open once again Preview I get always a windows with error:

Error
Error parsing '${value.getDays()==0&&value.getHours()<24?"":(value.getDays()+(value.getHours()/24).intValue()).toString().concat("d ")}${"".format("%02d", value.getHours()%24)}:${"".format("%02d", value.getMinutes())}:${"".format("%02d", value.getSeconds().intValue())}': lexical error at position 20, encountered invalid character '&', expected expression token

de.odysseus.el.tree.TreeBuilderException: Error parsing &#39;${value.getDays()==0&amp;amp;&amp;amp;value.getHours()&amp;lt;24?&quot;&quot;:(value.getDays()+(value.getHours()/24).intValue()).toString().concat(&quot;d &quot;)}${&quot;&quot;.format(&quot;%02d&quot;, value.getHours()%24)}:${&quot;&quot;.format(&quot;%02d&quot;, value.getMinutes())}:${&quot;&quot;.format(&quot;%02d&quot;, value.getSeconds().intValue())}&#39;: lexical error at position 20, encountered invalid character &#39;&amp;&#39;, expected expression token
<br>	at de.odysseus.el.tree.impl.Builder.build(Builder.java:108)
<br>	at de.odysseus.el.tree.TreeStore.get(TreeStore.java:61)
<br>	at de.odysseus.el.TreeValueExpression.&lt;init&gt;(TreeValueExpression.java:70)
<br>	at de.odysseus.el.ExpressionFactoryImpl.createValueExpression(ExpressionFactoryImpl.java:445)
<br>	at de.odysseus.el.ExpressionFactoryImpl.createValueExpression(ExpressionFactoryImpl.java:78)
<br>	at net.datenwerke.rs.utils.juel.JuelServiceImpl.evaluate(JuelServiceImpl.java:181)
<br>	at net.datenwerke.rs.utils.juel.JuelServiceImpl.evaluate(JuelServiceImpl.java:167)
<br>	at net.datenwerke.rs.utils.juel.SimpleJuel.parseAsObject(SimpleJuel.java:36)
<br>	at net.datenwerke.rs.utils.juel.SimpleJuel.parse(SimpleJuel.java:28)
<br>	at net.datenwerke.rs.base.service.reportengines.table.entities.format.ColumnFormatTemplate.format(ColumnFormatTemplate.java:54)
<br>	at net.datenwerke.rs.base.service.reportengines.table.entities.Column$5.format(Column.java:426)
<br>	at net.datenwerke.rs.base.service.reportengines.table.hookers.ExecuteAsTableReportPreviewHooker.adjustResult(ExecuteAsTableReportPreviewHooker.java:156)
<br>	at net.datenwerke.rs.base.service.reportengines.table.hookers.ExecuteAsTableReportPreviewHooker.adjustResult(ExecuteAsTableReportPreviewHooker.java:1)
<br>	at net.datenwerke.rs.core.server.reportexecutor.ReportExecutorRpcServiceImpl.executeAs(ReportExecutorRpcServiceImpl.java:228)
<br>	at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
<br>	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
<br>	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
<br>	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
<br>	at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373)
<br>	at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
<br>	at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
<br>	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
<br>	at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
<br>	at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
<br>	at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89)
<br>	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>	at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
<br>	at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
<br>	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
<br>	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
<br>	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200)
<br>	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
<br>	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
<br>	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
<br>	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
<br>	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678)
<br>	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
<br>	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
<br>	at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:394)
<br>	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
<br>	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:836)
<br>	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1747)
<br>	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
<br>	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
<br>	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
<br>	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
<br>	at java.base/java.lang.Thread.run(Thread.java:834)
<br>

Problem is with saving or reading again such format template. I suppose it is encoding. How can I use < or > and & in template?!?!

Last edited by Patryx (2019-06-14 12:48:23)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi Patryx,

can you post a minimal example of this with some screenshots ?

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi Eduardo,
Unfortunately I can't paste screenshots nowadays because it was over 2 weeks ago and I have different environment. I hope I clearly explained all steps in my post so it would be easy to repeat by your team.
Steps:
1. In Configure list tab, click button Edit format.
2. Choose Format: Template and as template we are possible to write, for example:

${value.getDays()==0&&value.getHours()<24?"":(value.getDays()+(value.getHours()/24).intValue()).toString().concat("d ")}${"".format("%02d", value.getHours()%24)}:${"".format("%02d", value.getMinutes())}:${"".format("%02d", value.getSeconds().intValue())}

Very briefly, this one calculate sum of interval in Postgresql to present it as days and time (in our case we know there won't be months nor years).
As example:
Old format:     

0 years 0 mons 0 days 115 hours 24 mins 36.390416 secs

New format:

4d 19:24:36

When I save such a variant with such a format, it causes a problem.
When after saving variant I open once again Preview I get always a window with error (which I pasted before).

Last edited by Patryx (2019-07-02 14:11:08)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Presentation of Postgresql column INTERVAL in ReportServer

Hi Patryx,

thanks for the description. I raised ticket RS-3543 for this.

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: Presentation of Postgresql column INTERVAL in ReportServer

Good luck