Chart Sales By Month
I am attempting to create a chart showing what is essentially sales by month.
The tables are setup as follows:
Reports --< Invoices--< Items
Sales::Date is a DD/MM/YYYY
Items contains a summary field of Items::Cost called Items::sItemCost
The relationship between Reports and Invoices is as follows in order to allow the user to filter the chart by date:
Reports::FilterStart ≤ Invoices::Date AND Reports::FilterEnd ≥ Invoices::Date
The chart appears on a layout based on Reports, as this layout will display a tab showing other charts.
I am not quite sure how to approach this method, as it is a bit more complex than generating a report that sorts by month using a break field where Invoices::cMonthSort = Year ( ReceivedDate ) & Right ( "0" & Month ( ReceivedDate ); 2 ). When I use this approach on the chart, the chart labels appear correctly (using another calculation field as the label), but the summary field Items::sItemCost only shows the Item records related to a single Invoice record - probably the first one in that month range.