AX 2012 Excel Add-in - RPC Issue

I hope everyone is doing well, and into the Dynamics World, on this fine Tuesday. We will continue our focus on EP tomorrow, but today, I wanted to spend a little time and write about a specific issue when using the Excel Add-in for Microsoft Dynamics AX 2012. What I'm talking about, can happen in a few different possibilities. Specifically however, and more to the direct error message. If you have Failed records when using the Excel add-in and the error message for each record is very generic, you should then move towards looking at the Application Event Log, where the Excel Add-in is being executed.



Within the Application Log, you will most likely see an RPC Exception 1702 or 1700 or 1704. Now the Excel Add-in is suppose to be connecting and communicating through WCF protocol for the Query Service, and other such services? Why RPC? Well there is a part of the .Net BC still used, from what I've found - needs clarification - with this process.

So how do we reach this error? Well most likely something has changed about a table your trying to access, or a Data, ie: Query or Service, that your trying to consume. For example you might have added a new import staging table to your instance, saw it fine in the Add Data, Add Tables list.



However now that you go to publish the data, from which you have built the excel table with - all records fail? This is a sign, that a Full CIL needs to be executed on the target instance in which the Excel Add-in is pointing to. This has been the fix for such issues, I've come across - every single time!

In short, running into errors with the Excel Add-in, specifically RPC errors in the Application Event log, then perform a full CIL compile on the target instance. Doing so should get you past such issues, and should allow you to proceed using Excel Add-in and your changed or new table - for loading data.

That's all for right now, I will leave you with a link to the past post I did about the Excel add-in.: AX 2012 - Excel Add-in Revisited. We will continue the EP and BI paths, as well as I have a slew of highlights, book reviews, and spotlight post coming soon. Along with a new interview from a Microsoft Executive, to help close out FY12 for Microsoft. So check back soon, and often. Till Next Time!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012 Personal BI - By Example - Customer Aging III

Well I hope everyone is having a great start to a brand new week! I wanted to write today to continue our focus on the PowerPivot Example for personal BI with AX 2012 - CustomerAging III. For those that are just now getting into this series, the following are links to the previous two post for this topic.

• AX 2012 Personal BI - By Example - Customer Aging I
• AX 2012 Personal BI - By Example - Customer Aging II

Last we left off, we had placed the Open Balance in the values area, as well as having created a computed column that concatenated both the AccountNum - Name, to give us our Customer column. We placed this in the row labels, and we have now what you see in the screen shot below.



Now lets continue this effort, by adding a column label, that will give us our buckets of aging, or aging intervals. In order to do that, we will need to identify from the PowerPivot query, what field best fits this need. Since I know this query well, I know that the field.: StatRepIntervalLine_1_Heading is what we are looking for.



Now that we have placed this as our Column Label, lets take a look at the report area, and see what our data looks like with the change.



As we can see from the above image, we now have our aging category, or buckets - that being made up of the StatRepIntervalLine datasource from the query within AX 2012. This further split up the Customer account information, so we have better insight into what our customer aging really looks like.

Now that we have our aging intervals, lets continue by adding both hortizonal and vertical slicers. These slicers are used to slice the displayed data in our report, so that we can get a dynamic glimpse by the values in which we choose to slice by. This helps make the report very dynamic and interactive within the scope of the workbook itself. Our targets for these slicers are "Customer Group" or CustGroup_1_Name for the vertical slicer, and "Aging Category" or StatRepIntervalLine_1_Heading for the horizontal slicer selection, as seen below.



Like the Value label before, lets change the name of these two slicer columns so they help make the report have a friendly consumption experience. Doing so, should yield an outcome, similar to what you see below.



Now that we have our slicers, and can look at our Customer Aging data in several different ways, let continue to finish out this report, by adding some conditional formatting. With this formatting, the idea is to take and place some visual cue to the data itself. This conditional formatting, as the name implies, will change based on the conditions we set forth.

This Conditional formatting can be added to any Excel workbook, and you launch the setup of such formatting from the Home ribbon within Excel 2010. Below is a screen shot of where to launch this from.



With this, the idea is to give some sort of data based formatting, that gives a nice visual cue to the report. It helps the numbers stand out, so that we can further have a truly refined Customer Aging report, from AX 2012. In adding the conditional formatting, you should end up with an output similar to that we have below here.



Now that we have a nice report, we have basically finished our first series on Personal BI for AX 2012 with PowerPivot. Notice that we actually left the scope of AX 2012 in the first part of this series. Since then we have been working 100% in the scope of PowerPivot & Excel 2010. The Query, when we imported the data, created a snapshot of the data and actually created a cube for us.

This data lives within the workbook, which can support up to 4 GB of size for imported data. The next steps in this would be to take this report and publish it to SharePoint, that would start the next phase in the BISM, which is a focus on Team Based BI, or collarbative BI.

We will pick back up on this series doing just that, and adding this as a WebPart, so we can place on AX 2012 role center pages. We will see how no development is needed, to publish this new personal BI artifact, the excel workbook, to SharePoint. Further we will see how little effort it takes to consume this workbook as a webpart and place it on an AX 2012 Role Center page.

Well I hope everyone has a great week, and check back soon as more to come. Till Next Time!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

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!"