Your message has been sent.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
In Microsoft Dynamics NAV, reports are used for printing information from the database, and to execute batch jobs (process information in batches). Our focus will stay with the printing part of this definition. Modifying batch jobs requires a lot of programming knowledge, so our story about them ends here.
Even though reports are traditionally seen as objects used to print data, reports in Microsoft Dynamics NAV 2009 provide a more versatile functionality: they can print information, process it, or do both simultaneously; or they can present data on screen, or export it to Excel or PDF.
To better understand how to create and modify reports in Microsoft Dynamics NAV 2009, we need to do some theory (with a bit of practice as we go).
Reporting in the Classic client
Reporting in previous versions of Microsoft Dynamics NAV, as well as in 2009's Classic client uses technology based on C/SIDE objects called Reports.
Creating simple reports is a fairly simple task—all you need to do is click New in the Object Designer within the Reports view, then the New Report wizard takes over.
You need to specify the table over which the report will be based, and then decide whether you are in the mood to draw the layout yourself, or you want to have the wizard do the legwork on your behalf. There are three types of layouts the wizard can create:
- Form type report: For each record in the table, fields will be arranged vertically one below another, and often arranged in two or three columns. For each field, the caption is printed to the left, and the value to the right. Records are arranged vertically with some white space in between.
- Tabular type report: The report prints a table, where each row represents a record in the underlying table, and each column represents a field. There is also a header row, which displays column captions. This is the most versatile type of report, because it can be used for documents.
- Label type report: The report is divided into tiles arranged horizontally, then vertically, and each record occupies a single tile. Only values are printed, and captions are omitted: good for printing mailing labels or item barcode stickers.
When you specify which table you want to use as your base and make your pick about the report type, clicking the OK button will launch the wizard for the report of the chosen type. Each of the types has its own wizard with a different set of questions asked and steps to follow.
Components of a report
A report consists of data set definition, report layout, and options page. Now, being proprietary all the way home, all of the folowing three use specific terminology:
- Data Items: the data set
- Sections: the layout
- Request form: the options
Reports need to be connected to some data, which resides in tables. Data items are tables we decided to use in our reports. Data items are also the first thing we see after the New Report wizard has completed its chore. A typical data item looks like the following:
You can add as many data items as you need. To add a data item, position the cursor on the first empty line, and start typing the table name (or number) in the DataItem field. Alternatively, you may position the cursor on an existing data item line, then press F3 (or choose New from the Edit menu). This will insert a new line just before the line you selected.
When the report is run, data items are executed in sequence, meaning that all the records from the first data item will be processed (and inserted into the report) before the report moves on to the next data item. In most cases, this is not how you want it to be.
Data items come with a concept of indenting. By the clicking left and right arrows you can indent a data item to the left or right. This can help establishing a parent-child (or master-detail) relationship between data items, meaning that for each parent (or master) record all its child (or detail) records will be processed before the report moves on to the next master record.
Indenting alone has not yet established the parent-child relationship. It has achieved only one thing: it will make the report execute all child records for each of the parent records. To complete the relationship setup, we need to set some data item properties:
The most important property is DataItemLink, and we can type in the link setup manually, or we can invoke the DataItem Link editor by pressing F6 or clicking the Assist button. It will open the DataItem Link editor, which is used to establish the mapping between the fields in the child table (Field column) to the equivalent fields in the parent table (Reference Field column).
Other than indenting, data items can accomplish a few other things as well, so you will frequently use these properties as well:
Data items simply define where the data comes from and what kind of relationships exist between different tables; sections define how this data is going to be laid out on the report. Section Designer is where you do this, and you can invoke it by clicking Sections in the View menu.
You'll immediately see that it's no state-of-the-art editor, but it accomplishes its purpose. The folowing is what a typical Section Designer will look like:
When you create your report using the wizard, it will create several sections for you, depending on the report type and choices you made. For every data item that you add manually, one section is inserted. A section is a part of a report with specific behavior. This behavior is embedded in the section type, and to see which section types there are, simply press F3, or select New from the Edit menu:
This is what they do:
In Section Designer, each section is represented by a gray horizontal bar which goes all the way across the screen, and carries a label, such as Sales Header, Body (1). This label is actually an identifier: Sales Header is the name of the data item, Body is the section type, and the number in parentheses is the ordinal number of the section for that data item. If there are several sections for the same data item, they will all be numbered consecutively starting at 1.
eBook Price: $35.99
Book Price: $59.99
Sections contain field controls and labels. The difference is that field controls display a value from a table field (or another source, such as a variable, a constant, or a C/AL expression), while labels simply print static text.
To add table fields to a section, select Field Menu from the View menu. It will bring up the list of fields in the data item table for the currently selected section. In the Field Menu, select the desired fields, position your mouse pointer over the section into which you want to add them, and click the left mouse button.
When fields are added, both field controls and labels are added to the same section. Typically, you will want labels in a header section and field controls in a body section, and there is no way but to move them around manually.
When a report is started, and before it is printed, users usually have a chance to define some filters and options. This is called the request form, and this is what it may look like:
The request form consists of several tabs. If we didn't change the default properties, one tab will be shown for each data item, and we can use these to specify any filters we want applied. By setting the ReqFilterFields property of a data item, we may choose which fields will be included by default.
To hide a data item from the request form, clear its ReqFilterFields property, and set the DataItemTableView property to some value (typically, you set it to use ascending sort order by the primary key).
In most cases, however, enabling filtering for data items simply won't do, and functional requirements will call for much more. If you need to enable printing multiple copies, or to hide or display certain sections or even data items based on the user's choice, you'll need to do some more design and programming. This is where one more Request Form tab comes in handy: the Options tab.
To enable the Options tab, choose Request Form from the View menu, and it will open the Request Options Form Designer. It looks, feels, and functions exactly like the Form Designer for standard C/SIDE forms. The only difference is that you can only define one request options form per report, and that the definition for this form is embedded into the definition of the report itself.
A fully fledged request options form might look like the following (it belongs to report 5600 Fixed Asset - Analysis):
Now for the hard part: all of these report elements can have application logic behind. And they usually do. Only the simplest of printouts can be achieved without any coding, and some reports, such as the one in the example above typically require a fair amount of coding. Just take a quick peek at the code behind dozens of triggers in this report, and you'll get the gist.
Reporting in the RoleTailored client
Things look quite differently in the RoleTailored client. As it utilizes the service tier, and no logic is ever executed on the client, the native C/SIDE reporting architecture is incapable of providing a robust and scalable reporting platform for the RoleTailored client. Instead of porting the native reporting functionality to the service tier, Microsoft has decided to make use of its standard reporting platform: Microsoft SQL Server Reporting Services.
The truth about Reporting Services
Microsoft Dynamics NAV 2009 does not utilize SQL Server Reporting Services. It makes use of the ReportViewer .NET control in local processing mode, which means it uses a client-side .NET class to render reports and doesn't require any Reporting Services components on the client or server end.
In fact, the only thing the ReportViewer control and Reporting Services have in common is that both use RDL (Report Definition Language), but the ReportViewer control uses the client version, called RDLC. The difference between RDL and RDLC is that RDLC files don't contain information about how to query the data source, while RDL files do.
Effectively, when using RDLC, the ReportViewer control is aware only of the layout; it is the host application's duty to retrieves, processes, and feeds the data to the ReportViewer control in the form of an ADO.NET DataTable object. This is why Microsoft Dynamics NAV can't publish any reports to a Report Server and why all the RDLC reports can only be used from within the RoleTailored client.
Always look on the bright side of life, however; RDLC and RDL are completely interchangeable, and can be easily converted from one to another. When future versions of Microsoft Dynamics NAV bring tighter integration with Reporting Services, reports in RDLC will be as good as those in RDL, and all the hard work that you invest into preparing good-looking RoleTailored reports now won't go in vain then.
Now that you know a little bit of reporting technology of Microsoft Dynamics NAV 2009 (still not enough to qualify as a geek, though), let's see how reporting truly works in the RoleTailored client:
Obviously, the RoleTailored client is only in charge of presentation, and all the logic is executed on the server. Also, there have been important changes in how the report itself is executed, especially in how the data source is queried, and triggers are fired. All in all, these changes in architecture have inevitably reflected on how reports are designed.
In the RoleTailored client, reports consist of the following components:
- Data Items
- RDLC layout definition
- Request page
Although the two clients use different reporting components, all of them are stored in the same C/SIDE report object. Each of these clients will use that part of the report definition that it needs.
Data items are the same for both clients, and everything we said about them in the context of the Classic client applies here as well. The only two differences are the RDLC layout definition, which is used in place of sections, and request page, which is the RoleTailored equivalent of request form.
Thankfully, we don't need to do any of these manually; there are tools that help us translate just about everything we did from Classic to RoleTailored flavor.
RoleTailored report creation
There are two ways to create a report for the RoleTailored client: to transform a Classic report, or to create one from scratch. Transforming is the easy way around, but it presumes that you have a Classic version of the report already. On the other hand, creating a new RoleTailored report without an existing Classic version of the same can be an equally annoying experience as creating the Classic version first.
Since it's advisable to always have both versions anyway, your general approach to report creation should be to create the Classic version first, then test it thoroughly. When you are happy with the results, you can role-tailor it by using the built-in transformation tools.
The RoleTailored representation of each report is stored inside the report object, and you can access it by opening a report in Object Designer, then choosing Layout from the View menu. Report layout will open in your default RDLC editor.
Let's take a look at a typical standard report, from the RDLC point of view. In Object Designer, locate report 6 Trial Balance, and click the Design button. When the report is opened, click the View menu, then Layout, and here it comes:
At this point feel free to go like: Whoa, this thing's in color! User experience of any of the RDLC editing tools is far superior to that of the Section Designer, and the colorful user interface is just the first hint of this.
OK, we cheated a bit. We took a finished report and bragged how nice everything was. That was easy. Does it look that easy if we have to do it with brand-new reports, those that we built from scratch? See for yourself.
Create a report of any type you like using the New Report wizard, and look at its layout, using View, Layout: you'll see it's empty. That's because it was never transformed. Transforming a report for the first time is no more complicated than viewing its layout; you only need to run different commands.
Go back to Report Designer in the Classic client, open the Tools menu, then click Create Layout Suggestion. Again, your default RDLC editor will open; this time Microsoft Dynamics NAV will convert the report layout definition from Classic client's sections into an RDLC one.
When you close the RDLC editor and return back to Report Designer, it will ask you:
As you can see from the text message, the RDLC layout you worked on was stored in a temporary location on your disk, so any changes you made in the RDLC editor actually affected only this temporary file. Unlike sections, the RDLC layout is not saved into the report automatically, which is a good thing—in case you messed up, this confirmation dialog gives you a chance to simply rollback. If you click Yes, the RDLC file will be imported into report; No will keep the things as they are.
Transforming request option forms
You guessed it right! For those reports that have their layout converted to RDLC, request option forms can't be run in the RoleTailored environment. That's simply because they are normal forms, and forms are no good for the RoleTailored experience. The RoleTailored client will look for the request option page definition instead.
Request option page is the same thing as request option form, only RoleTailored. You can see the request option page definition if you click the View menu, then pick Request Page. It looks and feels exactly like the Page Designer you are already familiar with.
As with all Classic forms, you have two options with request option forms: you can transform them into request option pages using the Form Transformation tool, or you can design them from scratch. Take our advice: the few controls you need to declare aren't worth the bureaucracy of the Form Transformation tool; just go and type them directly there in Request Option Page Designer.
Everything you learned about designing pages can be applied equally successful to designing request option pages. The only difference is that a request option page doesn't have the PageType property.
You don't have to bother transforming your Classic reports to RoleTailored ones if they contain complex layout or request option forms. The RoleTailored client is capable of running non-transformed Classic reports as well.
If you omit transforming any of the Classic reports into their RoleTailored counterparts, and you access such reports from the RoleTailored client, the RoleTailored client will launch the Classic client runtime in the background and display the report as if it was accessed from the Classic client (without displaying the whole client to the user).
Of course, for this functionality to work, you must have the Classic client installed on user's machines.
Now that you know how to create forms, pages, and reports—the building blocks of the Classic and RoleTailored clients—it is the time for you to integrate those elements into menu navigation for easy access by the users. You do this using MenuSuite objects.
MenuSuites work differently between the clients: in the Classic client they are displayed in the navigation pane, while the RoleTailored client displays them in the Departments page. Take a look and compare them:
Apart from the looks, there is a huge conceptual difference between these two. In the RoleTailored client, navigation is primarily achieved through role centers where the vast majority of tasks start and users should have very few reasons to leave their role centers for the Departments page. On the other hand, in the Classic client all navigation starts in the navigation pane.
A little bit of theory
The navigation pane organizes navigation elements into menus (in the previous screenshot these are Financial Management, Sales & Marketing, Purchase, and Warehouse), groups (Sales and Order Processing in the screenshot), and items (Customers, Contacts, Quotes, and Orders in the screenshot). Menus and groups are used merely for logical organization, while items are responsible for displaying various user interface components, such as forms and reports.
eBook Price: $35.99
Book Price: $59.99
The definitions of these menus, groups, and items are stored in MenuSuite objects. If you go to MenuSuite in Object Designer, you'll see that there aren't too many of them: depending on your localization, you'll probably see between three and five of them. That's because MenuSuites don't work like other objects in Microsoft Dynamics NAV—one MenuSuite doesn't represent one navigation pane (or Department page), but instead it represents one level. To understand how levels work, let's see how many levels there are and what they represent:
Even though levels are fixed, and you can't change them, names are not set in stone—you may change them to whatever you like, if you see it fit.
There is another set of levels, which start with 1010 and end with 1090, and have Dept – in front of their name: these are used by the RoleTailored client to construct the Departments page. Numbering scheme, names, and meanings are exactly the same as here, only the ID is shifted up by 1000. This means that in Microsoft Dynamics NAV you can have at most 15 levels, or up to 15 MenuSuite objects per client (up to 15 for Classic, and up to 15 for the RoleTailored one).
When the application starts (precisely, when a company is opened), it uses the levels of MenuSuites to construct the navigation pane: it starts with the lowest level (10 MBS) and adds all menus, groups, and items it contains, then moves up to the next level to do the same, all the way up to the level with the highest number (typically 90 Company). Each of the levels contains only differences from the previous one: additions, changes, and deletions. For example, if level 10 MBS defines the menu Warehouse, and you delete the same menu on level 80 Partner, it won't be included in the navigation pane. Also, if you include it again on level 90 Company, it'll be back.
And some practice
There are three ways in which to edit a MenuSuite object:
- By creating a new MenuSuite object in Object Designer. As soon as you create a new one, it automatically opens in the Navigation Pane Designer.
- By designing the object in Object Designer: you select which level you want to edit, click the Design button, and it will launch the Navigation Pane Designer for that specific level.
- By invoking Navigation Pane Designer from the Tools menu: it will always launch the Navigation Pane Designer for page 90 Company.
The Navigation Pane Designer is completely integrated into the navigation pane itself, which means that it merely switches the navigation pane itself from navigation mode into design mode. Even though there are visual clues that suggest the mode you are currently in, it might not be immediately obvious. The difference is subtle: in design mode, the name of the MenuSuite being designed is displayed to the left of the selected menu name, and the titles of any menus inherited from a previous level are preceded by the guillemet (») character.
To create a new MenuSuite, you simply click New while in Object Designer's MenuSuite view. Whenever you are creating a new MenuSuite object, the system asks you two questions: which kind of client you intend this MenuSuite for, and which level you are creating. Only those levels that haven't already been consumed by an existing MenuSuite object can be created.
Careful with those Object IDs
Once you choose the intended client and level, you can't change your mind any more. Or better said: you shouldn't. What you actually can do is change the Object ID. Don't do this, because this can introduce a serious mess. An unfortunate fact about MenuSuites is that level and client information are hardcoded into the Object ID, and while changing the Object ID from 80 to 90 won't have any consequence whatsoever, changing it from 80 to 1080 might make a total mess, to the extent of crashing the RoleTailored client on every start.
The reason for this is that MenuSuites in Object ID range 10 to 90 can define items of type form, while MenuSuites in range 1010 to 1090 can't. However, simply changing the Object ID for a MenuSuite won't convert form references to page references. This means that with changing an Object ID from the Classic client's range into the RoleTailored one, you literally sneak items of type form into the Departments page, something that the RoleTailored client isn't resilient enough to endure. Therefore, those Object IDs aren't to be touched!
Designing MenuSuites is easy; all you'll ever need to do is available at the right click. Depending on where you place your cursor, one of two different menus will drop down. If you right-click a menu, you get the options for editing the menus. If you right-click a group or an item, you guessed it right—the options displayed allow you to edit groups and items. Simple and intuitive!
Let's see what's on the menu about menus. Design a MenuSuite and right-click the Sales & Marketing menu. You'll get the following options:
That's about menus. Let's see about groups and items. While designing a MenuSuite, right-click on a group or an item, and you'll get the chance to pick one of these:
Obviously, creating and editing MenuSuites is not rocket science. They are very simple objects; they contain no code and no documentation trigger; and they come with very few properties, most of which you can't even access the way you can with other types of objects. The only other things you can do with MenuSuites besides those few listed above are export and import them as text or FOB file (don't try XML, it won't do anything useful), and compile them.
Compiling a MenuSuite object will go through all items defined in that MenuSuite and check whether the objects referred to by the items still exist. If they don't, you'll get to know that. When working on implementation projects, make sure you compile your MenuSuites before every deployment.
One thing worth noting is that you don't have to create two versions of MenuSuite objects for the Classic and RoleTailored clients manually. If your MenuSuite objects are huge, then you can benefit from the Form Transformation Tool—it can also transform your Classic client MenuSuites into RoleTailored client ones saving you many uneventful hours of right-clicking your way through Navigation Pane Designer.
Customizing other objects
Customizing Microsoft Dynamics NAV is not just about forms, pages, reports, and MenuSuites. There are codeunits, dataports, and XMLports. Let's just see what they are all about, before we call it a day.
We wanted to make this article code-free, and show you what kind of stuff you can do with Microsoft Dynamics NAV without ever coming anywhere near C/AL. And from what you've seen so far, there's an impressive lot of development you can do without any coding at all.
Codeunits are all about code. That's why they are called that after all. They are objects used to encapsulate blocks of code with similar purpose or that accomplish a specific thing. Codeunits are the cornerstone of the business logic of Microsoft Dynamics NAV, and you should make extra sure that people modifying them have good knowledge of the functionality of the application area they are modifying and the business logic behind the code contained in them. Don't ever settle for less.
Dataports are on their extinction path. They have been used by previous versions of Microsoft Dynamics NAV to export and import data in textual formats, and in their heyday they were even able to handle XML.
Nowadays, they are limited to the Classic client (the RoleTailored client can't run them), they can cope with text data only, their number has been decimated, and there are but a sorry few left in the standard application. Their functionality has been completely taken over by their younger brethren, the XMLports, and if you need to develop any text import or export functionality, go for XMLports, they are the new standard.
XMLports are objects that have been used to import and export XML data into and out of the application, but in Microsoft Dynamics NAV 2009 they have been upgraded to support the variable and fixed text formats as well. Since the RoleTailored client doesn't understand dataports, XMLports are effectively the new standard for handling data import and export, and whatever import/export requirements you might have, they are best met with XMLports.
XMLports also have a powerful feature that delivers a conclusive advantage over dataports, and that's the ability to stream data directly from and into various sources and destinations. While dataports were limited to reading and writing to text files, XMLports can read and write directly or indirectly to files, memory, BLOB fields, and automation objects.
Although designing XMLports might require no programming at all, making any real use of them does. As they primarily do streaming, you'll have to program at least some C/AL code to specify streaming sources and destinations.
In this article we have learned how to modify the Microsoft Dynamics NAV 2009 application, something that is an integral part of literally every implementation project. We have seen the palette of tools, wizards, and designers we can use to facilitate the creation of new objects.
The C/SIDE integrated development environment makes it possible to develop new functionality quickly, and we have seen that it can even happen without writing a single line of code.
We have gone through data modeling concepts, and explained how data modeling maps real-life objects and their relationships, and how to make best use of that knowledge when designing new tables and their relations.
About the Author :
David Roys is a Microsoft Most Valuable Professional (MVP) for the Microsoft Dynamics NAV product. He has worked in the computer industry since 1992 and currently works in New Zealand for Intergen Ltd., a leading Microsoft Gold Partner and Dynamics Presidents Club member.
Since getting his honors degree in Computing Science from Staffordshire University, England, he has worked with a variety of custom-written and packaged financial solutions in a variety of roles. His first programming job provided experience of financial systems using CICS, COBOL, and JCL on an IBM 3090 series mainframe. From being a very junior developer in a large organization, he went on to be a oneman IT department at a small food manufacturing company in a role that allowed him to learn and develop solutions for a Danish ERP package called 'Concorde XAL'. David moved into the world of consulting and ERP reselling in 1996 where he enjoyed working with some truly brilliant people at Columbus IT Partner and was able to work on international projects in South Africa, Hungary, Poland, and Ireland. With many years of experience in XAL and Axapta he moved to New Zealand in 2002 to work as a consultant for Ernst & Young in their IT Consulting practice delivering financial solutions in Navision Attain. He now works in his dream job as a Dynamics NAV consultant and developer for Intergen Ltd., a bunch of fun-loving, incredibly smart people. David firmly believes that ERP systems are boring and is committed to bringing some entertainment to this dull and listless world.
Vjekoslav Babić is a Microsoft Dynamics NAV expert, consultant, and architect with ten years experience in the IT industry and six years experience delivering project success on large-scale, high-risk, and international implementations of Microsoft Dynamics solutions. He has project experience in various industries, including telecommunications, insurance, pharmaceuticals, industrial gasses, chemicals, food and beverage, manufacturing, printing, distribution, and retail. He is a Project Management Institute certifi ed Project Management Professional, an accomplished Microsoft Certifi ed Trainer with a track record of successful trainings and presentations, a Microsoft Certifi ed Business Management Solutions Professional with several Microsoft Dynamics NAV and Microsoft Dynamics CRM specializations, and holds a number of Microsoft technical certifi cations. Vjekoslav has published more than forty articles on business solutions, software development, database design, and internet technologies; he is the author of the NAV Insights column and an Editorial Advisory Board member with MSDynamicsWorld.com. An active blogger, he frequently writes about Microsoft Dynamics implementation methodologies, Sure Step, and Project Management topics on his blog NavigateIntoSuccess.com.
Based in Zagreb, Croatia, he is employed as a consultant at Microsoft.
Contact Vjekoslav Babić through his blog http://NavigateIntoSuccess.com
Books From Packt