Configuring Vendor Ratings and Scorecards in Dynamics AX
Dynamics AX allows you to configure your own Vendor Evaluation Criteria and then track the vendor performance against any or all of the criteria that you have defined. When you combine this with the Power BI tools, you get a great way to rate vendors, and also a nice way to present Vendor Scorecards.
In this worked example we will show how to configure the Vendor Evaluations, and also how to create a simple Vendor Scorecard without breaking a sweat.
Creating Vendor Evaluation Criteria Groups
The first step in configuring the Vendor Ratings within Dynamics AX is to create some Vendor Evaluation Criteria Groups. These will be used to group common sets of evaluation criteria together and allow you to get summary results on the evaluations at the criteria group level.
How to do it…
To set up new Vendor Evaluation Criteria Groups, follow these steps:
- Select the Vendor evaluation criteria group menu item from the Vendors folder of the Setup group on the Procurement and sourcing area page.
- When the maintenance form for the Vendor evaluation criteria groups is displayed, click on the New button on the menu bar to add a new record.
- Enter your Criteria Group code in the Name field and a brief description in the Description field.
- After creating any other Vendor Evaluation Criteria Groups in the maintenance form, click the Close button to exit.
Creating Vendor Evaluation Criteria
Once you have set up your Vendor Evaluation Criteria Groups, you will need to set up some Vendor Evaluation Criteria codes that you will use to rate your vendors.
How to do it…
To set up new Vendor Evaluation Criteria, follow these steps:
- Select the Vendor evaluation criteria menu item from the Vendors folder of the Setup group on the Procurement and sourcing area page.
- When the maintenance form for the Vendor evaluation criteria is displayed, click on the New button on the menu bar to add a new record.
- Enter your Criteria code in the Name field, a brief description in the Description field, and select the Vendor evaluation criteria group that you want the evaluation criteria to be associated with.
- After creating any other Vendor Evaluation Criteria in the maintenance form, click the Close button to exit.
Configuring Vendors for Evaluation
The final step in the setup process is to link the Evaluation Criteria with the Vendors within Dynamics AX. This is done through the Procurement Categories.
How to do it…
To set up new Evaluation Criteria, follow these steps:
- Select the Procurement categories menu item from the Categories folder of the Setup group on the Procurement and sourcing area page.
- Within the Procurement categories maintenance form select the top level node of your Categories and then open up the Vendor evaluation criteria groups tab within the detail area.
- To associate your Vendor Evaluation criteria groups click on the Add button in the tabs menu bar.
- Select all of the Evaluation Criteria Groups that you want the vendors to be measured on, and click on the Select button to add them to the selection box.
- Once you have finished, click the OK button to return to the main form.
- Now all of the Vendor Evaluation Criteria Groups should be associated with your procurement category.
- This will open up a Vendor Selection form. Select all of the vendors that you want to include in the evaluations, and click on the Select button to add them to the selection list.
- When you are finished, click on the OK button to return to the main form.
- You can continue this process for any of the leaf nodes in the Procurement Hierarchy.
- When you are finished, click on the Close button to exit from the form.
Recording Vendor Evaluations
Once the evaluation criteria have been configured, you can start recording the vendor ratings.
How to do it…
To Evaluation Ratings to your Vendors, follow these steps:
- Select the Procurement categories menu item from the Categories folder of the Setup group on the Procurement and sourcing area page, and expand the Vendors tab.
- Select the vendor that you want to record the evaluations against and click on the Evaluations button in the menu bar.
How it works…
This will open up the Ratings maintenance form with all of the Evaluation Criteria Groups listed on the left, and all of the Evaluation Criteria for that group on the right.
To update any of the vendor ratings, just select the rating for the criteria that you want to update and select the rating that you would like to apply.
Continue this process for all of the evaluation criteria that you want to report on.
Notice that the Average Rating for the Evaluation Criteria Group will change as you enter in the results.
Repeat this for all of the Criteria Evaluation Groups.
Once you have finished, you can just click Close to exit out of the form.
Creating a Vendor Evaluation Scorecard through Power BI
Now that you have all of the vendor ratings recorded within Dynamics AX, you will probably want to generate a vendor evaluation scorecard. Rather than having a static report, why not do this through Power BI, and create some interactive dashboards that you can drill around in.
How to do it…
To create a Vendor Scorecard, follow these steps:
- Start off by opening Excel, and from the Dynamics AX ribbon bar, select the Add Data option from the Design group.
- Filter out the tables to just the ones that begin with VendReview* and select the following tables:
- Then click on the OK button to add the tables to your spreadsheet.
- Select the VendReviewCriterionGroupRating tab that is created and add the Average rating field to the spreadsheet.
- Select the VendReviewCriterionRating tab that is created and add the Rating field to the spreadsheet.
- Click on the Fields button within the Design group of the Dynamics AX ribbon bar to return to data mode.
Then select the Refresh button in the Data group of the Dynamics AX ribbon bar, and select the Refresh All option.
Now you will be able to see all of the overall ratings for the vendors that you entered into Dynamics AX.
And within the other tab you will be able to see all of the detailed ratings.
- Now we need to link the two tables through Power Pivot. To do this, select the POWERPIVOT ribbon bar and click on the Manage button within the Data Model group.
This will open up a blank Power Pivot canvas.
- Return to the spreadsheet and select the VenReviewCriterionGroupRating sheet and click on the Add to Data Model button within the Tables group of the POWERPIVOT ribbon bar.
This will add the table to the Power Pivot model.
- Return to the spreadsheet and select the VenReviewCriterionRating sheet and click on the Add to Data Model button within the Tables group of the POWERPIVOT ribbon bar.
Now both tables will be within the model.
- From the View group of the Home ribbon bar, select the Diagram View menu to switch to the diagram version of the editor.
- Then select the Vendor evaluation criteria group.Name field within the Ax_VendReviewCriterionGroupRating table, and drag it over to the Vendor evaluation criteria.Name1 field within the Ax_VendReviewCriterionRating table to link the two tables.
- Once you have done that, you can close down the Power Pivot Model window and return to Excel.
- Now we will start creating the Vendor Scorecard within Power View. To do this, select the Insert ribbon bar within Excel, and click on the Power View button within the Reports group.
This will create a new worksheet within Excel with a Power View report embedded within it.
- Delete the default table that was created, and give your scorecard a title.
- Create a data panel for the Vendor.
- Then create another one showing the overall ratings by Evaluation Criteria Group.
- And finally, create a panel that shows the detailed Evaluation Criteria ratings.
- Now convert the Evaluation Criteria Group Ratings to a column chart.
- To report off the detailed ratings, we need to have the results as a number rather than the text selection that is used by default. To do this, open up the Power Pivot Model and create a new column called RatingValue on the VendReviewCriterionRating table.
- Set the field to be the left most character of the rating, and then change the data type to be a number.
- Now we can add some additional panels to the scorecard to break out the results.
How it works…
Selecting any particular evaluation criteria group will allow you to highlight different areas of the evaluation detail.
How cool is that.
In this example we have shown how to set up the Vendor Evaluation Criteria within Dynamics AX, and then use that data to create a simple Vendor Evaluation Scorecard.
This is just the starting point. Once you have mastered this, you may want to:
- Add date ranges to your scorecard. Dynamics AX will track all of the changes to the Vendor Ratings through effectivity dates.
- Push ratings automatically to the rating system through API’s
- Create a vendor rating entry form in InfoPath to make it even easier to update the ratings.
This isn’t too bad for an out-of-the-box function, and a little bit of Excel reporting
This is great, but I can’t connect the fields in Power Pivot as I’m getting an error “The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values.”
Jack, I may have missed a step – when I come across this it’s because I need to create a new field that combines all of the key fields together, making a key that you can then use to link the tables. Have you tried that?