MDX 几个比率问题

关注

转载最新推荐文章于 2026-06-07 11:35:26 发布 1.1k 阅读 AI 写同款· GEO 优化 ›

用分析服务SSAS解决占比、同比和环比问题本文将介绍用分析服务SSAS如何解决占比、同比和环比三个指标。首先理解一下这三个指标的基本概念：占比是指当前成员的值与该成员父级的值的比率，意思是在总数中所占的比重，比如某水果商店总销售额为14，其中苹果的销售额为2，占比就是14.3％。同比是指在一个期间内的值与以前某个期间的值进行比较，并用以前期间值的百分比来表示，比如将今年的数据与去年的数据比较或将今年八月的数据与去年八月的数据比阅读详情

维度配置

view plaincopy to clipboardprint?
<?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>

<?xml version="1.0" encoding="UTF-8"?>

</Hierarchy>

</Dimension>

<Dimension name="Company" caption="组织" table="Company"

primaryKey="FID">

</Level>

</Hierarchy>

</Dimension>

</Cube>

</Schema>MDX环比

view plaincopy to clipboardprint?
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 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其它

view plaincopy to clipboardprint?
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

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示例

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/redvalley/archive/2008/06/18/2560638.aspx

收录于