Including Zero's

[TEM3]

I am working with Crystal Reports 8.5 and Oracle 9i tables.

I have run into this issue often and have not figured out a report "game plan" for dealing with it. The problem is with time-period reports that count actions or activities. As long as something happened during the period, there is no problem getting a count. But on many reports the powers-that-be wish to see all activities, even ones where nothing happened (no records in the table) during the time period.

I need a general plan to define all the actions/activities I want to count, total up the ones that actually happened and list the others with zeros. Any suggestions?

 

[IanWaterman]

YOu need to create a table which contains all the activities. Do a left join from that table to your data. When join results in a Null you can report zero for those activities.

Ian

 

[TEM3]

Cool. I will try it. But let me see if you think this example is what you mean......

One report tracks monthly activities related to Motor Vehicle Tapes (MVR's). The tapes occupy various custody locations during the month and these locations are used to track what was going on with the tape. The table/field that holds the location data is {LABSTAT.location}. The table/field that holds all possible locations is {CUSTCODE.Custody Of}. I already have {LABSTAT.Location} left joined to {CUSTCODE.Custody Of} to take advantage of other CUSTCODE table fields, such as the Custody Description. Are you saying I should also left join another instance of CUSTCODE to LABSTAT (LABSTAT ---> CUSTCODE and CUSTCODE2 -----> LABSTAT) to look for the null (or zero used) locations??

 

[IanWaterman]

NO

I think that will lead to multiple duplicate records sets.

Are you saying that you can have lcations which do not have Custcodes too.

Older versions of Crystal allow a full outer join, which is like a combined left and right outer join, but I don't think you can do that in CR8.5 or Oracle 9i

I think you will need to use a command or in CR8.5 Customise SQL (or build a view in your database) and union the two data sets together.

eg

Select location, custodyOf from
Labstat, custcode
where Labstat.custcodefield = custcode.custcode_key (+)
union
Select location, custodyOf from
Labstat, custcode
where custcode.locationfield = labstat.location_key (+)


Hope your SQL is up to the task.

Ian

 

[TEM3]

Revisiting this issue (and avoiding SQL).......

OK I am trying to measure activity in certain custody locations. The table CUSTLOC has all the possible locations. I have selected the locations (CUSTLOC.Location) of interest. CUSTLOC is my left most table and everything else is "Left Outer Joined" to CUSTLOC.Location (CUSTLOC.Location ---> LABSTAT.Locker).

I am using the following formula to count activity (hits to the location):

if (isnull({LABSTAT.Locker})) then 0 else
distinctcount({LABSTAT.Evidence Control Number},{CUSTLOC.Location})

BUT, I am still not seeing locations with no activity!

 

[IanWaterman]

Please send you record selection formula and the SQL from the report.

Not sure what is going wrong.

Ian

 

[lbass]

You can't make any selections on right hand tables (unless you go the command route, in which case there IS a way to do this) and still get all locations. Note also that to distinctcount all but zeros, you need to create a formula {@null}, by opening and closing a new formula without entering anything. Then create a formula like this:

if isnull({LABSTAT.Locker}) then tonumber({@null}} else
{LABSTAT.Evidence Control Number}

Then insert a distinctcount on this formula. To get the zero display, use a different formula (but don't try counting it, since it will count zero):

if isnull({LABSTAT.Locker}) then 0 else
{LABSTAT.Evidence Control Number}

-LB

 

[TEM3]

Here is the selection formula:

{LABITEM.Item Type} in ["PH05", "PH06", "V001"] and
{CUSTLOC.Location} like ["ED2?", "ED1?", "ED0?", "ESTS", "MP01", "MR01", "MVR?", "NESTS", "SESTS", "VD??"] and
{LABSTAT.Status Date} in DateTime (2008, 03, 01, 00, 00, 00) to DateTime (2008, 03, 31, 00, 00, 00)

The CUSTLOC.Location field contains all the locations. Some of those selected above were not used during the month and I want those to report as zeros.

 

[lbass]

As I said, you cannot select on the right hand tables and still maintain the left join. So leave the selection formula as:

{CUSTLOC.Location} like ["ED2?", "ED1?", "ED0?", "ESTS", "MP01", "MR01", "MVR?", "NESTS", "SESTS", "VD??"]

Then create a conditional formula like this:

if {LABITEM.Item Type} in ["PH05", "PH06", "V001"] and
{LABSTAT.Status Date} >= DateTime (2008, 03, 01, 00, 00, 00) and
{LABSTAT.Status Date} < DateTime (2008, 04, 01, 00, 00, 00)
then {LABSTAT.Evidence Control Number} else tonumber({@null})

Then insert a distinctcount on this formula at the location group level.

If you upgrade your CR, you would be able to build the criteria into the From clause of the command and still maintain the left join.

-LB

 

[TEM3]

Thank you once again. I have purchased CR 11, but am so use to 8.5 I have not taken (or really had) the time to learn the newer user interface. Guess I should.......