?
Solved

Crystal Report Parameter select All number format

Posted on 2008-10-31
12
Medium Priority
?
782 Views
Last Modified: 2013-11-15
I have a crystal Report that has a number of subreports, each linked by PO Number and PO Line (both being defined as Parameters).  If the PO Line is blank, I want to select all lines within the subquery.  Problem is that Crystal will not allow null values.  It has been suggested to use '*' if the parameter was defined as a string, however, PO Line is a numeric field.  In each subquery I had the following logic:
WHERE
   {?POLine} is Null Or PO_LINE = {?POLine}
How do I best handle this?
Thanks.

0
Comment
Question by:lilputian
[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
  • 5
12 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 22856728
What version of Crystal are you using>...?

0
 
LVL 1

Author Comment

by:lilputian
ID: 22857105
Version 11.0.0.1282
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 22858290
Please post the record selection criteria from one of the linked subreprots.  This is created automatically when you link a subreport to a main report.  It will also help us to modify it so that it meets your requirements.

Also, you stated you have subreports, but then mention subqueries.  When you say subqueries, do you really mean subreports?


0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:lilputian
ID: 22858333
Sorry, yes I mean subreports.  I have 4 different subreports that are linked to the main report to the 2 parameters.  The subreports use the CMD to create the SQL statement.  The SQL statement includes the WHERE statement as follows:

WHERE
   PO_NUM = {?PONum}
   And ({?POLine} is Null Or PO_LINE = {?POLine})

The problem being that ?POLine can never be null because that parameter option is not avaliable.

 
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 22858566
To clarify:

1)  The end user will input parameters for both PO# and Line # (12345 and line 5, for example)?
2)  Alternately, the user wants to select "All lines"?

Try the following:

1)  Leave the joins like they are
2)  Drill down through the Crystal Reports field explorer in the main report to the parameters and edit the Line Number parameter to have it's first value be  a number that is grossly outside the range of possible line number (999999 is a good one to use).  Make sure this value is the first value in your pick list.  You've now created the numeric equivalent of an explicit "All Lines" option.
3)  Set the description to be "All lines" and set the property for show description only to be true.  Allow the user to input custom values (like 5, for example).
4)  Modify the WHERE clause in the subreport to be:

WHERE
   PO_NUM = {?PONum}
   And ({?POLine} =  999999 Or PO_LINE = {?POLine})

Now, when the user runs the report, they'll see a nice, plain English option for "All Lines", which will pass a dummy value to the database for processing.  If the user selects "All Lines" then that parameter value will be meaningless to the query and all lines will be returned.


0
 
LVL 1

Author Comment

by:lilputian
ID: 22858608
I like it ... sounds like this could work.  I'll try it out.
0
 
LVL 1

Author Comment

by:lilputian
ID: 22858852
So now, what happens is that the Parameter input for the PO Line shows a pick list as well as a box for entering a value.  The pick list contans 2 lines, one for All Lines and the other as ....  When you select all lines from the pick list, it displays 999,999 in the entered value box.  Not sure I like that display, could be confusing to the users.  Maybe I should just define the field as string and then use the to_number function in my SQL statement.

What do you think?
0
 
LVL 1

Author Comment

by:lilputian
ID: 22858868
Is it possible to manipulate a parameter, ie. change it from string to number, and then link that new value to the subreports?  
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 800 total points
ID: 22858873
Well, first thing first - did it work?  If so, that's a good thing!

Secondly, if you convert the parameters to strings then account for that in your SQL for the sake of a more user friendly display, that's fine. I've had to do that for several reports (clients that like entering in dates in MM/DD/YYYY format instead of the Crystal Date format, for example.  Also, do you have more than 998 possible lines on a PO?  If not just use 999 as your default value, which gets rid of the comma, at least.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 22858891
Well you could make the parameter a string parameter with a default explicit value of 'All Lines' (as opposed to a number parameter with a text description) and still let users enter in an explicit value for a line number (5), but you'd probably have to check for the existence of a numeric  value for the parameter first.  It can probably be done but it seems unnecessarily complex.
0
 
LVL 1

Author Comment

by:lilputian
ID: 22858942
Just did some testing with a string option.  Seems to be too much manipulating within the WHERE condition.  I'll change it back to a numeric and use the 999, or I could possibly use 0, as a line should never = 0.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 22859068
Very cool - glad I could help:)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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