×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Creating a Parameter that contains Multiple Default Values

Creating a Parameter that contains Multiple Default Values

Creating a Parameter that contains Multiple Default Values

(OP)
I am trying to create a report that has multiple default parameters.  I have tried creating a command as seen below however it returns all values and not the ones I have specified.  I am using Crystal 11.  My Table name is CEVENT and my field Name is CEVENT_DESC.

Why is this not working - anyone have any other solutions on how I can have a report have multiple default values and allow a user to add additional values>  Please HELP!

SELECT 'PART 1 CRIME' AS CEVENT_DESC FROM CEVENT  
UNION
SELECT DESTINCT CEVENT_DESC FROM CEVENT
WHERE (CEVENT_DESC = 'ASSAULT IP') or (CEVENT_DESC = 'ASSAULT JO') or (CEVENT_DESC ='ASSAULT PE') or (CEVENT_DESC ='BURGLARY IP') or (CEVENT_DESC ='BURGLARY JO') or (CEVENT_DESC ='BURGLARY PE') or (CEVENT_DESC ='CDS IP') or  (CEVENT_DESC ='CDS JO') or (CEVENT_DESC ='CDS PE') or (CEVENT_DESC ='DOMESTIC IP') or (CEVENT_DESC ='DOMESTIC JO') or (CEVENT_DESC = 'DOMESTIC PE') or (CEVENT_DESC ='DIS,DERLY IP') or (CEVENT_DESC ='DIS,DERLY JO') or (CEVENT_DESC ='DISODERLY PE')or (CEVENT_DESC = 'RAPE') or (CEVENT_DESC ='ROBBERY IP') or (CEVENT_DESC ='ROBBERY PE') or (CEVENT_DESC =' ROBBERY JO') or (CEVENT_DESC ='THEFT IP') or (CEVENT_DESC ='THEFT JO') or  (CEVENT_DESC = 'THEFT PE') or (CEVENT_DESC ='TRAFFIC STOP') or (CEVENT_DESC ='VANDALISM JO') or (CEVENT_DESC ='VANDALISM IP') or (CEVENT_DESC ='VANDALISM PE')

 

RE: Creating a Parameter that contains Multiple Default Values

Is your database case sensitive ?

Other than that the query is fine. You can optimize it by changing
SELECT 'PART 1 CRIME' AS CEVENT_DESC FROM CEVENT
to
SELECT 'PART 1 CRIME' AS CEVENT_DESC
As it is now the first clause returns as many records 'PART 1 CRIME' as many records you have in CEVENT table . (Because of the UNION these records are shown as distinct and that is why you see just one)
The other possible optimization is to use numeric ID ( if you have one in CEVENT table) This will actually resolve the issue if the database is case sensitive. I assume that your data is normalized and you have a list of something in CEVENT table with CEVENT_ID and CEVENT_DESC

Then the query may be transformed to:
SELECT 'PART 1 CRIME' AS CEVENT_DESC
UNION
SELECT CEVENT_DESC FROM CEVENT
WHERE CEVENT_ID IN (1,2,3,.....100,101)


If you have multiple records with same CEVENT_DESC then my assumption is wrong  

www.R-Tag.com   Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

RE: Creating a Parameter that contains Multiple Default Values

You should use the command to show only those values that are NOT in the default list, like this:

SELECT DISTINCT CEVENT_DESC
FROM CEVENT
WHERE Not(
(CEVENT_DESC = 'ASSAULT IP') or (CEVENT_DESC = 'ASSAULT JO') or (CEVENT_DESC ='ASSAULT PE') or (CEVENT_DESC ='BURGLARY IP') or (CEVENT_DESC ='BURGLARY JO') or (CEVENT_DESC ='BURGLARY PE') or (CEVENT_DESC ='CDS IP') or  (CEVENT_DESC ='CDS JO') or (CEVENT_DESC ='CDS PE') or (CEVENT_DESC ='DOMESTIC IP') or (CEVENT_DESC ='DOMESTIC JO') or (CEVENT_DESC = 'DOMESTIC PE') or (CEVENT_DESC ='DIS,DERLY IP') or (CEVENT_DESC ='DIS,DERLY JO') or (CEVENT_DESC ='DISODERLY PE')or (CEVENT_DESC = 'RAPE') or (CEVENT_DESC ='ROBBERY IP') or (CEVENT_DESC ='ROBBERY PE') or (CEVENT_DESC =' ROBBERY JO') or (CEVENT_DESC ='THEFT IP') or (CEVENT_DESC ='THEFT JO') or  (CEVENT_DESC = 'THEFT PE') or (CEVENT_DESC ='TRAFFIC STOP') or (CEVENT_DESC ='VANDALISM JO') or (CEVENT_DESC ='VANDALISM IP') or (CEVENT_DESC ='VANDALISM PE')
)

Use this to populate the picklist. Use prompt text that says "In addition to Part I Crimes, what other crimes would you like to report on?"

Then use a selection formula like this:

(
(
(CEVENT_DESC = 'ASSAULT IP') or (CEVENT_DESC = 'ASSAULT JO') or (CEVENT_DESC ='ASSAULT PE') or (CEVENT_DESC ='BURGLARY IP') or (CEVENT_DESC ='BURGLARY JO') or (CEVENT_DESC ='BURGLARY PE') or (CEVENT_DESC ='CDS IP') or  (CEVENT_DESC ='CDS JO') or (CEVENT_DESC ='CDS PE') or (CEVENT_DESC ='DOMESTIC IP') or (CEVENT_DESC ='DOMESTIC JO') or (CEVENT_DESC = 'DOMESTIC PE') or (CEVENT_DESC ='DIS,DERLY IP') or (CEVENT_DESC ='DIS,DERLY JO') or (CEVENT_DESC ='DISODERLY PE')or (CEVENT_DESC = 'RAPE') or (CEVENT_DESC ='ROBBERY IP') or (CEVENT_DESC ='ROBBERY PE') or (CEVENT_DESC =' ROBBERY JO') or (CEVENT_DESC ='THEFT IP') or (CEVENT_DESC ='THEFT JO') or  (CEVENT_DESC = 'THEFT PE') or (CEVENT_DESC ='TRAFFIC STOP') or (CEVENT_DESC ='VANDALISM JO') or (CEVENT_DESC ='VANDALISM IP') or (CEVENT_DESC ='VANDALISM PE')
) or
CEVENT_DESC = {?Parameter}
)

This would return all Part I Crime valus plus any others the user selects.

-LB




  

RE: Creating a Parameter that contains Multiple Default Values

(OP)
I am sorry, this isn't helping to solve the problem I am having.  All I need to be able to do is always select the following (CEVENT_DESC = 'ASSAULT IP') or (CEVENT_DESC = 'ASSAULT JO') or (CEVENT_DESC ='ASSAULT PE') or (CEVENT_DESC ='BURGLARY IP') or (CEVENT_DESC ='BURGLARY JO') or (CEVENT_DESC ='BURGLARY PE') or (CEVENT_DESC ='CDS IP') or  (CEVENT_DESC ='CDS JO') or (CEVENT_DESC ='CDS PE') or (CEVENT_DESC ='DOMESTIC IP') or (CEVENT_DESC ='DOMESTIC JO') or (CEVENT_DESC = 'DOMESTIC PE') or (CEVENT_DESC ='DIS,DERLY IP') or (CEVENT_DESC ='DIS,DERLY JO') or (CEVENT_DESC ='DISODERLY PE')or (CEVENT_DESC = 'RAPE') or (CEVENT_DESC ='ROBBERY IP') or (CEVENT_DESC ='ROBBERY PE') or (CEVENT_DESC =' ROBBERY JO') or (CEVENT_DESC ='THEFT IP') or (CEVENT_DESC ='THEFT JO') or  (CEVENT_DESC = 'THEFT PE') or (CEVENT_DESC ='TRAFFIC STOP') or (CEVENT_DESC ='VANDALISM JO') or (CEVENT_DESC ='VANDALISM IP') or (CEVENT_DESC ='VANDALISM PE')

and then give the user the option to add others from a pick list.  Is there a way to do that.

 

RE: Creating a Parameter that contains Multiple Default Values

That's what my suggestion should do--so please explain in what way it did not work.

-LB

RE: Creating a Parameter that contains Multiple Default Values

(OP)
It is disregarding the rest of my selection critera such as the date range and departments and returning all information in the database.

RE: Creating a Parameter that contains Multiple Default Values

Please show the actual formulas you used and explain where you entered them.

-LB

RE: Creating a Parameter that contains Multiple Default Values

(OP)
(
({@Created Date} = {?Date} and
{CADUNITLOG.CADUNITLOG_UNIT_CLASS} = "L" and
{CADUNITLOG.CADUNITLOG_ON_DEPT} = "04")

and

{CEVENT.CEVENT_DESC} = {?Events to Show} or {CEVENT.CEVENT_DESC} = 'ASSAULT IP' or {CEVENT.CEVENT_DESC} = 'ASSAULT JO' or {CEVENT.CEVENT_DESC} ='ASSAULT PE' or {CEVENT.CEVENT_DESC} ='BURGLARY IP' or {CEVENT.CEVENT_DESC} ='BURGLARY JO' or {CEVENT.CEVENT_DESC} ='BURGLARY PE' or {CEVENT.CEVENT_DESC} ='CDS IP' or {CEVENT.CEVENT_DESC} ='CDS JO' or {CEVENT.CEVENT_DESC} ='CDS PE' or {CEVENT.CEVENT_DESC} ='DOMESTIC IP' or {CEVENT.CEVENT_DESC} ='DOMESTIC JO' or {CEVENT.CEVENT_DESC} = 'DOMESTIC PE' or {CEVENT.CEVENT_DESC} ='DIS,DERLY IP' or {CEVENT.CEVENT_DESC} ='DIS,DERLY JO' or {CEVENT.CEVENT_DESC} ='DISODERLY PE'or {CEVENT.CEVENT_DESC} = 'RAPE' or {CEVENT.CEVENT_DESC} ='ROBBERY IP' or {CEVENT.CEVENT_DESC} ='ROBBERY PE' or {CEVENT.CEVENT_DESC} =' ROBBERY JO' or {CEVENT.CEVENT_DESC} ='THEFT IP' or {CEVENT.CEVENT_DESC} ='THEFT JO' or {CEVENT.CEVENT_DESC} = 'THEFT PE' or {CEVENT.CEVENT_DESC} ='TRAFFIC STOP' or {CEVENT.CEVENT_DESC} ='VANDALISM JO' or {CEVENT.CEVENT_DESC} ='VANDALISM IP' or {CEVENT.CEVENT_DESC} ='VANDALISM PE')

RE: Creating a Parameter that contains Multiple Default Values

Should be:

{@Created Date} = {?Date} and
{CADUNITLOG.CADUNITLOG_UNIT_CLASS} = "L" and
{CADUNITLOG.CADUNITLOG_ON_DEPT} = "04" and
(
{CEVENT.CEVENT_DESC} = {?Events to Show} or
{CEVENT.CEVENT_DESC} = 'ASSAULT IP' or
{CEVENT.CEVENT_DESC} = 'ASSAULT JO' or
{CEVENT.CEVENT_DESC} ='ASSAULT PE' or
{CEVENT.CEVENT_DESC} ='BURGLARY IP' or
{CEVENT.CEVENT_DESC} ='BURGLARY JO' or
{CEVENT.CEVENT_DESC} ='BURGLARY PE' or
{CEVENT.CEVENT_DESC} ='CDS IP' or
{CEVENT.CEVENT_DESC} ='CDS JO' or
{CEVENT.CEVENT_DESC} ='CDS PE' or
{CEVENT.CEVENT_DESC} ='DOMESTIC IP' or
{CEVENT.CEVENT_DESC} ='DOMESTIC JO' or
{CEVENT.CEVENT_DESC} = 'DOMESTIC PE' or {CEVENT.CEVENT_DESC} ='DIS,DERLY IP' or {CEVENT.CEVENT_DESC} ='DIS,DERLY JO' or {CEVENT.CEVENT_DESC} ='DISODERLY PE' or
{CEVENT.CEVENT_DESC} = 'RAPE' or
{CEVENT.CEVENT_DESC} ='ROBBERY IP' or
{CEVENT.CEVENT_DESC} ='ROBBERY PE' or
{CEVENT.CEVENT_DESC} =' ROBBERY JO' or
{CEVENT.CEVENT_DESC} ='THEFT IP' or
{CEVENT.CEVENT_DESC} ='THEFT JO' or
{CEVENT.CEVENT_DESC} = 'THEFT PE' or
{CEVENT.CEVENT_DESC} ='TRAFFIC STOP' or {CEVENT.CEVENT_DESC} ='VANDALISM JO' or {CEVENT.CEVENT_DESC} ='VANDALISM IP' or {CEVENT.CEVENT_DESC} ='VANDALISM PE'
)

The parens here are not optional.

-LB

RE: Creating a Parameter that contains Multiple Default Values

(OP)
Ok got it to work.... now is there a way to add an 'ALL' Parameter in the selection list as well?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close