Solved

Parameters - bring all values back

Posted on 2014-09-22
6
120 Views
Last Modified: 2014-09-23
I am using CR2008

I using a SQL command in my report.

I have a number values in a parameter and it allows multiple values

The synatx is as follows

where (MA_SchemeID IN {?Scheme} and {?Scheme}  = 0)

I am using {?Scheme}  = 0 to bring back all values, but this is not working

Can anybody help with the correct syntax?

Thanks
0
Comment
Question by:halifaxman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Expert Comment

by:Ido Millet
ID: 40336878
Change "and" to "OR"
0
 

Author Comment

by:halifaxman
ID: 40338512
Thanks for the answer

I have changed it to or and get the following error message

An expression of non-boolean type specified in a context where a condition is expected near a ','
0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 40338717
FWIW, Ido was correct about needing OR there.  As for the error ...

 If the parameter allows multiple values, then I would guess that {?Scheme}  = 0 is getting translated into something like (1,2,3) = 0, and that's what is causing the error.

 Try this

where (MA_SchemeID IN {?Scheme} OR 0 IN {?Scheme})

 Note that if you enter values for the parameter like 1, 2, 3 and 0, that would count as "all", because one of the parameter values is 0.

 Also, now that I think about it, if you think that you might be using the "all" option a lot, it might be more efficient to put the "all" test first:

where (0 IN {?Scheme} OR MA_SchemeID IN {?Scheme})

 It probably wouldn't make much difference.  It just occurred to me that it might be more efficient.

 James
0
Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

 
LVL 100

Expert Comment

by:mlmcc
ID: 40338990
If that doesn't help, post the new formula.

If this is part of the selection formula then please post the full formula.

mlmcc
0
 

Author Closing Comment

by:halifaxman
ID: 40339169
Thanks - much appreciated
0
 
LVL 35

Expert Comment

by:James0628
ID: 40339388
You're welcome.  FWIW, Ido really deserves some of the points, because he posted part of the solution.  If you like, you can ask to have the question re-opened and then split the points between us.

 James
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question