For the layout where you are creating this chart, what, exactly, is selected in Layout Setup | Show Records from ?
Layout is from Customers 3
Showing only Customer Name and the chart
And what calculation expression are you using th access the data in the two related table occurrences?
Does each record in the related table represent a single data point or do you need to compute monthly totals of multiple records in the related tables for each data point on your chart?
gYear1, gYear2 Global fields
OrderYear (from Order_Drct) = Year (OrderDate)
cMonth (from Order_Drct) = OrderDate-Day(OrderDate)+1
CondID_Cust (from CustOrders_DrctYear1) = Case (__pk_OrderDrctID = Sibling_Cust::__pk_OrderDrctID;Sibling_Cust::__pk_OrderDrctID)
Customers3 ---- CustOrders_DrctYear1 ( __pk_CustomerID = _fk_CustomerID) AND (gYear1=OrderYear)
CustOrders_DrctYear1 ------ Sibling_Cust ( _fk_CustomerID = _fk_CustomerID) AND (cMonth = cMonth)
CustOrders_DrctYear1 ------ Unique_Cust ( CondID_Cust = __pk_OrderDrctID)
same applies for CustOrders_DrctYear2
I need to compute monthly totals of multiple records in the related tables for each data point on the chart
Your relationships won't work for that as they match by customer and year. Totals computed from there would be for that customer for the entire year--one value per year per customer.
What kind of chart are you trying to produce and are CustOrders_DrctYear1 and CustOrders_DrctYear2 two separate tables or two occurrences of the same table?
CustOrders_DrctYear1, CustOrders_DrctYear2, Sibling_Cust, Unique_Cust are all occurences of the the same table Orders_Drct
The chart produced is attached.. You can see that the values for 2012 are correct but the values for 2011 are all the same for all months
I don't see how you got ANY correct data for a layout based on Customers 3 with the data to be charted in related tables using your relationships. What expressions/calculation fields are you using for the two data series?
I'd set up a chart like this from an occurrence of Orders_Drct, using a find to limit the records to one customer and a summary field to get sub totals for each of the bars in your bar chart. That approach takes a special calculation that combines month and year in just the right format as it requires sorting the records on a single field to get the parallel bars for each month like this.
Expressions used for the chart:
x-axis as Monthname(cMonth)
y-axis series1: 2012, data: Unique_Cust::csumSiblingAmt_Cust (summary of total amount sales)
series2: 2011, data: Unique_Cust 2::csumSiblingAmt_Cust)
Can you help with the " special calculation that combines month and year in just the right format as it requires sorting the records on a single field to get the parallel bars for each month like this " ?
The only way that series 2 would yield correct results is if there is only one record for each customer for that year. Series 1, on the other hand would yield yearly totals.
Let ( d = YourDatefield ; Right ( "0" & Month ( d ) ; 2 ) & Year ( d ) )
Select Text as your result type.
For January, 2012 dates, this produces: 012012 and it will sort January dates ahead of February dates but also separately groups the Janauary dates by year.
You'd have a single Y axis data series and you'd use the found set/summarized groups option in your data source part of portal set up.