Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Total Value wont update after filtered its members

  1. #1
    Join Date
    Jan 2016
    Posts
    8

    Default Total Value wont update after filtered its members

    Hi Support,

    I have faced one issue while using Mondrian cube which is “Total Value” in PivotTable could not be updated depends upon its available members. Please find the replications steps in below.

    I have framed the MDX Query with the below combinations.
    Cube : HR,
    Measure : Org Salary,
    Dimension : Store Type.
    And I have got the exact PivotTable with the below MDX Query.

    SELECT {[Measures].[Org Salary]} dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS , ({{Drilldownlevel({ [Store Type] })}}) dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS FROM [HR]

    Measures
    Store Type Org Salary
    All Store Types $39,431.67
    Deluxe Supermarket $8,511.43
    Gourmet Supermarket $2,872.60
    HeadQuarters $4,449.60
    Mid-Size Grocery $1,120.31
    Small Grocery $930.44
    Supermarket $21,547.28


    In the PivotTable, the “Total Value” is “$39,431.67” which is the summary values of its members.

    Then, I have framed the MDX Query to excluded the members “Small Grocery” and “Supermarket” from the PivotTable.

    select {[Measures].[Org Salary]} DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS,
    {{Hierarchize(Union(Intersect(Except(Descendants([Store Type], 1.0, SELF_AND_BEFORE), {[Store Type].[Small Grocery], [Store Type].[Supermarket]}), DrilldownLevel({[Store Type]})), Except(DrilldownLevel({[Store Type]}), {[Store Type].[Small Grocery], [Store Type].[Supermarket]})))}} DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS
    from [HR]

    Measures
    Store Type Org Salary
    All Store Types $39,431.67
    Deluxe Supermarket $8,511.43
    Gourmet Supermarket $2,872.60
    HeadQuarters $4,449.60
    Mid-Size Grocery $1,120.31


    As I have expected, the selected members have been removed. But, the “Total Value” didn’t updated which was maintained with the same value “$39,431.67”.
    But, the expected values is “$16,953.94”.

    Please suggest me if I have made any mistakes in MDX Query or any other possible solution to fix the problem.

    The screen-shots have been attached for your convenience.

    Thanks in advance.

    Regards,
    Ramesh G.
    Attached Images Attached Images   

  2. #2
    Join Date
    Jan 2013
    Posts
    796

    Default

    The "All" member of a hierarchy rolls up the values of all children, not just those that happen to be projected on an axis. To get the total value of a set of members use the Aggregate() function, something like

    WITH
    SET [storeTypes] as 'Except([Store Type].[Store Type].members, {[Store Type].[Small Grocery], [Store Type].[Supermarket]})'
    MEMBER [Store Type].[Total] as 'Aggregate([storeTypes])'
    select measures.members on 0, { storeTypes, [Store Type].[Total] } on 1 from sales

  3. #3
    Join Date
    Jan 2016
    Posts
    8

    Question

    Hi Mcampbell,
    Thank you for the reply.
    I have tried “Aggregate” what you suggested, it is working fine. But, it need to be modified the entire structure of framing MDX Query what I use currently, to insert “Aggregate” which makes complexity in my development.
    So, I have found another solution which is “VisualTotals”. The MDX Query is,
    SELECT {[Measures].[Org Salary]} dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS , VISUALTOTALS( ({{hierarchize(Union(Intersect(VISUALTOTALS(Except(Descendants([Store Type], 1, SELF_AND_BEFORE) , {[Store Type].[Small Grocery], [Store Type].[Supermarket]})), Drilldownlevel({ [Store Type] })) , Except(Drilldownlevel({ [Store Type] }) , {[Store Type].[Small Grocery], [Store Type].[Supermarket]})))}}) ) dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS FROM [HR]

    I got the appropriate result.
    Measures
    Store Type Org Salary
    -All Store Types $16,953.95
    Deluxe Supermarket $8,511.43
    Gourmet Supermarket $2,872.60
    HeadQuarters $4,449.60
    Mid-Size Grocery $1,120.31

    It is working fine in Attribute Hierarchies but throws exceptions for some User-defined Hierarchies.
    The replications steps are below,
    Cube : HR,
    Measure : Org Salary,
    Dimension : Store
    The dimension store has following members Canada, Mexico and USA. I have written MDX Query to drill the member USA which shows its child members CA, OR and WA.
    SELECT {[Measures].[Org Salary]} dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS , VISUALTOTALS( ({{DrilldownMember(DrilldownLevel({[Store]}),{[Store].[USA]})}}) ) dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS FROM [HR]


    Got exact PivotTable,
    Measures
    Store Org Salary
    -All Stores $39,431.67
    +Canada
    +Mexico
    -USA $39,431.67
    +CA $14,861.50
    +OR $7,848.97
    +WA $16,721.20

    By using the below MDX Query, I have removed the one of the child member “CA” from “USA”.
    SELECT {[Measures].[Org Salary]} dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS , VISUALTOTALS( ({{hierarchize(Union(Intersect(VISUALTOTALS(Except(Descendants([Store], 2, SELF_AND_BEFORE) , {[Store].[USA].[CA]})), DrilldownMember(DrilldownLevel({[Store]}),{[Store].[USA]})) , Except(DrilldownMember(DrilldownLevel({[Store]}),{[Store].[USA]}) , {[Store].[USA].[CA]})))}}) ) dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS FROM [HR]

    Got exact PivotTable with updated “Total Values”.
    Measures
    Store Org Salary
    -All Stores $24,570.17
    +Canada
    +Mexico
    -USA $24,570.17
    +OR $7,848.97
    +WA $16,721.20

    But, I have experienced an exception when performed the same operation with another cube.
    Cube : Sales,
    Measure : Sales Count,
    Dimension : Store.


    The Same MDX Query,
    SELECT {[Measures].[Sales Count]} dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS , VISUALTOTALS( ({{hierarchize(Union(Intersect(VISUALTOTALS(Except(Descendants([Store], 2, SELF_AND_BEFORE) , {[Store].[USA].[CA]})), DrilldownMember(DrilldownLevel({[Store]}),{[Store].[USA]})) , Except(DrilldownMember(DrilldownLevel({[Store]}),{[Store].[USA]}) , {[Store].[USA].[CA]})))}}) ) dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS FROM [Sales]

    I got the below exception.
    JPivot had an error ...
    org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspTagException: 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 [select {[Measures].[Sales Count]} DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS, VisualTotals({{Hierarchize(Union(Intersect(VisualTotals(Except(Descendants([Store], 2.0, SELF_AND_BEFORE), {[Store].[USA].[CA]})), DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]})), Except(DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]}), {[Store].[USA].[CA]})))}}) DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS from [Sales] ])
    org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspTagException: 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 [select {[Measures].[Sales Count]} DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS,
    VisualTotals({{Hierarchize(Union(Intersect(VisualTotals(Except(Descendants([Store], 2.0, SELF_AND_BEFORE), {[Store].[USA].[CA]})), DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]})), Except(DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]}), {[Store].[USA].[CA]})))}}) DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS
    from [Sales]
    ])
    at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:491)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:401)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest(RequestFilter.java:139)
    at com.tonbeller.wcf.controller.RequestFilter.doFilter(RequestFilter.java:264)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: javax.servlet.ServletException: javax.servlet.jsp.JspTagException: 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 [select {[Measures].[Sales Count]} DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS,
    VisualTotals({{Hierarchize(Union(Intersect(VisualTotals(Except(Descendants([Store], 2.0, SELF_AND_BEFORE), {[Store].[USA].[CA]})), DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]})), Except(DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]}), {[Store].[USA].[CA]})))}}) DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS
    from [Sales]
    ])
    at org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:862)
    at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:791)
    at org.apache.jsp.testpage_jsp._jspService(testpage_jsp.java:263)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
    ... 19 more
    Caused by: javax.servlet.jsp.JspTagException: 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 [select {[Measures].[Sales Count]} DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS,
    VisualTotals({{Hierarchize(Union(Intersect(VisualTotals(Except(Descendants([Store], 2.0, SELF_AND_BEFORE), {[Store].[USA].[CA]})), DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]})), Except(DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]}), {[Store].[USA].[CA]})))}}) DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS
    from [Sales]
    ])
    at org.apache.taglibs.standard.tag.el.core.IfTag.condition(IfTag.java:65)
    at javax.servlet.jsp.jstl.core.ConditionalTagSupport.doStartTag(ConditionalTagSupport.java:122)
    at org.apache.jsp.testpage_jsp._jspx_meth_c_005fif_005f1(testpage_jsp.java:1205)
    at org.apache.jsp.testpage_jsp._jspService(testpage_jsp.java:193)
    ... 22 more


    javax.servlet.ServletException: javax.servlet.jsp.JspTagException: 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 [select {[Measures].[Sales Count]} DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS, VisualTotals({{Hierarchize(Union(Intersect(VisualTotals(Except(Descendants([Store], 2.0, SELF_AND_BEFORE), {[Store].[USA].[CA]})), DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]})), Except(DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]}), {[Store].[USA].[CA]})))}}) DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS from [Sales] ])
    javax.servlet.ServletException: javax.servlet.jsp.JspTagException: 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 [select {[Measures].[Sales Count]} DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS,
    VisualTotals({{Hierarchize(Union(Intersect(VisualTotals(Except(Descendants([Store], 2.0, SELF_AND_BEFORE), {[Store].[USA].[CA]})), DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]})), Except(DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]}), {[Store].[USA].[CA]})))}}) DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS
    from [Sales]
    ])
    at org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:862)
    at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:791)
    at org.apache.jsp.testpage_jsp._jspService(testpage_jsp.java:263)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest(RequestFilter.java:139)
    at com.tonbeller.wcf.controller.RequestFilter.doFilter(RequestFilter.java:264)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: javax.servlet.jsp.JspTagException: 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 [select {[Measures].[Sales Count]} DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS,
    VisualTotals({{Hierarchize(Union(Intersect(VisualTotals(Except(Descendants([Store], 2.0, SELF_AND_BEFORE), {[Store].[USA].[CA]})), DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]})), Except(DrilldownMember(DrilldownLevel({[Store]}), {[Store].[USA]}), {[Store].[USA].[CA]})))}}) DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS
    from [Sales]
    ])
    at org.apache.taglibs.standard.tag.el.core.IfTag.condition(IfTag.java:65)
    at javax.servlet.jsp.jstl.core.ConditionalTagSupport.doStartTag(ConditionalTagSupport.java:122)
    at org.apache.jsp.testpage_jsp._jspx_meth_c_005fif_005f1(testpage_jsp.java:1205)
    at org.apache.jsp.testpage_jsp._jspService(testpage_jsp.java:193)
    ... 22 more




    It only happened while filtering child members of any parent member in some of the User-defined hierarchies only. And the exception occurred because of adding VisualTotals.
    For example,
    Store, Time in Sales cube, Geography, Store, Time in Sales Ragged cube etc.….
    Please provide me the possible solutions.

    Regards,
    Ramesh G.

  4. #4
    Join Date
    Jan 2013
    Posts
    796

    Default

    Do you see a stack trace anywhere in your logs that originates in Mondrian? The stack trace you posted is from the servlet.

    The query VisualTotals query you have above runs for me without error on Mondrian 3.12.

  5. #5
    Join Date
    Jan 2016
    Posts
    8

    Default

    Hi mcampbell,

    I am using updated Mondrian version (3.2). And I have found another issue.
    I got the exception “#ERR: mondrian.olap.fun.MondrianEvaluationException: Could not find an aggregator in the current evaluation context” in the cell of “Total Values”. This happened while using “VisualTotals” in MDX Query. Please find the below replication steps.

    Cube : HR,
    Measure : Avg Salary,
    Dimension : Pay Type.

    The mentioned issue happened for all dimensions if I use the correspondent measure. The same issue happened for the measure “Employee Salary” too in the same cube. And I have experienced same issue in each cube for some different measures like “Gewinn-Wachtsum” measures from the cube “Sales”.

    The MDX Query is,

    select {[Measures].[Avg Salary]} DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS,
    VisualTotals({{DrilldownLevel({[Pay Type]})}}) DIMENSION PROPERTIES MEMBER_TYPE, PARENT_UNIQUE_NAME ON ROWS
    from [HR]

    The Result is,

    Measures
    Pay Type Avg Salary
    -All Pay Types #ERR: mondrian.olap.fun.MondrianEvaluationException: Could not find an aggregator in the current evaluation context
    Hourly $40.31
    Monthly $84.16

    Kindly provide us a solution for that and the mentioned one in my prior update.

    Regards,
    Ramesh G.

  6. #6
    Join Date
    Jan 2016
    Posts
    8

    Default

    Hi,

    Can anyone reply my last query?

    Regards.
    Ramesh G.

  7. #7
    Join Date
    Jan 2016
    Posts
    8

    Default

    Hi,

    Can anyone there?

    Regards,
    Ramesh G.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.