Trouble grouping and totaling by two fields in a report

blh

Be patient with me I’m kind of new to NAV. I’m having trouble getting data to jive on a report. I am trying to get totals from the Item Ledger Entry table for the Quantity, Remaining Qty. and Cost Amount (Actual) fields and group them according to Location Code and Global Dimension 2 (Product Group Code). I’ve tried using a wizard to create the report but I can’t seem to get the numbers to jive when I double check them. I’ve been filtering the Item Ledger Entry table with certain Location Codes and Product Group Codes and cutting and pasting the data into excel and then totaling it to double check the numbers in my report. In at least one instance the report is actually grabbing all the entries from a transfer order instead of just the one entry with the certain product group code. The code from the wizard has “CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY = "Item Ledger Entry".FIELDNO("Global Dimension 2 Code");” in the OnPreSection of the Item Ledger Entry Group Footer section and has the same code (except for “Location Code” where “Global Dimension 2 Code” is) in another Item Ledger Group Footer section. I’ve looked at other posts concerning grouping by two fields but none seem to have run into this specific problem. Any help would be greatly appreciated.

DaveT

Hi,
Welcome to Mibuso

I think what you need is not too hard to acheive. Use the Totalfields and GroupTotalFields properties

If you can post the code then I will have a look.

blh

Thanks for your quick reply and help. Below is the code:

Item Ledger Entry - OnPreDataItem()
LastFieldNo := FIELDNO("Global Dimension 2 Code");
Item Ledger Entry, GroupHeader (3) - OnPreSection()
CurrReport.SHOWOUTPUT := FooterPrinted;
FooterPrinted := FALSE;
Item Ledger Entry, GroupHeader (4) - OnPreSection()
CurrReport.SHOWOUTPUT :=
CurrReport.TOTALSCAUSEDBY = "Item Ledger Entry".FIELDNO("Location Code");
Item Ledger Entry, GroupHeader (5) - OnPreSection()
CurrReport.SHOWOUTPUT :=
CurrReport.TOTALSCAUSEDBY = "Item Ledger Entry".FIELDNO("Global Dimension 2 Code");
Item Ledger Entry, GroupHeader (6) - OnPreSection()
CurrReport.SHOWOUTPUT :=
CurrReport.TOTALSCAUSEDBY = LastFieldNo;
Item Ledger Entry, GroupFooter (8) - OnPreSection()
IF NOT FooterPrinted THEN
LastFieldNo := CurrReport.TOTALSCAUSEDBY;
CurrReport.SHOWOUTPUT := NOT FooterPrinted;
FooterPrinted := TRUE;
Item Ledger Entry, GroupFooter (9) - OnPreSection()
CurrReport.SHOWOUTPUT :=
CurrReport.TOTALSCAUSEDBY = "Item Ledger Entry".FIELDNO("Global Dimension 2 Code");
Item Ledger Entry, GroupFooter (10) - OnPreSection()
CurrReport.SHOWOUTPUT :=
CurrReport.TOTALSCAUSEDBY = "Item Ledger Entry".FIELDNO("Location Code");


Let me know if you need anymore information. Thanks again!

DaveT

Hi,

This code look fine - it's printing header 4 and footer 10 for location and header 5 and 9 for global dimension 2. Now the question is - is the value correct?

Can you check the Totalfields and GroupTotalFields properties of the dataitem Item Ledger Entry

Lee_Durrant

blh,

You have a Global Dimension that is exactly the same name as an existing field. The chances are that when you think that you are applying a filter to the Global Dimension, Navision is actually filtering on the standard Product Group Code field.

Try renaming your Global Dimension to something else and see if it resolves the issue.

Regards,

Lee Durrant

blh

Guys,
Thanks so much for your help. Lee you made a great observation. It turns out that this is where the discrepancies came in my numbers. When I set up the report I set it up to look at that Global Dimension 2 which has a caption of Product group Code. When I was exporting the data to double check it I was exporting and sorting on the actual field Product Group Code. I don’t know why my predecessor set it up this way or even more importantly why some items have different values in Product group Code as opposed to the value in Product Group Code. I’ll be asking my supervisor a few questions when he comes in Monday! Dave thanks for looking over the code. Thank you both again and I look forward to running into you guys in the future on Mibuso.
blh