Solved

Parameters - bring all values back

Posted on 2014-09-22
6
115 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
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 34

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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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 34

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

805 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