Before you start to develop a report, you should think about what exactly you want to achieve. Ask yourself the following questions:
Who is the report intended for?
What is the purpose of the report?
How is the report going to be used?
Where should the information come from?
How should the information be visualized?
It doesn't matter what technology you are using, the development of a report always boils down to two things: the data model and the layout.
The data model is actually the most important phase in report development, because if you get it wrong or if you have to make fundamental changes to it, it usually means you will have to redo the layout. So, take your time to think about the data model thoroughly.
This means that you have to know what information is required in the layout and where it is from. Are you going to use one or more tables? Is the same information available in different tables, in that case, which one are you going to use?
Note
Remember, in Dynamics NAV, information travels from the master tables, via documents and/or journals, towards ledger entries and posted document tables. Some information is copied from one table to another, in order to keep track of history or changes.
Make a list of all the tables that contain the information and then decide which ones you are going to use. In real life, this probably means you will have to consult an expert or a business user and explain that the information they require resides in multiple tables, so they can make an informed decision as to which table you should use in the report.
Tip
Ledgers or Posted Documents?
Dynamics NAV allows posted documents to be deleted after they have been printed. So, if confronted with the choice of using ledger entry tables or posted document tables as data sources for a non-document report, I recommend using ledger entries.
Once you have defined which tables you are going to use, you will need to think about the relations between the tables, how you are going to link the tables, and also in what order? The order will have an influence on the performance of the report, but also on the kind of information that might be missing.
For example, if you are asked to create a report to display inventory by location, the questions you should ask are:
What is inventory, is it the sum of the quantities in the item ledger entry table?
What quantity field should I use: Quantity, Remaining Quantity…?
What is a location? Is it the Location Code field in the Location table or something else?
Do I need to include items for which there is no inventory?
Do I need to include locations for which there is no inventory?
Do I have to display the item number or also the description?
Should I include any translations, substitutions, variants, or cross-references?
The answers to these questions will define which tables and fields you are going to include in the data model and in which order you are going to iterate over them.
Note
A good idea is to make a draft drawing of the layout of the report you want to create on a piece of paper. Write down the fields that need to be visible on the report and then find out which table they are from. After that, if there are multiple tables, find out how the tables are related and write that down. Having an entity relationship (ER) model helps a lot here, especially in a customized database.
In this way, when you open the designer, you already know what you need to do. Both novice and experienced developers make the mistake of not thinking before they begin. It can then get confusing very quickly.
A good suggestion is to have a requirements session with the user, during which you can create a mock-up of the report. Then you can define each field, column, grouping, sorting and printing option. Based on this session, you can then create the data model for the report. This mock-up is also referred to as a format design document.
Although the data model is important for the reasons I just explained, the layout of the report determines how the user will perceive it. So, if the layout is not easy to interpret, or if you can't see the wood from the trees, however cleverly you construct the data model, the report is not going to be used.
There are many out of the box reports in the Dynamics NAV application and most of them are never used. One of the reasons is that they have an inadequate layout.
How do I visualize the information such that the report clearly reveals its intention and the user quickly finds what he or she is looking for? That's the most important question to ask when creating the layout.
Creating a report is not difficult, but making it easy to understand, so that you can spot trends and learn from your data, takes some consideration. The main goal of a report is to communicate information clearly and effectively, for example, graphically. A report needs to create insights by communicating its key points in an intuitive way.
Using the example of the inventory by location report, you might consider how you are going to visualise the inventory. Are you going to display a number, or a data bar? Is it important to include a key performance indicator, for example compare the current inventory with the reorder point (or some other important value)? Or are we using items with an expiry date? If so, do items close to expiry need a different color? Then, in what order are you going to display the locations and items?
Testing is a phase that is often neglected, for different reasons. The most frequent excuse for the lack of tests is not having enough time. That might actually be true when you are developing the report, but, in the end, when users complain about bugs and missing functionality, you will wish you had tested more thoroughly.
Of course, this needs to be specified in your report's design document. Its test criteria should mention which formats the report needs to be tested in.
Another reason tests are usually dismissed or poorly carried out is a lack of understanding of the business case. How is the user going to use the report? Is all the information on there, and is it correct? As a developer in the NAV world, you have to put yourself in the shoes of the user. Only then will you truly understand if what you developed is ready or not.
Tests should include export into different formats (PDF, EXCEL, WORD) and actually printing the report on a printer. Test it on different clients: Windows, web, and tablet.
Using the example of the inventory by location report, a test verifies if the inventory is correct and corresponds to the inventory on the item card. Are there any locations or items missing from the report?