PowerPivot has another great feature that allows you to create relationships between tables so that you can report off both tables within the same query.
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.
For this example we will add a new set of data to the Excel spreadsheet through the Dynamics AX Excel add-in. To do this, follow these steps:
- From the Dynamics AX ribbon bar, click on the Add Data menu button and select the Add Tables option.
- Select the table that you want to add to the data model and click the OK button. In this example we will add the EcoResProductTranslation table so that we can get a product description to go along with the part code.
- From the field chooser drag and drop the description fields over to the worksheet.
- Before retrieving the data, we will filter the results by clicking on the Filter button within the Data group of the Dynamics AX ribbon bar. This will allow us to add a filter on the Language to restrict it to en-us.
- Once we have finished adding the fields and filters for the query, click on the Fields button to return to query mode, and click the Refresh button in the Data group of the Dynamics AX ribbon bar to fetch all of the data.
HOW TO DO IT
To link multiple tables through the table designer tool, follow these steps:
- Add the new table query to our PowerPivot data model by clicking on the Add to Data Model button within the Tables group of the PowerPivot ribbon bar.
- This will automatically open up the PowerPivot Manager window, and you should be able to see the query as a new data source in the model.
- From the Linked Table ribbon bar, click on the Diagram View button within the View group so that you can see the tables visually.
- Connect the two tables by just selecting one of the key fields and then dragging it over to the other field in the table.
HOW IT WORKS
To create a report off this linked data, click on the PivotTable button and select the PivotTable option.
This will return you to Excel, and you can specify where you want to show the Pivot Table – select the defaults to create a new worksheet and click the OK button.
Now you will see both tables within the field explorer on the right.
You can select fields from either table.
The data will then be linked based on your relationship that you have defined within the table designer.