Parent Child Heirarchical Report (1 Viewer)

I have a table of equipment with a unique Equipment id, and a field for Parent ID if the equipment is a child part. Eg a Rack, Expansion Shelf, Disk.

I have created a report of the equipment in a hierarchical layout e.g.

This is all working very well. The report consists of main report listing the site and Level 1 equipment, and then Level 2 equipment is a Sub report of level 1 and the level 3 equipment is a sub report of the level 2 report.

Now to the problem - speed! This is fine when there are only a small number of items. When you get to a report with 15 or 20 level one devices each with 20 level 2 devices which in turn each have 20 odd level 3 devices it becomes really really slow - many minutes (5-10) to generate the report. I am sure this is due to it calling the sub reports for every child level.

To try and fix this I have tried to create a query / queries that produce a single sorted list in the same way as the report but am just not getting the results I want. I can create a query that lists everything with a level indicator, but that doesn't seem to help with the tree type report layout I require.

Ideally I need the Level 1 and Level 2 items listed in a sensible sorted (alphabetical) order which appears to be a major stumbling block. The nearest I've managed is a stored CTE view on the SQL server, but I can't get the equipment sorting in any order.

The equipment_ID's are not necessarily created in any specific order, so cannot be used. The equipment descriptions are stored in a separate equipment table. What am I missing?

Here is an often referenced article on hierarchical structure that may give you some insight.

You might want to post a copy of the db and give specific instructions to repeat/isolate an issue. I think that may be an efficient way to get some answers/ideas/comments.

I am not a SQL Server user.

Thanks jdraw - After writing the post (airing your dirty laundry often makes you think about the issue! ) I suddenly had a thought about creating a table using a looped recordset in the correct order. The link confirms that this is a great way of handling it.