At one point or another, everyone has probably used Pivot tables within Excel to slice and dice information. PowerPivot is an extension of this functionality, so it makes sense to initially show how this works before showing all of the new features that are available within PowerPivot.
NOTE: This is part of the Using PowerPivot to Analyze Your Dynamics AX Data blog series where we show how PowerPivot can assist you with advanced data analysis and reporting, making those extracts from Dynamics AX to Excel even more valuable. If you want to see all of the other posts in this series, click here.
HOW TO DO IT
To use PivotTables to analyze information from within Dynamics AX, follow these steps:
- For this example we will just start with a query within AX and export the data to Excel.
- Once we are in Excel, we can create a Pivot Table for analysis just by clicking on the Pivot Table button within the Tables group of the Insert ribbon bar.
- Excel will ask us where we should grab the data from and also where we want to put the Pivot Table results. Use the defaults, and just click on the OK button to continue.
- Excel will create a new worksheet for you and also show all of the columns from the source worksheet for reporting.
HOW IT WORKS
All you have to do to create a report through the Pivot Table is drag and drop the fields into the designer panel
If you click on the Pivot Chart button within the Insert ribbon bar, you will be able to add a Pivot Chart that is based on the data that you have reported off.
You can select the style of chart that you want to add to the worksheet from the Chart Gallery, and click the OK button to add it to the worksheet.
Any changes that you make to the data will be reflected in both the Pivot Table and Chart.