Displaying the zone even if there is null data

[rajrev]

Hi,

I have a prob. with displaying the particular field in a report which is avaiable in one table but not in the linking table.

tables
t1 - empid,name, zoneid
t2 - zoneid,zone name

parameter - zoneid

for ex. Enter zone id as "z1", we can get the list of employees under z1. and we can display the zonename in the page header.

Now I Enter zoneid as "z5" which is avaiable in t2(zone) table, but not related to t1. (ie no one under z5)
now i want to display the zonename (for z5) in the page header.
any one have any suggestion or question pl. let me know.
Thanks
MK

 

[dgillz]

Write a formula:

if isnull({ZoneID}) then "None Identified" else {ZoneName}

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Turkbear]

Hi,
Check the link type between the tables .

To link T1 and T2 with an EquiJoin ( the default) both tables would need data that matches ( only records with
matching data in the linked fields will be returned).

Depending on the 'direction' or your linlk ( T1 to T2 or T2 to T1) you will need to use a Outer Join( Left or Right) from the table that will always have a value to the one that may not have one.

 

[rajrev]

Thanks for your replay.

Turkbear, I tried your suggestion, just change the inter join into RIGHT outer Join (also tried the Left outer Join)
still i coundn't get the result. do i have to change the formula.

Thanks
MK.

 

[dgillz]

The formula should work on either an equal or left outer join. Did you try the formula and have it fail? If so what happened?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[rajrev]

dgillz, i know that your formula is work for the null values.
but my question is I need to display the value for that code (even if no record uner that zone.)
ie
EmpTbl
empid empname zoneid
11111 xxxx 1
444 uuuu 4
222 ssss 5
666 hhhh 4

ZoneTbl
zoneid zonename
1 south
2 west
3 north
4 east
5 ew
6 se

parameter is zone id

zone id = 4
then the reprt output is
--------------------------------------
Zone : east

empid empname
444 uuuu
666 hhhh

This one working good.
---------------------------------------
suppose if we enter
Zone id = 2

then the output i need
----------------------------------------
Zone : west

empid empname

-----------------------------------------
hope i'll make clear about the problem. any suggestion /questions pl. let me know.
Thanks
MK

 

[Turkbear]

Hi,
Ok, how are you using the selection criteria?

Please post the actual selection formula used..

( If, for instance, you are using
{EmpTbl.zoneid} = 5, you will get no records if the EmpTbl is the leftmost table in your linking structure, even with OuterJoins)

If you use ZoneTbl as the leftmost ( driving) table and use a Left Outer Join to EmpTbl on zoneid, you can use any zoneid the exists in that table ( {ZoneTbl.zoneid = 5} for instance) and get your data, even if the zoneid is not in th EmpTbl ( of course you will not get any empnames)

 

[rajrev]

Turkbear, I'm using the zonetbl.zoneid in my formula.
the forula
if ({?zoinid} <>&quot;ALL&quot; ) then {zoneTbl.zoneName} else &quot;ALL&quot;
for the zone filed in the report
need more info.? pl let me know.
thanks,
MK