Complex crystal report formula

[donnitadk]

I have an SQL database that houses Municipal Codes in a hierarchy structure. I need a way to show in Crystal Reports the full Municipal Code. Below is more information and a quick sample of data.

The columns/fields in the database include:
CodeID / Code / Level / ParentCodeID

The Code ID is a unquie ID and is the record that I want to pull the data for. I want to return the code from each level. Level is a value between 1 & 6. The ParentCodeID links back to CodeID at the previous levels. Below is an example

CodeID / Code / Level / ParentCodeID
200 / 6 / 5 / 186
186 / 104 / 4 / 163
163 / 2 / 3 / 28
28 / 1 / 2 / 21
21 / IPMC / 1 / NULL

I want a formula that will look at CodeID 200 and give me the result "IPMC.1.2.104.6" The formula needs to simply give me the Code, then look at the ParentCodeID, then go to that record and get the code and continuing that process until the ParentCodeID is NULL. (It would also have to be in backwards order to show correctly.) I have no idea how to get this to work or if it is even possible in Crystal Reports.

 

[Madawc]

I think you need to link records. Then you'll have the details you want at each detail line.

You can't do a specific look-up except by using a subreport at detail-line level, which would be slow.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[GJParker]

You'll need to add the same table (alias) into the report 6 times and link the tables in a chain codeid to parentcodeid. That way you will have all of the information together in one record.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

You could also look at the hierarchical grouping option within Crystal. Once your records are clustered appropriately, you could then use a variable to collect the values into the desired string.

-LB