Exclude duplicate records from Detail and Summaries

[oticonaus]

I am using Crystal XI, and data from a MS SQL Server, and also Maximizer 9. There are 2 SQL tables (Serial_History & Customers) and 4 Maximizer views (Client, Events, Account Number, and Company Type).

I am selecting customers to show based on data in Maximizer, and then calculating units sold from the Invoices table.

This all works fine, except for a few customers, who have 2 entries in the Events view. For these customers, the calculated units sold is doubled.

The question then, is how do I select only distinct records from the Events table? I have tried all sorts of formulas, links, grouping, to no avail. The best I can manage is to suppress the detail record if it is a duplicate, but all of my summaries (sum) still count the duplicate.

My select query:
SELECT CLIENT.Name_Type, ACCOUNT_NUMBER.Account_Number, CLIENT.Client_Id, CLIENT.Contact_Number
FROM ACCOUNT_NUMBER ACCOUNT_NUMBER LEFT OUTER JOIN CLIENT CLIENT ON ACCOUNT_NUMBER.Client_Id=CLIENT.Client_Id
WHERE CLIENT.Name_Type='C'
SELECT "Customer"."Name", "Customer"."Blocked", "Customer"."No_"
FROM "syd"."dbo"."Customer" "Customer"
WHERE "Customer"."Blocked"<>1

SELECT "Serial_History"."Sell-to Customer", "Serial_History"."Invoiced Quantity", "Serial_History"."Item No_", "Serial_History"."Invoice No_", "Serial_History"."Credit Memo No_", "Serial_History"."Invoice Date", "Serial_History"."SalesPerson Code", "Serial_History"."Item Type", "Serial_History"."Family", "Serial_History"."Order Type"
FROM "syd"."dbo"."Serial History" "Serial_History"
WHERE ("Serial_History"."Invoice Date">={ts '2006-06-01 00:00:00'} AND "Serial_History"."Invoice Date"<{ts '2007-03-31 00:00:01'}) AND ("Serial_History"."Order Type"=0 OR "Serial_History"."Order Type"=1)
ORDER BY "Serial_History"."Sell-to Customer"

SELECT U_COMPANY_TYPE.U_Company_Type, U_COMPANY_TYPE.Contact_Number, U_COMPANY_TYPE.Client_Id
FROM U_COMPANY_TYPE U_COMPANY_TYPE
WHERE (U_COMPANY_TYPE.U_Company_Type='HOBAMember' OR U_COMPANY_TYPE.U_Company_Type='FOBAMember')
ORDER BY U_COMPANY_TYPE.U_Company_Type

SELECT U_EVENTS.U_Events, U_EVENTS.Contact_Number, U_EVENTS.Client_Id
FROM U_EVENTS U_EVENTS
WHERE U_EVENTS.U_Events='2007 HOBA Rego'

 

[satinsilhouette]

I am thinking since you are accessing more than one datasource, this may not be accomplishable unless you pull the second datasource into a subreport. Crystal does not handle joining to two different datasources very well unless brought in by a subreport.

I am wondering also, if you have SQL Server at your disposal, could you bring the maximizer views into the sql database as well? Or write a sp to handle all the information into a view then access it with Crystal.

 

[Madawc]

I have tried all sorts of formulas, links, grouping, to no avail.

What, exactly? Because this is the standard solution.

A summary total will count everything, though Distinct Count is useful in some cases, changing from Count to Distinct Count might do it.

Failing that, try a Running total, which can count or sum based on a rule. Or add just once per group, which should solve your problem. You can also do tests like

{acc.no} <> Previous({acc.no})

, but that's not necessary in this case.

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[oticonaus]

Thanks to both of you for your reply.

I managed to construct a workaround to this problem, however, it was not as elegant as I would have liked.

I added a group (#5) based on the Customer Number, then suppressed the detail section and moved the various fields from the detail to the group 5 header.
I then calcuated the count of the records in the group, and suppressed that field so it would not be seen.
I think changed all of my summary fields on each higher group header to a formula which, when divided by the count of clients in group 5, gave me the number I wanted.

As I said, it was not elegant, but it works.

I am sure there must be a better way.

Madawc, I expect I could have used the Rnuning Total option also.
As for subreports, I haven't yet tackled them - maybe next time.