维度配置
<?xml version="1.0" encoding="UTF-8"?> <Schema> <Parameter name="tt" type="Table" /> <Parameter name="Company" type="Table" /> <Parameter name="Fact" type="Table" /> <Dimension name="tt" caption="时间" table="tt" primaryKey="month" type="Time"> <Hierarchy> <Level name="Year" column="month" type="Years" /> <Level name="Month" column="month" type="Months" /> </Hierarchy> </Dimension> <Dimension name="Company" caption="组织" table="Company" primaryKey="FID"> <Hierarchy> <Level name="Company" column="FID" childColumn="fid" parentColumn="fparentId"> <Property name="name" column="fname" /> </Level> </Hierarchy> </Dimension> <Cube name="Fact" caption="收入" table="Fact"> <DimensionUsage name="tt" foreignKey="Ftt" /> <DimensionUsage name="Company" foreignKey="FCompanyID" /> <Measure name="famount" caption="金额" column="famount" aggregator="sum" formatString="#,##0.00"/> </Cube> </Schema>
MDX环比
with member Company.[增长环比] as '((tt.currentmember,measures.famount)-(tt.PrevMember,measures.famount))/(tt.PrevMember,measures.famount)',formatString='0.00%',solve_order=1 select {Company.members,Company.[增长环比]} on rows, tt.members on columns from fact
其它
with member item.[合计] as 'sum(item.levels(0).members)' member measures.[zb] as '(item.currentmember,measures.famount)/(item.[合计],measures.famount)',formatString='0.00%',caption='成本占比%' member measures.[增长率] as '((tt.[2008年6月],measures.famount)-(tt.[2007年6月],measures.famount))/(tt.[2007年6月],measures.famount)',formatString='0.00%',solve_order=2 member measures.[成本占比变化] as '(tt.[2008年6月],measures.[zb])-(tt.[2007年6月],measures.[zb])',formatString='0.00%',solve_order=2 member tt.[同期比] as '0',solve_order=1 select {item.levels(0).members,item.[合计]} on rows, union(tt.members*{measures.members,measures.[zb]}, {tt.[同期比]}*{measures.[增长率],measures.[成本占比变化]}) on columns from Fact
示例