Exclude Duplicate Records from Running Totals

[GaryCWL]

Database: SQL Server 2000
Application: Crystal Reports XI

Background Information:
I've created a report for Marketing that shows Win/Loss ratio bases on Quotes that turned into Orders. I've created a report that already has three groupings Calendar Month/Year, Sales Rep, and Discounted %.

Problem:
I wanted to see/ask if it is possible to exclude quotes from the report that are duplicate quotes.

For example, we may have 5 quotes for the same customer but for different distributors/sales reps and only one of the quotes turned into an order. In this example, we would have a Win/Loss ratio of 20%. The correct ratio should be 100%.

I'm trying to find out the following:
1) How to do a Group By with a Having Count(*) > 1 in a formula so that I don't have to create a separate grouping
2) How exclude the duplicates and only keep the one that has a corresponding Order Number

Thanks much.

 

[Madawc]

Try Insert > Crosstab. That allows the data to be grouped differently for the entire report.




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

 

[lbass]

Can you exclude the records with no order from the report altogether? Try a record selection formula of:

not isnull({table.orderID})

Or you might be able to achieve the effect based on table joins, if you there are multiple tables.

If you are using running totals, you could use an evaluation formula like the above in the evaluation section (use a formula->x+2).

-LB

 

[GaryCWL]

Thanks for the responses.

I forgot to mention that I have a cross tab with Sales Rep (Rows), Discount (Column), Count of Discount and Count of Orders (Summarized Fields).

In response to LBASS, I cannot exclude the records (Quotes) with no order info because that'll defeat the purpose of determining the Win/Loss Ratio (if a quote turned into an order). Marketing/Sales wants to see how many quotes were quoted and of that, how many turned into an order. But instead of having an inflated quote count they would like me to exclude the duplicate quotes (quotes with the same project name).

If I use an Evaluation Formula in the X+2, how do I specify it to reset the count on change of Group #3 and exclude all quotes where the Project Name (field in DB) is duplicate (having count > 1)?

Thanks,
G

 

[lbass]

This is very hard to respond to without seeing sample data, so I'll just make a comment about how you can deal with duplicates. Using the evaluation formula area, you can write a formula like this:

not isnull({table.orderID}) and
{table.projectname} <> previous({table.projectname})

This assumes that you can order or group by projectname and that you only want to count those projects with an order, and that each of these duplicate records contains the same result for order ID--either it's present or not.

For further help, you need to provide sample data at the detail and group level and then show some sample results.

-LB