Multi Level Report - 15 levels 1

[suemak]

I need to create a 15 level report on Bill of Material Data. In other words, you start with a PartNO, and it's made up of 3 other parts, then those are made up of 2 other parts, etc.etc....down to 15 levels. I have the data without any level informaiton in the raw data through ODBC. I've been able to create a query that correctly identifies all the levels, but of course, it repeats the first level for each of the others, etc because it creates a new record with all of the levels each time I add a new level.

When I use this to create a report, even with turning on shrink on all fields, and on the detail, I get big spaces because I have to hide duplicates. I've tried using groupings, though I know I'm limited to 10 groups. I ended up with the same problem anyway. I also tried to make the fields so short that you could barely se them (.01 height) and turn on *Can grow*...still same results.

I've tried doing different queries and subreports, but always end up with big spaces on my report because of duplicated fields in my query.

In addition to wanting all these levels showing on a report, they want about 15 descriptive fields to print for each level. I may go grey on this one! At this point if I can just get the levels to print without spaces, I'll worry about the other fields afterwards.

Would anyone have any ideas on how to go about this? I don't need a lot of details, just a direction to try. I'm basically working with a big tree of data (Explorer reminds me of what I need to do).

Thanks for any help you can give me!!!!! Sue

 

[MichaelRed]

I'm not sure that this can (pratically) be made to work. The most I'v ever done is ~ 7 levels. You do not get much data on the report in my approach either (at least not at that number of levels).

Basically, you need to group the "upperlevels" of data into group headers (and footers), keeping only the lowest (2 - maybe three) levels of data in the details. So you end up printing most (hopefully ALL) of hte repeating stuff in the group headers/footers, and only (or mostly) the unique info in the detail.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[suemak]

Hi Michael, Thanks for the reply. Because they need to print so much data at each level I tried your approach and couldn't get it work. I think they should just get rid of most of their data at this point. LOL I'm glad to hear you did get it to work for 7 levels though..that's encouraging. I'm going to give this approach another try.
Sue

 

[MichaelRed]

Oh well. Somethings never change. The "great neabolous They" keep asking for the imposisble.


Another errant thought from a wayward perspective would be to actually analyze the overall process needs / requirements. Perhaps some (a lot?) of the 'stuff' sould be condensed in some manner, or eliminted if is (or should be) obvious to the user in the enviornment.

Something like a report Header or footer as a 'dictionary page'. This could just be a 'cheat sheet' for decoding some of the little used fields which you could "Code" to save space?

Another approach, although not very different from my first suggestion, could you "condense" a few levels of the mostly repeating stuff into one sub report and the details which really matter into a second sub report. For 15 levels, let me guess that the top three (hopefully four) are more or less the stuff which repeats a lot. Make these the Group header. Take the next several and make a subreport. Make the remainder another sub report.???


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[suemak]

I spent two days at this site getting their requirements. The problem is they have bought a new software package (with ODBC thank heavens) but they want the report to look exactly like the one the old system produced. (Why buy the new one?). I talked mostly with the engineers who will use this report and they are quite adamant! This is going to be quite a challenge...
I was just experimenting with adding an autonumber field to the table I've created with the main query that has all the data. I'm wondering I can use these numbers in the detail section and then do calculated controls. The formulas will be long, but it might work. The only problem I have with this is how to create an incremented number field in the query. For now, just to test the concept, I've run the query, and added an autonumber field to the resulting table to get the autonumbers. Any idea how to get the query to create an autonumbered field? I really appreciate your help Michael.....Sue suemak@home.com

 

[MichaelRed]

If the target table has an autonumber field, any record addition will (AUTOmatically) generate a value for the record. Just ignore the autonum filed in a maketable (or update) query. For select queries, you just reference them like any other field.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[suemak]

thanks Michael, I tried this last night and couldn't get it to work. Guess I was getting tired and frustrated. LOL I went in this morning and took your advice and it worked great! I think I may almost have this solved. I really appreciate your help. I'll let you know how I make out. Sue (now I just have to go write 15 zillion formulas....haha)

 

[suemak]

Michael, I thought I did the autonumber OK this morning, but when I went back in I realized I was looking at the wrong table. I have created a table with an autonumber field. When I try to append to it, I get 0 records added due to data validation error. Just how would I go about doing this? I can use either a maketable or append query, but I've tried both and get the same error. Is there any way to create an autonumber in the query itself? Thanks again...I'm sorry to be a pest! Sue

 

[MichaelRed]

In either case, just leave the autonumber field out of the query (append / update). The TABLE is 'responsible' for the autonumber. If you create a record in amy way, it is assigned the (autonumber) value.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[suemak]

thanks Michael...no matter what I did, I couldn't get the make table to work. Maybe it's because I have 254 fields already. (Even compacting didn't help) But, I searched through the FAQ's (I'm just starting to learn my way around the forums) and found how to write a function that I can call from my query. It works!!! Thanks again for your help. This forum is great. Sue

 

[MichaelRed]

254 fields????????????????????

Best break that up. Unless it is changed, the LIMIT is 255 for a single recordset!!!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[suemak]

Yes, you're right...the limit is still 255. I've gotten rid of a few by combining text fields. I'm finally getting somewhere with the report. It's actually running too. I will be able to do this with all 15 levels as long as I stick with one line in my detail section. They had wanted 6 fields listed underneath each other at one part of the report, but doing this ends up creating havoc with the spacing of the lines even with shrink on. So, I am going to see if I can fit all of their data on one line. If I can, I can get this done. This has probably been one of the most challenging reports I've ever done! LOL And what makes it worse, is they are currently only using 6 or 7 levels in their Bill of Materials. But, because their main program allows for 15 levels, they want them in the report *just in case*......