Extending Microsoft Dynamics AX 2012 Cookbook: This is a brilliantly accessible book, packed with practical examples, that's perfect for business professionals who want to make more of the advanced features of Dynamics AX to save money and increase management efficiency.

Power View is a new reporting tool that was introduced by Microsoft with the release of Microsoft SQL Server 2012. Unlike many of the other reporting tools that are available, this one is designed so that users can create their own dashboards and reports without needing a lot of help, making everyone just a little more productive. Gone are the days where you have to export data from Dynamics AX to Excel, and then create a one-off dashboard when you want to analyze some data. Now you can create a dashboard in Power View, save it, and return to it any time you like.

With the R2 release of Microsoft Dynamics AX 2012, Power View was also embedded directly into the forms allowing users to create dashboards and reports on the fly without even knowing that they are using Power View.

In this chapter we will show how you can use Power View with the Dynamics AX 2012 R2 to create reports, how the users are able to save the reports for others to use, and also how you can export the reports...

The Dynamics AX 2012 R2 release has links to Power View embedded directly into the application, allowing the users to create their own reports and dashboards with just a click of a button.

In this quick recipe, we will show how you can quickly create a report directly from the Dynamics AX application.

With the Dynamics AX 2012 R2 release, there are additional reporting cubes hidden within SharePoint that are not currently linked to the main forms. If you find them, then you are able to create Power View dashboards and reports.

In this example we will show how you can use the Sales cube to build a more elaborate Power View dashboard.

Although creating a Power View dashboard is a simple task, you probably don't want to recreate the same report over and over again, and others may be interested in using your dashboard as well.

In this quick recipe, we will show how you can save your dashboards back to the Power View Reports folder in Dynamics AX so that you and others are able to access the data at any time.

How it works...

Once your dashboard is saved, you can access it from the Power View Reports folder within your Role Center content:

With Microsoft Dynamics AX 2012 R2, a new Web Part was added that allows you to easily embed your Power View dashboards into the Role Centers. Any report or dashboard that you create may be added this way, and users are able to view and filter the information directly within the Role Center, or pop the dashboard out of the Role Center and edit the report on the fly.

This recipe will show you how to customize your Role Center and add a Power View report or dashboard.

If you spend your days recreating the same presentation, by refreshing the data in reports from Dynamics AX and then reformatting the information, then Power View has a very cool feature that you will be able to take advantage of that allows you to export any of the dashboards that you create to PowerPoint.

This is not your ordinary export that just takes a screen capture of your current view and then pastes it into your presentation, instead Power View is embedded into your presentation. If you have security to access the data, then your PowerPoint becomes an interactive dashboard whenever you enter into presentation mode. If you deliver the presentation to someone who does not have access to the source data, then the reports look just as they did when you last updated the data.

You can use this to create internal review presentations, or even start with a template and then filter the data individually to be delivered to customers or vendors for...

Up until now, we have just been creating dashboards and reports within Power View from the pre-defined cubes that are delivered with Dynamics AX 2012 R2 or later. You are not limited to those data sources for reporting though, and you can use Excel and PowerPivot for Excel to create your own data sources that you can report off. This is also an option that is available to all versions of Dynamics AX, so if you have not yet upgraded to the R2 or higher releases, then you can still take advantage of all the benefits of Power View for user reporting.

In order to use Power View in this way you need to have a PowerPivot Gallery site configured in SharePoint. This is where you will store your data sources, dashboards, and reports that you create.

If you do not currently have a PowerPivot Gallery site configured, then the following recipe will step through the process of creating the site in SharePoint.

You can create Power View reports directly off the data within Dynamics AX by building your own personal data sources through Excel and saving them to a PowerPivot gallery in SharePoint. This gives you the flexibility of accessing data that may not be in the existing Power View cubes, including custom tables and fields that you have added, without having to become a DBA and tweak the reporting cubes themselves.

In the following recipe we will work through a simple example of how you can use the PowerPivot add-on for Excel to access inventory information from Dynamics AX, and then save it to a PowerPivot gallery in SharePoint so that we can report off it.

Getting ready

Before you are able to create a PowerPivot data source, you need to make sure that you have the PowerPivot add-on configured in Excel. If you have it installed, then when you open up Excel, there should be a PowerPivot tab in the ribbon bar:

If you don't have it installed, then follow...

Once you have PowerPivot data sources published to your PowerPivot gallery in SharePoint, they become linked to Power View, and you are able to create dashboards and reports from them. Users can be given access to the PowerPivot gallery directly, or they can be given links to the Power View report designer so that they are able to create their own ad-hoc reports at any time.

In the following recipe we will show how you can create Power View reports directly from the PowerPivot gallery.

If you want to make your Power View dashboards and reports even more useful to the users, then you may want to add links to them directly within your Dynamics AX forms. This does require a little bit of X++ code, and a little bit of configuration in the AOT.

In this recipe we will show how you can easily add a button to the ribbon bar of an existing form that will link directly to a Power View report that you have, and even filter out the data so that you are just seeing the data that is related to the current record that you are in.

Power View and PowerPivot are tools that are simple enough for almost everyone to use, and allow you to quickly build reports and dashboards that are useful. It becomes even more useful to the power users since they can also create their own data sources within Excel, a tool that everyone is familiar with using.

Once you start creating dashboards, you will discover that there are a lot of cool features that you can take advantage of. You may want to try:

Also, you don't just have to use Power View within Dynamics AX. Since...