Give user option of Select ing one county or all counties

[kpoore]

Hi,
I am doing a report where I want users to be able to select one county from list or all counties.In the past, two separate reports were done. There has got to be a way to do this in one report.
Also, on the same report I want to give the user the ability to select all records that have a certain field blank - this will tell them that these records still need work .
Yes, I am a beginner (-:
Thanks you guys,
Karen Poore

 

[IdoMillet]

Karen,

Add to the list an option of 'ALL'
and in your record selection formula have
something like:

{Country} = {?Country} OR {?Country}='ALL'

Alternatively, you can turn on the
'Allow Multiple Values' option in the
parameter design dialog (which would let
the user pick a number of specific countries.

Cheers,
- Ido ixm7@psu.edu

 

[IdoMillet]

On the second question, you can create a
parameter and control your record seelction
using an IsNull() condition (if by blank you mean Null)
if the parameter value indicates that that condition should be used.

Cheers,
- Ido ixm7@psu.edu

 

[dgillz]

If {?county}="ALL" then true else
{?county}={county}

This will return all values if the parameter field = "ALL", and will return only the county you selected if it is not all.


Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[kpoore]

Ido,
Thanks!
What I had done was:
{rAmended_Program.cscd_title} = {?CSCD}
or {rAmended_Program.cscd_title} = {?CSCDs}
or {rAmended_Program.program_title} = {?Range}
Which gave me 3 options: 1. Set to select any 1 county 2. Set for multiple values where user could add their selection of particular cos 3. Range - where it begin with 1st county on list then set to no upper bounds.
Now with your help:
{rAmended_Program.cscd_title} = {?CSCDs}
or {?CSCDs} = 'ALL'
or {rAmended_Program.program_title} = {?CSCD}
Where user can select all counties, one county or add multiple values (counties) to view.
I did get the idea of IsNull (don't really know how to use it) but could not get it to work.
I'll work on it for a while. Is this going to be included in my above selection statement? Seems like they all go together. Have not worked with SQL designer yet.
I am so glad I found this list. Our programmer quit and they gave Crystal 8.5 to me to learn - no one else knows it!
Karen Poore - State of Texas

 

[kpoore]

Ido,
On my IsNull problem, heres what I put:{rAmended_Program.cscd_title} = {?CSCDs}
or {?CSCDs} = 'ALL'
or {rAmended_Program.program_title} = {?CSCD}
or IsNull ({?Accepted})
I could not put IsNull into my parameter list because field value is a DateTime data type. I do want people able to bring up all records where this one field: Accepted Date is blank.
Any suggestions? My brain is already onto Sat (-: Not to mention I do not know what I am doing - but I am trying - and getting paid to do it!!!
Karen

 

[IdoMillet]

Hi Karen,

Add a parameter than asks the user if they want to include only records with no dates in your date field.

When you create that parameter (let's assume you call it "Only_Null_Dates", select its data type as Boolean. The user will be able to select True or False.

In your record selection formula you then add a condition such as:

...AND
(IsNull({your_date_field}) OR NOT {?Only_Null_Dates} )

This would ensure that if the user selected true for the date restriction option, only records with Null dates would be included.

Cheers,
- Ido




ixm7@psu.edu

 

[Ngolem]

IDO:

this formula in a record select won't deliver what you are saying:

"...AND
(IsNull({your_date_field}) OR NOT {?Only_Null_Dates} )

This would ensure that if the user selected true for the date restriction option, only records with Null dates would be included. "

Since it is based on "OR" then it will always draw in Null fields...the parameter field would be irrelevant...

KPoore:

I would attack your problem in this manner:

create 2 parameters as was suggested:

{?selectCounty} selecting "ALL" or the county name...in my formula method you would also allow a partial name so that the user would not be forced to memorize the exact spelling of the county

the record select statement would be

uppercase({table.county}) like switch
(
uppercase({?selectCounty}) = "ALL","*",
true,uppercase({?selectCounty}) + "*"
)

An uppercase comparison eliminates any case sensitivity and "uppercase({?selectCounty}) + "*" " catches complete or partial spellings of the county"

As for null values...the above formula will not work for Null values and actually I think would render the formula unworkable period if you know nulls were allowed.

create the numeric parameter to give the option to show records {?ReportType}

1. show county records with null records
2. show county records without null records
3. show only null records

then your record select could be

if {?ReportType} = 1 then
( isnull({table.county}) or
uppercase({table.county}) like switch
(
uppercase({?selectCounty}) = "ALL","*",
true,uppercase({?selectCounty}) + "*"
)
)
else if {?ReportType} = 2 then
( not isnull({table.county}) and
uppercase({table.county}) like switch
(
uppercase({?selectCounty}) = "ALL","*",
true,uppercase({?selectCounty}) + "*"
)
)
else
isnull({table.county}) ; // this would be the default

I think that will do the trick.

hope this helps

Jim

 

[kpoore]

THANKS big time Ido and Jim.
I work 10 hr days so I am off on Monday. First thing Tuesday morning I'll give it a shot.
You guys are widing my scope/mind on all of this (-:
Have a nice weekend,
Karen

 

[dgillz]

Since the counties are static values, add a drop down list with all the county names as default values. "ALL" can also be added to the list, and you can make the parameter so it does not accept additional input, i.e. they must pick from the list. Then use the formula I posted earlier, and you won't need to worry about upper and lower case sensitivity or misspellings. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[IdoMillet]

Jim,

Please explain again why you think my solution would be problematic. Karen wanted a way to:

"...give the user the ability to select all records that have a certain field blank - this will tell them that these records still need work."

The expression I suggested:
...AND
(IsNull({your_date_field}) OR NOT {?Only_Null_Dates} )

would do exactly that.

Cheers,
- Ido ixm7@psu.edu

 

[kpoore]

Hi Ido,
Well, it worked! I do not know 100% why though. Here's the select statement:
{rAmended_Program.cscd_title} = {?CSCDs}
or {?CSCDs} = 'ALL'
and (IsNull({rAmended_Program.accepted_date})
or not {?OnlyNullDates})
What is this last IsNull... actually saying?
Is it something like if user select true at prompt the first part holds - if true is not selected then defaults to False (I noticed in parameter query when prompted it's default is false).

Thanks so much,
Karen

 

[IdoMillet]

Karen,

and (IsNull({rAmended_Program.accepted_date})
or not {?OnlyNullDates})

simply means that records are always selected if the date is null. If the date is not null, than it would survive the cut only if the parameter is NOT requiring Nulls.

Glad it worked for you )

Cheers,
- Ido


ixm7@psu.edu