Dynamics AX
  RSS Feed  LinkedIn  Twitter
Want to turn you're data into a true asset? Ready to break free from the report factory?
Ready to gain true insights that are action focused for truly data informed decisions?
Want to do all of this across mutliple companies, instances of Dynamics and your other investments?
Hillstar Business Intelligence is the answer then! (www.HillstarBI.com)

Hillstar Business Intelligence for Microsoft Dynamics AX and NAV on Mobile, Desktop, Tablet

Let us prove to you how we can take the complexity out of the schema and truly enable users to answer the needed questions to run your business! Visit Hillstar Business Solutions at: www.HillstarBI.com

Tuesday, October 25, 2011

AX 2012 - Excel Add-in Revisited

With the release of Microsoft Dynamics AX 2012, came some great new ways of achieving business requirements, and user experiences. One of those is the Excel Add-in for Microsoft Dynamics AX 2012. I first wrote about this topic, back in June, in which you can find here: Microsoft Dynamics AX 2012 Excel Add-in. This has been a hot post actually, with a lot of comments, and a lot of visits.

It's rightfully so that it should be, being that it's new, and there is some confusion on how to do some of the more complex data sets, within AX, that go beyond just a simple table, which that first post covered for us. With that in mind, I thought I would help clear the air a little bit, in how the Excel Add-in is actually very flexible, and something to watch out for in December from Microsoft on this topic.

First, lets look at the starting point, in which we can start to work with more advanced data sets in AX. This concept, is enabled through the use of document services. This is the way, in which complex data sets in AX are exposed internally and externally of AX 2012. This is true, even for the Excel Add-in.

From the excel point of view, what we are talking about is the Add Data ribbon button, and when the drop down appears clicking on the Add Data, instead of Add Tables.

When clicking on this, for the first time, if you've not setup any Document Data Sources within AX 2012, your selection options will be blank. Now we have talked a little bit about this new concept of Document Data Sources, in the following post.: AX 2012, PowerPivot and a Dash of OData Feeds

In that above post, I covered with you how to enable Excel 2010 PowerPivot to work with OData Feeds from AX 2012. This was enabled through having Query Type of Document Data Sources enabled through the Organization Administration module, Setup, Document Management and finally Document Data Sources.

Here we can also add a document data source, of type service. These services must be document services and extend from the AIF Document Services types. These can not be custom services that do not extend as part of the AIF document services.

With that in mind, we can add a couple of document services, to our list, from within that form, as seen below.

Now that we have these services as document data sources within AX 2012, we can now go back to Excel 2010, within the Excel Add-in and click on our add data, and see our two new services we can make use of.

With this, now we can work with more complex data sets within AX 2012. This also means, that Office 2010, very much is a major part of the user experience in regard to AX 2012. This also means, that some very complex data set needs, can be accomplished through the use of Query Objects, and Document Services that wrap those query objects to expose them internally and externally of AX 2012.

With this said, there are some issues, and area's to watch out for. Basically, there are some super normalized data sets, like customers, vendors, etc. To help address those needs, Microsoft will be releasing in December a resource kit, that will contain artifacts that help enable the use of the Excel Add-in, for the master data, that has some super normalization aspects to it, that make it a little harder to work with from Excel. The basic concept will still be used that you see above here, that will be enabled with staging tables that the process will still use document services to enable.

I hope this helps further explain the value of the Microsoft Dynamics AX 2012 Excel Add-in and how it can be used right now with even more complex data sets, than just simple tables. You enable this through the use of Document Services. Also, it's very important to understand that Microsoft will be releasing that resource kit to help out with some of the super normalized master data elements. Finally, you can create your own document services, that wrap your own custom data sets, or query objects, that in turn can be consumed and made use of in a bi-directional nature through Excel. This is a huge benefit to customers of AX 2012, and something that needs to be considered when thinking about designing and developing scope for customer.

That's all for now, check back soon as more to come. Till next time!

"Visit the Dynamics AX Community Page today!"

Labels: , , , , , , , , , ,


Blogger Marek said...

Hi. I was wondering, if it is possible to use the "Export to Microsoft Excel" funcionality to export the contents of a table to Excel, edit the data in Excel and then publish it back to AX. When I use this function, I can still sync the Excel sheet with data from AX (one way), but I cannot get it to publish the data back to AX. Thanks for any help.

7:06 AM  
Anonymous Anonymous said...

Any word yet on when that resource kit is coming out from Microsoft, of where we can find it?

2:09 PM  
Anonymous Anonymous said...

Have anyone tried the General journal entries?

I can not create a new journal and publish it? (Running AX 2012 CU2)

1:47 AM  
Anonymous Roger Grove said...

I am keen to find out more about the Excel-Add-in Resource Kit. Has there been any more news? Where did you find out about it, I can then follow up myself.

3:12 PM  
Blogger Dax Moldova said...

It is possible to modify number of max rows imported into table, for my case this number is 275(I can import 275 rows of data to table at once) can I modify this number to 2-3 thousands records?

5:27 AM  
Blogger Mohamed Hathoot said...

I lost in how to import Item with variants Configuration, SIZE AND COLOR .
please do u have any clue how to complete task with excel Addin ..
if it is not possible with Excel addin how can I imgrate item with variants ?

1:58 AM  

Post a Comment

<< Home

Copyright 2005-2011, J. Brandon George - All rights Reserved