INF: How to Use OLAP Number Formats with Office Web Components (301456)

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 "########" 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:

<HTML>
<HEAD><meta name="Example 1" contents= "NOINDEX, NOFOLLOW">
<TITLE>OLAP Number Formats UseProviderFormatting Sample</TITLE>
<META NAME="GENERATOR" Content="Microsoft Visual Studio">
<META HTTP-EQUIV="Content-Type" content="text/html; charset=UTF-8">
</HEAD>
<BODY bgcolor=lightskyblue text="#000000" link="#006600" vlink="#669966"
alink="#990000">
Server Name  : <input id=ServerName style="width:70%" value="localhost">
</input><br> Database Name: <input id=Catalog style="width:70%" value="Foodmart 2000">
</input><br>
Cube Name    : <input id=Cube style="width:70%" value="Sales"></input><p>
<button ONCLICK=Connect_OnClick() ID=Button1>Connect</button><p>
<OBJECT CLASSID="clsid:0002E552-0000-0000-C000-000000000046" id="MyPT" VIEWASTEXT>
</OBJECT>

<script language=VBScript>

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

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

</BODY>
</HTML>

Save the HTML page and then open it in Internet Explorer.
Set the names of the Server, Database, and Cube, and then click the Connect button.
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 "########" format.

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.

Create a new HTML page using the following code:

<HTML>
<HEAD><meta name="Example 2" contents= "NOINDEX, NOFOLLOW">
<TITLE>Set Number Formats from Cube Format_String</TITLE>
<META NAME="GENERATOR" Content="Microsoft Visual Studio">
<META HTTP-EQUIV="Content-Type" content="text/html; charset=UTF-8">
</HEAD>
<BODY bgcolor=lightskyblue text="#000000" link="#006600" vlink="#669966"
alink="#990000">
Server Name : <input id=ServerName style="WIDTH: 70%" value="localhost"></input><br>
Database Name: <input id=Catalog style="WIDTH: 70%" value="Foodmart 2000"></input><br>
Cube Name    : <input id=Cube style="WIDTH: 70%" value="Sales"></input><p>
<button ONCLICK=Connect_OnClick() ID=Button1>Connect</button>
<button ONCLICK=Format_OnClick() ID=Button2>Set Formatting</button><p>
<OBJECT id=MyPT style="WIDTH: 502px; HEIGHT: 217px" 
classid=clsid:0002E552-0000-0000-C000-000000000046 width=502 height=217 
VIEWASTEXT>
<PARAM NAME="XMLData"
VALUE='<xml xmlns:x="urn:schemas-microsoft-com:office:excel">&#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;</xml>'>
</OBJECT>
<script language=VBScript>
function Connect_OnClick()
   MyPT.ConnectionString = "Provider=MSOLAP.2;Data Source=" & _
       ServerName.Value & ";Initial Catalog=" & Catalog.value
   MyPT.DataMember = Cube.value
   ' Put measures on the view.
   MyPT.CommandText = "select AddCalculatedMembers(Measures.members) " & _
     "on columns " & _
     "from [" & Cube.value & "]"
end function

function Format_OnClick()
   GetFormatsFromCube MyPT
end function

sub GetFormatsFromCube(pt)
   if pt.ConnectionString = "" or _
      pt.DataMember = "" then
         exit sub
   end if
   if pt.Connection is Nothing then
      exit sub
   end if
   CubeName = pt.DataMember
   if Left(CubeName,1) <> "[" then
      CubeName = "[" & CubeName & "]"
   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 "select ..."
   set cs = createobject("ADOMD.Cellset")
   set cs.ActiveConnection = pt.Connection
   cs.Open _
   "select " & _
   "AddCalculatedMembers(Measures.members) " & _
   "on columns " & _
   "from " & CubeName & " " & _
   "cell properties format_string "
   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("FORMAT_STRING")
      next
   end sub
</script></p>
</BODY>
</HTML>

Save the HTML page and then open it in Internet Explorer.
Set the names of the Server, Database, and Cube, and then click the Connect button.
Click the Set Formatting button.
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 "########" format.

INF: How to Use OLAP Number Formats with Office Web Components (301456)

SUMMARY

MORE INFORMATION