"Not in" syntax

[jblain]

I'm looking to find records that are in 1 date range but not in another. Is there a way to do this using Crystal. I can do this using a SQL statement but am having trouble inside of Crystal. I would like to use date range parameter vales with this.

Thanks

 

[Madawc]

You could do a pair of boolians, each formula something like

{MUCH.CeaseDate} in [Date(2007,1,1) to Date(2007, 12, 31)]

Then use the name, or the name preceded by not, this will work OK.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

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

 

[jblain]

Sorry, I'm using Crystal 9. I've tried a select statement like this: adbase.customer in {?Range 1} and adbase.customer not in {?Range 2}.
The big thing is I need to use parameter vales for the dates. I would also like to use date ranges so it's easier for the sales staff to run the report.

Thanks

 

[bdreed35]

Use this logic:

{adbase.customer} in {?Range 1} and
not({adbase.customer} in {?Range 2})

~Brian

 

[lbass]

Can you clarify whether you really are trying to exclude dates that fall in range2 versus people who HAVE dates that fall in range2?

-LB

 

[jblain]

Here is an example. I work for a newspaper and I'm trying to find customers that placed an ad with us in Jan. 2006 but have not placed an ad with us in Jan. 2007.

Thanks

 

[lbass]

Use a record selection formula like this:

{table.date} = {?Range1}

Then insert a group on customer and create a formula {@inRange2}:

if {table.date} in {?Range2} then 1

Then go to report->selection formula->GROUP and enter:

sum({@inRange2},{table.customer}) = 0

If you need to summarize across customers, you will need to use running totals as the more usual inserted summaries would include the non-group selected records.

-LB

 

[cmmrfrds]

Since you can write SQL, put it is a Command and then create you parameters inside the Command interface window.

 

[jblain]

I guess I will have to read up on using commands. I have never used one. I did get it to work using lbass's suggestion but may try using a command.
Thanks

 

[cmmrfrds]

In our organization where we have about 2000 reports most of the report writers have converted over to using Commands since it is easier to optimize with control over the SQL and to prebuild the data for ease of reporting. We make extensive use of Oracle's Analytical Functions to push all the aggregations down to the Oracle server which makes for efficient reporting and even doing things that were impossible with Crystal generated SQL.