Windows 7 PivotTables X Server formatting, transl

In this post I’ll walk you through three Analysis Services features that we now support in Excel PivotTables – server formatting, translations, and member properties. One thing to keep in mind as you read is that since all these are defined in Analysis Services (i.e. on a server), every PivotTable created that pulls data from Analysis Services will get the benefit of these features without the PivotTable author or user needing to do anything.
Server formatting
When designing a model in Analysis Services, formatting can be associated with values. Excel PivotTables will display this formatting by default (you can control it in the connection properties dialog for the connection being used by the PivotTable, so if you want to turn off the formatting,Windows 7, you can).
(Click to enlarge)
Here is an example of a PivotTable displaying number formatting as defined on the server – in this case dollars with two decimals. As you add fields to the Values area of the PivotTable, the formatting is done automatically.
(Click to enlarge)
The next screenshot illustrates fill color formatting defined on the server. In this case, the formatting is actually based on a rule (MDX expression), setting different fill colors based on the value in each cell. You can think of this as conditional formatting defined in Analysis Services. Again, the user didn’t have to do anything but add the fields to the PivotTable to get server defined conditional formatting based on centralized business rules. In the example, the rule colors cells with acceptable values green, cells with unacceptable values red and cells with values in between are colored yellow.
(Click to enlarge)
This is pretty powerful … business logic can be defined in an Analysis Services model, and everyone that views the data sees that business logic in their spreadsheet without having to do anything except add fields to their PivotTable.
Translations
For global companies, it is important that employees and customers in different countries can access corporate data in their native language. Analysis Services 2005 offers a new “translations” feature that enables you to have multiple translations of the same model. For example, you might have you product catalog available in English, French, German and Spanish. Excel 12 PivotTables expose these translations in the report itself as well as in the field list. Based on the language settings on the machine where Excel is running, Excel will automatically pick the same language if it exists on the server. If that language does not exist, the default language specified on the server will be picked. You can override this automatic behavior by specifying a locale identifier in the connection information for the PivotTable and thereby force the use of a specific language.
Let’s look at an example. Here is a PivotTable displaying the English translations of the server model, which is what you would see if you open the Excel file on a machine running English Excel 12.
(Click to enlarge)
Now, I’ll pick a different connection file pointing to the same server model. This connection file specifies that I want the Spanish translations.
(Click to enlarge)
(Click to enlarge)
In this case I chose a new connection, but the same thing would have happened had I opened the Excel file on a Spanish machine running Excel 12.
Member properties
When analyzing data, it can be very helpful to get at additional information about an item to make better decisions. For example, if you’re looking at product sales, it might be very helpful to be able to quickly look up all the information on a specific product to better understand the sales amount for that product. Member properties in Analysis Services allow you to do just that. Excel 12 PivotTables expose member properties in tooltips, so all you have to do is to hover over the product that you want more information on, and this information will be displayed in a tooltip.
(Click to enlarge)
The member properties can also be added inside the PivotTable itself when you need to print the information or just have it be visible all the time. Here is what that looks like.
(Click to enlarge)
Finally, the Excel Object Model can be used to make member properties display instead of the members those properties pertain too. For example, if you have quarters represented as “First Quarter 2004”, “Second Quarter 2004”, etc., you can define a member property with abbreviated names like “Q1 04”, “Q2 04” etc. and set a flag to show the properties when the field is placed in the column area, making the report more readable (less wide).
<div