Formula

Hi mlmcc

First, I dropped in the sales value from the GL table in the detail section which is o.k, then as soon as I dropped in the State from Sales table, the total sales value doubled up. I don't get it ! Why would it double?

I grouped the State using the Group Expert, but the total sales value for State had doubled up. This is where I don't understand why CR is multiplying the sale value twice. My theory is because there are two transactions (i.e., one from the GL table the other is from the Sales table), hence it shows the amount twice.

Would it matter if my datasource is an EXCEL file?

I'm stucked !!!

Can you post the report?

Make a copy and change the extension to TXT.

I think the issue is in the links and without seeing the full tables I can;t really say.  

mlmcc

I've attached a concise version of my sourcefile. I usually create a a blank report, use the attached file as the datasource and let CR do the table link. As mentioned above, from GL table, I bring in this column In pctr local curr. in the detail section, as soon as I drop in the sales office (State), my figures seem to double up.

sorry, LOI is the Sales table.

Using the Excel spreadsheet.  Which fields do I need to link on and then use

mlmcc

Table linked:
LOI            
Bill.Doc.      BC      Material
GL            
RefDoc.No.      BC      Material

Then use:
1. GL: drop in the In pctr local curr (column S) in the detail section......this is the sales value
2. GL: drop in the RefDoc.No. (column B) somewhere in the detail section
3. LOI: drop in the Sales Office  which is the State (column C) somewhere in the dtail section, the sales value should double up.

As always, appreciate your help on this issue.

I'll give it a try shortly

mlmcc

I don't see any duplication.

To get the report you want what are the fields?

mlmcc

The followings fields are used in my report:

From LOI table select and drop "Sales Office" into the detail section (column C if view in Excel)
From LOI table select and drop "Invoiced Quantity"  into the detail section (column BB if view in Excel)
use the Group Expert to group  "Sales Office"
Right click the "invoiced quantity" >Insert>Summary by group "Sales Office"
Right click the "invoiced quantity" >Insert>Summary by total
Then, from GL table, select and drop "In pctr local curr. " into the detail section (column S if view in Excel)
The "invoiced quantity" total by "sales office" should amplify its value the moment "In pctr local curr. " is included in the detail section.

In summary: the LOI fields are used first (e.g., group "Sales Office" and total "Invoiced Quantity" ). The moment "In pctr local curr. "  from GL table is included into the detail section, the total "Invoiced Quantity"  double up....it could also happy by select and dropping any field from the GL table.

many thanks

You have some records in the LOI table that don't have records in the GL table.

Make the joins LEFT OUTER

mlmcc

how do I make the join LEFT OUTER in CR?

many thanks

Hi mlmcc

My total sales using GL.In pctr local curr (GL table, total sales of column S if view in Excel),
is -$219,966.10. But, as soon as I drop in 'Sales Office' (LOI table, column B if view in Excel) the total sales goes up -$6,677,810.35

Report fields used:
Using the DB2 as the source file
create a blank report
From GL table, drop in "In pctr local curr " into the detail section (this is column S if view in Excel)
Right click on the "In pctr local curr " >Insert Summary>Grand Total
From GL table, drop in "RefDoc#No#" in the detail section (this is column B if view in Excel)
From LOI table, drop in "Sales Office" in the detail section (this is column C if view in Excel)
Your report footer total goes up to -$6677810.35 which is incorrect. And I cannot fix it, that is, either left or right outer join  selected the total still wrong.

The total sales should -$219966.10 (total of column S if view in excel from GL table)

Many thanks