在mdx查询中查找每个后续财务期年度的数据 [英] Find data for each subsequent financial period year in mdx query
问题描述
In my database cube, data is distributed like this, for each quarter, per year
2005 - > Q1 -> 10 2006 - > Q1 -> 4 2007 -> Q1-> 4
Q2 -> 20 Q2 -> 4 Q2-> 44
Q3 -> 5 Q3 -> 4 Q3-> 3
Q4 -> 4 Q4 -> 4 Q4-> 3
2006财政年度和2007我需要从中获取数据
For financial year 2006 & 2007 i need to fetch data from
2005 -> Q2,Q3,Q4 & Q1 of 2006 ie, 20, 5,4,4
2006 -> Q2,Q3,Q4 & Q1 of 2007 ie, 4,4,4,4
我有这样的构建mdx查询-:
I have build mdx query like this - :
WITH MEMBER [LTII_NIRS_E] AS
SUM({[DIM TIME].[YEAR - QUARTER - MONTH].[YEAR].[2005].[Q2]:[DIM TIME].[YEAR - QUARTER - MONTH].[YEAR].[2006].[Q1]},[Measures].[Measures].[LTII NIRS E])
SELECT [LTII_NIRS_E] ON COLUMNS, {[DIM TIME].[YEAR -
QUARTER - MONTH].[2006]:[DIM TIME].[YEAR - QUARTER - MONTH].[2008]}
On ROWS FROM [NH_INCID] WHERE [Business Component].[Region].[Novelis North America]
但是它仅提供2005财政年度的数据
But it gives data only for 2005 financial year
即执行查询后的结果如下
ie, result look like this after executing query
2006 -> 33
2007- > 33 -> it should give 16 instead of 33
我应该在上面的查询中的SUM函数中写什么,以便它为每个会计年度计算.请帮助我进行此查询?
What should i write in SUM function in above query so that it calculates for each individual fiscal year.Please help me with this query ?
推荐答案
我用滞后时间来给我指定时间到第一季度的暗淡时间层次结构中指定年份成员的第一季度之前的三个季度.之所以有效,是因为我在计算所得的成员中使用currentmember,然后在行轴上指定了这些成员.
I used lag to say give me the three quarters before the first quarter of the specified year member of the dim time hierarchy through the first quarter. This works because I use currentmember in the calculated member and then specify the members on the rows axis.
WITH MEMBER [LTII_NIRS_E] AS
'Sum({[DIM TIME].[YEAR - QUARTER - MONTH].currentmember.firstchild.lag(3): [DIM TIME].[YEAR - QUARTER - MONTH].currentmember.firstchild}, [Measures].[LTII NIRS E])'
SELECT [LTII_NIRS_E] ON COLUMNS,
{ [DIM TIME].[YEAR - QUARTER - MONTH].[2006], [DIM TIME].[YEAR - QUARTER - MONTH].[2007], [DIM TIME].[YEAR - QUARTER - MONTH].[2008]} on ROWS
FROM [NH_INCID]
WHERE [Business Component].[Region].[Novelis North America]
这篇关于在mdx查询中查找每个后续财务期年度的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!