Formula field, Parameter and null field values 1

[piovesan]

Hi all.
Here is one I can't figure out.

The facts:
I have a table of projects with details about the projects. (Table name is PROJ) RELATED to that table, is another table that identifies the employee responsible for the project (EMPLTABLE.EMPID) The tables are related through the PROJID key in each table.
There can only be ONE employee for each project BUT, sometimes there is NO related record in that EMPLTABLE (no employee identified). That is where my problem starts.

So.... I created a report that lists all the projects, and I have a couple of parameters created, one for Start and End date query and one for a Status query. I had put the EMPLTABLE.EMPID field straight in the report at first but I didn't like the way it looked when there was no employee record so instead, I created a formula called "Planner Name" and it looks like this:
if not isnull({EMPLTABLE.EMPID}) then {EMPLTABLE.EMPID}
else "None Assigned"

I use that formula in my report instead of the field so I get that "None Assigned" text in the report rather than just seeing nothing.

All is OK up to here.
Now... my manager wants to be able to QUERY by the employee name. So I created a parameter called "Planner" and I added all the EMPLTABLE.EMPID records, and I also added a value called "NONE ASSIGNED".
Then I added into the record select formula this:
(if {?Planner} = "NONE ASSIGNED" then isnull ({EMPLTABLE.EMPID})
else {EMPLTABLE.EMPID}={?Planner})

This works to select any of the actual Employee names from the list, but if I select "NONE ASSIGNED", I get no records.

I also tried adding a value to the parameter called "ALL" and added that to the select formula, and I get ALL the projects that have employees assigned, but I don't get any of the projects that don't have an employee assigned.

Any help would be appreciated.
Thanks!

 

[lbass]

You have a left join from the project table to the employee table, right?

Please copy the entire selection formula into your post.

-LB

 

[piovesan]

Hi lbass. Yes, I have a left outer join from the project table to the employee table.
OK I seemed to have gotten it to work when I select the NONE ASSIGNED parameter. But not when I select the "ALL" parameter, I only get the none assigned. I think I must have something wrong with my [highlight #FCE94F]parenthesis[/highlight] in the select statement?
Here is the complete select statement I have:
{PRJTYPE.APTYPE} in ["PLANNING", "PLANNING 2"] and
(if {?Planner}="NONE ASSIGNED" then isnull ({EMPLTABLE.EMPID})
else if {?Planner}="ALL"
then
({?Planner}="ALL" and isnull ({EMPLTABLE.EMPID}) )
else { EMPLTABLE.EMPID} = {?Planner}) and
{PROJ.ADDDTTM} in DateTime ({?Start Date}) to DateTime ({?End Date}) and
(if {?Status} = "O" then {PROJ.STAT}="O"
else if {?Status} = "C" then {PROJ.STAT}="C"
else {?Status}="ALL")
Thanks!

 

[kray4660]

Since you are checking for Nulls, be sure that Default Values for Nulls is checked in the Selection Expert (formula section). So if you select All for Planner, then you are stating that {?Planner) = 'ALL' (kinda redundant) and the the Empltable.Empid is Null. Not sure if this is what you want. If the EmpID is a string. It might work better if you check for zero length strings (Len(Empltable.Empid) = 0)

 

[lbass]

Try the following, being careful to use the parentheses as shown. Note that you should NOT set the report options to "convert null values to default".

{PRJTYPE.APTYPE} in ["PLANNING", "PLANNING 2"] and
(
(
{?Planner}<>"ALL" and
(
(
{?Planner}="NONE ASSIGNED" and
(
isnull({EMPLTABLE.EMPID}) or
trim({EMPLTABLE.EMPID})=""
)
) or
{EMPLTABLE.EMPID}={?Planner}
)
) or
{?Planner}="ALL"
) and
{PROJ.ADDDTTM} in DateTime ({?Start Date}) to DateTime ({?End Date}) and
(
(
{?Status} <> "ALL" and
{PROJ.STAT}={?Status}
) or
{?Status} = "ALL"
)

-LB

 

[piovesan]

Thank you lbass! You have done it again! This works.
Now my only problem is the additional request I just got for this report.
They want to ALSO be able to query on Plan Area.
This is another table related to the Project table in a One:Many relationship. It is in the same situation as the Employee table that way, with a left outer join from the project table to the PLA_GRID table that contains the field for Plan Area. (PLA_GRID.PLAN_AREA)
So I thought I could easily take what you did above and do the same thing. Which "would" work except I think for one small problem.
In the (PLA_GRID.PLAN_AREA), there are some fields that are null (same issue as employee which I can now deal with thanks to you), BUT they have also used a code in that field that is "ALL". So "ALL" becomes a valid plan area.
So what I attempted to do was again create a formula where I tried to rename the null values, and I thought it may be easier if I rename the "ALL" values as well.
So in my new formula called PLANAREA, I have this:

if isnull ({PLA_GRID.PLAN_AREA})
then
"NO PLAN AREA"
else
if not isnull({PLA_GRID.PLAN_AREA})
and {PLA_GRID.PLAN_AREA}<> "ALL"
then
{PLA_GRID.PLAN_AREA}
else if
{PLA_GRID.PLAN_AREA}= "ALL"
then "All Area Projects"

So that on the report, the areas that they have in the table as "ALL" shows up as "All Area Projects" etc.

So then I created a parameter, and included the choices for:
All Area Projects
NO PLAN AREA
and of course an "ALL" choice that I want to bring up ALL the records.

In the selection, I thought I could insert into what you had above lbass, but I must be either doing something wrong again with my parenthesis, or it's the whole "ALL" thing that is getting messed up.
Can you add what would be the appropriate selection for this piece of the puzzle?
Thanks!!

 

[lbass]

You need to show the whole selection formula again with this new selection.

-LB

 

[piovesan]

{APDEFN.APTYPE} in ["PLANNING", "PLANNING 2"] and
(
(
{?Planner}<>"ALL" and
(
(
{?Planner}="NONE ASSIGNED" and
(
isnull({APEMP.EMPID})or
trim({APEMP.EMPID})=""
)
) or
{APEMP.EMPID}={?Planner}
)
) or
{?Planner}="ALL"
) and
(
(
{?PlanArea}<>"ALL" and
(
(
{?PlanArea}="NO PLAN AREA" and
(
isnull({PLA_GRID.PLAN_AREA}) or
trim({PLA_GRID.PLAN_AREA})=""
)
) or
{PLA_GRID.PLAN_AREA}={?PlanArea}
)
) or
{?PlanArea} ="ALL"
) and
{APPRJ.ADDDTTM} in DateTime ({?Start Date}) to DateTime ({?End Date}) and
(
(
{?Status} <> "ALL" and
{APPRJ.STAT}={?Status}
) or
{?Status} = "ALL

 

[lbass]

You‘re missing the final paren, but that’s probably just a paste errror.

I am unclear about how the results differ from what you expect. Can you show an example of what you expect vs what you get? Of course the selections from one parameter will limit the results you can get from the other.

Also, you have not used the formula you created in the selection formula.

-LB

 

[piovesan]

Thanks lbass. I think I was trying too many different scenarios and just totally missed adding the new formula into the selection. Everything was working for me except the "All Area Projects" selection so I added that and now it all works fine!! Whew.... step away from the computer