Advice on a good strategy

I have a general question and a specific question.

1.) I'm doing some reporting using the Crystal Reports software my company has installed; however, I see threads of people using all kinds of more powerful software (VB, C, etc) to work with Crystal (web development, etc). Crystal Reports is on a terminal server here, and my audience will use that software to view the reports. Am I then limited to the Crystal (or Basic) syntax found in the software, or can I somehow use something like Perl or C to do my programming? I'm not sure how all of this goes together.

2.) More specifically, I'm trying to build a parts list tree (parent/child). The database is organized such that it makes this correlation difficult. In one Table (Part Master), is a list of all unique parts. In another table is a list of all unique jobs (times we've made the parts) (Jobs) that would show the part being made (one part shown) (link to Part Master). In the third table (Materials), the Jobs are listed with all of the sub components used for the main part for that job. Here, I'd want to capture the sub-components, but then loop back through the original Part Master to capture THEIR subcomponets, and so on. There could be up to 5 levels of this.

Basically, I want a user to enter a top-level assembly number, then I want to show every part used for that assembly, all the way down. My problem is that I really need to loop through the original list of returned records (I think). If I find the first part, and the first level of sub-parts, I may have passed those records already, so I need to re-read the list. I attempted build an array of the entire list of parts and do the logic at the end, but there are 82,000 of them (limit is 1000 and I don't plan to do Case with 82 levels). I'm inclined to believe that Crystal Reports (v.10) does not support multi-dimensional arrays, making this even tricker.

Can someone help me on a strategy to attack this one?Well, first you probably need to realize, that even if they are using VB or C-whatever or anything else in conjunction with Crystal, that Crystal is still a data-analysis/reporting engine. All it does is spit out the data in whatever format you design the report to do so. You can connect directly to your database (I am guessing it is a relatively standard engine, although you don't mention it), and then use crystal to select certain sets of data, group that data, and summarize that data, no matter what the general GUI interface to your data is. So just use crystal, point it to your database through ODBC or OLEDB (or whichever method you want), and go. Now, how you allow your users to view those reports is another matter, you might put shortcuts on the desktop (if the full version of CR is accessible through the terminal server), or use or develop a CR viewer for them to open the reports in. For that, you would need another tool, such as VB or C-whatever.

It does sound as if you have several layers of one-to many relationships to account for in the description of your project. My presonal experience leads me to usually start for the smallest partion, and work up. If you want a jobs report, that will show the detail down to the smallest sub-part, you would probably know that you need to start from an AssembyID, display each individual part, and below each part, a list of sub-parts. So you would add each of those tables to your report and link them by their primary keys (I usually use a left join if there is any possibility of missing links, i.e., a sub-part entry that is not in the table, otherwise an inner join would be more efficient). Then group by jobid, partid, and sub-part id. Add all the fields you want for each job (be sure to keep the field boxes in the correct group).

Obviously, this is hugely simplified and requires a lot more, but that is a basic approach. More detail could be given, if you give me a list of tables and their relationships.

ScottJ