Mdx语句集锦

 

Mdx语句

1、 百分比的格式化

 

with member [时间.时间].[<%=subStr1 %>] as 'Sum(([时间.时间].[全部]<%=subStr4 %> : [时间.时间].[全部]<%=subStr5 %>))'

     member [入库机构.入库库存机构].[合计] as '<%=hjdeptnames %>'

     member [material num.器材编码].[合计] as '[material num.器材编码].[所有器材编码]'

     member [出库机构.出库库存机构].[小计] as '[出库机构.出库库存机构].[所有出库库存机构]'

     member [Measures].[满足率] as '[Measures].[发料数量]/[Measures].[申请数量]',FORMAT_STRING = "#.#%"

select

    NON EMPTY Hierarchize(Crossjoin({[时间.时间].[<%=subStr1 %>]},

           Union(Crossjoin({<%=StrWuzi%>},{[Measures].[申请数量],[Measures].[发料数量], [Measures].[发料单价],[Measures].[发料小计]}),

                Crossjoin({[material num.器材编码].[合计]},{[Measures].[申请小计],[Measures].[发料小计],[Measures].[满足率]})

           )

       ))  ON COLUMNS,

    NON EMPTY  Crossjoin({<%=deptnames %>, [入库机构.入库库存机构].[合计]}, {[出库机构.出库库存机构].[所有出库库存机构].[计划与物资管理部].Children,[出库机构.出库库存机构].[小计]}) ON ROWS

from [nySQerp]

 

当然也可以格式化成其他的。比如：#,###.00，￥#,##0.00等

 

2、 为字段设置颜色

WITH MEMBER [Measures].[Profit] AS

'([Measures].[Store Sales] - [Measures].[Store Cost])',

FORMAT_STRING = Iif([Measures].[Profit] < 100000, '|#|style=green',

'|#|style=red')

SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,

{[Product].CurrentMember.Children} ON ROWS

FROM [Sales]

 

3、 对union的运用

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].[入库数量小计]}

                            ),

                            Crossjoin(

                                   {[时间.时间].[<%=subStr1 +"出库"%>]},

                                   {[Measures].[发料数量], [Measures].[出库数量小计]}

                            )

                     )

              ),

              Crossjoin({[时间.时间].[<%=endTime%>]},{[Measures].[期末结余]})

       )ON COLUMNS,

      

       NON EMPTY Union(

                     Crossjoin(

                            {[库存机构.库存机构].[所有库存机构].Children},{[material num.器材编码].[所有器材编码].Children,[material num.器材编码].[小计]}

                     ),

                     Crossjoin({[库存机构.库存机构].[合计]},{[material num.器材编码].[小计]})

       ) ON ROWS

from [zancun]