Personalization, Customization and Productivity Tools

[Darkjedi]

I made a report that adds all the DollarsSold for the last 24 periods from SO_SalesOrderHeader, AR_Customer and AR_CustomerSalesHistory. This way we can tell which customers to take off or put back on the mailing list. The problem is that some of the periods repeat and is doubling the money. Does anyone know of a easy way to only report it once?

[connex]

What fields do you think you need from SO_SalesOrderHeader for this report? I expect that that could be your problem because it is an invoice based report whereas AR_CustomerSalesHistory is a Customer summary report. If you've linked the SO_SalesOrderHeader table by division and customer number then you would get duplicate periods - one duplicate for each customer invoice.

[Darkjedi]

The reason for the SO_SalesOrderHeader is so I can get any current standard or backorders. I have to see if the customer has done over 500 dollars with us for the last 6 months. If they have we send them a catalog.

I have 24 of these formulas so I can retrieve the last two years of the customer history. 01 changes and the year changes on each formula. Then I have a formula that total these field for the 6 months.

if {AR_CustomerSalesHistory.FiscalPeriod} = "01" and
ToNumber({AR_CustomerSalesHistory.FiscalYear})=(Year(CurrentDate)) then
{AR_CustomerSalesHistory.DollarsSold}
else 0

Thanks

Mark

[VKZIMM]

I would create a subreport for checking the current Sales Order entry on change of Customer Number, link on the Customer Number and Share the total of the orders.

[connex]

You definitely have to remove the SO tables from the main report as Mary suggests - sub reports are one solution. I handle this type of report by linking to the MAS data through MS Access.  Note that uses do not need to have MS Access to run the resulting report as the Jet database drivers are included with Windows.

In MS Access you can create a Union query that will combine the linked SO and AR history tables into a single view with common columns. I also add a column for document type: S for sales order, I for invoice. No data is actually held in MS Access and the report is quite fast. The bonus is that the report is simple. You change the report to just printing off of AR History and then use the Set Location command to direct the report to the union query in MS Access.