Select Statement Help

[Bennie47250]

Using Crystal 7.0 on an MS SQL database

Our sales are broken into Regions and Territories. Need to design a report that excludes sales to our export regions and territories.

In some regions, all the territories are excluded but in one region, only need to exclude a few territories. There is a range of regions that need to be excluded.

With all the different conditions, not sure how to combine them together for one select statement.

Each individual condition is shown below. How would these be combined into one select statement?

To exclude regions and all the territories in the region

not ({Sales_Table.Sales Region } in [76, 48, 47, 46, 39, 38, 37, 36, 35, 34, 33, 32, 31, 30, 15, 8])

To exclude a range or regions
not ({Sales_Table.Sales Region} in 800 to 899)

To exclude specific territories in a region
{ Sales_Table.Sales Region} <> 60.00 and
not ({Sales_Table.Sales Area} in [616.00, 680.00, 698.00])

Thanks
Bennie

 

[IanWaterman]

This will bring back all records to Crystal and then filter. So it might be slow to execute

Create a formula

@Regions

//To exclude regions and all the territories in the region

If {Sales_Table.Sales Region } in [76, 48, 47, 46, 39, 38, 37, 36, 35, 34, 33, 32, 31, 30, 15, 8] then false
else
//To exclude a range or regions

If {Sales_Table.Sales Region} in 800 to 899
else
//To exclude specific territories in a region

If { Sales_Table.Sales Region} = 60.00 and {Sales_Table.Sales Area} in [616.00, 680.00, 698.00] then false
else true

In select expert

@Regions = true

Ian

 

[Madawc]

In Crystal 10, and probably also 8.5, you could put the test into a formula field, e.g.

{ Sales_Table.Sales Region} <> 60.00 and
not ({Sales_Table.Sales Area} in [616.00, 680.00, 698.00])

This would give True or False, and you could then say @NotTerritory in the record selection to get the test.

Not sure if this will work in Crystal 7, which I have never used. Using brackets should work.

A lot of advantages in an upgrade, if you can afford it.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[Bennie47250]

Ian,

Your approch works and on the report are the correct records. However, I have a Qty field and a subtotal on that field that is incorrect.

The subtotal does not agree with the Qty shown on the report, it is greater. Like the sales that were excluded by the @regions formula are included.

How can I get the subtotal to add up to the Qty's that are shown on the report?

 

[IanWaterman]

Add a Running total which replicates your subtotal and place this on details. You should be able to see where the toatl starts to go adrift.

As the formula is in slect expert then no additional records will get through. Are you sure you do not have any conditional suppression on details? If you do you can not use standard summaries. You need to use running Totals with evaluate condition that is reverese of your suppression condition.

Ian

 

[Bennie47250]

Ian,

Used the @regions as a "group selection" in the select expert.

Change to use it as part of my record selection and that corrected it.

Thanks!