Monday, June 22, 2015

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
saurav-nav.blogspot.com

No comments:

Post a Comment