Dynamics AX 2012

With document management in Dynamics AX 2012 it is possible to transfer data stored in Dynamics AX tables into documents of different types like Word, Excel or some other, different types.

You can use this feature if you have to export data from Dynamics AX into a document with standard text for which a SSRS-Report is not the best solution. An example could be information about a credit or debt which is not related to a specific invoice and is better send by a letter with standard text then by a collection letter related to an invoice.

So, let’s start by creating a new document type by using document type form (in: Organization administration -> Setup -> Document management -> Document types):



1. Create a new type and name it.
2. Choose the class (in this example a “Microsoft Word document”).
3. Set up the “Archive directory” (this is where the created documents are stored)
4. After this is done you have to create the Document table. In this case a Microsoft Word document table.

Press “Options” to associate the document template to a Dynamics AX table:



Here you associate the Dynamics AX table on which the needed data primarily depend with the document template.

In Dynamics AX 2012 the underlying database is highly normalized, which means that most of the needed data is spread over many tables.
Unfortunately document management does not accommodate this fact: By default you just can link only one table to the specified (main) table.

Fortunately there is a solution for this problem: Just create a view which contains all the tables needed to receive all necessary data.

Two important information:
1. When creating the view the top table of the view should be the table you have specified in document management.
2. You have to setup an relation between the specified table and the just created view



By clicking the “field”-tab you can start associating the table/view-fields to document-bookmarks.

Press “CTRL” + “N” to create a new row. In the “Data Table” use the name of the view as data source. Note, that you cannot choose the views name from the list of tables. You have to type in the view’s name manually or you can use copy & paste.

In the “Data Field”-field choose a field from the view. In the “Bookmark”-field enter a name for the bookmark you will later use in the associated document.



In this example I used the new “Computed Columns in Views”-feature of Dynamics AX 2012(as mentioned in a previous post) to do some calculations. The first calculates the difference in days between two dates. The second one calculates the discount which was deducted by the customer.

In Dynamics AX 2009 you had do this sort of calculations in the Word-document itself with the features provided by Word which were not so convenient than the new computed columns feature of Dynamics AX 2012.

If you have finished creating your Word-bookmarks to create these bookmarks in your Word template, too.



In my example I have extracted the customers address (top left bookmarks), the customers ID (“Kd.-Nr.” is German and short version for customer ID), the paid amount and the Payment date from Dynamics AX.

As you can see I have named the Bookmark for payment date “TransDate” like the Bookmark in the document type form in Dynamics AX 2012.
After saving the work you can check if it works:

You have to go to a form which uses the primary table of the document type as its own primary table to receive data. I you go to a form which uses another primary table you can use this document type but it will receive no Dynamics AX data.



Select a dataset in Dynamics AX. Go inside the form to File -> Command -> Document Handling.

A new “Document Handling”-form opens. Choose “New” and the name of the document type you want to use.



Dynamics AX 2012 needs a moment to extract the data. Word opens a new document based on the created template. (I am sorry, but I have no screenshot of a such a Word document filled with Dynamics AX data).

Restrictions of document types:
1. It is not a restriction of Dynamics AX document types itself, more a restriction of Word bookmarks: even you want to use a specific value multiple times you have to create a new bookmark in Dynamics AX and in Word, because you can only use a Dynamics AX bookmark with one Word bookmark.

Possible sources of errors:
1. If an Word-document with just the text but without the Dynamics AX data opens, please check if you are in the right form (which uses the table as primary table as in your document type). Otherwise you will not receive any data from Dynamics AX 2012.
2. An other error which can occur is, if the Document Type has more bookmarks than the work-document which receives the data. Here you have to check that the number of bookmarks in Dynamics AX document type and in Word-Dokument. The number has to be exact the same.

This is the third and last part of “What’s no longer possible in AX-reports”. Let’s start with some updates of topics I presented in Part I & II.

Update: Setting an element in a fixed position at the bottom of the report
As mentioned in Part I this bug was topic of a change request. Some days ago we became answer by Microsoft support. The change request was rejected. Reason: It’s a known issue of Reporting Services.

Update: 254 column limit in SalesInvoiceTmp (maybe every temporary table filled by a RDP class)
This bug seems to be a bigger one. The bug is confirmed. But why it occurs is still subject of an investigation by Microsoft support-technicians. Here it seems we will have a good chance that this bug will be fixed when it is finally found.

And here a new one:

Differences of displayed data in SSRS Reports in Visual Studio and Dynamics AX
I mentioned in a tweet before. But I think it is too important. So I raise this topic again. Data displayed in a SSRS report started in Visual Studio und a SSRS report started from Dynamics AX 2012 client may show different data.
The differences could be:
- numbers of rows displayed
- (sub)sets of rows displayed

Reasons are that on the one hand Visual Studio does not use the Reporting Engine of Reporting Services but its own. On the other hand each report, which is called via Dynamics AX 2012 client, gets its own unique id, which allows Dynamics AX to identify different reports which were called at the same time / while another report still was processed. This is of significant importance to show only the own data in a report and not data of different reports. If a report is started inside Visual Studio this unique id is ignored and datasets of different reports may be displayed in one report.



Conclusion:
Report Services itself are well known and part of SQL Server since version 2000 (as add on) and on a regular basis since version 2005. Of cause there were some issues when using Reporting Services for typical SQL Server tasks. But in general most time it was fun working with Reporting Services and SQL Server as main data source.

Working with Reporting Services and Dynamics AX 2012 one the other hand could be a little bit (ore some bit more) frustrating. Why? Quite simple! The field of operation is a different one: E.g. Designing a sales invoice with fixed positions of report elements was never subject of my work when working directly with SQL Server data but it is a common need when designing an invoice-template for Dynamics AX.
And that is exactly problem: The “old” Dynamics AX MorphX reporting engine was designed to fit this and most of the other typical Dynamics AX tasks like generation Invoices or other bills and receipts.

SQL Server Reporting Services (as delivered together with SQL Server) is designed to fit most of typical tasks like processing and displaying mass data in a clear and structured way.
Reporting Services delivered with Dynamics AX 2012 is still (let’s say over 80 percent) SQL Server Reporting Services. Lots of the old MorphX report features are missing. In the end this might cause some extra work when building a report which tries to fit the Dynamics AX users’ needs when generating an SSRS-report in Dynamics AX 2012.

My hope is that Microsoft realizes that for many common tasks in Dynamics AX 2012 Reporting Services in its current version is not adequate and a massive increase of functionality and different, more accurate behavior (e.g. when placing report elements) is needed.

A very useful new feature in Dynamics AX 2012 are Computed Comumns in views.

Computed Columns is one of the small and understated but highly usefull new features in Dynamics AX 2012.
Computed Columns in Views is an old but well established and useful feature in SQL Server since many versions. But this feature was available in Dynamics AX since version 2012.

Well, here is an example how I used Computed Columns in a View: In CustTrans-table there are two columns of interest. First “AmountCur” which represents the Amount which has to be payed. “SettledAmountCur” is the amount which already was paid. Now I am interesed in the difference between “AmountCur” and “SettledAmountCur”. Unfortunately there is no column in “CustTrans”-table which holds just this specific information. So I had to create a view with Computed Columns.

First I created a View “InvoiceDiscountDeclined”. Data source is “CustTrans”-table with the name ”CustTrans_1”.
Then I created an Method direct at the View with the name “DeclinedDiscount” (and also another one with name “DateDifference”, but this computed column is not in the scope of this article). Then I wrote the method below:



Important: The value returned by the method has to be string (str) and is computed as static method on the server.

For the calculation I used the “SysComputedColumn”-class of Dynamics AX 2012 which has lot of methods for various mathematical operations. I my example I used “subtract”-method which needs two table fields.

“SysComputedColumn”-class needs three different information:
1. Name of the view (red)
2. Name of the data source (blue). In my example the name is “CustTrans_1” and not just “CustTrans”!
3. The fieldname (together with the table-name; now we use “TransTable”)

In contrast to display-methods you just cannot drop the method into the fields list. You have to create a new computed column-field by right-clicking fields-note. Name the field. Set the “ViewMethod”-property to the name of the method (as in my example “DeclinedDiscount”).



When you press “Save”-button Dynamics AX 2012 creates a “Create View”-SQL-statement which creates a view in the underlying SQL Server-database. While synchronizing the view to the database, the method is called. After synchronization the method is not called any more, until your resynchronize the view to the database.

Well, that’s sounds good and indeed it is most time. But creating a view with computed columns could be tricky. My client crashed several times when I tried to change a just created view with computed columns.

But in the end the creation of the view with the computed columns had a happy end:



Some words why I have created this view and why I used computed columns to archive the goal:
In Dynamics AX document management I created a word-template which represents a collection letter send to clients if there are unsettled amounts open. (There are some special scenarios which cannot be handled by using “CollectionLetter”-SSRS-Report.)

In Dynamics AX 2009 you had to calculate the amounts with Word-Formulas and Bookmarks which was kind of tricky. Using a Computed column and just passing the calculated value to the Word-Document is much easier.

When designing reports I sometimes faced a strange problem. After testing a new SSRS-report successfully I am always checking the SSRS-report’s temporary tables for table fields which were created by me during design-process but which are not used at the end (e.g. because of design-changes).

If I find one or more of this obsolete table fields I just delete the fields because they are no longer needed for the report. In a second step I refresh the datasets of the SSRS-report in Visual Studio. (Without refreshing the datasets the report would run into an error because of the missing table fields.) As last step I deploy the SSRS-report again.

When testing the report just after refreshing the datasets and redeploying the SSRS-report in most times there is no problem. SSRS-report just executes as expected because the deleted table-fields are not needed by the report.

But sometimes I get a strange error message like this (sorry, only available in German language):



The error-message says that Dynamics AX was not able to run report because “PurchOrderCollectLetterIndic”-field in “PurchPurchaseOrderHeader”-table is missing.

Indeed I just had deleted the mentioned field from the mentioned table. (FYI: The table holds the data for Header of a Purchase Order.) I also just refreshed the datasets in Visual Studio and redeployed the report. But the SSRS report failed.

We tried (mostly) everything: Restarting the client. Restarting IIS. Even restarting AOS. But nothing helped. The only solution was to wait some hours because magically the error disappeared after some time.

Long story short: We missed the obvious…

…. Restarting SQL Server Reporting Services!!!!

Restarting Reporting Services just costs you some seconds. The worst effect which is caused by an restart is that for about one minute reports are not available or just a little bit delayed.

Now the question is why restarting Reporting Services solves the problem? The solution is simple:
Reporting Services caches report-data for some time and does not recognize the change in table-data. After some time of not running a SSRS-report Reporting Services shuts down most of its application pool. When requesting a new report, SSRS restarts its application pool. When doing this it loads the report data completely new and recognizes the change in table data. The result is that SSRS Report now runs perfectly fine.

Conclusion: If you face an error regarding SSRS reports in Dynamics AX 2012 which could be caused by refreshing-problems first restart SQL Server Reporting Services before you restart AOS or something other which may affect other users

This is the second part of “What’s no longer possible in AX-reports”. After finishing writing the first part I thought, that I just present some nasty, but not so important restrictions. Unfortunately Microsoft support just confirmed a restriction which could affect report design in a very bad way:

254 column limit in SalesInvoiceTmp (maybe every temporary table filled by a RDP class):
Some days before Christmas I was still designing Sales Invoice-report: After adding 20 new columns to SalesInvoiceTmp-table and implementing these columns in Sales Invoice-report no report-data were shown in the report any longer. Only label-data were displayed in the report. The behavior was the same when running report in Visual Studio or direct in Dynamics AX. First I thought that this behavior was caused by a programming-mistake by me but I found no reason why X++-code should have caused this. Then I checked SalesInvoiceTmp-table while running the report. SalesInvoiceTmp-table was filled correctly and all needed data were available. Because I had no clue what may have caused this I deleted the before added 20 new columns. Result: Sales Invoice-report worked correctly.
I started adding the columns one by one and after reaching 255 columns in SalesInvoiceTmp-table report just showed label-data again. After deleting one column report just worked fine.

I opened a support case at Microsoft support and just yesterday support confirmed that this behavior is caused by SalesInvoiceTmp-table with more than 254 columns. Support directly opened a change request because this restriction seems to be by design.

If one asks why I need more than 254 columns in SalesInvoiceTmp: Default SalesInvoiceTmp has about 200 columns when delivered with Dynamics AX, because there is just one dataset for header- and detail-data. In the end a programmer just can add approximately 50 columns for customization. 50 columns sound much, but if a client has just some special wishes to be implemented in Sales Invoice-report you may need all of these 50 columns and maybe some more.

SalesInvoiceTmp-table & default AOS settings:
Default maximum buffer-size in AOS settings is 24KB. Recommendation by Microsoft is not to raise this size. When starting customizing Sales Invoice report I had to add start some columns. But after adding some columns with an overall size below 1000 Bytes an error-message appeared, that maximum size of SalesInvoiceTmp-table was reached and columns need to be deleted.

The answer of Microsoft was fast and simple: Raise maximum buffer-size in AOS settings to 48 KB. This is the only solution to solve this problem and the only possible solution when additional columns need to be added. Because SalesInvoiceTmp-table with about 200 columns by default is very big and so it reaches the 24KB limit very fast.

Till now we found no negative impacts of raising the buffer-size. But according to Microsoft support another raise above 48KB may cause problems.

So the big question is: Why is Sales Invoice report just using SalesInvoiceTmp-table as its only dataset if this solution causes that much trouble? The question gets even more interesting when you check the other reports: Most of them have two datasets. One for header-data and another one for detail –data.
It took me some time to discover the reason, which is simple but also shows another problem of SSRS reporting engine:

Paper-size limits space for report-elements:
When starting redesigning Sales Invoice report I have just deleted the default report because it had a very ugly layout. I was setting paper-size to DIN A4 paper which has a height of 297 millimeters. When starting design there was plenty of space but later in the design-process and after adding more and more design-elements (which were printed one by another as unique elements printed only on the first page, continuing with reoccurring elements for invoice details in the middle section of the report and ending with unique elements only printed on last page) space went short.

So I reexamined the default Sales Invoice report. The solution used in default report is simple: One big table with only one column is used as a container-element. The table has some rows. In every row one report-element is embedded, mostly tables. With this solution it is also possible to print the elements one by another.

The reason why a table is used as container-element is that the table which is used as container element can be bigger than the paper size. When printing the report the paper size which was set in the report properties is used correctly and report-layout is not negatively affected by not wanted page breaks.
The downside of this solution is also the explanation why only one dataset is used: You have to assign a dataset to the table used as container element. But for the elements inside this table you cannot assign other datasets than that one used in the container element table.

This is the reason why Sales Invoice report just as one dataset. To my mind this is a very bad limitation, which makes report designers work a lot of harder.

Enough for today. It seems that a third part of “What’s no longer possible in AX-reports” is needed.

After developing SSRS-reports in Dynamics AX 2012 for at least six month here are the first impressions what is possible – and more important – what is no longer possible with the new SSRS reporting engine in Dynamics AX:

Header/Footer:
There is only one header, one body and one footer available per report. It is no longer possible to use a second header or footer (like Prolog/Epilog or Programmable Section) in a report.
This means it is no longer possible to use a secondary footer for an invoice summary with single positions one below the other. To make it worse: Hiding header or footer on a page only means that the content of the header and footer is not displayed. But: The space which would be consumed by the non-hidden header/footer (by setting the height) cannot be used in report and is displayed as a white space in the report.

If header/footer is only displayed on the first or last page of report the size should be set as small as possible to prevent waste of space on the pages where header/footer are not displayed.
Note: Behavior of header/footer vary between running report in Visual Studio or in Dynamics AX 2012 because in Visual Studio a hidden header/footer by Property “Show on first/last page” is not displayed at all and wastes no space.

Missing property “LabelTabLeader” (for automatic setting of colons after labels):
The property “LabelTabLeader” was an AX-standard till Dynamics AX 2009 to set dots and colons automatically after a label like this: “….:”. In SSRS reporting engine for Dynamics AX 2012 this property is no longer available. Even "better": According to Best Practices for designing SSRS reports colons are no longer officially supported.

Workaround suggested by Microsoft Support: Set colons manually with an expression like =":" after every label. Problem: Every default report has to be changed.
We asked for a change request at Microsoft’s SSRS/AX 2012-Team, because we do not have the time to edit every used default report manually. According to Microsoft support re-implementing the “LabelTabLeader”-property is not a simple job and still discussed at Microsoft.

Setting an element in a fixed position at the bottom of the report:
As mentioned above: There is only one report-footer available and because of technical restrictions you cannot size it as big as sometimes needed for example for an invoice summary where the single positions are one below the other. Common requirement is that the invoice summary is based on the bottom of the summary independently of the used space above the summary.
With Dynamics AX 2009 it was rather simple to achieve this requirement: You just used a programmable section only on the last page.
Because there is no second footer available we tried several other things to fix the sales summary at the bottom of the invoice-report but outside the footer. But we always failed.
Microsoft Support confirmed that there is no option in SSRS reporting engine available to fix an element on a specific space on report if it is not possible to place the element in header or footer.
This issue is also topic of a change request, which is also still discussed at Microsoft.

Paper Size of default Dynamics AX 2012 reports:
According to Microsoft Best Practices an AX 2012 report has to be designed in a way which allows the report to be printed in US letter and also in DIN A4.
Unfortunately most of the default reports are designed in a way which uses US letter paper size in its complete width without proper margins. Because the width of DIN A4 is 210mm and the width of US letter is 216mm a lot of white pages are printed in DIN A4.
This means that a lot of the default reports have to be redesigned to be used proper by users outside USA.

Update 06.02.2012:
Starr informed me that my statement, that all the reports are optimized for US Letter is wrong. He checked some more reports which were not optimized for DIN A4 or US Letter at all. Indeed he is right: I have checked some of more reports and some paper-sizes I discovered were not neither US Letter nor DIN A4. It seems that when many columns needed to be displayed width was set to fit all columns even the report was not really printable anymore.

He also asked what we do to make the reports printable: Our solution is simple but also time-intensive: Together with our client we identified the most important reports which need to be printed and all these reports will be redesigned. Not because of the wring paper-sizes but also because of the “beautiful” design of the standard-reports.

This was the first part of “What’s no longer possible in AX-reports”. Part 2 will follow soon and there are still some more nasty restrictions of SSRS for Dynamics AX 2012 which were unknown in Morph X-reports for Dynamics AX 2009 and prior.

Cumulative Update 2 for Dynamics AX 2012 is available since a couple of days.
According to the list in this Microsoft support article CU2 includes over 70 new hotfixes plus the hotfixes of CU1.
As always you need to have access to Partner Source or Customer Source if you want to download the Cumulative Update.

Like CU1, CU2 can be installed as slipstream installation, when installing Dynamics AX 2012 on e.g. a client.

First impression:
We have installed CU2 some days ago on our development system. Even though some bugs were fixed a lot of more seem to be unfixed.

Moreover we have the subjective impression that AOS tends to crash more often after installing CU2.
Which actions cause these crashes we cannot say exactly by now. We have some ideas but we need to test some more. We are in close contact to Microsoft support.

Second impression / update (15.12.2011):
Here are two possible reasons which can cause a crash of the AOS:
- Synchronization of AOT while other developers develop on the same system
- Closing the Client (by using the “Close”-button ) while Dynamics AX 2012 is processing and not responding

Our workaround to prevent unnecessary AOS-crashes:
Before a developers starts a synchronization of AOT he informs the other developers. While synchronization of AOT the other developers try not to do any tasks which may cause other parallel synchronization-tasks of AOT.

Some weeks ago I was asked to install a Dynamic AX 2012 as complete local installation for one of our consultants. Completely local in this case means, that all components of a Dynamics AX 2012 installation (SQL Server, AOS and client) are installed on a laptop with Windows 7 Professional as OS.

Even though the AX setup-assistant indicated that Reporting Services were installed correctly, default reports were not deployed at all. I tried to deploy SSRS-Reports manually in AOT and also via AX 2012 management shell. Both attempts failed with the following error message:

The network path was not found. If User Account Control (UAC) is enabled on the machine, close the application, right-click the application, and then click Run as administrator.

The error-message suggests the issue to be caused by not having administrative permission when deploying the reports. But that was not the case.
After opening a support case at Microsoft Dynamics AX support it took us over two weeks to find the real issue:

In opposition to Windows Server 2008 R2 in Windows 7 the "Remote Registry”-service is not enabled by default. After enabling and starting the service via Start -> Administrative Tools -> Services deployment of AX 2012 default SRS reports was successful. I suggest changing Start type from Manually to Automatic.

Note: Starting the necessary SQL Server-services and in the end the AOS causes a massive lengthening of the startup-time of windows. This is the reason why we have decided to refrain from local installations on laptops. Instead we will setup a Virtual PC which can be easily used on our consultant laptops without slowing down the daily startup of Windows 7.

Today I faced a problem that took me more than two hours to solve.

I had to install a Dynamics AX 2012 Development-Environment with two independent Servers. Both are Windows Server 2008 R2. One hosts a SQL Server 2008 R2 with Report Services and Analysis Services. The other one hosts the AOS and the Dynamics AX 2012 Help Server.

“Help Server” is a new feature of Dynamics AX 2012. It hosts the default help system of Dynamics AX 2012 as well as it gives you the possibility to add your own documentation. Of course this was also possible with Dynamics AX 2009. But according to Microsoft the new Help Server should be simpler.
We will see. But after today’s installation problems I have my doubts.

Back to the problem and to the hard- and software we use.
Both Servers were maiden-like. Meaning: The one hosting the AOS had just a Standard-Installation of Windows Server R2 with all Patches and Service Packs when starting installation. The second one hosting the Database and Reporting & Analysis Services had an additional installation of SQL Server 2008 R2 with SP1. “Help Server” needs IIS running. So IIS was installed on the first server

Long story short: After a more or less problem-free installation I’ve added the users as the last step of my installation-duties. Fewer problems than expected – just a prefect day. That was what I thought.
Then I checked Help Server… and got an error-message that AX 2012 was unable to connect to Help Server Website.
After checking the URL direct in an Internet-Browser I got the following error-message:

Could not load type 'System.ServiceModel.Activation.HttpModule' from assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.TypeLoadException: Could not load type 'System.ServiceModel.Activation.HttpModule' from assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.

A quick, first Google-search showed that this is a common error-message linked to .NET.
Different token-numbers point to different problems. After more than one hour of searching I realised that the day was not that perfect I first thought. Then I found a helpful forum-post which finally led me to the following Microsoft Support-Page: http://support.microsoft.com/kb/2015129

The cause of the problem in short words:
This issue occurs because the Applicationhost.config file for Windows Process Activation Service (WAS) has a section defined that is incompatible with the .NET Framework 4.0.

The resolution:
Run the following command in command shell:
aspnet_regiis.exe /iru

The Aspnet_regiis.exe file can be found in one of the following locations:
(32-bit OS): %windir%\Microsoft.NET\Framework\v4.0.30319
(64-bit OS): %windir%\Microsoft.NET\Framework64\v4.0.30319

Just try the solution provided in this article maybe it helps you, too.

To additional tips:
1. Execute command shell as Administrator.
2. Restart IIS with the command IISReset