Follow on from Age Bands Report

[peterb58]

Now I want to do some Record Selection.

I have a Date_Of_Birth field which is a date value. A Parameter asks the user to select which month(s) they wish to view. The options are 'January', 'February' etc. Selecting those options is the easy bit. What I want to cover is if the customer wants to view all birth dates. How do I cope with an ALL option. The DOB is a date, but my paramer is a string. I know how to use MonthName to get the text value for a month in a date filed, but can I go back converting January to a number without a formula?

Thanks

Pete

 

[Turkbear]

Hi,
One way:
On your parameter list ( parameter should be a number type), have the Month name as the displayed value but use the month # as the passed value..Then, for ALL, pass a 0 and test for it first..

If {?Param} = 0 then
true
else
Month({DOBfield}) = {?Param}

Or something like that..

 

[lbass]

Try:

if {?Month} <> "All" then
monthname(month({table.dob})) = {?Month} else
if {?Month} = "All" then true

You need to add "All" as a default option in the parameter setup screen.

-LB

 

[peterb58]

Thanks guys,

Working fine. Answers are so simple when you know more of the tricks!!

Pete

 

[bdreed35]

Using "monthname(month({table.dob})) = {?Month}" is not going to allow that criteria to pass to the database, making your report less efficient than it could be.
You can verify that by checking the SQL Query under the Database menu when you have run the report for a particular month.

I can think of 2 ways to recitfy this, if you are interested. Both ways involve SQL Expressions.

The easiest way is to create a SQL Expression that uses the database monthname function. This may or may not be available depending on the db you are using.
The SQL Expression would then be used in your selection criteria like this:

if {?Month} <> "All" then
{%monthname}= {?Month} else
if {?Month} = "All" then true

If you don't have a monthname function, you can use the month function, and do the same thing, but then you need to alter your parameter.
THe parameter would need to be changed to a number type.
Then the default values would need to change to 1 to 12, and then the appropriate month name could be entered as the description.
You would then set the parameter to only display the description.


~Brian

 

[peterb58]

Brian,

Thanks for the extra info. We use Sybase SQL 7 at the moment, looking at moving to 9 soon. I can do some basic SQL, but most of the work will be done by views provided by the DEV team.

Running the supplied solutions on my database with 5000 dates did not seem to be very slow. The Database will not grow much bigger.

If speed does become an issue then we will look at moving more work to Sybase, but it is adequate at the moment.

many thanks