Reporting on items NOT found in records...

[patelh]

hello,
New to Crystal reports:

I have a table called 'Server' with two fields, 'ServerID' and 'hotfix'. This table contains hotfixes found on a machine. A machine may have multiple hotfixes.

I am trying to report on machines that do NOT have a selected range of hotfixes:

Format required:
Machine X
Missing 'y' hotfix
Missing 'z' hotfix
Etc...

How can this be done?
My idea was to concatenate all 'hotfix' records per server into a single string and use the IN statement to find those machines which match and disregard (the 'NOT IN' does not work for me!). Any ideas would be grateful...

Regards,
Hamen
Hamen_Patel@aimfunds.com

 

[chelseatech]

Several methods come to mind...
1) A cartesian join in SQL to join every server to every fix and (and therefore every combination) and then join that to the Server/HotFix table via a left join and look for nulls on the right..... a bit of messy SQL and will make your DB do some work.

2) a Main Report of All the HotFixes, and then a subreport of a Server and Left Join to a Server/Hotfix table - again look for NULL values in the joined table.

I assume for both you can find/create a view of Servers and Hotfixes seperately.

Where is your data stored? That can limit your choices.. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[patelh]

The data is stored in SQL. The problem here is that there is only one record per server containing a list of all hotfixes for that server:
ie:
ServerID | Q233405;Q24532:q23495:etc...

Hamen

 

[kenhamady]

There is no "not in" in CR, but you can use:

not ( X in Y ) Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com