Your self join probably didn't work due to using the same match field on the layout side of the relationship. For that approach to work, you'd need 12 different calculated match field values, one for each month of the year, each linked to a different TO of order detail. But it would make more sense for the layout to be based on a TO of products not setting up a self join with the layout based on Order Details. Using Products would give you a row for each product on your report whether it was purchased in the last year or not. You then have the option to show whether or not a given product with zero sales is or is not included in your report.
What you describe is called a "cross tab" report and there are a number of different approaches that you can try from one row portals, repeating fields, ExecuteSQL calculation fields to Virtual List reports. If you search this forum by looking for Cross Tab, you should be able to find discussions of at least several of these approaches. If you don't like what you find in one discussion, keep searching.
Sorry I worded that badly, I meant I had 12 different calculated fields for each month i.e c_APR = 4 etc with 12 different TO joins but no total value was showing up? Would this method of worked?
Rather than looking by product, the purpose of this report is to use the nominals as they break down the products into the categories.
I have no idea what you mean by "nominals" but products in the products table may be broken down by category as well. If your layout shows one row per category, then a sub summary layout part can product that row for multiple records using a layout based on either table.
The only difference would be if a given category had zero sales for the year. In that case, you'd still have a row in products where you would not if using order details. That's a difference that would be unlikely to occur for most businesses, but is not an impossible result.
But there is more than one way to calculate a monthly total using your relationships. Which method did you use?
A summary field? A calculation field using the sum function?
This won't affect your current results, but you might consider a different month calculation for the match fields:
DateField - Day ( dateField ) + 1
Calculates the date of the first day of the month for the value in dateField. This includes the year so sales that took place on January 2016 would be in one group and sales that took place on January 2017 would be in a different group.
Nominal is our product category for accounting purposes. I've just replicated what I did before. The same values are showing up for each month.
I have a summary field on order details. Each month summary on the report would be a field like this nominal_product_orderdetail#apr::s_sum_total etc.
Looks like you put the summary field from the wrong table occurrence into your columns of data.
Assuming that your layout specifies nominal_product_ORDERDETAIL in Layout Setup | Show Records From,
The summary field in the Apr column must be nominal_product_ORDERDETAIL#Apr::SummaryField, not nominal_product_ORDERDETAIL::SummaryField. You then pick this field from a different table occurrence in each column.
It was that. The problem was that I needed the self join as well as the month calculation join.
I'm not sure if these figures are correct but at-least they are different now.
Not sure how I could test how accurate the figures are.
Use a layout based on Order Details where you also have the fields needed for month and category. Put your summary field from the same table occurrence on this layout.
Perform a find for one month and category.
The value in the summary field should match to the same "cell" on your cross tab report.
If you find that this layout is uacceptably slow to update, there are ways to use a summary table to produce the same report where you'd have one record for each month for each row of your report instead of possibly millions (depending on business volume) of records.
I do a report in similar fashion that shows monthly totals over a 5 year span where there is from 500 to over a 1000 "detail" records generated each day. That summary table makes it possible to produce the desired report nearly instantaneously. (The summary table "pre-calculates" the totals as stored values via a script that adds records from the current days transactions each night.)