Other Date Ranges ...

Support information related to Pervasive SQL Databases

Other Date Ranges ...

Postby robinsonky » Wed Nov 23, 2011 4:30 pm

Using List Options | Filters
I have a report working successfully with the default value of = @max(), but how do I specify a different range like '@max()-9' to '@max()' to get the last 10 days?
robinsonky
 
Posts: 50
Joined: Wed Oct 19, 2011 8:41 pm

Re: Other Date Ranges ...

Postby Bob Cergol » Wed Nov 23, 2011 5:04 pm

You can't use an expression as a filter default so you will have to write an expression in "free conditions" on the condition tab page.

In case you're using a Dynamic SQL view -- and haven't tried out @Param# -- you might also want to explore how you can also add your own where clause to the view using a paramter, set a default value and then prompt the user at runtime for a value.

Regards,
Bob
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm

Re: Other Date Ranges ...

Postby robinsonky » Wed Nov 23, 2011 6:46 pm

Bob, maybe if I state the goal.

[strike]...........[/strike]

solved it, never mind :mrgreen:
robinsonky
 
Posts: 50
Joined: Wed Oct 19, 2011 8:41 pm

Re: Other Date Ranges ...

Postby Bob Cergol » Wed Nov 23, 2011 7:41 pm

BTW... I think our mutual friend Fern mentioned to me that you're using P.SQL as the backend.
If that's right -- and your data dates back to the early days of DAW's Btrieve driver, then you have blank date values. SQL doesn't understand that -- and you'll find rows becoming invisible in many date selection scenarios. The only way I figured out around this was to use a combinatino of the Cast and Coalesce functions like this:

cast(coalesce(job.shipped,'1753-01-01') as date) as job_Shipped.

Coalesce returns a blank value as the string '1753-01-01' or an actual date value as a string. Cast then converts that string back to a date data type.

Also did you know you can create a System Parameter to display all those ugly "zero" dates as blanks in Dynamic AI output? -- i.e. '01/01/1900' or in my example '01/01/1753' would display as blank:
Key = Application
KeyName = ZERODATE
KeyType = DATE
DisplayVal = yyyy-mm-dd (i.e. whatever your zero date value is such as 1753-01-01 or 1900-01-01 - no quotes around the value)

Cheers,
Bob
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm

Re: Other Date Ranges ...

Postby robinsonky » Wed Nov 23, 2011 10:04 pm

Bob Cergol wrote:BTW... I think our mutual friend Fern mentioned to me that you're using P.SQL as the backend......
Bob


No, I have two sites (albeit they'll be one network very soon) and this one is MSSQL, but thanks for the tip. Once this one is sold in then I'll start trying to convert the head office too.

In the longer term (12 months?) I think we'll be MSSQL throughout. Pity, and Novell/PSQL has been a fantastic servant for 20 years+, but I can't bring in Novell/Btrieve knowledge in anyone under 40.
robinsonky
 
Posts: 50
Joined: Wed Oct 19, 2011 8:41 pm

Re: Other Date Ranges ...

Postby Bob Cergol » Wed Nov 23, 2011 10:22 pm

I'm way over 40! :mrgreen:

Back when I was selling an ERP type of system my 1st choice of backend was Btrieve. It was fast, incredibly sturdy and required no attention.

However -- it was the Btrieve -- so called "navigational engine" that I think we both loved, not their pasted-on so-called relational engine -- Scaleable SQL, then P.SQL -- but hey -- I think you're talking about the days before Novell became Unix!
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm


Return to Pervasive SQL

cron