MDX：如何将具有多个成员的元组转换为具有单个成员的元组？

发布于 2024-08-17 13:42:31 字数 1489 浏览 11 评论 0原文

我有这个 MDX 查询（基于 Foodmart 示例数据库）：

SELECT
{[Measures].[Profit]} ON COLUMNS,
{CROSSJOIN([Product].[All Products].Children, [Time].[1997].Children)} ON ROWS
FROM [Sales]

这会生成如下结果：

[Product].[Product Family] [Time].[Year] [Time].[Quarter] [Measures].[Profit]
--------------------------+-------------+----------------+-------------------
Drink                     |1997         |Q3              |7203.3445
Drink                     |1997         |Q4              |8005.2245
Food                      |1997         |Q1              |60814.47140000001
Food                      |1997         |Q2              |57323.3736

我想要的是：

[Product Family],[Year],[Quarter] [Measures].[Profit]
---------------------------------+-------------------
Drink, 1997, Q3                  |7203.3445
Drink, 1997, Q4                  |8005.2245
Food, 1997, Q1                   |60814.47140000001
Food, 1997, Q2                   |57323.3736

我知道我可以使用 SetToStr() 来序列化行标题到一个字符串。所以现在我想使用该结果作为行标题：基本上将 ROW 轴上原始多成员元组中的每个元组转换为只有一个成员的元组，该成员的值是原始成员名称的串联。所以基本上是这样的：

SELECT
{[Measures].[Profit]} ON COLUMNS,
SetToStr(
    {CROSSJOIN([Product].[All Products].Children, [Time].[1997].Children)}
) ON ROWS
FROM [Sales]

...但是这当然不起作用，因为 SetToStr() 返回一个字符串，而不是一个集合。所以我需要某种方法来将这个字符串“转换”回一组，但只有一个成员。

这在标准 MDX 中可能吗？如何？我可以在收到结果集后重新处理它，但我确实可以使用纯 MDX 解决方案来解决这个问题。

原文

I have this MDX query (based on the Foodmart sample database):

SELECT
{[Measures].[Profit]} ON COLUMNS,
{CROSSJOIN([Product].[All Products].Children, [Time].[1997].Children)} ON ROWS
FROM [Sales]

This generates a result like this:

[Product].[Product Family] [Time].[Year] [Time].[Quarter] [Measures].[Profit]
--------------------------+-------------+----------------+-------------------
Drink                     |1997         |Q3              |7203.3445
Drink                     |1997         |Q4              |8005.2245
Food                      |1997         |Q1              |60814.47140000001
Food                      |1997         |Q2              |57323.3736

What I would like to have, is this:

[Product Family],[Year],[Quarter] [Measures].[Profit]
---------------------------------+-------------------
Drink, 1997, Q3                  |7203.3445
Drink, 1997, Q4                  |8005.2245
Food, 1997, Q1                   |60814.47140000001
Food, 1997, Q2                   |57323.3736

I know I can use SetToStr() to serialize the row headers to one string. So now I would like to use that result as rowheader: basically turning each tuple from the original multi-member tuples on the ROW axis into a tuple with just one member, who'se value is a concatenation of the original member names. So basically this:

SELECT
{[Measures].[Profit]} ON COLUMNS,
SetToStr(
    {CROSSJOIN([Product].[All Products].Children, [Time].[1997].Children)}
) ON ROWS
FROM [Sales]

...but of course this does not work, because SetToStr() returns a string, not a set. So I need some way to 'cast' this string back to a set, but with only one member.

Is this possible in standard MDX? How? I can rework the resultset after receiving it but I could really use a pure MDX solution to tackle this problem.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

絕版丫頭 2024-08-24 13:42:31

WITH MEMBER [Measures].[name] 
AS [Product].currentMember.Name || ',' || [Time].CurrentMember.Name
SELECT { [Measures].[name] , [Measures].[Sales]} ON COLUMNS
,      ([Product].Children * [Time].Children)    ON ROWS
FROM   [SteelWheelsSales]

WITH MEMBER [Measures].[name] 
AS [Product].currentMember.Name || ',' || [Time].CurrentMember.Name
SELECT { [Measures].[name] , [Measures].[Sales]} ON COLUMNS
,      ([Product].Children * [Time].Children)    ON ROWS
FROM   [SteelWheelsSales]

回复收藏 0 原文

~没有更多了~