I'm lost...how do i write a stored procedure that will.... 1

[WhiteKnight2K]

Hello there! I have encountered a problem that I think is solvable with nested subreport but CR doesnt allow for this. For starters:

A Tree Group can have many Contacts. Each Contact has a certain Contact_Type where the Contact_Type can be Owner, Renter, Manager etc. Also a Tree Group contains many Trees. I want to create a report that will show for each Tree Group, the Trees associated with it, as well as one contact which is the highest rank where the ranking is as follows: Highest - Owner
Second - Manager
Third - Renter

If i put the Contact_Type in the Group Header, only the first one will show up (whichever one is first in the database) whereas i would want the highest ranking Contact_Type to show up only.

If i put the Contact_Type in the Details section, the Trees are repeated for each Contact_Type associated with the Tree Group. For example

Tree Group 1
Contact Type
Tree 1 Owner
Tree 1 Renter
Tree 2 Onwer
Tree 2 Renter
as so on...

If i put the Contact_Type in the Group Footer, only the last Contact_Type picked from the database for that Tree Group will show up (which may or may not be the highest ranking).

Also, the Tree Group is already in a subreport so i cant make a Contact_Type subreport.

I was thinking of maybe using a stored procedure to return only the highest ranking Contact_Type, which i think would have to use SQL to select the record, but I do not know how to create one. I am using a MS Access database and i dont know how to create a stored procedure and the help files do not help me.

Please, if you can offer any assistance, please do so,

Thank you!

 

[lbass]

Hi-

I think you could just write a formula @rank:

if {Contact_Type} = "Owner" then 1 else
if {Contact_Type} = "Manager" then 2 else
if (Contact_Type} = "Renter" then 3 else 999

or write this as a select-case statement, if there are many Contact Types:

select {Contact_Type}
Case "Owner": 1
Case "Manager": 2
Case "Renter" : 3
Default : 999

Then you could either do a summary (minimum) of @rank and place in the group header. If that's not available via a right click of @rank (I can't remember), then create a formula @highestrank:
minimum({@rank},{Tree_Group}) //and place in group header.

Or you could sort by @rank, Asc, which would put the highest rank at the top, and then place {Contact_Type} in the group header--but this might mess up the order of your tree details and cause redundancy that you would have to deal with.

-LB

 

[WhiteKnight2K]

great lbass! I'll try what you suggested....never thought of that myself but I hope it works =o)!