locked
Skewed Report Totals RRS feed

  • Question

  • I just started with SQL and SQL Reporting Services, mostly learning as I go.
    I am working in Transportation/Logisitcs and stuck with a report.
    I have two tables.
    One has information for loads we haul: Load Number, Origin, Destination, Total Rate, etc.
    The second has pays for each load: Load Number, Pay Type, Pay Amount, PaidToID, etc.

    Loads Table
    LoadNum    TotalRate
    1                100
    2                200

    Load Pays Table
    LoadNum    PayType      PayAmount
    1                Driver Pay     70
    1                Agent Pay    15
    2                Driver Pay     50
    2                Driver Pay     80
    2                Agent Pay    35

    I want to get the totals for each pay type and run a report showing that with the total revenue for the loads:
    Total Rate    Driver Pay    Agent Pay
    300              200             40

    The Query Statement I am using is something like
    SELECT LoadsTable.LoadNum, LoadsTable.TotalRate, LoadPaysTable.Paytype, LoadPaysTable.PayAmount FROM LoadsTable, LoadPaysTable WHERE LoadsTable.LoadNum = LoadPaysTable.LoadNum
    The results I get are like this:
    LoadNum    TotalRate       PayType        PayAmount
    1                100                Driver Pay       70
    1                100                Agent Pay      15
    2                200                Driver Pay       50
    2                200                DriverPay        80
    2                200                Agent Pay      35
    Resulting in the sum of Total Rate total to be 800 which is not correct.
    I am tinking that this would be a common scenario but haven't been able to find a solution.
    Thanks for any help is greatly appreciated!
    Friday, May 30, 2008 12:39 PM

Answers

  • The easiest thing may be to have multiple datasets in your query.  Have one that returns just the Loads, and a second one that returns the LoadPays.

     

    Then you use a Table or a List to display the Loads, and you can nest the details inside of the first object to get the individual Pay Amount details.

     

    The other thing I tried was to create a computed column in your Query that holds the Average Rate per line Item.  That way it breaks the 100 or 200 dollar numbers across individual items.  That way when you sum them up, you'd have two 50s, and three 66.66667 numbers which would get your totals correct.  You'd have to use some formatting or rounding to make sure that the Averages don't end up throwing the report total off by a penny here and there.

     

    Here's a sample query for the second option. (I've taken the liberty of rewrting your WHERE clause as a JOIN)

    Code Snippet

    SELECT lt.LoadNum, lt.TotalRate, lpt.Paytype, lpt.PayAmount, av.AvgTotalRate

    FROM LoadsTable lt

      INNER JOIN LoadPaysTable lpt ON lt.LoadNum = lpt.LoadNum

      INNER JOIN (

        SELECT lt2.LoadNum, lt2.TotalRate/CAST(COUNT(*) AS float) AS AvgTotalRate

        FROM loadstable lt2

          INNER JOIN LoadPaysTable lpt2 ON lt2.LoadNum = lpt2.LoadNum

        GROUP BY lt2.LoadNum, lt2.TotalRate

      ) av ON lt.LoadNum = av.LoadNum

     

     

    Friday, May 30, 2008 2:42 PM