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

Regards,
Saurav Dhyani

Comments

Popular posts from this blog

BC 21 and Higher - PowerShell Cmdlet (Replacement of Business Central Administration).

Hi Readers, As discussed in last article about deprecating of Business Central Administration, there are few common actions that we use in administration till Business Central 20. For our on-prem customers, we will still require doing activities. As Microsoft suggest we need to start using PowerShell cmdlet.    Let's see how to do those via PowerShell, or Administration Shell. I will be keep adding commands as you comment to this article.

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 - 

MSDYN365BC - Data Upgrade To Microsoft Dynamics 365 Business Central on premises.

Hi Readers, We have already talked about the number of steps for upgrading to Business Central on Premises from different NAV versions. After that article, I received multiple requests for an article which list down steps for Data Migration. In this article, we will discuss steps of data migration to MSDYN365BC (on-Prem) from NAV 2017. For this article, I am considering a Cronus Demo Database without any customization. For an actual upgrade project, we will have to complete object merge using compare and Merge process. After the Merge Process, the next step is data migration. Let's discuss those steps. Direct Upgrade to Microsoft Dynamics 365 Business Central (on-Prem) is from following versions - 1. NAV 2015. 2. NAV 2016. 3. NAV 2017. 4. NAV 2018.