very simple parameter ?

[jhermiz2]

I created a parameter called "FunctionalGroup" to allow managers to limit the records returned by a functional department from our company. Works fine if the user selects a value for this parameter....

But I want it so that if NO selections are made to show ALL functional groups. Do I need to change this in the SELECT EXPERT section formula? For instance, a department can be BD (Business Development) and it will display all the records JUST for this department. But I want it so that if NO selection is made in functional group to show all departments.

I'm a newbie....

JOn

 

[jhermiz2]

Right now I have it so that it is...

{dbo_TBLEMPLOYEE.DEPARTMENT} = {?FunctionalGroup}

But I really need it to if they select a group from the parameter it shows just that group. But if they dont i want to show all of them. Surely this is simple with something involving the '*' I'm just not used to crystal syntax.

 

[BurnsORegan]

Try putting this into the selection criteria:

(
if {?FunctionalGroup} = ""
then {YOURFIELD} <> ".." //or something it will neve equal
else {YOURFIELD} = {?FunctionalGroup}

and
)

From here it is not part of the dselection criteria:

You must replace {YOURFIELD} with the relevant field name i.e. the one you currently are doing the Selection on.

Good Luck

BurnsORegan

 

[jhermiz2]

Where do I put this? Can you give a bit of a direction. Do I go to the top to "Select Expert" select that tab...and type that in?

Jon

 

[jhermiz2]

that worked thank you.

Jon

 

[BurnsORegan]

That's right. In your selection formula (Select Expert / Show Formula / Formula Editor)

You can just cut and paste it,here it is with you field names, put it at the top to ensure the AND is correct.

(
if {?FunctionalGroup} = ""
then {dbo_TBLEMPLOYEE.DEPARTMENT} <> ".." //or something it will neve equal
else {dbo_TBLEMPLOYEE.DEPARTMENT} = {?FunctionalGroup}
)
and


BurnsORegan

 

[jhermiz2]

Burns one last question if you dont mind. I want to create another parameter for the actual employee name.

The problem is the fields have LNAME And FNAME ... but we have several employees with the same LNAME. So is it possible to join these two and allow the user to select from that?

The thing is when I create a parameter and goto set defaults it only allows me to choose one field.

Thanks,
Jon

 

[BurnsORegan]

Create a Parameter field called ActualName:
Create a formula fiel which concatenates the 2 names, call it FullName: (put this line into it)

{FNAME} & " " & {LNAME}

Put the formula field into the detail section of the report and then into you selection criteria add:

AND {@FullName} = {?ActualName}

That should then do it. Note the space between the " ", you could put a comma here or leave that part out depending on your requirement of the user running the report.

I suggest for ease of use, in your parameter field "ActualName" pre-define the data possible for selection so as not to incur typing problems.

Good luck

BurnsORegan

 

[Turkbear]

Hi, Just a small refinement which may speed up the return of records when no selection is made:

(if {?FunctionalGroup} = ""  then
 True
 else
 {dbo_TBLEMPLOYEE.DEPARTMENT} = {?FunctionalGroup})
AND
(if {?ActualName} = "" then
True
else
{@FullName} = {?ActualName})

The True eliminates any value checking..

 

[BurnsORegan]

Apologies and thanks for tip. I am not a coder by Skill.....

Is it working though?

 

[jhermiz2]

This doesnt work!!

If {?EmpName} = ""
then {dbo_TBLEMPLOYEE.LNAME} <> ".."
else {dbo_TBLEMPLOYEE.LNAME} = {?EmpName} and

if {?FunctionalGroup} = ""
then {dbo_TBLEMPLOYEE.DEPARTMENT} <> ".." //or something it will never equal
else {dbo_TBLEMPLOYEE.DEPARTMENT} = {?FunctionalGroup} and
{TBLWORKORDR.WORKORDERNUMBER} <> "IMEAL" and
{dbo_TBLLABOR.ACTDATESTART} in {?Start Date}

Please help...

the empname when not selected and then I select a functional group it shows all the functional groups!!!

 

[jhermiz2]

I changed it to this:

if {?FunctionalGroup} = ""
 then True //{dbo_TBLEMPLOYEE.DEPARTMENT} <> ".." //or something it will never equal
 else {dbo_TBLEMPLOYEE.DEPARTMENT} = {?FunctionalGroup} and

(if {?EmpName} = "" then
True
else
{dbo_TBLEMPLOYEE.LNAME} = {?EmpName}) and
{TBLWORKORDR.WORKORDERNUMBER} <> "IMEAL" and
{dbo_TBLLABOR.ACTDATESTART} in {?Start Date}

It works fine if I dont select a LNAME. But If I do select an EmpName (LNAME) than it shows ALL employees...

 

[jhermiz2]

Wow...
I changed it to this:

(if {?EmpName} = "" then
True
else
{dbo_TBLEMPLOYEE.LNAME} = {?EmpName}) and
if {?FunctionalGroup} = ""
 then True //{dbo_TBLEMPLOYEE.DEPARTMENT} <> ".." //or something it will never equal
 else {dbo_TBLEMPLOYEE.DEPARTMENT} = {?FunctionalGroup} and
{TBLWORKORDR.WORKORDERNUMBER} <> "IMEAL" and
{dbo_TBLLABOR.ACTDATESTART} in {?Start Date}

And it worked!

Thanks allllllll!!!

 

[BurnsORegan]

I must be missing something then as I have it working here with my Field name in.

Try the IsNull function instead of "":

(
If IsNull{?EmpName}
then {dbo_TBLEMPLOYEE.LNAME} <> ".."
else {dbo_TBLEMPLOYEE.LNAME} = {?EmpName}
)
and
(
if IsNull{?FunctionalGroup}
then {dbo_TBLEMPLOYEE.DEPARTMENT} <> ".."
else {dbo_TBLEMPLOYEE.DEPARTMENT} = {?FunctionalGroup}
)
and

{TBLWORKORDR.WORKORDERNUMBER} <> "IMEAL" and
{dbo_TBLLABOR.ACTDATESTART} in {?Start Dateburnsoregan

 

[BurnsORegan]

Sorry:

(
If IsNull{?EmpName}
then {dbo_TBLEMPLOYEE.LNAME} <> ".."
else {dbo_TBLEMPLOYEE.LNAME} = {?EmpName}
)
and
(
if IsNull{?FunctionalGroup}
then {dbo_TBLEMPLOYEE.DEPARTMENT} <> ".." // or something
else {dbo_TBLEMPLOYEE.DEPARTMENT} = {?FunctionalGroup}
)
and

{TBLWORKORDR.WORKORDERNUMBER} <> "IMEAL" and
{dbo_TBLLABOR.ACTDATESTART} in {?Start Date}


BurnsORegan