4 Replies Latest reply on Jul 15, 2014 3:22 AM by gdurniak

    Complex Report Help Needed

    stanmcman@yahoo.com

      WIN 8 Pro, FMPA13, FMS12

       

      This is a little complicated; I will try to be clear, thorough, and concise.

       

      Description:

      Trucking companies are required to file quarterly fuel taxes based on the miles they travel in each state and the fuel they purchase in each state.

       

      Companies have trucks. Trucks take trips. Trips go through states. The flow is as follows:

       

      Customer > Truck > Trip > State; Trip and State are joined by 2 line items tables: MilesLineItem & FuelLineItem

       

      (Please Note: This is the same principle as: Customer > Branch Location > Invoice > Product, with Invoice and Product joined by LineItem, except I have 2 join tables)

       

      I can successfully create new customers, create new trucks for each customer, create new trips for each truck, with every trip having different quantities of miles and fuel for various states via the line items.

       

      I can also successfully run a sub-summary report in MilesLineItems & FuelLineItems which gives me the total miles or fuel for each state, for each truck, for each customer. I can successfully manipulate that data depending on how I sort.

       

      The Problem:

      I have a two-fold problem:

       

      1. I need to combine the related data from the MilesLineItems and the FuelLineItems, so I can get the total miles and fuel together for each state, for each trip, for each truck, for each customer. Do I need to create another table that connects these two join tables? Or should I be able to do this from the Trips table? If so, I don’t know how. What relationships would I need to do this? And more importantly, what is the best way to do this?
      2. Once I can get #1 accomplished, I need to run the equivalent of a Cross-Tabs report where I can have all 48 states listed as rows, and have the columns be calculations based on sub-summary totals for the miles and fuel for each trip, or truck, or customer, depending on the sort. I also need this report to show all 48 states, not just the ones that have miles or fuel.

       

      Example: ABC Transport Inc has truck# 1 which goes on a round-trip from Grand Rapids MI to Chicago IL back to Grand Rapids, and buys fuel in IL. Here’s what the trip would look like:

       

      State Miles State Fuel (gallons)

      MI 200 IL 85

      IN 45

      IL 50

      IN 45

      MI 200

       

      My sub-summary report from MilesLineItem would look like this:

      State Miles

      IL 50

      IN 90

      MI 400

       

      My sub-summary report from FuelLineItem would look like this:

      State Fuel (gallons)

      IL 85

       

      Here is the ultimate report I need to create:

      State Miles Fuel MPG (other columns of calculated data based on the total miles and fuel for each state, based on the found set)

      AL 0 0

      AR 0 0

      … … …

      IL 50 85

      IN 90 0

      … … …

      MI 400 0

      … … …

      Etc… all the way through all 48 states.

       

      How can I effectively do this?

       

      Please ask questions if I didn’t explain it clearly.

       

      Thank you in advance for any help.

        • 1. Re: Complex Report Help Needed
          usbc

          Well asked and thanks for including the OS and FM version.

           

          "Customer > Truck > Trip > State; Trip and State are joined by 2 line items tables: MilesLineItem & FuelLineItem"

           

          You may have a compelling reason for splitting and rejoining Miles and Fuel into separate tables. If not having them in one table with a few more fields couls reduce the complexity. If the only reason for using two tables is user data entry - look/feel there may be a simpler strategy. Such as the 2nd and third report you show could come from a single table. Again, unless there is some other reason...

          • 2. Re: Complex Report Help Needed
            stanmcman@yahoo.com

            I tried combining both LineItem tables into one, but I had trouble with data entry.  I use portals on the Trip table to enter the miles and fuel.  When I tried combining both miles and fuel together into one table, for every record I made in the mile portal, it added a line in the fuel portal, since they were now part of the same table.  Is there an easy way to combine them, and yet keep them separate from a portal standpoint?

            • 3. Re: Complex Report Help Needed
              usbc

              Yes. You can have one table and use two separate TOs for connecting to the parent table. That is to say, the lineitens table will generate the two key fields upon creation and they will drive each portal (showing only the fields as apropriate). There may be refinements to avoid unwanted lineitems such as portal filtering or using multiple conditions in one of the relationships (this equals that AND those does not equal these). You'll know best.

              • 4. Re: Complex Report Help Needed
                gdurniak

                You could also try using a separate "Report" Table

                 

                Find and import the records you need to see,  and show them there

                 

                Clearing the table each time

                 

                greg

                 

                > I need to combine the related data from the MilesLineItems and the FuelLineItems, so I can get the total miles and fuel together for each state