Category Archives: reporting

Optimizing your Aged Accounts Receivables Report

Overview
Doing numerous upgrades from an older version of Navision to NAV 2015 and 2013, one common complaint is how slow the reports are running. This is especially true for larger reports like the Aged Accounts Receivables, Aged Accounts Payables , Inventory Valuation, etc.

The old reports that took a long time, such as the Inventory Valuation report, will still take a long time. It doesn’t matter what version you go to. However, there are some reports that used to be quick, but is slow after the upgrade.

One of these reports is the Aged Accounts Receivables report (Report ID: 10040 – Aged Accounts Receivable).

The Breakdown
CAUTION: I’m about to get “programmer”. If you want the faster report, just skip down to the bottom and download the object.

Removing the Data Type of Column from the report, we get the following DataItem that the report loops through:

AgedReceivables

On initial look, the report looks simple enough. There are 3 data items:

  • Customer – The report loops through the customer record to see which customer we need to calculate the aging for
  • Cust. Ledger Entry – For every customer record it finds, it will loop through the all of the customer ledger for that customer. For any customer ledger that has a remaining amount, it’ll put it into a temporary table
  • Integer – This dataitem loops through the same records that are inserted into the temporary table on the Cust. Ledger Entry dataitem and summerizes the information to display in different aging “buckets”.

The Problem
The reason why this report is slow is if you check the DataItemTableView property on the Cust. Ledger Entry dataitem, you’ll see that the report is looping through ALL of the customer ledger for that customer.

NoFilterSet

This report will run fine if you’re A/R aging is small. However, this report will get slower as time progresses with more transactions. Worst, it’ll consume all the memory on the server and force you to restart.

The problem becomes real apparent when you have EDI customers that are running hundreds of invoices per day.

The Solution
The idea of the original A/R aging report is correct. Basically, look at the remaining amount based on the date criteria; if there is a balance, then it goes into the calculation.

The problem is that it’s not running any type of filters to exclude old transactions that has no relevance in our calculation.

To address the performance problem, here are the main things we will need to do:

  1. Look at only transactions that are marked as Open
  2. If the report is to be backdated, look into the only the history that pertains to the date criteria

First thing we do is to set the proper DataItemTableView property with the filter of Open.

FilteronOpenOnly

Then we need to add a new Detailed Cust. Ledger Entry dataitem to look at the application history of our A/R transaction:

The Property:

DCLEProperty

The Code:

AddDetailedCustLedger

Basically, we’re limiting the reads of the database to only open transactions and the subsequent A/R applications from the Aged as of Date set on the report.

Here are the report objects and the text file for your reference:
OptimizedARAging

Conclusion
Not sure what the developer at Microsoft is thinking when programming this report. Aged Accounts Receivable/Payable is one of the most data intensive reports next to the inventory valuation. Reading through every record just does not make sense.

Yes, it’ll work in the short run, but give a few years and the report will slow to a crawl, which is already experienced by customers upgrading.

Dynamics NAV 2009 Reporting Book

Let me first start by saying that I’m not a fan of doing book reviews. In my very humble opinion, most books about Dynamics NAV that are released, although a good read and provides some valuable information, does not bring much INCREMENTAL value to the NAV professional. What do I mean by Incremental value? It’s the money and most importantly time and attention, that you spend reading the books verses what you get out of it.

Most information written can be obtained for free on the Customersource or Partnersource website. In fact, the training materials that can be downloaded for free goes into greater detail than what’s covered in the books. Well, not exactly free. I’m assuming if you can access Customersource, you’re a customer that’s current on the Enhancement Plan, or if you can access Partnersource, you’ve bought a Partner Plan.

To be a good NAV professional, I don’t think there’s a good substitute than what’s on those training manuals or even on the Application Designer’s Guide that comes with your product installation disk.

Reading the training manuals is, personally, where I get the most incremental value on becoming a good technical and applications person.

In addition, there are Navision forums such as http://www,mibuso.com and dynamicsuser.net that frequently has contributors to give you ideas on a particular problem you’re having.

My general feeling is, why pay for something when you can get the better stuff that you’ve already paid for (Customersource) or for free (online forums)?

Having said that, recently, I’ve been granted a sneak peek as a reviewer at a book that a fellow NAV colleague, Steven Render, has been writing. The book is titled Microsoft Dynamics NAV 2009 for Professional Reporting. You can find the book here:

http://www.packtpub.com/microsoft-dynamics-nav-2009-for-professional-reporting/book

From my previous blog posts, I’ve been pretty disappointed at the lack of documentation and training material available for the NAV 2009 RDLC reporting feature. The only document released by Microsoft is the training manual #80146 and the information that you can gather from NAV blogs.

Training manual #80146 is pretty good at going through the RDLC for Navision, but that’s about it. By moving to SQL, it really opens a whole new world of reporting possibilities, other than the RDLC. What this book does is that it goes through and explains the different options out there and shows you how to go about creating reports based on these different options.

I understand that there are books out there about RDLC and SQL Reporting Services, but most of the information on those books does not apply to me as a Navision professional. This is why this book appealled to me because it was a Navision book that talked about reports.

I highly recommend this book, until Microsoft can release additional resources for download on Customersource on reporting and truly take advantage of the “Microsoft Stack”.

Navision RDLC reporting – SetData and GetData – Why It Is REQUIRED

Ever wondered why there’s no tutorial on how to create a Sales Order report from scratch in the RDLC? The reason is because it takes a LONG TIME! Even for an experienced developer, it takes a long time. As I previously mentioned on my article, Microsoft really needs to address this in future versions.

The reason for SetData and GetData is not because of performance reason as stated in the manual 80146B. For additional information on defining SetData and GetData, please look here.

For multiple pages, the header data is dependant on whether there are lines. If you’re printing multiple form type reports like the sales order and you do not use the SetData and GetData, the header will only link to the lines displayed on the first page of the report. So this means that if your sales order is printed to the 2nd page, the header information will all disappear.

Here’s an example if you create a report without using the SetData and GetData logic:

This is the first page. As you can see, the header displays nice and pretty. I used whiteout to remove some sensitive information in Paint.

Now this is what happens when you print the 2nd page:

No, it’s not an error. You’re seeing it correct. It’s a blank page. I didn’t even have to use Paint to remove any information.

The reason why the 2nd page is blank, again, is because the link was done only on the first page on the header. If the report goes to the 2nd page, the link is essentially gone, therefore, no value is loaded and so nothing is displayed.

 

So when you create a report that has headers in forms (sales order, quote, etc). You need these:

Shared Offset As Integer
Shared NewPage As Object
 
Public Function GetGroupPageNumber(NewPage As Boolean, PageNumber As Integer) As Object
    If NewPage
        Offset = PageNumber – 1
        NewPage = False
    End If
    Return PageNumber – Offset
End Function
 
Public Function IsNewPage As Boolean
    NewPage = True
    Return NewPage
End Function
 
Shared HeaderData As Object
 
Public Function GetData(Num As Integer) As Object
   Return Cstr(Choose(Num, Split(Cstr(HeaderData),Chr(177))))
End Function
 
Public Function SetData(NewData As Object)
    If NewData <> “” Then
        HeaderData = NewData
    End If
End Function

 And you need these controls with the proper code:

We spent hours and hours trying to get our report header to print on multiple pages. Don’t make the same mistakes we did!

EDIT – Thanks to Steven for pointing this out. It turns out that this was mentioned on the 80146B manual on Chapter 3 page 35. Shows you that you shouldn’t go through the 300+ page manual quickly!

Open Suggestions to Make Navision RDLC Reporting More Efficient

Working with the new RDLC reporting for a few months now after the NAV2009 release, I thought I would like it as I work with it more. Sad to say, that hasn’t happened yet. It’s like being in a bad relationship and hoping some day that it’ll magically get better. And from the general consensus from NAV forums like mibuso.com and dynamicsuser.net, I’m not alone.

The new RDLC reporting tool in Visual Studio makes creating new reports is a lot longer (and tougher) than if you were to create the same report in the C/side report writer. For NAV, we’re all about efficiency. This RDLC reporting tool is by no means efficient. The report writer should be simple and can be easily picked up by non-developers (i.e. end users), RDLC reporting tool is not.

When I saw the demo for the capabilities of the new reports, I must admit I was drooling. If only I had known at the time that I would have to pull all of my hair out to realize only a portion of those capabilities.

Microsoft said that NAV2009 (Navision 6.0) was going to create new opportunities for partners, I’m not sure who they’re referring to. For companies that create external reporting tools like Jet Reports or Pivotier, I have to say this RDLC reporting tool benefits them the most. They are probably laughing all the way to the bank. But for the rest of the general public, I’m not so sure what kind of opportunties they’re talking about…

So in an attempt to make this relationship better and easiers for partners and end users alike, I would like to make 2 suggestions on how Microsoft can help us improve the relationship we have with the new RDLC reporting tool. Yes, just 2 suggestions.

Suggestion #1:
If Everything Has to be in Table Format, Why Not Just Get Rid of the Layout Designer?

For this suggestion, I’m going to use report 10048 (Customer/Item Statistics). This is one of the more popular reports for customers to analyze who bought what.

Before a standard C/Side report can be converted into the RDLC format, you must first create the sections in the C/side report designer. This is what it looks like on the Section Designer:

The report printed from this is pretty straight forward:

It’s not perfect, but a novice Navision developer or the ned user can easily go in and modify the formating easily and add and remove fields very easily.

Now let’s look at the RDLC layout for this same report:

The output is almost exactly the same. If you save this report to Excel, the formatting would still be messed up. So all the features stayed the same and all of the problems came along with it. Other than the ability to save it as PDF, there’s no benefits that can be seen.

This report is not what the customer signed up for and it’s not what we’ve all see in the demos. I spent a few hours creating the proper layout so the report can take advantage of the features described in COURSE: 80146B – Report Design in Microsoft Dynamics NAV 2009. Here’s what I came up with:

This is the RDLC layout that I had to create in order to generate a report like that:

In order to take full advantage of the capabilities that you saw in the NAV reporting demo, you have to put everything into Table(s). For every standard NAV report, none of the formats created allows you to easily convert it into the format that is needed. Even if you used the Create Layout Suggestion feature in the report designer, it wouldn’t format it properly into what is needed.

The problem is that the reporting tool is still trying to replicate how standard C/side report works. Standard C/side report does not work well in the RDLC environment. It’s what we’ve been told. We get it. But why is the tools built within NAV saying the opposite?

If everything nice and cool in the RDLC requires Table(s) to work, why not just eliminate the RDLC layout designer completely? For list type reports (which is what we’re dealing with), we only use the layout designer to put fields we want to be displayed. We’re not making anything pretty like Sales Order, Sales Invoice, etc. If the user needs list type report to look pretty, or add some wierd format, it’s easy enough to export this report to Excel and manipulate the formatting there.

If that’s the case, why not just allow a table-like report designer and have this designer automatically create the report layouts for us? This designer should allow us to easily group records, create formulas, bold, underline, etc. Allow us to utilize the capability of charting, document mapping, etc with option like interface.

Basically, as I mentioned previously, eliminate the layout designer completely and have the computer AUTOMATE the layout process for us. Create the appropriate tables for us to allow us to take advantage of the new reporting features.

Having this will allow the end users to easily create their own reports within NAV with greater efficiency and would promote the non-IT end users to be more involved with NAV. And we know for a fact that the more involved the user becomes with how the data flows, the better they will trust in the system and the happier the users will be.

The last thing we want NAV to become is a software that only people with high technical ability knows what’s going on.


Suggestion #2:

For form type reports, blow it up!

For form type reports, such as sales order, sales invoice, purchase order, etc. We don’t need nice features like document map, expand/collapse, etc. So why are we fussing with the RDLC layout report writer?

Even when the sales order is properly formatted in the RDLC, the preview of the report will not match what’s actually printed. If you print a sales order right now, the preview will not match what is being pritned on paper.

Microsoft seems to have taken away the WYSIWYG (What You See Is What You Get) functionality in print preview. Any report writer will tell you that having a WYSIWYG preview is very important when we create a delicate form type report with nice graphics, lines, boxes, etc. How can we make quick changes to align everything perfectly if we cannot quickly view them? We have to either print them on paper or print them to PDF.

The suggestion here is simple, again, eliminate the RDLC layout designer and create a new report designer for forms that allows NON-TECHNICAL to create nice and professional forms.

Conclusion:
I will repeat this over and over again. The best customers we have are the customers that actively tries to learn the system. Most of these users are non-technical.

I understand the desire to move to a more general report writing tool like Visual Studio, I understand the need to move into a language that are more generally accepted. However, I can easily teach someone how to create and modify reports in C/side environment to a non-IT person. I cannot say the same with RDLC reporting tool and Visual Studio.

As I mentioned in the previous article, NAV is a business software, NOT an IT software. As such, everything in NAV must be designed so a business person can easily utilize its full potential. The ease of use should not stop at the front end, it should extend to the back end as well. Including development.

How to Define SetData and GetData in RTC Reports in Dynamics NAV Navision

For some reason unfamiliar to me, Microsoft likes to write tutorials that does not easily teach you what you need to do to get the job done.

For basic List type reports, yes, it’s quick and simple, however, when it comes to reports involving forms (i.e. sales order, sales invoice, etc), I cannot find a good tutorial on how to get it done.

Forget about the Create Layout Suggestion feature on the form type reports. It sucks. You’d save a lot more time by re-creating your layouts in the standard NAV RDLC report.

One of the most important aspects of the Form type report when you’re trying to edit the standard report is 2 functions: SetData and GetData.

In this example, I’m modifying report 5703 (Transfer Order) report. I’m going to add the Shipment Date and the Shipment Method Code to the report in the report.

Here’s what you need to do, step by step:

1. Create the fields in the classic report and point to the proper field. It doesn’t matter where you put it as we’re not displaying the classic report.
Pic1

2. Click on View –> Layout. This is to modify the report when running the report in the RTC environment. Note that you’ll need Visual Studio 2005 and above to do this.
Pic2

3. You want to add 4 additional text boxes. Two for the caption box and 2 for the text box. I just copy and paste from the existing text boxes above. Number your GetData function 21 to 24 as shown in the example.

Background and the reason for this, if you’re not interested, skip to #4:
Now you need to do a little detective work to find where the SetData and the GetData is. If you notice on the upper left corner, there’s a small hidden field in red. If you highlight the field, the code reads:
Pic3

What’s the reason behind using SetData and GetData? The reason is because you cannot add field text boxes in the report header. The argument here is performance. If you like to read, there’s a section in the 80146B manual that you can download from Customersource or Partnersource. So most of the coding is done on the lines area.

Now, back to the tutorial, where to set the SetData and GetData? Here’s where you need to play a little detective.

4. On the lower right corner, there are some additional hidden boxes in red. Highlight each one of them until you find this one (Note the HeaderInfo. It corresponds to the SetData).
Pic4

5. Highlight the text box and go to the Value property. Click on the Property Page icon to display the property of the Value:�
Pic5

6. Go to the General tab and click on the fx button next to the Value
Pic6

7. Type in the 4 additional fields you’ve set from row 21-24.
Pic7

8. Click OK and close and save the report

9. Go back to the RTC and run the Transfer Order report:
Pic8

There you have it. Seems like an awful lot of steps for something so simple… But this is supposed to make our life easier so we’ll walk along and wait for the benefits to come.

Accounting Cost vs. True Cost

In Microsoft Dynamics NAV, when doing costing and profitability analysis, you need to differentiate between a transaction’s True Cost and Accounting Cost. Don’t bother looking up these terms in the manual, I made them up for a lack of better terms.

To better explain the difference between True Cost and Accounting Cost, we will use this example:

8/15/08 – Item A was received at 10 pieces for $2.00 each.
8/30/08 – 6 pieces of item A was sold for $5.00 per piece
9/1/08 – The vendor invoice for Item A is posted at $3.00 per piece
9/15/08 – The freight invoice came in and item charge is used to allocate an additional $1.00 per piece
9/20/08 – The rest of the 4 pieces of item A was sold for $5.00 per piece

Accounting Cost:
Assuming on 9/30/08, you’re asked to do a sales analysis for the month of August. When the costing is analyzed for the sales made on 8/30/08, the COGS that accounting recognize will be $2.00 per piece. This means that if we’re printing reports based on Value Entry posting date filter from 8/1/08 to 8/31/08, the profit margin would be 60% per piece. Not bad! For accounting, the cost is indeed $2.00 per piece since that’s the only amount that was recognized in that period. (Some companies makes an accrual on the G/L side for the expected cost of good sold, but that’s a separate topic). This number to management, of course, is incorrect.

True Cost:
In actuality, the cost of the item should be $4.00 per piece because each piece came in at $3.00 with an additional $1.00 in freight charges. The margin of the item should be 20%.

Another scenerio is you’re running the sales report from 9/1/08 to 9/15/08, you would show 0 quantities sold, but the cost recognized in that period would be $12.00 ($1.00 in the additional vendor cost + $1.00 in the freight cost * 6 pieces sold). If you did not check this report and you present this report to the management, be prepared field a load questions on the integrity of both you and the numbers.

Solution:
Both Accounting Cost and the True Cost are correct! Do not assume otherwise! It’s just a matter of how the user wants to look at the numbers. For accounting, they need the numbers to be recognized in the proper periods so the previous period numbers does not get changed. For management, they want to see the true cost of sales transaction. What to do?

As a simple rule, the Value Entry stores the accounting cost, the Item Ledger Entry stores the true cost. Since most NAV reports dealing with Contribution Margin uses Value Entry table, we typically remove those field from the report because they are misleading for everyone. We create separate reports using the Item Ledger Entry table taking the Cost Amount (Actual) since it rolls up all the costs associated with the sale transaction.

One thing to note when presenting the report off of the item ledger to the management, depending on when you post the vendor invoice and other landed cost charges, the profitability number will change. This means that, in our example, the profitability report ran on 9/1/08 will be different than the same report with the same filters ran on 9/20/08. However, in my experience, once you properly explain this concept to accounting and management, they will understand.

[EDIT] You can also use the Value Entry table for calculating True Cost. However, just filter on the Valuation Date instead of the Posting Date.