Need help in creating pick parameter

[rorymo]

Hi,
I am having a problem getting correct data out of a database, I'd appreciate any help!
I am using Crystal Reports v10, Sql Server database.
My report in question is gathering IT asset information
(machine name, serial no, model, ip addr., primary contact, etc.). There is a field called "Secondary Contact" on the report that contains the name of a person to be contacted in case the primary person is unavailable.
Each person is associated with a particular asset (Unix server, etc.)
The problem is that there can be more than one person associated with one asset.I need to create a pick parameter to choose which secondary contact has what machines.
Ex: machine AB-11234 has 3 secondary contacts:
John Doe, Jane Doe, Bob Smith
Pick parameter chooses Jane Doe to see what machines she
is associated with.
Currently, there is a report that returns this info, but it is acting as a subreport within the main report.
Since I need the query info "up front", I am doing away with the subreport. I have tried to introduce the formulas, etc from the sub into the main, but there are so many additional tables from the sub that the linking is a nightmare and I can never get that to work.
My record selection criteria is:
if {?Secondary Contact} = "ALL" then True
else {@Contact Last Name} = {?Secondary Contact}
I have changed the linking scenario numerous times, I either get every record in the table or I get the pick parameter name for every record.
Any help would be greatly appreciated!
Thanks,
Rory

 

[lbass]

Can you show a sample of what you want displayed in this report? It is unclear whether you want to choose just one secondary contact per asset/machine or whether you want to show all secondary contacts and then also show for each secondary contact all assets/machines for which they are secondary contacts.

-LB

 

[rorymo]

Hi,
thanks for replying.
I want to be able to pick one secondary contact person from the (pick) list and display all the assets (machines) associated with that person.
It would be like showing what Unix boxes an SA is responsible for.
I don't want to be able to pick multiple secondary contacts, just one.
It may return multiple records because the person may be responsible for more than one asset.
Hope this is a little clearer.
Thanks,
Rory

 

[lbass]

If that is what you really mean, then all you need to do is place the assets field in the detail section and use a record selection formula of:

{?secondarycontact} = {table.secondarycontact}

But I think you want this as data nested within a larger report, and that is what we need to see a sample of.

-LB

 

[rorymo]

Hi LB,
Thanks for replying.
I did what you suggested, it does return the secondary contact, but it returns the same name for every record, not just the ones that he is associated with.
The report displays the contents of these fields (I am just showing a few fields since it is very lengthy.)
EX:
System Name CM Number Family/Class Location Status
xyz001 A0000023 H/W Server Rm 207 Active

Organization APP Responsible Org Install Date
my org name A00 Enterprise Svcs 9/23/05

Originating Vendor Contact Primary Contact Secondary
XYC computers John Doe Jane Smith

What is returned after I pick Jane Smith to search on, is every record in the database. The Contact Secondary field displays Jane Smith even when she is not the secondary contact. If Jane Smith is only associated with 2 machines, the query will display every machine in the db and Jane smith as being the contact on every machine.
Thanks,
Rory

 

[lbass]

You will have to explain what tables you are using and how they are joined--on what fields, and with what type of joins.

-LB

 

[rorymo]

LB,
I am using 17 tables, they are joined by an "id" field,
all are using inner joins.
I have decided to start over and add the tables gradually, then I'll be able to pinpoint the link problems.
I appreciate your help and time spent...
This will probably be the way that I can isolate the problem. If this doesn't work, I'll post again.
Thanks again for your help..
Rory