Creating a Parameter that contains Multiple Default Values

[hootlehead]

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')

 

[RTag]

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

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[lbass]

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

 

[hootlehead]

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.

 

[lbass]

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

-LB

 

[hootlehead]

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

 

[lbass]

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

-LB

 

[hootlehead]

(
({@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')

 

[lbass]

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

 

[hootlehead]

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