译者:hxhgxy 来源:http://blogs.msdn.com/excel
PivotTables 11: Key Performance Indicators, Actions, and Named Sets
Today, I will cover three additional features of Analysis Services that Excel 12 PivotTables support – Key Performance Indicators, Actions, and Named Sets.
Key Performance Indicators
• Value. The current value of the business metric – this could be a physical measure like Sales, a calculated measure like Profit, or a custom calculation defined specifically in the KPI.
• Goal. The target for the business metric – this is usually an MDX expression that resolves to a value.
• Status. A number defining the current status of the Value, normalized in the range -1 (very bad) to +1 (very good) – this is also an MDX expression.
• Trend. An indication defining how the business metric is developing over time – getting better or worse relative to its goal. Trend is also normalized between -1 and 1, and also an MDX expression.
In Excel 12, KPIs are listed in the field list in a special KPIs folder. Here is an example of a KPI for Profit Margin.
Each KPI component can be added to the PivotTable Values area by checking the checkbox just like any other field. Let’s take a look at an example, specifically, and example of adding Value, Goal, Status, and Trend to a report on our Products and Product Categories. Here is what the report looks like when I add those four components.
As you can see, Value and Goal are presented as numbers. Status and Trend, on the other hand, are nice graphical representations – they can be used to get a very quick visual overview of your business as it is easy to pick out outliers etc. As I mentioned, Status and Trend are normalized values between -1.0 and 1.0. Since these sorts of numerical values are not very interesting to show in a report, we have worked with the SQL Server Analysis Services 2005 team to develop a set of images to represent the Status and Trend for any KPI. The images to be used are defined in the Analysis Services model, so everyone that looks at the Status or Trend in Excel sees the same graphic. Those of you that remember the conditional formatting post I wrote on Icon Sets have probably already figured out we are using that capability in Excel 12 as part of this KPI feature.
Even better, since this is a PivotTable, as I expand/collapse items in the PivotTable or perform other operations, the KPI components will automatically be calculated in the new context. For example, if I expand “Touring Bikes”, the PivotTable will show the values of the KPI components and update the Status and Trend graphics accordingly.
细分:
转载请注明来源"ExcelHome"并保留原文链接。固定链接:https://www.excelhome.net/lesson/article/excel/576.html |