Forum Discussion

LutherMiller's avatar
LutherMiller
Qrew Member
6 years ago

Bar chart with complicated summary field

Hi,
I need add a bar chart report to a system and I having trouble with one of the items.
I have a table, let's call it YearMonthRevenueByContract that has the Year, Month, reference to a Contract table, and a Revenue amount for that month. There are multiple records in this table each month, usually with different contracts. The same contract might have records in more than one month.
The contract table has a Type field.
The bar chart will be total Revenue by contract Type for a given year. The Revenue part is adding up fine. But count of Contracts though is double counting. E.g. if the same contract is in January and February, it is counted twice for the year instead of once. Any idea how I can set this up?
I can definitely create a summary report with the distinct contract count grouping by year, but how do I get that into this bar chart by Type?
Thanks!

reports and charts
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    Luther,  


    Does this describe your application?


    Your reports could be made at least two ways.

    You can pass Related Contract Type to the Contract Revenue table and make the reports there.  This will let you get the dollars by period and Contract Type.  If you add up the number of Contract Revenue records that can easily exceed the number of Contracts

    The other option is to create Summary fields in the Contract table for the Revenue where you use the Date in the Contract Revenue table as a filter.  For example you could create

    Revenue Month to Date
    Revenue Year to Date

    Now those dollars can be used in the reports built in the Contracts table and the number of Contracts will be equal to or greater than the number of entries in the report.



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------