Cant figure how to specify multiple criteria sets for multiple columns 1

[jcmeyer]

My company has purchased Best Software's FAS Fixed Asset Accounting package that utilizes CRW8. I want to create a report with 4 columns and records sorted by account number. Asset selection for each column will be different, but will all be based on 3 fields: acquisition date, disposal date, and activity code ("Disposed" or "Active&quot.

Here is my logic behind the report:
Column 0: account numbers (row "labels&quot
Column 1: acquisition date <= 12/31/2000 AND activity code <> &quot;Disposed&quot;
Column 2: 01/01/2001 <= acquisition date <= 12/31/2001
Column 3: 01/01/2001 <= disposal date <= 12/31/2001
Column 4: acquisition date <= 12/31/2001 AND activity code <> &quot;Disposed&quot;

Looking like:
(Col 0) (Col 1) (Col 2) (Col 3) (Col 4)
acct# - Beg Bal - Purchases - Disposals - End Bal
010315 1000 500 300 1200
&quot; &quot; &quot; &quot; &quot; &quot; &quot; &quot; &quot; &quot;

With totals on each column. I have been able to produce each column INDIVIDUALLY, but I cannot produce all 4 columns on one report. I am looking for the simplest way, but at this point ANYTHING that gets me where I need to be will be appreciated.

 

[kenhamady]

The trick is to get all of the records that meet any of these criteria into one resultset. You do this by creating the following selection formula:

acquisition date <= 12/31/2000 AND activity code <>&quot;Disposed&quot;
OR
01/01/2001 <= acquisition date <= 12/31/2001
OR
01/01/2001 <= disposal date <= 12/31/2001
OR
acquisition date <= 12/31/2001 AND activity code <> &quot;Disposed&quot;


Now that the records are all in the report you create 4 formulas to control the columns:

The first column formula will say:
if acquisition date <= 12/31/2000 AND activity code <>&quot;Disposed&quot;
then 1
else 0

If you do a sum of this column for each account you will get the number for the first column. Then do the same for the other 3 columns. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[jcmeyer]

Okay, I see it now. The &quot;if&quot; formula that flags the record as true or false... that formula goes in the &quot;details&quot; section right? Then my subtotals, which appear in the group footer, will add up those details based on account number? I have not tried this yet... my database is still being converted (software upgrade), but I will let you know how it works out! Thanks!

 

[jcmeyer]

Thanks Ken! I got it to work perfectly! I didnt sort the records coming into the report though... I allowed them all to flow to the report, but then I limited each in the details section with the formulas, then subtotaled per account, then grand totals. I guess I was trying to do too much without formulas. I thought it was so simple that I didn't need to use them.

Again, thanks!