Skip to main content

NAV 2013 & Later - Grouping In Export To Excel Report Without Layout.

HI All,

In This Article we will see how we can use Grouping (Group Header, Group Footer) Via Code in NAV 2013 R2 Reports.

This situation is faced during upgrade of a Report where we had put code for Some Task Say Export to Excel in Sections Like Group Header and Footer in 2009 or Previous Version.

In the Demo we will see a case of Export to Excel Report on Item Ledger Entry Table Having Grouped by Location Code and  Item Category Code.

1. As i Need Grouping on Location Code and Item Category Code in Item Ledger Entry Table i need to have a Key on Table 32 Item Ledger Entry as shown below.

2. Now Let's Create a Report with Data Item Item Ledger Entry and Set Key as created above, to sort the data in the way i want.

3. Now i hope we all know how to write basic Export To Excel Report, so i am not digging into same. If Not Refer Here.

This is what my Way of Grouping - There might be some other great ways and i would love to learn if there are other ways to do same.

My Way Is -

<a> I Need to identify No. of Entries for my grouping Field (as of now consider grouping on Location Code Only).
<b> Group Header will be Printed before the First Record of the Group.
<c> Group Footer will be Printed after the Last Record of the Group.
<d> Other Than Group header and Footer Rest is Body of the Report.

For <a> as my data is sorted with the Key i send the current value to a function which tells me the No. of Records as shown below.

For <b>, <c> and <d> i used some parameter while printing body of the Report as shown below.

5. And here is the calling of all the functions from Body of Item Ledger Entry.

But as in Navision My Location can be Blank in Item Ledger Entry so i changed the Code a bit to resolve issue. I Intialized by CurrLoc with a Random Value as shown below.

So let's run the Report and see output.

Now What if i have to add group of Item Category also. Minor Changes in some fucntions as Shown Below.

Changed in Function - OnInitReport and OnAfterGetRecord are highlighted in Red.

Changed in Function - MakeExcelDataBody and EntryCount are highlighted in Red.

And Here is the Final Output as we wanted.

Hope you find the article useful.

If the point is still not clear download the Report from SkyDrive for better understanding.

SkyDrive Link  OR Mibuso Link

File Name - SauravNavGroup.07.00.01.rar

Saurav Dhyani


Popular posts from this blog

Welcome - Microsoft Dynamics 365!

Hi All, In this article we will discuss the new introduced member of Dynamics Family. Yes you heard it correctly, one more new member with Name "Microsoft Dynamics 365" . #ProjectMadeira   #Dynamics365

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing - 

NAV 2013 Credential Type - How to Use?

Hi all, Please do these activities when you have time, its a long activity and i would suggest do all activities in one go. So be clam and lets start. For NAV 2013 R2 Refer Here. For NAV 2016 Steps have been Reduced Please Refer Here. I have installed NAV 2013 and its work great with my windows credentials. I was checking the service console (Microsoft Dynamics NAV Administration) in NAV 2013 and found out that Credentials Type have multiple Options.