MDX计算方法(百分比……)
来源:互联网 发布:linux snmptrap用法 编辑:程序博客网 时间:2024/05/17 02:59
本文介绍了一些经常编写的MDX语句的写法。以下MDX语句可以在SSAS的示例库:Adventure Works中运行。
例子模型
以下的MDX中用到的Hierarchy如下:
百分比
1)某个子项占总体的百分比。比如:每种Product的销售额占所有Product销售额的百分比。
例子模型
以下的MDX中用到的Hierarchy如下:
百分比
1)某个子项占总体的百分比。比如:每种Product的销售额占所有Product销售额的百分比。
WITH MEMBER [Measures].[Sale Amount Ratio] AS
'[Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount], [Product].[Product Categories].[All])' , FORMAT_STRING = '0.00%'
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Sale Amount Ratio]} ON 0,
NON EMPTY [Product].[Product Categories].[Product Name].Members ON 1
FROM [Adventure Works]
'[Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount], [Product].[Product Categories].[All])' , FORMAT_STRING = '0.00%'
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Sale Amount Ratio]} ON 0,
NON EMPTY [Product].[Product Categories].[Product Name].Members ON 1
FROM [Adventure Works]
2)某个子项占其父项的百分比。比如:每种Product的销售额占其所属的SubCategory销售额的百分比。
WITH MEMBER [Measures].[Sale Amount Ratio] AS
'[Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount], [Product].[Product Categories].CurrentMember.Parent)'
, FORMAT_STRING = '0.00%'
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Sale Amount Ratio]} ON 0,
NON EMPTY CROSSJOIN([Product].[Subcategory].[Subcategory].Members,
[Product].[Product Categories].[Product Name].Members) ON 1
FROM [Adventure Works]
'[Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount], [Product].[Product Categories].CurrentMember.Parent)'
, FORMAT_STRING = '0.00%'
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Sale Amount Ratio]} ON 0,
NON EMPTY CROSSJOIN([Product].[Subcategory].[Subcategory].Members,
[Product].[Product Categories].[Product Name].Members) ON 1
FROM [Adventure Works]
3)某个子项占其祖先的百分比。比如:每种Product的销售额占其所属的Category销售额的百分比。
WITH MEMBER [Measures].[Sale Amount Ratio] AS
'[Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount],
ANCESTOR([Product].[Product Categories].CurrentMember, [Product].[Product Categories].[Category]))'
, FORMAT_STRING = '0.00%'
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Sale Amount Ratio]} ON 0,
NON EMPTY CROSSJOIN([Product].[Category].[Category].Members, [Product].[Product Categories].[Product Name].Members) ON 1
FROM [Adventure Works]
'[Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount],
ANCESTOR([Product].[Product Categories].CurrentMember, [Product].[Product Categories].[Category]))'
, FORMAT_STRING = '0.00%'
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Sale Amount Ratio]} ON 0,
NON EMPTY CROSSJOIN([Product].[Category].[Category].Members, [Product].[Product Categories].[Product Name].Members) ON 1
FROM [Adventure Works]
分配、分摊数量
1)根据一个Measure值来分配数量。比如:按照每种Product占总体的销售额多少来分摊成本。
WITH MEMBER [Measures].[Product Cost] AS
'([Measures].[Internet Total Product Cost], [Product].[Product Categories].[All])*
[Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount], [Product].[Product Categories].[All])'
, FORMAT_STRING = '0.00'
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Product Cost]} ON 0,
NON EMPTY [Product].[Product Categories].[Product Name].Members ON 1
FROM [Adventure Works]
'([Measures].[Internet Total Product Cost], [Product].[Product Categories].[All])*
[Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount], [Product].[Product Categories].[All])'
, FORMAT_STRING = '0.00'
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Product Cost]} ON 0,
NON EMPTY [Product].[Product Categories].[Product Name].Members ON 1
FROM [Adventure Works]
2)根据一个Hierarchy来分配数量。比如:在Product Hierarchy中计算每种Category的成本的时候,可以根据每种Category下有多少个产品来进行分配。
WITH MEMBER [Measures].[Product Cost] AS
'([Measures].[Internet Total Product Cost], [Product].[Product Categories].[All])/
Count(
Descendants (
[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].[Product Name],
SELF
),
INCLUDEEMPTY
)'
, FORMAT_STRING = '0.00'
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Product Cost]} ON 0,
NON EMPTY [Product].[Product Categories].[Category].Members ON 1
FROM [Adventure Works]
'([Measures].[Internet Total Product Cost], [Product].[Product Categories].[All])/
Count(
Descendants (
[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].[Product Name],
SELF
),
INCLUDEEMPTY
)'
, FORMAT_STRING = '0.00'
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Product Cost]} ON 0,
NON EMPTY [Product].[Product Categories].[Category].Members ON 1
FROM [Adventure Works]
平均值
1)简单平均值。比如:计算一个月中每天平均的销售额是多少。
WITH MEMBER Measures.[Avg Gross Profit Margin] AS
[Measures].[Internet Sales Amount]/
COUNT(Descendants([Ship Date].[Fiscal].CurrentMember, [Ship Date].[Fiscal].[Date]), INCLUDEEMPTY)
SELECT
{[Measures].[Internet Sales Amount], Measures.[Avg Gross Profit Margin]} ON COLUMNS,
[Ship Date].[Fiscal].[month].Members ON ROWS
FROM [Adventure Works]
[Measures].[Internet Sales Amount]/
COUNT(Descendants([Ship Date].[Fiscal].CurrentMember, [Ship Date].[Fiscal].[Date]), INCLUDEEMPTY)
SELECT
{[Measures].[Internet Sales Amount], Measures.[Avg Gross Profit Margin]} ON COLUMNS,
[Ship Date].[Fiscal].[month].Members ON ROWS
FROM [Adventure Works]
2)加权平均值。没有想到好的例子。
基于时间的计算
1)同比和环比。比如:今年每月的销售额和去年同期相比的变化
这里要补充的是,在同比MDX中,采用COUSIN或ParallelPeriod都可以,但是采用ParallelPeriod更好一些。
2)累计到当前的统计。比如:得到一年中每一个月的累计销售额。
WITH MEMBER Measures.[Additive Internet Sales Amount] AS
SUM(
PeriodsToDate([Ship Date].[Fiscal].[Fiscal Year],[Ship Date].[Fiscal].CurrentMember),
[Measures].[Internet Sales Amount]
)
SELECT
{[Measures].[Internet Sales Amount], Measures.[Additive Internet Sales Amount]} ON COLUMNS,
[Ship Date].[Fiscal].[month].Members ON ROWS
FROM [Adventure Works]
SUM(
PeriodsToDate([Ship Date].[Fiscal].[Fiscal Year],[Ship Date].[Fiscal].CurrentMember),
[Measures].[Internet Sales Amount]
)
SELECT
{[Measures].[Internet Sales Amount], Measures.[Additive Internet Sales Amount]} ON COLUMNS,
[Ship Date].[Fiscal].[month].Members ON ROWS
FROM [Adventure Works]
3)移动平均值。比如:计算一种Category过去三个月的平均销售额合计。
WITH MEMBER Measures.[Average Internet Sales Amount] AS
AVG(LastPeriods(3, [Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], Measures.[Average Internet Sales Amount]} ON COLUMNS,
NON EMPTY ([Product].[Product Categories].[Category].Members,
DESCENDANTS([Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Month], SELF)
) ON ROWS
FROM [Adventure Works]
AVG(LastPeriods(3, [Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], Measures.[Average Internet Sales Amount]} ON COLUMNS,
NON EMPTY ([Product].[Product Categories].[Category].Members,
DESCENDANTS([Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Month], SELF)
) ON ROWS
FROM [Adventure Works]
- MDX计算方法(百分比……)
- MDX常见计算方法(百分比/分配/平均值/基于时间的计算)
- MDX常见计算方法(百分比/分配/平均值/基于时间的计算)
- MDX中常见的计算方法
- css中的百分比计算方法
- mondrian中用mdx计算父级百分比和整体百分比
- BB Gap Report的计算方法以及有用的MDX查询表达式(对于Property,Aspect等不同Dimension的过滤)
- Android颜色值(#AARRGGBB)透明度百分比和十六进制对应关系以及计算方法
- 我的Android进阶之旅------>Android颜色值(#AARRGGBB)透明度百分比和十六进制对应关系以及计算方法
- 我的Android进阶之旅------>Android颜色值(#AARRGGBB)透明度百分比和十六进制对应关系以及计算方法
- 【农历】计算方法--- (阳历转阴历)…
- MDX的上下文(Context)
- MDX的上下文(Context)
- 另类MDX学习(转)
- MDX解决方案读书笔记(一)
- MDX查询基础(一)
- Matlab中的waitbar(进度条)的应用……(显示程序运行的百分比)
- newton(计算方法)
- Sql Server备份与还原经典文档
- MDX集合运算
- 浅谈游戏辅助程序的制作
- Struts 学习笔记之ActionForm
- Ajax和JavaScript UI快速开发的思索
- MDX计算方法(百分比……)
- 类的初始化顺序
- WebServer.java 用JAVA编写Web服务器
- VC中自定义字体CreateFont
- Java流控制文件上传!!!
- 如何将openfire的服务设为启动的服务?
- js屏蔽退格返回历史记录
- 【第一章 Design】 5 事物支持
- Apache2.2.x+tomcat单IP多域名多站点的虚拟主机配置详解(win2003环境)