Calculated members under certain conditions
Hi, I'm using a calculated member which seems to fail when put in columns above any other Dimension. Let me be more precise with an example.
I have a cube that gathers information about a tracker system which is based on ticket assignments (and reassignments). The fact table shows for each ticket at least one entry per day and per reassignment during the ticket's lifetime. The 2 native measures are: "Tickets" (which is a distinct count on the ticket number) and "PossessionHours" (which is a sum of the PossessionHours field). Based on this I've created a calculated member to get the average possession time (AvgPossessionHours=PossessionHours/Tickets).
Among the dimensions we have "Area" and "Status". Based on this, the cube works perfect when I'm using the native measures, and also if I use the calculated member as the last element in the column. The problem occurs when I add something after the calculated member. Here we have a couple of example queries:
This query works fine:
with member [Measures].[AvgPossessionHours] as '([Measures].[PossessionHours] / [Measures].[Tickets])', FORMAT_STRING = "#,###.00"
select NON EMPTY Hierarchize(Union(Crossjoin({[Measures].[PossessionHours]}, {[Status].[All Status]}), Crossjoin({[Measures].[PossessionHours]}, [Status].[All Status].Children))) ON COLUMNS,
NON EMPTY {[Area].[All Areas]} ON ROWS
from [Possession]
This other query also works fine:
with member [Measures].[AvgPossessionHours] as '([Measures].[PossessionHours] / [Measures].[Tickets])', FORMAT_STRING = "#,###.00"
select NON EMPTY Crossjoin(Hierarchize(Union({[Status].[All Status]}, [Status].[All Status].Children)), {[Measures].[AvgPossessionHours]}) ON COLUMNS,
NON EMPTY {[Area].[All Areas]} ON ROWS
from [Possession]
But this one throws an exception (stack below):
with member [Measures].[AvgPossessionHours] as '([Measures].[PossessionHours] / [Measures].[Tickets])', FORMAT_STRING = "#,###.00"
select NON EMPTY Hierarchize(Crossjoin({[Measures].[AvgPossessionHours]}, Union({[Status].[All Status]}, [Status].[All Status].Children))) ON COLUMNS,
NON EMPTY {[Area].[All Areas]} ON ROWS
from [Possession]
I wonder if I'm doing something wrong or if this is a known bug. I'm using the Mondrian version that comes with JPivot 1.5.0
I'd be thankful if you could give me an answer on this, so I could know if I should work on the schema and data or just instruct the users to avoid this kind of queries until the problem is solved in a new version.
Thanks in advance,
--Claudio
PS: Here's the exception I get:
javax.servlet.ServletException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute "test" with value "${query01.result.overflowOccured}": An error occurred while getting property "result" from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [with member [Measures].[AvgPossessionHours] as '([Measures].[PossessionHours] / [Measures].[Tickets])', FORMAT_STRING = "#,###.00"
select NON EMPTY Hierarchize(Crossjoin({[Measures].[AvgPossessionHours]}, Union({[Status].[All Status]}, [Status].[All Status].Children))) ON COLUMNS, NON EMPTY {[Area].[All Areas]} ON ROWS from [Possession]])
javax.servlet.ServletException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute "test" with value "${query01.result.overflowOccured}": An error occurred while getting property "result" from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [with member [Measures].[AvgPossessionHours] as '([Measures].[PossessionHours] / [Measures].[Tickets])', FORMAT_STRING = "#,###.00"
select NON EMPTY Crossjoin(Hierarchize(Union({[Status].[All Status]}, [Status].[All Status].Children)), {[Measures].[AvgPossessionHours]}) ON COLUMNS,
NON EMPTY {[Area].[All Areas]} ON ROWS
from [Possession]
])
at org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:825)
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:758)
at org.apache.jsp.testpage_jsp._jspService(testpage_jsp.java:258)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest(RequestFilter.java:139)
at com.tonbeller.wcf.controller.RequestSynchronizer.handleRequest(RequestSynchronizer.java:127)
at com.tonbeller.wcf.controller.RequestFilter.doFilter(RequestFilter.java:263)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)
