Hi Lucas,
Thank you for posting in MSDN managed newsgroup!
From my understanding to your question, you want to know whether Pivottable supports the client MDX set and how you can retrieve the client
data.
So far as I know, the Pivottable of OWC (office web component) takes advantage of the Pivottable service of SQL server Analysis service. The
PivotTable Service will maintain a local cache on the client computer. This way, client side will save the time to avoid retrieving data very
frequently from the server side, which is very time-consuming and gives a slow response to the customer. Besides, there are two properties for
SQL analysis service to control the client cache refresh mode: Isolation and non-isolation. The two links below will introduces more information for
you regarding this:
PivotTable Service
http://msdn.microsoft.com/library/en-us/olapdmpr/pt_intro_61b9.asp?frame=true
Managing the Client Cache
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmpr/pt_keyco_98x1.asp
Furthermore, when you retrieve the MDX data set from the SQL analysis service, you can directly use them in the view. Based on the Office web
component, I build one sample for you regarding this. I list the source code below. You will need to modify the source code to build the connection
to your SQL Analysis service.
<!-- Code begin -->
<HTML>
<HEAD>
<TITLE>New Document </TITLE>
</HEAD>
<BODY onload='init()'>
<OBJECT id="pt" style="WIDTH: 502px; HEIGHT: 217px" height="217" width="502" classid="CLSID:0002E55A-0000-0000-C000-
000000000046" VIEWASTEXT>
<PARAM NAME="XMLData" VALUE='<xml xmlns:x="urn:schemas-microsoft-com
ffice:excel">
<x
ivotTable>&#
13;
<x:OWCVersion>11.0.0.5531 </x:OWCVersion>
<x
isplayScreenTips/>
<x:CubeProvider>msolap.2
</x:CubeProvider>
<x:CacheDetails/>
<x
ivotView>
<x:IsNotFiltered/>
</x
ivotView>
</x
ivotTable>
</xml>'>
</OBJECT>
<br>
<br>
<input type="button" value="custom view with MDX" onclick='CustomView()'> </input>
<script language="vbscript">
sub init()
' you will need to change the data source according to your scenario
document.pt.ConnectionString = _
"provider=MSOLAP.2;Data Source=<<Data source name>>;Initial Catalog=FoodMart 2000;Integrated Security=SSPI;Persist Security
Info=True;Client Cache Size=25;Auto Synch Period=10000;"
document.pt.DataMember = "HR"
end sub
sub CustomView()
Dim sQuery
sQuery = "select "
sQuery = sQuery + "{[Measures].[Unit Sales]} on columns, "
sQuery = sQuery + "order(except([Promotion Media].[Media Type].members,{[Promotion Media].[Media Type].[No Media]}),[Measures].[Unit
Sales],DESC) on rows "
sQuery = sQuery + "from Sales"
document.pt.ActiveView.AutoLayout
document.pt.CommandText = sQuery
end sub
</script>
</BODY>
</HTML>
<!-- Code end -->
In addition, you can also use "ActiveData" property() of Pivottable object to retrieve the data information in client application when you obtain the
MDX set from SQL Analysis service. For some specified fields sets, you can also specify the "AlwaysIncludeInCube" property of PivotFieldSet Object
to always include this set in the PivotTable list's local cache.
You will obtain the Office XP web component toolkit from the link: Please go to:
http://www.microsoft.com/downloads/...77-2100-4586-A13C-50E56F101720&displaylang=en
Please feel free to let me know if you have any further questions.
Does this answer your question? Thank you for using Microsoft NewsGroup!
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! -
www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
