Parameter fields, if null then all
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5713
Printed Date: 03 May 2024 at 6:37pm
Topic: Parameter fields, if null then all
Posted By: Takesen
Subject: Parameter fields, if null then all
Date 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...
http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=5050&KW - http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=5050&KW
Thank you!
-Takesen |
Replies:
Posted By: DBlank
Date 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.
|
Posted By: Takesen
Date 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
mailto:%7b@field - {@field } = '...All'
else
mailto:%7b@field - {@field } = {?parameter}
i'm thinking i might need to replace my @field
with the {command.field}
|
Posted By: DBlank
Date 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.
|
Posted By: DBlank
Date 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?
|
Posted By: Takesen
Date 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.
|
Posted By: DBlank
Date 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.
|
Posted By: DBlank
Date 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 
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.
|
Posted By: Takesen
Date 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...
|
Posted By: DBlank
Date 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#?
|
Posted By: Takesen
Date Posted: 06 Mar 2009 at 2:26pm
|
yes i did change the {?parameter} to the actual name {?id_nbr}... neither work. |
Posted By: Takesen
Date Posted: 06 Mar 2009 at 2:29pm
|
Originally posted by DBlank
FYI - I have almost stopped a reply or 2 because hitting the wrong post button after a lengthy typing session
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. lol i just read that...
yea, the original post before i lost it was a lot more in depth and thorough haha... yea i hate that button sometimes =( because the "post your reply" and the "reply" buttons are labeled the same thing.... lol ah well
|
Posted By: DBlank
Date Posted: 06 Mar 2009 at 2:30pm
|
You could also convert this to using numbers. May be more reliable than text.
or you can add in the else part but I don't think you need it
if {?parameter} < 999999 then
{?parameter}= tonumber({person.id_nbr},0) else {person.id_nbr}={person.id_nbr}
|
Posted By: Takesen
Date Posted: 06 Mar 2009 at 2:38pm
|
*sigh* when i go to put 999999 into the criteria i get:
"this array must be subscripted. For example: Array"
|
Posted By: Takesen
Date Posted: 06 Mar 2009 at 2:52pm
|
Alright, i got it.
I used the text you suggested the first time.
just needed to reverse the order....
if {?id_nbr} <> "999999" then
mailto:%7b@id_nbr%7d=%7b?id_nbr - {@id_nbr}={?id_nbr } else mailto:%7b@id_nbr%7d=%7b@id_nbr - {@id_nbr}={@id_nbr } on the then part,
yours had
mailto:%7b?id_nbr%7d=%7b@id_nbr - {?id_nbr}={@id_nbr }
but it was filling the left with the right side.
when i reversed it, it worked perfectly.
Thank you very much Dblank!
couldn't have done it without you! =)
|
Posted By: DBlank
Date Posted: 06 Mar 2009 at 2:54pm
|
Weird,
didn't know the order in the then matters but that is good to know.
Thanks for the update and good luck finishing it up
 |
Posted By: Takesen
Date Posted: 06 Mar 2009 at 3:02pm
|
Thanks!
I thought it was kinda odd too...
But it works! i'm not gonna complain! lol
 |