Creating SSRS-Reports in Dynamics AX 2012 – What’s no longer possible in AX-reports

Creating SSRS-Reports in Dynamics AX 2012 – What’s no longer possible in AX-reports (Part I)

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.

Creating SSRS-Reports in Dynamics AX 2012 – What’s no longer possible in AX-reports (Part II)

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. 

Creating SSRS-Reports in Dynamics AX 2012 – What’s no longer possible in AX-reports (Part III)

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.