Mondrian排序显示

数据库表结构:

CREATE TABLE bi_holdprobability
(
  holdprobability_id integer NOT NULL DEFAULT nextval('bi_holdprobability_serial'::regclass),
  reference_time integer,--参考时间
  reference_concreteTime character varying(100),--参考时间时分秒
  add_user integer,--新增用户
  contrast_time integer,--对比时间
  contrast_concreteTime character varying(100),--对比时间时分秒
  holdprobability_num integer,--留存数
  CONSTRAINT holdprobability_key PRIMARY KEY (holdprobability_id)
)

Schema 填写:

<?xml version="1.0" encoding="UTF-8"?> <!-- APP用户留存率 -->
<Schema name="holdProbability">
<Cube name="holdProbability"><!-- 数据立方的名称 -->
    <Table name="bi_holdprobability"/>
    
    <Dimension name="參考時間" foreignKey="reference_time">
    <Hierarchy hasAll="true" allMemberName="全部" primaryKey="time_id">
        <Table name="bi_timeyhd"/>
        <Level name="年" column="time_year" datatype="String" uniqueMembers="false"/>
<Level name="月" column="time_month" datatype="String" uniqueMembers="false"/>
       </Hierarchy>
    </Dimension>
    
<Dimension name="對比時間" foreignKey="contrast_time">
    <Hierarchy hasAll="true" allMemberName="全部" primaryKey="time_id">
        <Table name="bi_timeyhd"/>
<Level name="月" column="time_month" datatype="String" uniqueMembers="false"/>
       </Hierarchy>
    </Dimension>

    <Measure name="新增用戶數" column="add_user" aggregator="sum" datatype="Numeric" />
   
    <Measure name="留存數" column="holdprobability_num" aggregator="sum" datatype="Numeric"/>
    </Cube>
</Schema>

MDX JSP页面的填写:

<%@ page session="true" contentType="text/html; charset=utf-8" %>
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
<!-- CurrentMember.Parent -->
<jp:mondrianQuery id="query01" 
jdbcDriver="org.postgresql.Driver" 
jdbcUrl="jdbc:postgresql://localhost:5432/PYLake" 
catalogUri="/WEB-INF/queries/app_holdProbability.xml"
    jdbcUser="postgres" jdbcPassword="zhj" connectionPooling="false">
   with 
    member [Measures].[本月用戶數] as '([Measures].[新增用戶數], [對比時間].[全部])', 
    format_string = "|#|"
    
    member [Measures].[留存率] as 
    '([Measures].[留存數] / [Measures].[本月用戶數])', 
    format_string = IIf(((([Measures].[留存數] / [Measures].[本月用戶數]) * 100.0) >= 100.0), 
    "|#.00%|arrow='up'", IIf(((([Measures].[留存數] / [Measures].[本月用戶數]) * 100.0) < 100.0),
    "|#.00%|arrow='down'", "|#0.00%|arrow='none'"))
 
   
    
select {[新增用戶數],[留存數],[Measures].[留存率]} ON columns,
  NON EMPTY{([參考時間].[全部],[對比時間] )} ON rows
from [holdProbability]

</jp:mondrianQuery>


<c:set var="title01" scope="session">APP概況>>用戶留存率</c:set>