0

My table has two relevant fields, PartNo and SubPartNo. These contain all the part numbers used to build an entire assembly. The main assembly (1st level) uses PartNo. Depending on the complexity of the assembly, there could be hundreds of records with the 1st level part number. Those records will all contain SubPartNo's.

If a SubPartNo also contain parts, the SubPartNo will also be listed as a PartNo elsewhere in the table. These can in turn have sub parts, and so on.

My report needs to lists the main assembly part, then each relevant sub part and their subsequent chains of numbers. How can I accomplish this in Crystal Reports?

enter image description here

4444
  • 3,541
  • 10
  • 32
  • 43
highrise955
  • 11
  • 2
  • Thanks for the reply 4444. I will look into SQL recursion and see if it is what I need. – highrise955 Feb 27 '17 at 12:55
  • You might explore hierarchical queries. In Oracle, it is start with / connect by. More ideas here: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – Sun Mar 06 '17 at 18:26
  • Thanks Sun, I will check it out. – highrise955 Mar 07 '17 at 19:24

1 Answers1

0

If you have to do this in Crystal Reports, you're gonna have a bad time.

A formula would be particularly ill suited to recursion because it evaluates each entry in turn, with very limited comparison capabilities. For example, when it's evaluating Entry #42 in your list, the only other entries it can compare it to are #41 and #43. Not adequate for your needs.

While it is possible to use subreports in Crystal, which would be perfect to go down a single layer of your recursion, you unfortunately cannot nest subreports. So you could get the sub-parts for a main part, but none of the sub-sub-parts or beyond.

The only way I can think to do this is to write a stored procedure to use within your report that handles all the recursion for you - before the data even reaches the report. That's completely doable, and I'd recommend you research SQL recursion to help you get the job done.

4444
  • 3,541
  • 10
  • 32
  • 43