How to report on same data for different dates, same report

[PhilipOfOrange]

I could use some suggestions about how to use different selection criteria on different columns in the same report. My company develops and markets POS (point of sale) software and a client wants one of our reports modified to add Year To Date sales for best selling products in addition to sales for the same product for a user defined period (typically, last week, month or four week period) in the same report. I have tried linking the item sales table to itself under an alias and I get wild numbers that have no relation to fact.

We are a Delphi shop that uses the Crystal supplied VCL for Delphi for our runtime distribution. User dates are passed from Delphi. Our tables are Paradox.

 

[IdoMillet]

You can do this using simple conditional totals.

Create one expression that returns the amount you want to total if the date is in the first date range and zero otherwise. Then total it.

Do the same for the other date range.

Cheers,
- Ido

 

[PhilipOfOrange]

Your reply forced me to think about this a little more. I think this is an impossible task. If the record selection criteria that is passed to the report, is for a period that is less than year to date (for instance the last month), then the record set that crystal uses, after pass 1 of the processing model, is not going to contain all the records that are needed to supply year to date information. There is no way around this that I know of.

 

[IdoMillet]

Don't give up so easily )

If you read carefully my previous message, you would notice that there is no need for selection criteria.

The two conditional totals will ignore any amounts that are not within their defined date range since such amounts will be evaluated as zero.

Cheers,
- Ido