Filtering reports on null date value

Aug 31, 2006 3 Replies

Rate this thread:

Bill Yater 19 years ago

I am trying to create a custom report in RMS Active Reports which will show me all items which have not sold since a specific date. I've got the following report filter in my .QRP file:

Begin Filter FieldName = "Item.LastSold" FilterOp = reportfilteropBetween FilterLoLim = "8/1/2006" FilterHilim = "8/31/2006" FilterNegated = True FilterConnector = reportfilterbooleanconAND End Filter

This filter will show me items which did sell at some time, but have not sold since August 1. That mean that the item.lastsold date will be poplated with a date which is earlier than 08/01/2006. However, it will NOT show me items which have not sold at all. For those items, the item.lastsold date is NULL.

I can do this through a simple SQL WHERE clause: where (lastsold is null or lastsold < '2006-08-01')

However, I can't use raw SQL in my report program. I need some way to either specify a date value of NULL in my report filter dialog, or a way to manually set a filter in code, setting the FilterOp to IS (whatever the correct ReportFilterOp is for that) and the FilterLoLim and FilterHiLim to NULL (or "NULL")

Bill Yater The Worth Collection snipped-for-privacy@worthltd.com

Vote

Glenn Adams [MVP - Retail Mgmt] 19 years ago

GO to the Reports directory and make a copy of the report you want to start from (probably Sales - Detailed Sales)

Rename you copied file something like this: "Custom - MyReportDescription.qrp"

"Custom - " is important - not that there are spaces on both sides od the dash...

Make sure your copied report is not marked as Read Only and open it in Notepad.

In the Report summary section, just after the TablesQueried secion, you will find SelCriteria=""

make that: SelCriteria = " (lastsold is null or lastsold < '2006-08-01') "

Save your report and reopen SO Manager. The new report will be available under Reports/Custom

Only problem is that you have to edit the file every month to reset the last sold date...

Glenn Adams Tiber Creek C> I am trying to create a custom report in RMS Active Reports which will show

Vote

Bill Yater 19 years ago

Thanks, Glenn. That worked great.

You're correct that if I hard-code the date, I would need to modify the .QRP file every time I wanted to change the expression.

Really, for our purposes, we only care about items which have not sold in the last 30 days. I would like to code an expression so it shows items which have not sold (lastsold is null) or have not sold in the last 30 days.

In SQL, I would use the expression "lastsold < dateadd(month, -1, getdate()". However, when I use the DateAdd function in my SelCriteria, I get an "incorrect syntax" error when I run the report.

What is the equivalent of the DateAdd function in Active Reports?

Bill Yater

"Glenn Adams [MVP - Retail Mgmt]" wrote:

Vote

Bill Yater 19 years ago

Disregard my earlier post. I realized that I forgot a closing paren in my expression.

Us>

Vote

Join the Discussion

Have something to add? Share your thoughts — no account required.

Didn't find your answer?

Ask the community — no account required