Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Parameter fields, if null then all Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet Topic: Parameter fields, if null then all
    Posted: 06 Mar 2009 at 9:59am
Hi again!
 
This is mostly directed towards Dblank, since i'm going to be refering to a previous post to an answer he made like back in december... But i was wondering does the Add command function work for an expression?
I'm actually taking one of the fields from the table and just taking the last 6characters from it...
 
Would i be able to use the @field... or would i need to use the actual field itself that i'm trimming from?
 
here is a copy of the post i'm regarding....
 
Originally posted by DBlank

You can set up a command to do this.
In your Database expert there is the Add Command option just above your DB source.
Double click the Add command and put the following code
 
SELECT FIELD (your vendor field that you want to select from here)
FROM table (the table your vendor field is in)
UNION
SELECT '...All'
 
In the Database Expert, Join this new command on the vendorname field to the vendor table on the vendor name field.
 
Point your Paramenter field to this command field instead of your vendor.vendorname.
 
Change your select expert to handle this with an if statement.
if {?Paramter}="...All" then
{Vendor.vendorname}={Command.field}
else {?Parameter}={Command.field}
 
And the link to the entire thread if you'd like to review it...
 
Thank you!
-Takesen
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2009 at 11:30am
Hey Takesen,
In the ADD Command I believe you would have to refer to the actual field itself as the formula does not exist until after the data is pulled.
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet Posted: 06 Mar 2009 at 11:44am

Yea,

been working with it since i posted, i'm having some trouble with the parameter though.

 
See what i'm trying to do is basically
 
if isnull({?parameter}) then
      {@field} = '...All'
else
      {@field} = {?parameter}
 
i'm thinking i might need to replace my @field
with the {command.field}
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2009 at 12:20pm
Are you trying to make it so the options in the GUI select parameters are the last 6 characters of a field?
You can handle this in the UNION statement by triming the field you are selecting from the table.
e.g.
SELECT right(vendorfield,6) as L6Characters
FROM table
UNION
SELECT '...All'
 
IN your select statement then use the {?paramter} selected to = the last 6 characters of the connected field.
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2009 at 12:50pm
Now that I think about it, that would screw up the joins because this command changes your joining to the table. It might work not joining the Command if you only have one other table in the report...
Are you trying to make it so a user can select from a list that has a max of 6 characters where the last 6 characters of a field return all possible matches to multiple rows that match these 6 characters?


Edited by DBlank - 06 Mar 2009 at 12:53pm
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet Posted: 06 Mar 2009 at 1:25pm
.... okay i just spent 10mins typing out my reply... and acidently hit the wrong post reply button at the bottom of the page lol so i'm gonna try to make this sort and sweet.... *sigh*
 
 
I'm trying not to have any choices actually on the list, there's far too many numbers to actually be there. so i want them to  be able to input the 6characters to pull up that id_nbr.
 
basically our system is storing id_nbrs as 12char string. looks like:
000000123456
 
We only use the 123456
so there's no point in having to enter 000000 every time we wanna run the report... And it just looks ugly on the actual report itself.
 
So i want them to be able to enter id_nbr 123456
and it pull up that person...
or id_nbrs... 123456, 654321, ect.
and pull up that list of persons.
 
BUT if they do not enter ANY number. i want it to pull up ALL persons
 
If that's not clear lemme know i'll try to clarify the best i can.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2009 at 1:44pm
Cool.
Set your paramter field up as a string with a max length of 6
If your GUI accepts a NULL then use the select criteria:
If isnull({?Parameter})=False then {?parameter}=right(table.id_nbr,6)
 
Leave the else off and it will handle all values when parameter isnull.
If it doesn't handle NULL values select a number you want to substitute for all (example "999999") and change your criteria to that:
If {?Parameter}<>"999999" then {?parameter}=right(table.id_nbr,6)
Leave the else off and it will handle all values when parameter =999999.


Edited by DBlank - 06 Mar 2009 at 1:46pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2009 at 1:55pm
FYI - I have almost stopped a reply or 2 because hitting the wrong post button after a lengthy typing session Wacko
 
This time though on the above post I held down my CTRL key too long and set filters on accidently and then couldn't get them back off. Had to log out of the OS and then back in just to finish that one. Making me laugh though.
IP IP Logged
Takesen
Senior Member
Senior Member


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
Quote Takesen Replybullet Posted: 06 Mar 2009 at 2:06pm
Hmmm...
i must be doing something wrong...
 
i set up the parameter
string...max 6characters
have the value of 999999 in there.
any other value is manually enetered....
 
then i have ...
 
if {?parameter} <> "999999" then
     {?parameter}= right({person.id_nbr},6)
 
no go...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2009 at 2:13pm
you did change the {?paramater} to then name of what you called the parameter?
Does either part work? All if you type in 999999
or just one if yuo type in a valid ID#?


Edited by DBlank - 06 Mar 2009 at 2:15pm
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.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.