Date formulas

[runnerlk]

I am trying to create a manual cross-tab. My rows are crime codes. My columns need to be the count of those crime codes by month to include the current month, previous month, current month-previous year and year to date.

My date field for the columns is OfficerRecords.DateReported(datetime).

I am not able to create the proper syntax to create the formulas.

My field for the rows is OfficerRecords.UCRCode

database = msde
ODBC Connection

Any help would be greatly appreciated.

Lou

 

[synapsevampire]

Database = msde should state that the database is SQL Server, and you might consider including the version.

You might also help by providing the expected output as opposed to a text description.

It sounds like you want to create a series of totals, and then use some totals in conjunction with others in a columnar fashion.

This Month Last Month This Month-This Month Last Year
_________________________________________________________

So I would create a record selection formula which pulls only those periods, and then use a Running Total field to specify the criteria for each total.

The Running Total would suffice for the This Month/Last Month, and then you would use a formula to subtract This Month from the This Month Last Year RT to supply the 3rd value.

-k

 

[runnerlk]

It is version 2000.
Crystal version 10

Expected output =

UCR code 3/04 2/04 3/03 Y-T-D

0311 Assault 3 2 4 5