mondrian查询语句示例

<jp:mondrianQuery id="query01" jdbcDriver="oracle.jdbc.driver.OracleDriver" jdbcUrl="jdbc:oracle:thin:jbpm/123456@192.168.1.64:1521:orcl" catalogUri="/WEB-INF/queries/nanyang.xml">
with member [时间.时间].[<%=subStr1 %>] as 'Sum(([时间.时间].[全部]<%=subStr4 %> : [时间.时间].[全部]<%=subStr5 %>))'
  member [时间.时间].[<%=subStr1 +"入库"%>] as 'Sum(([时间.时间].[全部]<%=subStr4 %> : [时间.时间].[全部]<%=subStr5 %>))'
  member [时间.时间].[<%=subStr1 +"出库"%>] as 'Sum(([时间.时间].[全部]<%=subStr4 %> : [时间.时间].[全部]<%=subStr5 %>))'
  member [库存机构.库存机构].[合计] as '[库存机构.库存机构].[所有库存机构]'
  member [material num.器材编码].[小计] as '[material num.器材编码].[所有器材编码]'
  member [时间.时间].[<%=beginTime%>] as '[时间.时间].[全部]<%=subStr4 %>'
  member [时间.时间].[<%=endTime%>] as '[时间.时间].[全部]<%=subStr5 %>'
  member [Measures].[期初结余] as '[Measures].[当日结余数量]'
  member [Measures].[期末结余] as '[Measures].[当日结余数量]'
select
 NON EMPTY Union(
  Union(
   Union(
    Crossjoin({[时间.时间].[<%=subStr1 %>]},{[Measures].[单价]}),
    Crossjoin({[时间.时间].[<%=beginTime%>]},{[Measures].[期初结余]})
   ),
   Union(
    Crossjoin(
     {[时间.时间].[<%=subStr1 +"入库"%>]},
     {[Measures].[收料数量], [Measures].[移入数量],[Measures].[直接入库数量], [Measures].[退料(实名制)数量],[Measures].[移出拒收数量],[Measures].[发料拒收数量],[Measures].[入库数量小计]}
    ),
    Crossjoin(
     {[时间.时间].[<%=subStr1 +"出库"%>]},
     {[Measures].[发料数量],[Measures].[移出数量],[Measures].[撤销入库数量],[Measures].[工程领料(实名制)数量],[Measures].[赠品领料(实名制)数量],[Measures].[装机领料(实名制)数量],[Measures].[维护领料(实名制)数量],[Measures].[终端领料(实名制)数量],[Measures].[一般领料(实名制)数量],[Measures].[一般领料(实名制)数量], [Measures].[出库数量小计]}
    )
   )
  ),
  Crossjoin({[时间.时间].[<%=endTime%>]},{[Measures].[期末结余]})
 )ON COLUMNS,
 
 NON EMPTY Union(
   Crossjoin(
    {[库存机构.库存机构].[所有库存机构].Children},{[material num.器材编码].[所有器材编码].Children,[material num.器材编码].[小计]}
   ),
   Crossjoin(
    {[库存机构.库存机构].[合计]},{[material num.器材编码].[小计]}
   )
 ) ON ROWS
from [zancun]

</jp:mondrianQuery>