owc11 pivot table mdx query语法与ssas2008 mdx query语法不一样？解决办法
日期：2014-05-18 浏览次数：20474 次
owc11 pivot table mdx query语法与ssas2008 mdx query语法不一样？
一些查询，比如:
mdx query1:
WITH MEMBER [Measures].x AS username SELECT [Measures].x ON 0 FROM [Cube Is]
mdx query2:
WITH MEMBER [Measures].x
AS
BIDataSecurity.GetDimensionSet(USERNAME, "publisher")
SELECT [Measures].x ON 0
FROM [Cube Is]
mdx query3:
select
[Order].[Publisher].[Publisher] on rows,
{[Order].[Date].&[2009-12-20T00:00:00]:[Order].[Date].&[2010-01-03T00:00:00]} on columns
from [Cube Is] where ([Measures].[Count - Order])
mdx query4:
select
[Order].[Publisher].[Publisher] on rows,
[Order].[Date].[Date] on columns
from [Cube Is] where ([Measures].[Count - Order])
上面这4个mdx查询在ssas2008的mdx查询里面均能正常查询，但是搬到owc11 pivot table里去后,只有第4个mdx query4有返回结果，别的均提示：
无明细数据，查询无法进行。数据提供程序不能提供进一步的错误信息。
HTML代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="owcdetail.aspx.cs" Inherits="Default4" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head id="Head1" runat="server" />
<body>
<OBJECT
id='pivotTable'
style="Z-INDEX: 0; POSITION: absolute; TOP: 40px; LEFT: 10px; HEIGHT: 600px;
WIDTH:800px;"
height='600'
width='800'
classid='clsid:0002E55A-0000-0000-C000-000000000046'>
</OBJECT>
<script language="javascript" type="text/javascript">
pvt = document.all.pivotTable;
var pvtusername='<%=return_username%>';
var pvtpassword='<%=return_password%>';
var hostname='http://。。。。/olap/msmdpump.dll';
var catalogname='all_as';
//var mdxquery1='select [Order].[Publisher].[Publisher] on rows,[Order].[Date].[Date] on columns from [Cube Is] where ([Measures].[Count - Order])'
//var mdxquery2='WITH MEMBER [Measures].x AS username SELECT [Measures].x ON 0 FROM [Cube Is]';
//time_area={[Order].[Date].&[2009-12-20T00:00:00]:[Order].[Date].&[2010-01-03T00:00:00]}
var mdxquery3='select ([Order].[Publisher].[Publisher],[Order].[Item].[Item]) on rows, [Measures].[Count - Order] on columns from [Cube Is] where (<%=time_area%>)';
pvt.ConnectionString='Provider=MSOLAP.3;Persist Security Info=True;User ID='+ pvtusername +';Password='+ pvtpassword +';Initial Catalog=all_as;Data Source=http://。。。。/olap/msmdpump.dll;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error'
pvt.DataMember=catalogname;
pvt.DisplayFieldList = true
pvt.CommandText = mdxquery;
pvtconstants = pvt.Constants;
//设定透视表的属性
pvt.AutoFit = true;
pvt.DisplayToolbar = true;
pvt.DisplayExpandIndicator = true;
pvt.DisplayAlerts = true;
pvt.DisplayBranding = true;
pvt.DisplayDesignTimeUI = true;
pvt.DisplayFieldList = true;
pvt.DisplayOfficeLogo = false;
pvt.DisplayPropertyToolbox = false;
pvt.DisplayScreenTips = true;
pvt.ActiveView.TitleBar.Visible = true;
pvt.ActiveView.TitleBar.Caption = "<%=Title%>";
pvt.ActiveView.TitleBar.Font.Name = "arial";
pvt.ActiveView.TitleBar.Fon
一些查询，比如:
mdx query1:
WITH MEMBER [Measures].x AS username SELECT [Measures].x ON 0 FROM [Cube Is]
mdx query2:
WITH MEMBER [Measures].x
AS
BIDataSecurity.GetDimensionSet(USERNAME, "publisher")
SELECT [Measures].x ON 0
FROM [Cube Is]
mdx query3:
select
[Order].[Publisher].[Publisher] on rows,
{[Order].[Date].&[2009-12-20T00:00:00]:[Order].[Date].&[2010-01-03T00:00:00]} on columns
from [Cube Is] where ([Measures].[Count - Order])
mdx query4:
select
[Order].[Publisher].[Publisher] on rows,
[Order].[Date].[Date] on columns
from [Cube Is] where ([Measures].[Count - Order])
上面这4个mdx查询在ssas2008的mdx查询里面均能正常查询，但是搬到owc11 pivot table里去后,只有第4个mdx query4有返回结果，别的均提示：
无明细数据，查询无法进行。数据提供程序不能提供进一步的错误信息。
HTML代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="owcdetail.aspx.cs" Inherits="Default4" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head id="Head1" runat="server" />
<body>
<OBJECT
id='pivotTable'
style="Z-INDEX: 0; POSITION: absolute; TOP: 40px; LEFT: 10px; HEIGHT: 600px;
WIDTH:800px;"
height='600'
width='800'
classid='clsid:0002E55A-0000-0000-C000-000000000046'>
</OBJECT>
<script language="javascript" type="text/javascript">
pvt = document.all.pivotTable;
var pvtusername='<%=return_username%>';
var pvtpassword='<%=return_password%>';
var hostname='http://。。。。/olap/msmdpump.dll';
var catalogname='all_as';
//var mdxquery1='select [Order].[Publisher].[Publisher] on rows,[Order].[Date].[Date] on columns from [Cube Is] where ([Measures].[Count - Order])'
//var mdxquery2='WITH MEMBER [Measures].x AS username SELECT [Measures].x ON 0 FROM [Cube Is]';
//time_area={[Order].[Date].&[2009-12-20T00:00:00]:[Order].[Date].&[2010-01-03T00:00:00]}
var mdxquery3='select ([Order].[Publisher].[Publisher],[Order].[Item].[Item]) on rows, [Measures].[Count - Order] on columns from [Cube Is] where (<%=time_area%>)';
pvt.ConnectionString='Provider=MSOLAP.3;Persist Security Info=True;User ID='+ pvtusername +';Password='+ pvtpassword +';Initial Catalog=all_as;Data Source=http://。。。。/olap/msmdpump.dll;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error'
pvt.DataMember=catalogname;
pvt.DisplayFieldList = true
pvt.CommandText = mdxquery;
pvtconstants = pvt.Constants;
//设定透视表的属性
pvt.AutoFit = true;
pvt.DisplayToolbar = true;
pvt.DisplayExpandIndicator = true;
pvt.DisplayAlerts = true;
pvt.DisplayBranding = true;
pvt.DisplayDesignTimeUI = true;
pvt.DisplayFieldList = true;
pvt.DisplayOfficeLogo = false;
pvt.DisplayPropertyToolbox = false;
pvt.DisplayScreenTips = true;
pvt.ActiveView.TitleBar.Visible = true;
pvt.ActiveView.TitleBar.Caption = "<%=Title%>";
pvt.ActiveView.TitleBar.Font.Name = "arial";
pvt.ActiveView.TitleBar.Fon
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
