SSAS & Excel BI Tips Chapter Three: Pivot Chart

directly share a recent in-spread article (http://daily.zhihu.com/story/4360204), is about Excel PivotChart report fever gameplay, often see some bi products in the promotion of the idea of throwing some ideas, Say your product chart is how gorgeous, quickly abandon ugly Excel bar, Excel PivotChart report may indeed not be as good as some professional charting tools, but definitely not ugly, in addition, it is also the advantages of PPT and word seamless integration, This is not easy to do other tools, the so-called visual chart meaning that the decision-maker can be the most intuitive way to grasp the data trends, usually by the BA data analysis results into a visual chart to report to the decision-maker, and BA to complete the analysis process, or to master a variety of detailed data, the data into a chart easy , it is not easy to turn the chart into data.

The pivot chart is typically created directly to the query table or pivot table, and then inserted directly into the pivot chart in the Insert menu or the Analyze menu, so many of the pivot chart design features are and pivot Table-shared, such as the PivotChart Fields task pane, the Analyze menu bar, and so on, can be used to refer to the previous article (http://www.cnblogs.com/xpivot/p/4310998.html), It is important to note that the settings that are made in these shared functions also affect the associated pivot Table

This is not like the previous article has those independent function points can be said, then the step by step to complete a PivotChart report, and try to cover the work will be used frequently, the design of the fever is not the topic of this article. First, let's drag out a pivot table that shows what each country sells, as shown in.

The active cell is positioned on the pivot table, using the Excel insert or analyze menu to insert a perspective directly into the chart type, select the combo type of line in secondary Axis

Positioning on the perspective, expand the right of the + Sign shortcut button, you can set the visibility of each area and display location, such as moving the legend area from the right to the bottom, comparing the upper and lower picture differences

Set two x Y-axis label Description text

Adjust the perspective style and color in the shortcut button or the Design menu on the right

Displays the Sales Amount data label for each country

Delete key deletes some data labels, keeping only the highest and lowest values

Double-click a perspective blank area to activate the Format Style task pane, which is equivalent to the Format menu bar, and the style settings vary when you navigate to different areas of the perspective. Here you can fine-tune each area of the chart, and custom style rules can be applied to other perspectives to maintain a unique, unified style, saving the effort of repeating editing styles

Activating the United States histogram on the perspective will display the Quick Explore shortcut button next to the same button as the one mentioned in the previous article, and we'll look at the effect of using the quick explore on the perspective to drill down on the U.S. sales.

As a result, you can see that drilling down on a pivot table or pivot chart has the same effect as synchronizing the relevant pivot charts

The data source of the perspective is bound to the above pivot table, even if you copy this perspective to PPT, right click on the menu click Edit Data, it will open the Excel report that you just designed, if the PivotTable content in Excel has changed, and its related PivotChart will change accordingly, Whether these perspectives are in Excel or PPT.

See a handy and useful feature like this clustered column chart, Axis region is country, legend area is gender

Click the Switch Row/column button in the Design menu bar to switch between axis and legend area with one click, and the dimension of the pivot table will be swapped accordingly.

Finally share the next Xpivot in the Dashboard function (http://www.flybi.net/article/341), for a multi-perspective view of the global slice, Excel comes with a slicer to hierarchy support is not enough, So I developed this feature myself.

SSAS & Excel BI Tips Chapter Three: Pivot Chart