Reporting Services 中展示有CrossJoin函数的MDX查询结果

  讲解之前先要明确：Reporting Services只支持行数动态，不支持列动态。即列应该是固定的才行。

如下面的MDX语句：

WITH
MEMBER [Measures].[上报数0] AS
'sum([Measures].[CODE AVIMANAGEMENT 计数])'
MEMBER [Measures].[上报数] AS
IIF(ISEMPTY([Measures].[上报数0]),0,[Measures].[上报数0])
MEMBER [Measures].[结案数] AS
'sum([Measures].[上报数],[DIM_ABM_ISOVER].[ABM ISOVER].&[1])'
MEMBER [Measures].[区处理] AS
'sum(([Measures].[结案数],[DIM_ABM_DISFLAG].[ABM DISFLAG].&[3]))'
MEMBER [Measures].[街道处理] AS
'sum(([Measures].[结案数],[DIM_ABM_DISFLAG].[ABM DISFLAG].&[2]))'
MEMBER [Measures].[社区处理] AS
'sum(([Measures].[结案数],[DIM_ABM_DISFLAG].[ABM DISFLAG].&[1]))'
MEMBER [Measures].[整治率] AS
'IIF([Measures].[上报数]=0,0,[Measures].[结案数]/[Measures].[上报数])',FORMAT_STRING = "#0.00%"
SELECT
 {
 CrossJoin
 (
  Except({[DIM_ABM_AVITYPE].[AT PID],[DIM_ABM_AVITYPE].[AT PID].[Level 02].AllMEMBERS},[DIM_ABM_AVITYPE].[AT PID].[All].UNKNOWNMEMBER)
  ,{[Measures].[上报数],[Measures].[结案数],[Measures].[区处理],[Measures].[街道处理],[Measures].[社区处理],[Measures].[整治率]}
 )
 } ON COLUMNS,
[DIM DISTINCT].[D PINDEX].[Level 03].AllMEMBERS on rows
from [LGDZGDIG]

查看结果如下：

查询结果中，行是动态变化的。即会因为DIM_ABM_AVITYPE记录的变化而变化。这中数据展示Reporting Services不支持。

应该改成，纵轴变化的。

WITH
MEMBER [Measures].[上报数0] AS
'sum([Measures].[CODE AVIMANAGEMENT 计数])'
MEMBER [Measures].[上报数] AS
IIF(ISEMPTY([Measures].[上报数0]),0,[Measures].[上报数0])
MEMBER [Measures].[结案数] AS
'sum([Measures].[上报数],[DIM_ABM_ISOVER].[ABM ISOVER].&[1])'
MEMBER [Measures].[区处理] AS
'sum(([Measures].[结案数],[DIM_ABM_DISFLAG].[ABM DISFLAG].&[3]))'
MEMBER [Measures].[街道处理] AS
'sum(([Measures].[结案数],[DIM_ABM_DISFLAG].[ABM DISFLAG].&[2]))'
MEMBER [Measures].[社区处理] AS
'sum(([Measures].[结案数],[DIM_ABM_DISFLAG].[ABM DISFLAG].&[1]))'
MEMBER [Measures].[整治率] AS
'IIF([Measures].[上报数]=0,0,[Measures].[结案数]/[Measures].[上报数])',FORMAT_STRING = "#0.00%"
SELECT
{[Measures].[上报数],[Measures].[结案数],[Measures].[区处理],[Measures].[街道处理],[Measures].[社区处理],[Measures].[整治率]} ON COLUMNS,
CrossJoin(
  Except({[DIM_ABM_AVITYPE].[AT PID],[DIM_ABM_AVITYPE].[AT PID].[Level 02].AllMEMBERS},[DIM_ABM_AVITYPE].[AT PID].[All].UNKNOWNMEMBER)
  ,[DIM DISTINCT].[D PINDEX].[Level 03].AllMEMBERS
) on rows
from [LGDZGDIG]

 

如下图所示：

 

如果你还是执迷不悟，还是想要实现之前的那种效果，那在MDX语句中就针对每一种类型计算值。