Group by Formula

[brendalanigan]

Hello Everyone!

I hope somebody can help me with this query. I am using Crystal Reports 9 to report on an SQL Server 2005 database.

I have been asked to do a report that calculates the amount of products sold by each salesperson grouped by product. The problem arises because although there are only 3 products that are sold each product has multiple codes which refer to it. I have been asked to produce a report that displays totals for just the 3 products so I need to format the group to consolidate the product codes.

For example product A can have the product code of
'MLE' or "MLE-O" or "MLEA" or "MLEASLS" or MLED.

I have tried the following formula to consolidate the product codes

if {QRY_PROSPECTS.Prospect_Product} IN "MLE","MLE-O","MLEA","MLEASLS","MLED","MLER","MLESLS" Then "Product A" Else {QRY_PROSPECTS.Prospect_Product}

But I keep getting an error that a bracket is missing. I am not familiar enough with Crystal syntax to get around this. Is there an easier way to do this?

Thanks for reading.

 

[IanWaterman]

YOu are missing square brackets around the list

if {QRY_PROSPECTS.Prospect_Product} IN ["MLE","MLE-O","MLEA","MLEASLS","MLED","MLER","MLESLS"] Then "Product A" Else {QRY_PROSPECTS.Prospect_Product}


could also try

if {QRY_PROSPECTS.Prospect_Product} like "MLE*" Then "Product A" Else {QRY_PROSPECTS.Prospect_Product}

Ian

 

[Bloke152]

you need to put brackets around the conditions of your IN statement. You need to use square ones tho

IN ["MLE","MLE-O","MLEA","MLEASLS","MLED","MLER","MLESLS"]

 

[brendalanigan]

Thanks very much for that - cant believe it was something so simple.