main database vs lookup database

[Halfcan]

HI

I'm having a problem with a report that displays the 1st record with ZERO's.

I think it has to do with my databases. One database is called DEVICE LIST. It is a master list of devices that doesn't change.

The other database is called TICKETS. This database changes constantly. Since TICKETS does not have all the nessesary information to create a report,

In the past,
we have used the database DEVICE LIST as a lookup table for THE TICKET database.

So each TICKET database record looks at DEVICE LIST to get information such as its Customer, Market, Network, and Location.

Weve grouped by each of these in my prevous reports, and in my last big report, TICKETS was the main database,

and DEVICE LIST was the lookup.

The distinct field that was used to link the 2 databases was IP ADDRESS.

In Select expert, Basically if {TICKETS.IP ADDRESS = DEVICE LIST.IP ADDRESS}

then it was allowed to be reported on.

This of course would only display groups such as Market, Network, and Location (from DEVICE LIST) that have
a matching IP ADDRESS in the TICKET database.

NOW,
I need to display ALL the group names in DEVICE LIST, even if there were no matching records in the TICKETS database

for those groups.

In order to get ALL group names; Market, Network, Location, to display,

I had to reverse the way the databases where linked, So NOW DEVICE LIST is the main database, and TICKETS becomes the lookup database.



My problem is, since CR looks at DEVICE LIST first, it makes the first record line all ZEROS.

The subsiquent lines display information I want to see from the TICKETS database.

However, When I do calculations such as Averaging, the 1st record (with all ZEROS) is included and therefore the calculation is incorrect.

I've tried to suppressing if {TICKETS.IPADRRES <> DEVICE LIST.IPADDRESS} with the record fields in various places.

but it seems to give me either all, or nothing.

If I the fields in the TICKET.TICKET NUMBER group just above Details, I only have 1 line of zeros left to contend with.


Any ideas?


Andy

 

[synapsevampire]

If you want to eliminate the row that has all zeros, use something like this in the record selection formula:

({table.value1} <> 0 and {table.value2} <> 0 and {table.value3} <> 0)

Replacing table.valuex with your column names that are all zero.

Any rows that have all zeros in those rows will be eliminated from the data.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Halfcan]

this works the same as {TICKETS.IPADDRESS} = {DEVICELIST.IPADDRESS}

It will not let the other group names from DEVICE LIST show unless there is an associated {TICKETS.IPADDRESS}

I need all group names from DEVICE LIST to show, even if there is no matching {TICKET.IPADDRESS},

BUT also, I don't want a record with ALL zeros to show for a group if there is no matching {TICKET.IPADDRESS}

does this make sense?

Andy

 

[synapsevampire]

OK, I'm confused... This:

&quot;I don't want a record with ALL zeros to show for a group&quot;

got me, since you say for a group.

Please post some example data and expected output, that should simplify things.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Halfcan]

Sorry, I meant record. Haven't figured out how to fix this,
I am using excel files as the db, and I guess thats why I have no options for changing the link types.

Anyway, I've decided to do the calculations by hand,
by adding a 1 to good records, and a 0 to bad records,

and doing calcuations on the 1's records only.

Thanks for trying,
Andy

 

[stormtrooper]

You could try and change the joins in the visual linking expert. Right now it looks to be set at &quot;=&quot;. You could try left outer or right outer and see what happens.