Good Morning!

    I am having issues trying to create a report in Jet reports (I'm building a table to build a pivot table from). My issue is more conceptual. The goal is to explain inventory account activity purchases and sales. 

My controller wants this information and it's difficult to see what's going on if you're just looking at the G/L. I agree that it is. We go through a ISV for our Nav implementation and I do not have access to SQL reporting services, only Jet Reports.

I tried approaching this report in two ways.
  1. Start with the value entries as my master table and use the sum of "Expected Cost Posted to G/L" and "Cost Posted to G/L" fields to get totals of inventory going in and out. The totals work out on this just fine but I lose the ability to connect an item's cost to a G/L account because it's a 1:many relationship between the value table and the G/L.
  2. Start with the G/L as my master table and use the Amount field to get totals of inventory going in and out. I can link to the value table on each entry to get Item No. and Source No. information but if I do it this way I lose the ability to capture freight totals because of the way NAV makes freight entries. The G/L entries for freight that have corresponding value entries cancel each other out and there's a final entry that adds the total of the freight back that does not have a value entry associated with it. 
In short:
  1. If I try to start with value entries as the master table I lose the ability to connect items with an inventory account no.
  2. If I try to start with G/L entries as the master table I lose the ability to capture freight costs.
I should add that we use a charge item to add freight. 
The goal is to create a pivot table like this:
Purchase:
     Date:
            Source No. (Customer or Vendor)
                 Account No. (G/L inventory account)
                      Item 1                                                      Amount 1
                      Item 2                                                      Amount 2
Sale:
     Date:
          Source No. (Customer or Vendor)
                Account No. (G/L inventory account)
                     Item 1                                                       Amount 1
                     Item 2                                                       Amount 2