MDX随时间推移而没有特定的层次结构 [英] MDX Count over time without specific hierarchy

查看:61
本文介绍了MDX随时间推移而没有特定的层次结构的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

这是从我的问题开始算起时间.

我希望能够在不基于DateTime层次结构的情况下进行计数,但是可以将其作为计算得出的度量,当您向其拖动维度时可以使用.我目前有

I would like to be able to do the count without basing it on a DateTime Hierarchy, but have this as a calculated measure, which works when you drag a dimension against it. I currently have

成员[措施].[承诺总额]为
sum(
[Date Dim].[FY Hierarchy].[Finan Year] .members(0):[Date Dim].[FY Hierarchy] .currentMember
,[措施].[承诺计数])

with member [Measures].[Commitment Total] as
sum(
[Date Dim].[FY Hierarchy].[Fiscal Year].members(0):[Date Dim].[FY Hierarchy].currentMember
, [Measures].[Commitment Count])

然后使用计算:

选择[措施].[承诺总额]为0
,[Date Dim].[FY Hierarchy].[Finan Year] on 1
来自[多维数据集]

select [Measures].[Commitment Total] on 0
, [Date Dim].[FY Hierarchy].[Fiscal Year] on 1
from [Cube]

我想不将[度量].[承诺总额]建立在[Date Dim].[FY层次结构]上,但是要独立于计算,然后在select语句中使用日期轴-即我们可以通过日历年,会计年度,Fisal年/月Dim来计算.

I would like to have the [Measures].[Commitment Total] not built upon the [Date Dim].[FY Hierarchy], but be independent in the calculation, then use the date axis in the select statement - i.e. we could calculate by the Calendar Year, Fiscal Year, Fisal Year / Month Dim's.

所以我认为它看起来像这样:

So I'm thinking it'd look like this:

成员[措施].[承诺总额]为
sum(
NULL:[Date Dim] .currentMember
,[措施].[承诺计数])

with member [Measures].[Commitment Total] as
sum(
NULL:[Date Dim].currentMember
, [Measures].[Commitment Count])

然后使用计算:

选择[措施].[承诺总额]为0
,[Date Dim].[FY Hierarchy].[Finan Year] on 1
来自[多维数据集]

select [Measures].[Commitment Total] on 0
, [Date Dim].[FY Hierarchy].[Fiscal Year] on 1
from [Cube]

OR

选择[措施].[承诺总额]为0
,[Date Dim].[CY阶层].[会计年度] on 1
来自[多维数据集]

select [Measures].[Commitment Total] on 0
, [Date Dim].[CY Hierarchy].[Fiscal Year] on 1
from [Cube]

不确定我要问的内容是否可行?

Not sure if what I'm asking is possible or not?

推荐答案

我遇到了类似的问题,并按以下步骤解决了该问题:

I've had a similar problem, and solved it as follows:

SSAS不能真正告诉您所处的层次结构,因此您不能像在示例中那样简单地执行此操作.但是,以下内容对我有用.我已经尝试过将其修改为您的命名,因此请检查是否存在明显的语法错误...

SSAS can't really tell you which hierarchy you're on, so you can't do this as simply as in your example. however, the following worked for me. I've tried to rework it to your naming, so check for obvious syntax errors...

这一切都在您的多维数据集计算脚本中,您可能需要使用脚本视图"而不是块视图".

This all goes in your cube calculation script, you may need to use 'script view' instead of 'block view'.

CREATE MEMBER CURRENTCUBE.[Measures].[Commitment Total] AS NULL;

然后稍后在脚本中为每个层次结构定义它:

Then define it later in the script for each hierarchy:

SCOPE (DESCENDANTS([Date Dim].[FY Hierarchy],,AFTER));    
[Measures].[Commitment Total] =
sum(
[Date Dim].[FY Hierarchy].[Fiscal Year].members(0):[Date Dim].[FY Hierarchy].currentMember
, [Measures].[Commitment Count]);
END SCOPE;

SCOPE (DESCENDANTS([Date Dim].[CY Hierarchy],,AFTER));    
[Measures].[Commitment Total] =
sum(
[Date Dim].[CY Hierarchy].[Calendar Year].members(0):[Date Dim].[CY Hierarchy].currentMember
, [Measures].[Commitment Count]);
END SCOPE;

现在,它将根据查询中存在的层次结构相应地运行.请注意,如果查询中都没有层次结构,则它将为NULL.

Now it will behave accordingly, dependent on the hierarchy present in the query. Note that it will be NULL if neither hierarchy is in the query.

这篇关于MDX随时间推移而没有特定的层次结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆