Queries are like mining for gold, because everyone is searching for the motherload of data that is hidden within Dynamics AX, and once they find it, they mine it until it dries out and they have to start prospecting again. Also, the secret maps to that data are hidden away, usually on someone’s own personal hard drive, and are rarely shared with other prospectors. Office 365 and Power Query changes that because it allows you to share any of your queries with everyone within the organization, allowing everyone including the non-technical users that would never create a query.
No more claim jumping, there is enough data out there for everyone to mine.
If you want to publish out data to the users so that they can create their own reports, or run their own analysis, there is in a simple and secure way built into Dynamics AX. You can register your queries as Document Data Sources and then they automatically become available through the OData service within Dynamics AX. Additionally, all of the default security that you put in place around the data will be respected by the service because you are using Dynamics AX to create the query.
No more ODBC connections, or even worse… unsecured Access databases.
Create Reports directly off the Dynamics AX Cubes through Excel
Excel is probably the number one reporting tool because it is on everyone’s computer, and also because it is so easy to manipulate the data and create your own analysis, charts and tables. In the past though, the more technical users have had a slight advantage over the general users because they knew the secret table combinations that allowed them to piece data together directly from the database, giving them better insight and reports, whilst the less technical users had to resort to cutting and pasting data, or transcribing pieces of information from other reports into Excel which made their reports tedious to build and also prone to errors.
Those days are over though because Dynamics AX comes pre-delivered with a set of reporting cubes that have already simplified and summarized the data from you and you can connect to it directly from Excel. This makes reporting so much easier for everyone, and also makes sure that everyone has the same results when they create their reports.
Exporting Data to your client version of Excel when using Remote Desktop
Just because you are using a remote desktop to access Dynamics AX, doesn’t mean that you have to do everything there. One example of this is that you can choose to export data to your local version of Excel rather than to the version of Office that may be running on your remote desktop. All you need is to change one of the options within the Dynamics AX client.
This allows you to work with the exported data after you are disconnected from the remote desktop, it allows you to use any additional extensions that you may have installed on your local machine within Excel, and most importantly saves you the hassle of having to cut and paste the worksheet from the remote desktop over to your local machine.
Export to Excel when there is no Ribbon Bar
Not all of the forms within Dynamics AX have the ribbon bar enabled with the Export to Excel icon handy for you to use when you want to gram a copy of the data, but that doesn’t mean that there is no way to quickly get your data moved over to a linked worksheet.
All you need to do is select the Export to Microsoft Excel menu item from the Files menu, or for those of you that love shortcut keys, just press CTRL+T and Dynamics AX will create an Excel Workbook with all of the data from the form populated in it.
Add additional Dynamics AX fields to worksheets after exporting to Excel
Exporting to Excel from Dynamics AX is probably one of the most used features for all of the do-it-yourselfers out there because once the data is in Excel you can create your own reports, dashboards, and analysis in a tool that we are all very familiar with.
But just because Dynamics AX exports the fields that are showing on the form does not mean that you have to settle for that data. Using the Dynamics AX Add-In for Excel, you can see all of the fields that are available within the table or query from Excel and add in new columns on the fly, making your Excel queries even more useful.
Use Export to Excel and the Excel Add-in for Dynamics AX to create Reports in Excel
When you have the Dynamics AX Add-In for Excel installed, the Export to Excel function within Dynamics AX automatically links the data table that you exported back to the data within Dynamics AX. This means that you are able to refresh the data at any time, returning back the latest information without having to re-export from the client.
Once you have created the linked worksheet, you can build your own dashboards and reports from it within Excel and they will always be up to date.