Reporting Services 中展示有CrossJoin函数的MDX查询结果
来源:互联网 发布:网络金融征信系统查询 编辑:程序博客网 时间:2024/05/16 03:54
讲解之前先要明确: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语句中就针对每一种类型计算值。
- Reporting Services 中展示有CrossJoin函数的MDX查询结果
- MDX层次结构在crossjoin函数中调用多次
- Crossjoin (MDX)
- reporting services 常用到的函数
- 一步步学习Reporting Services(二) 在报表中使用简单的参数作为查询条件
- 在表达式中使用报表函数 (Reporting Services)
- Reporting Services 中报表 乱码
- Reporting Services中参数说明
- SpringBoot查询的结果按视图结果展示结果
- Reporting Services的小技巧
- Reporting Services 2005 的迁移
- 简单的MDX查询
- 查询包含有%的结果
- mybatis中查询有复杂关联对象的结果集
- Reporting Services
- Reporting Services
- Reporting Services
- Reporting services
- 苛
- Windows下定时备份Oracle数据库指定用户数据脚本
- oracle定时备份
- oracle 定时备份
- 批处理(bat)命令获得并格式化日期和时间
- Reporting Services 中展示有CrossJoin函数的MDX查询结果
- 取以某字符开始,以某字符结束的字符串(正则表达式:VB.net)
- 操作系统文件管理
- SecureCRT设置背景色
- IO输入与输出
- 消息称TCL将推Android平台水货手机
- 关于this指针的一个经典回答:
- make的简单使用规则
- 搭建Android2.3开发环境