Microsoft KB Archive/301456

= INF: How to Use OLAP Number Formats with Office Web Components =

Article ID: 301456

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server OLAP Services
 * Microsoft SQL Server 2000 Analysis Services
 * Microsoft Office PivotTable Component 9.0, run time

-



This article was previously published under Q301456



SUMMARY
There are two ways to have the Microsoft Office Web Components PivotTable control use Microsoft OLAP Server and Microsoft Analysis Server number formats. This article explains both methods of using server-side number formats with the PivotTable Office Web Component and provides code for two example HTML pages.



MORE INFORMATION
The PivotTable control does not use the Formatted_Value cell property by default because the cell contents are displayed as left-justified text. Because the data would be displayed as left-justified text, measures are not converted to &quot;########&quot; when the display is too narrow. This could result in numeric data being incompletely displayed.

The first method of using the OLAP number format requires setting the PivotTable.ActiveView.UseProviderFormatting property to a value of TRUE. The following HTML page illustrates the behavior that occurs when setting PivotTable.ActiveView.UseProviderFormatting to TRUE.   Create a new HTML page using the following code:   OLAP Number Formats UseProviderFormatting Sample     Server Name :  Database Name:  Cube Name   : <input id=Cube style=&quot;width:70%&quot; value=&quot;Sales&quot;> <button ONCLICK=Connect_OnClick ID=Button1>Connect <OBJECT CLASSID=&quot;clsid:0002E552-0000-0000-C000-000000000046&quot; id=&quot;MyPT&quot; VIEWASTEXT> </OBJECT>

<script language=VBScript>

function Connect_OnClick MyPT.ConnectionString = &quot;Provider=MSOLAP.2;Data Source=&quot; & _ ServerName.Value & &quot;;Initial Catalog=&quot; & Catalog.value MyPT.DataMember = Cube.value ' Put measures on the view. MyPT.CommandText = &quot;select AddCalculatedMembers(Measures.members) &quot; & _ &quot;on columns&quot; & _ &quot;from [&quot; & Cube.value & &quot;]&quot;

' Set the UseProviderFormatting property to TRUE. ' This will result in measures being displayed as left justified text. MyPT.ActiveView.UseProviderFormatting=true end function

</BODY> </HTML> </li> <li>Save the HTML page and then open it in Internet Explorer.</li> <li>Set the names of the Server, Database, and Cube, and then click the Connect button.</li> <li>Select one of the measures and decrease the width of the cell until only one character is displayed. Note that the data is not converted to &quot;########&quot; format.</li></ol>

The second method of using the OLAP number format uses ADO MD to query against the cube to obtain the Format_String property of the measure. The Format_String property is then applied to the Cell.NumberFormat property of the PivotTable. The following HTML page illustrates setting PivotTable.ActiveView.Cell.NumberFormat using the Format_String returned from a query. <ol> <li> Create a new HTML page using the following code: <HTML> <meta name=&quot;Example 2&quot; contents= &quot;NOINDEX, NOFOLLOW&quot;> Set Number Formats from Cube Format_String</TITLE> <META NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual Studio&quot;> <META HTTP-EQUIV=&quot;Content-Type&quot; content=&quot;text/html; charset=UTF-8&quot;> </HEAD> <BODY bgcolor=lightskyblue text=&quot;#000000&quot; link=&quot;#006600&quot; vlink=&quot;#669966&quot; alink=&quot;#990000&quot;> Server Name : <input id=ServerName style=&quot;WIDTH: 70%&quot; value=&quot;localhost&quot;> Database Name: <input id=Catalog style=&quot;WIDTH: 70%&quot; value=&quot;Foodmart 2000&quot;> Cube Name   : <input id=Cube style=&quot;WIDTH: 70%&quot; value=&quot;Sales&quot;> <button ONCLICK=Connect_OnClick ID=Button1>Connect <button ONCLICK=Format_OnClick ID=Button2>Set Formatting <OBJECT id=MyPT style=&quot;WIDTH: 502px; HEIGHT: 217px&quot; classid=clsid:0002E552-0000-0000-C000-000000000046 width=502 height=217 VIEWASTEXT> <PARAM NAME=&quot;XMLData&quot; VALUE='<xml xmlns:x=&quot;urn:schemas-microsoft-com:office:excel&quot;>&#13;&#10; <x:PivotTable>&#13;&#10; <x:OWCVersion>10.0.0.2621 </x:OWCVersion>&#13;&#10; <x:DisplayScreenTips/>&#13;&#10; <x:CubeProvider>msolap.2</x:CubeProvider>&#13;&#10; <x:CacheDetails/>&#13;&#10; <x:PivotView>&#13;&#10;  <x:IsNotFiltered/>&#13;&#10; </x:PivotView>&#13;&#10; </x:PivotTable>&#13;&#10; '> </OBJECT> <script language=VBScript> function Connect_OnClick MyPT.ConnectionString = &quot;Provider=MSOLAP.2;Data Source=&quot; & _ ServerName.Value & &quot;;Initial Catalog=&quot; & Catalog.value MyPT.DataMember = Cube.value ' Put measures on the view. MyPT.CommandText = &quot;select AddCalculatedMembers(Measures.members) &quot; & _ &quot;on columns &quot; & _ &quot;from [&quot; & Cube.value & &quot;]&quot; end function

function Format_OnClick GetFormatsFromCube MyPT end function

sub GetFormatsFromCube(pt) if pt.ConnectionString = &quot;&quot; or _ pt.DataMember = &quot;&quot; then exit sub end if  if pt.Connection is Nothing then exit sub end if  CubeName = pt.DataMember if Left(CubeName,1) <> &quot;[&quot; then CubeName = &quot;[&quot; & CubeName & &quot;]&quot; end if  ' We can't get cell properties using an ADO recordset ' so we use ADOMD cellset. If you can, you can avoid ' the security warning by using: ' set rs = pt.Connection.execute &quot;select ...&quot; set cs = createobject(&quot;ADOMD.Cellset&quot;) set cs.ActiveConnection = pt.Connection cs.Open _ &quot;select &quot; & _ &quot;AddCalculatedMembers(Measures.members) &quot; & _ &quot;on columns &quot; & _ &quot;from &quot; & CubeName & &quot; &quot; & _ &quot;cell properties format_string &quot; for i = 0 to cs.axes(0).Positions.Count - 1 totalName = cs.axes(0).Positions(i).Members(0).UniqueName pt.ActiveView.Totals(totalName).NumberFormat = _ cs(i).Properties(&quot;FORMAT_STRING&quot;) next end sub </BODY> </HTML> </li> <li>Save the HTML page and then open it in Internet Explorer.</li> <li>Set the names of the Server, Database, and Cube, and then click the Connect button.</li> <li>Click the Set Formatting button.</li> <li>Select one of the measures and decrease the width of the cell until only one character is displayed. Note that the data is converted to &quot;########&quot; format.</li></ol>

Additional query words: OFFICE WEB COMPONENT NUMBER FORMAT FORMAT_STRING BIHowto

Keywords: kbinfo KB301456

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.