|
Posted by Bill Yater on September 1, 2006, 2:57 pm
Please log in for more thread options Disregard my earlier post. I realized that I forgot a closing paren in my
expression.
Using the SelCriteria of
SelCriteria = "(lastsold is null or lastsold < dateadd(month, -1,
getdate()))"
works like a dream.
"Bill Yater" wrote:
>
> > Only problem is that you have to edit the file every month to reset the
> > last sold date...
> >
>
> 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:
>
> > 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 Consulting
> > http://www.tibercreek.com
> > glenn@tibercreek.com
> > ----------------------------------------------
> > Please DO NOT respond to me directly but post all responses here in the
> > newsgroup so that all can share the information.
> >
> >
> > Bill Yater wrote:
> > > 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
> > > byater@worthltd.com
> > >
> >
| 
Yahoo! 
Google 
Windows Live 
del.icio.us 
digg 
Netscape 
XML Sitemap