Just as you can do within Excel, you can also use any of the built in functions within the Data Model designer. This allows you to perform string and date manipulation as you create new column definitions, giving you more reporting options than are available within the default tables.
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 functions within calculated columns, follow these steps:
- For this example we will use date manipulation to convert our Ship Date field into year and month values. Open up the PowerPivot Data Model Editor and click on the Insert Function button within the Calculations group of the Linked Tables ribbon bar. This will show you a list of all the valid functions. Find the YEAR function and then click the OK button.
- This will take you to the first available new column and you just need to select the field that you want to convert to a year and then press enter.
HOW IT WORKS
Now you will have a new column for the year.
You can rename the column and then repeat the process for the Month as well.
Rather than create a Pivot Table, this time we will return to Excel and create a PowerView dashboard by clicking on the PowerView button within the Reports group of the Insert ribbon bar.
This will open up a PowerView canvas and you will see all of the tables and fields on the right.
Notice though that some of them have database symbol in the bottom right hand corner – that indicates that they are housed in the PowerPivot Model rather than within Excel.
Now all you need to do is drag and drop the fields into the query panels to create a new dashboard.
You can add as many different panels to the dashboard as you want.