如何实现“大于"基于 MDX 中的维度值 [英] How to implement "greater than" based on dimension value in MDX

问题描述

我想查询每个GDP增长率超过3的国家的销售总额.目前我可以这样显示每个国家的销售信息:

SELECT{[Measures].[Sales]} ON 0,{[Dim Company Info].[LOC].ALLMEMBERS} ON 1来自 [数据库]

但后来我对如何查询所有超过 3 的 GDP 增长率仍然空白.我搜索了 SO 并发现 filter 是答案，但是，我不知道将它包含在我上面的代码中的哪个位置.我该怎么办?

我尝试了以下方法，但我认为这不是我应该做的:

WHERE ([Dim Company Info].[Gdp Growth].&[3] : [Dim Company Info].[Gdp Growth].&[3].LastChild)

和

SELECT{[Measures].[SALES]} ON 0,{FILTER([Dim Company Info].[LOC].MEMBERS, [Dim Company Info].[Gdp Growth] > 3)} ON 1FROM [766 数据库]

和

SELECT{[Measures].[SALES]} 列，{[Dim Company Info].[LOC].MEMBERS} 行FROM [766 数据库]WHERE FILTER([Dim Company Info].[Gdp Growth], [Dim Company Info].[Gdp Growth] > 2)

解决方案

当维度成员按数字排列时，您可以使用下面的表达式

<块引用>

WHERE ([Dim Company Info].[Gdp Growth].&

现在让我们操纵最大数量，使其表现得像一个度量.

与成员措施.test作为当[措施].[互联网销售额]>0时，则(nonempty(([Product].[Product].currentmember,[Promotion].[Max Quantity].[Max Quantity]),[Measures].[Internet Sales Amount])).item(0).item(1).名称别的空端选择{[Measures].[网络销售额],measures.test}在 0，非空 ([产品].[产品].[产品])在 1来自[冒险作品]

现在你可以使用过滤器了，但是我们还需要对数据进行转换(注意使用 CInt)

与成员措施.test作为当[措施].[互联网销售金额]>0时，则Cint((nonempty(([Product].[Product].currentmember,[Promotion].[Max Quantity].[Max Quantity]),[Measures].[Internet Sales Amount])).item(0).item(1).名称)别的空端选择{[Measures].[网络销售额],measures.test}在 0，非空(筛选([产品].[产品].[产品],measures.test>0))在 1来自[冒险作品]

I would like to query the total sales of each country with the GDP growth rate of more than 3. So far, I can display the sales information of each country like such:

SELECT 
{[Measures].[Sales]} ON 0,
{[Dim Company Info].[LOC].ALLMEMBERS} ON 1
FROM [Database]

But then I am still blank on how to query all GDP growth rate of more than 3. I have searched SO and found filter to be the answer, however, I do not know where to include it in my code above. How do I go about this?

Edit: I have tried the followings, but I do not think that is what I am supposed to do:

WHERE ([Dim Company Info].[Gdp Growth].&[3] : [Dim Company Info].[Gdp Growth].&[3].LastChild)

and

SELECT 
{[Measures].[SALES]} ON 0,
{FILTER([Dim Company Info].[LOC].MEMBERS, [Dim Company Info].[Gdp Growth] > 3)} ON 1
FROM [766 Database]

and

SELECT 
{[Measures].[SALES]} ON COLUMNS,
{[Dim Company Info].[LOC].MEMBERS} ON ROWS
FROM [766 Database]
WHERE FILTER([Dim Company Info].[Gdp Growth], [Dim Company Info].[Gdp Growth] > 2)

解决方案

You can use your below expression when the dimension members are numericly arranged

WHERE ([Dim Company Info].[Gdp Growth].&3 : [Dim Company Info].[Gdp Growth].&3.LastChild) with a slight correction

WHERE ([Dim Company Info].[Gdp Growth].&[3] : [Dim Company Info].[Gdp Growth].[Gdp Growth].LastChild)

However if that is not the case, then you need to follow the below method.

I am reporting the product with the max quantity.

select {[Measures].[Internet Sales Amount]} on 0,
non empty
([Product].[Product].[Product],[Promotion].[Max Quantity].[Max Quantity]) on 1 
from [Adventure Works]

Now lets manipulate the max quantity to make it behave like a measure.

with 
member measures.test
as 
case when [Measures].[Internet Sales Amount]>0 then 
(nonempty(([Product].[Product].currentmember,[Promotion].[Max Quantity].[Max Quantity]),[Measures].[Internet Sales Amount])).item(0).item(1).name
else 
null end

select
{[Measures].[Internet Sales Amount],measures.test}
on 0,
non empty ([Product].[Product].[Product] )
on 1 
from [Adventure Works]

Now you can use the filter, but we need to cast(note the use of CInt) the data too

with 
member measures.test
as 
case when [Measures].[Internet Sales Amount]>0 then 
Cint((nonempty(([Product].[Product].currentmember,[Promotion].[Max Quantity].[Max Quantity]),[Measures].[Internet Sales Amount])).item(0).item(1).name)
else 
null end

select
{[Measures].[Internet Sales Amount],measures.test}
on 0,
non empty (
filter(
[Product].[Product].[Product]
,measures.test>0) 
)
on 1 
from [Adventure Works]

这篇关于如何实现“大于"基于 MDX 中的维度值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！