Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Record Selection Formula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
theotherdodge
Groupie
Groupie


Joined: 17 Nov 2008
Online Status: Offline
Posts: 40
Quote theotherdodge Replybullet Topic: Record Selection Formula
    Posted: 03 Dec 2009 at 7:24am

I have a report parameter that the user will enter a company number or multiple company numbers such as:  1,2,5

In the record selection formula, I can't quite get it to work.  It works if the user enters only 1 company, but if they enter multiple companies, it returns no records.  Here is what I have tried:
 
(if {?Company}<> 999 then ","+ToText({z_CustomerRankings.JCCo},0,'')+"," in ","+ToText({?Company},0,'')+"," else 1=1)
 
999 is the default in the parameter as there is no company 999.  The field is a number.
 
Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Dec 2009 at 7:48am
I assume you want 999 to be all records and that the original db field is an int the same as your param is. If so the following should work:
{?Company}= 999 or {?Company}={z_CustomerRankings.JCCo}
IP IP Logged
theotherdodge
Groupie
Groupie


Joined: 17 Nov 2008
Online Status: Offline
Posts: 40
Quote theotherdodge Replybullet Posted: 03 Dec 2009 at 7:51am
What happens if the user selects multiple companies such as 1,2,5 ?  How is that handled?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Dec 2009 at 7:55am
It should find all records =1 or =2 or =5.
If the enter 999 it will not filter at all hence the full record selection or 'all'.
Test it to make sure both parts work.
Sometimes an array error happens


Edited by DBlank - 03 Dec 2009 at 7:55am
IP IP Logged
theotherdodge
Groupie
Groupie


Joined: 17 Nov 2008
Online Status: Offline
Posts: 40
Quote theotherdodge Replybullet Posted: 03 Dec 2009 at 8:21am
Here is what I used:
 
(if {?Company}= 999 or {?Company}={z_CustomerRankings.JCCo} then ","+ToText({z_CustomerRankings.JCCo},0,'')+"," in ","+ToText({?Company},0,'')+"," else 1 = 1)
 
If I leave the parameter as 999 (which should be all companies) I dont get any results.
 
If I enter 1 company it gives the total of all companies.
 
If I enter multiple companies, it does not give me back any results
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Dec 2009 at 8:24am
Do not use any "if-then" or IN statements just use exactly:
 
{?Company}= 999 or {?Company}={z_CustomerRankings.JCCo}
IP IP Logged
theotherdodge
Groupie
Groupie


Joined: 17 Nov 2008
Online Status: Offline
Posts: 40
Quote theotherdodge Replybullet Posted: 03 Dec 2009 at 8:54am
Here is my record formula:
 

{?Company}= 999 or {?Company}={z_CustomerRankings.JCCo} and

{z_CustomerRankings.Mth} >= {?BeginMonth} and

{z_CustomerRankings.Mth} <={?EndMonth}

for all companies (leaving the parameter to the default) it gives me for all date ranges even though I put in a range of dates

for single companies, it works fine
 
for multiple companies (2,3) I dont get anything
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Dec 2009 at 9:04am
You have to parenth the OR statement so it applies it as a single condition then add your AND statements. If you don't it will stop evaluating as soon as it hits one OR statement that it can satisfy:

({?Company}= 999 or {?Company}={z_CustomerRankings.JCCo})

and

{z_CustomerRankings.Mth} >= {?BeginMonth}

and

{z_CustomerRankings.Mth} <={?EndMonth}

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Dec 2009 at 9:06am
I think there is a problem with your second statement part as well but can't tell as I am not familiar with your data set.
If you get no results using the parneth statement I gave you above then get rid of the date part to test and see if I am correct.


Edited by DBlank - 03 Dec 2009 at 9:07am
IP IP Logged
theotherdodge
Groupie
Groupie


Joined: 17 Nov 2008
Online Status: Offline
Posts: 40
Quote theotherdodge Replybullet Posted: 03 Dec 2009 at 12:47pm
Ok, I changed the formula to reflect what you have.
 
For a single company, it works
For all companies (leaving the default 999) it works
For multiple companies (2,3) Nothing returns
 
Maybe Im not clear on how Im having the user input multiple companies...  They actually enter each company followed by a comma just like above.  Not sure if that matters...
 
The Month part is fine.  I tried it without it to test the first part...
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
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



This page was generated in 0.016 seconds.