Crystal Reports XI editing issue. Very difficult to figure out 1

[ccloutier]

We are currently on Exact Progression 7.7.5 with Crystal Reports. We frequently export to Crystal for adjust, add to, remove or alter the data to our needs. Our Crystal version is XI. Currently I am trying to pull a sales report. I need to pull specific sales to calculate a specific sales representative’s commission. I need to see February’s sales for Company A, B and C. They all have four ship to locations, 1, 2, 3, & 4. However, I only want to see the ship to locations for 1, 2, & 3 of company “B”. How can I only show the desired ship to locations for “B” without eliminating location 4 for the other two companies? I'm spending days I can't spare trying to figure this out. Can anyone help me?

 

[IanWaterman]

In your select statement

CompanyField in ["A", "B", "C"] and
(If CompanyField in ["A", "C"] then Location in [1,2,3,4] else Location in [1,2,3])

Ian

 

[ccloutier]

Ian, thanks so much for responding.

However, I'm not sure this will work. I shortened the list. I'm literally looking at thousands of possible customer names and hundreds of ship to location numbers. So not all are pulled into the report. The desired dozen or so customer numbers are identified in select expert. Their ship to addresses are not actually in the report but I can pull it from database. I need take only one of these customers and identifiy only those ship to locations I want in the report. I don’t want to write a formula excluding the hundreds that I don’t.

I need to do this report for various salesman so there will be many reports. Once I find out how to include only some locations for specified customers I’ll be able to do the other reports but every report will have different customers / locations. I can't possibly list all the customer numbers or ship to locations in a formula. Is there a way to just seek out one customer name, number or group header and then only include the 5 or even 20 locations I need? I tried to adapt your formula to the one below but I am afraid this formula just will not seek out that name but eliminate any others already in the report.

if {OEHDRHST_SQL.cus_no} <> ["000000000400"] then {OEHDRHST_SQL.cus_alt_adr_cd}
in ["000000000000033","000000000000025","000000000000010"]
else {OEHDRHST_SQL.cus_alt_adr_cd} in ["100000000000000"]

Again, thanks for responding and any assistance you can give me.

 

[IanWaterman]

Unless you can define the locations to company in Database you will have to hard code the logic in the report.

YOu could set up definitions in a spreadsheet or create a new table in database to hold this info. Spreadsheet will make report slow to run as Crystal does not particularly like multiple datasources in same report.

Ian

 

[ccloutier]

You are brilliant!!!! I’ve been fighting with this report for days and finally, after speaking with you, I’ve solved the problem. Thanks so much!