Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Multiple selection report Post Reply Post New Topic
Author Message
rsellers
Newbie
Newbie
Avatar

Joined: 04 Mar 2014
Location: United States
Online Status: Offline
Posts: 6
Quote rsellers Replybullet Topic: Multiple selection report
     Posted: 11 Jun 2014 at 5:55am
I have a report with 8 parameters from 3 tables.
I'm creating a searchable purchase order report.
I'm trying to create a report with multiple parameters selection options.
 
A date parameter sorts record between 2 dates.
Then I have additional parameters for:
closed/open status
PO Number
Po requestor
product name
Part number
Part type
Vendor
 
The idea of the report is to allow the user to select a date range to search, then be able to select one or more of the other parameter options to search and return the specif purchase order results.
 
For example:
Date range =  1/1/2014 to 6/1/2014
All purchase by requester Joe from vendor ABC with part type= laptop
 
I have the report working correctly with the date range and any one other parameter. But as soon as I add the third or more parameter selection to narrow down the results I get all sorts of bogus results.
 
I've tried all sort of combinations but this is my first attempt at multiple item searching. So far this is what I have:
IF {PURCHASE.PODATE} = {?PO Date range}
Then
{?Vendor} IN {PURCHASE.VENDOR}
XOR
{?Purchase Order Number} IN {PURCHASE.PONUM}
XOR
{?Part Type} In {POITEM.TYPE}
XOR
{?Prioduct Name} IN {POITEM.PRODUCT}
XOR
{?PO Requestor} IN {PURCHASE.RESPONS}
OR
{?Part Number} IN {POITEM.PART_NUM}
 
I can get {PURCHASE.PODATE} and any other parameter to return valid. But if I try selecting
{PURCHASE.PODATE} and {POITEM.TYPE} and {PURCHASE.RESPONS}
 
This should retun a list of purchase orders between the specified date where a {PURCHASE.RESPONS} has ordered a specific {POITEM.TYPE}
 
Can someone suggest a way to make this type of search work?

Thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Jun 2014 at 7:01am
are you using a version above XI? I think optional parameters were introduced after that. If you are not using at least 2008 you have to enter a value in each param which will change how you do this.
If you do not have the optional params you will have to have the user us an 'all' option for each param.
Which circumstance applies to your set up?
IP IP Logged
rsellers
Newbie
Newbie
Avatar

Joined: 04 Mar 2014
Location: United States
Online Status: Offline
Posts: 6
Quote rsellers Replybullet Posted: 11 Jun 2014 at 8:19am
The about says I'm using 11.5 version.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Jun 2014 at 8:54am
I do not recall if that version allows for optional params or not but I think it does not.
Here is one way to handle it, although I think you want to use dynamic params based on your other post. That causes issues because you need to get 'all' values into your dynamic lists.
 
({PURCHASE.PODATE} = {?PO Date range})
and
({?Vendor}='All' or {?Vendor} IN {PURCHASE.VENDOR})
and
({?Purchase Order Number}=0 or {?Purchase Order Number} IN {PURCHASE.PONUM})
and
({?Part Type}='All' or {?Part Type} In {POITEM.TYPE})
and
({?Prioduct Name} = 'All' or {?Prioduct Name} IN {POITEM.PRODUCT})
and
({?PO Requestor}='All' or {?PO Requestor} IN {PURCHASE.RESPONS})
And
({?Part Number}=0 or {?Part Number} IN {POITEM.PART_NUM})


Edited by DBlank - 11 Jun 2014 at 8:55am
IP IP Logged
rsellers
Newbie
Newbie
Avatar

Joined: 04 Mar 2014
Location: United States
Online Status: Offline
Posts: 6
Quote rsellers Replybullet Posted: 11 Jun 2014 at 9:48am
DBlank,
I kept getting errors on the =o part.
 
({PURCHASE.PODATE} = {?PO Date range})
and
({?Vendor}='All' or {?Vendor} IN {PURCHASE.VENDOR})
and
{?Purchase Order Number} IN {PURCHASE.PONUM}
and
({?Part Type}='All' or {?Part Type} In {POITEM.TYPE})
and
({?Prioduct Name} = 'All' or {?Prioduct Name} IN {POITEM.PRODUCT})
and
({?PO Requestor}='All' or {?PO Requestor} IN {PURCHASE.RESPONS})
And
{?Part Number} IN {POITEM.PART_NUM}
 
I modified the section to match the above and it seems to work fine or at least the results are coming back when multiple criteria is selected.
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Jun 2014 at 10:21am
i guessed that some of these were numeric field types so i used zero as the 'all' option instead of the string 'All'. If these are all strings then the 'All' can function for them instead of the zero (=0).
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum