MS SQL Server OLAP - % format display problem in pivot table

Author

% format display problem in pivot table

urgent

2005-04-22, 8:24 pm

When pivot table (xp) connects to OLAP Cube, the percentage(numberic data
like 4,111,111) format wouldn't work any more. I can re-format them in excel,
but is there any way excel can automatically realize the format in cube???

Also, how to format %... in cube?

thanks in advance.

Deepak Puri

2005-04-25, 3:23 am

http://groups-beta.google.com/group...erver.olap/msg/
aa1ff98ef9cc6385? hl=en[color=darkred]

...
Excel alone may not support server-side formatting. But Office Web
Component (OWC) Pivot Tables support it:

http://support.microsoft.com/d_efau...b;en-us;3181_22

INFO: Using Analysis Server Format Properties with the Office XP
PivotTable Component

SUMMARY

SUMMARY

The Microsoft Office XP PivotTable component supports two undocumented
properties: UseProviderFormattin
g and DisplayCellColor. These properties
allow you to use server formatting for cells and totals in a PivotTable
view.

• When you set the UseProviderFormattin
g property to True, the
PivotTable component applies number formats that are stored on the
server to the totals that appear in the PivotTable view. The default
setting for this property is False.

• When you set the DisplayCellColor property to True, the PivotTable
component applies Forecolor and Backcolor settings that are stored on
the server to the cells that appear in the PivotTable view. The default
setting for this property is False.

• The PivotTable component does not support other format properties that
can be set on the server, such as FontName, FontSize, and FontFlags.

NOTE: The UseProviderFormattin
g and DisplayCellColor properties are
hidden members in the Microsoft Office XP Web Components object model.
These members are not documented and, therefore, these members are not
supported by Microsoft Technical Support. The sample code in this
article is provided "as-is."
..

There are Excel OLAP Add-Ins, including from Microsoft:

http://www.microsoft.com/offic_e/so...xcel_addin/over
vi
ew.mspx
...[color=darkred]

FORMAT_STRING='PERCE
NT', or for further customization:

http://msdn.microsoft.com/library/d...y/en-us/olapdma
d/agmdxadvanced_2aur.asp[color=darkred]
FORMAT_STRING Contents

The cell property FORMAT_STRING is used to format the VALUE cell
property, creating the value for the FORMATTED_VALUE cell property. The
FORMAT_STRING cell property handles both string and numeric raw values,
applying a format expression against the value to return a formatted
value for the FORMATTED_VALUE cell property. The following tables detail
the syntax and formatting characters used to handle string and numeric
values.
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Sponsored Links