强制mdx查询返回列名称 [英] Force mdx query to return column names

问题描述

从powerpivot连接到SSAS时，出现以下问题-如果由于某种原因mdx查询未返回任何行，则也不返回任何列名，并且powerpivot给出了错误.在SSMS中执行此类查询时，如果mdx不返回任何内容，则得到0行；如果mdx返回1行(列名和行本身)，则得到2行.所以-我可以以某种方式强制SSAS返回列名称吗? 查询当前看起来像这样:

When connecting from powerpivot to SSAS, I got the following problem - if for some reason no rows are returned by mdx query, no column names are returned either and powerpivot gives an error. When executing such a query in SSMS I got 0 rows if mdx returns nothing and 2 rows if mdx returns 1 row (column names and the row itself). So - can I somehow force SSAS to return column names? Query currently looks a bit like this:

SELECT NON EMPTY {[Measures].[Measure1]} ON COLUMNS,
NON EMPTY {Filter([DimLocalDate].[Date].&[20110101]:[DimLocalDate].[Date].&[20120101],
[JobStatus].[JobStatus].&[1] } ON ROWS
FROM [TheCube]

在我的应用程序中，客户可以指定日期和状态值.如果没有找到结果，我期望结果集为空，但是我得到了一个错误. 谢谢.

In my app customer can specify dates and status value. If no results are found I would expect an empty result set, but instead I got an error. Thanks.

推荐答案

我将在select之前检查您的NonEmpty子句是否返回一个空集，在这种情况下，将一个虚拟集放到行，该假集将在以下位置返回到powerpivot至少1行. 像这样:

I will check before the select if your NonEmpty clause will return a void set, and in that case put on row a dummy set that will be return to powerpivot at least 1 row. Something like:

WITH 
SET [mySet] as NON EMPTY ({Filter([DimLocalDate].[Date].&[20110101]:[DimLocalDate].[Date].&[20120101], [JobStatus].[JobStatus].&[1] })
SET [myRealSet] as IIF(COUNT([mySet]) > 0, [mySet], {[DummySet]}) 
SELECT NON EMPTY {[Measures].[Measure1]} ON COLUMNS,
[myRealSet] ON ROWS
FROM [TheCube]

需要帮助吗?

这篇关于强制mdx查询返回列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！