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

VIEW SERVER STATE permission on SQL Server?

Hi all, Sometime While trying to Login into a database we face an error message as shown below. --------------------------- Microsoft Dynamics NAV Classic --------------------------- You cannot start Microsoft Dynamics NAV Classic because you do not have the VIEW SERVER STATE permission on SQL Server. Contact your system administrator. --------------------------- OK    ---------------------------

NAV 2013 R2 - Cumulative update 12 Released.

Hi all, Please find below the details of  Cumulative Update 12 released for Microsoft Dynamics NAV 2013 R2. Title - Cumulative Update 12 for Microsoft Dynamics NAV 2013 R2 Build No. - 38053 Release Date - October, 2014 Local Version Included - AU, AT, BE, CH, DE, DK, ES, FI, FR, IS, IT, NA, NL, NO, NZ, SE, UK, RU Download Link Note: Implementing this cumulative update will require a database conversion unless you have already implemented update rollup 5.

Error After Restore SQL Backup of NAV 2013 Database

Hi all, we are facing a conman issue during restoring a SQL Database backup restored for NAV 2013. While Trying to run object from Object Designer we get below listed error (even the service is configured properly) - --------------------------- Microsoft Dynamics NAV Development Environment --------------------------- There are no NAV Server instances available for this database. You must ensure that a NAV Server instance is running and is configured to use the database before you perform this activity. --------------------------- OK    ---------------------------