Many To Many Relationship

[kwalters110]

I'm new to management reporting and this summer I've been creating alot of crystal 8.5 reports for various functions of the shop. One thing that I keep encountering is the need to analyze and relate data on a many to many relationship. While I find using a sub-report is an effective means to handling this, but it has its draw backs. For example, if there is any information in the sub-report that i need to make a calculation with, I can use a shared variable, however I loose the ability to do summations or sorting with any field that uses that variable.

If I don't use a sub-report and relate all the views in a single report causes record inflation. If I use this technique i need to handle over inflated summations by dividing by counts of certian records. This also hinders the ability to perform certian crystal manipulations and the report becomes much more difficult to maintain.

What do you guys use as typical solutions to the Many - Many scenario? I'm interested in hearing about different situations and you handled it.

-Keith

 

[Madawc]

Have you tried crosstabs?

Madawc Williams (East Anglia, UK)

 

[demoman]

Keith,

I am not trying to be a wise guy. But, I would try normalizing your database so that you do not have any many-to-many relationships. I have constructed thousands of databases over 23 years, and never have a MTM situation in the last 22. You are experiencing exactly the reason for not doing it. You can probably find info on the web about how to create a correlation table in a MTM environment.

 

[kwalters110]

Thanks for the response demoman. However normalization isn't exactly the problem. The database is running on Pervasive.SQL 8.5 and is maintained by our MRP software vendor I do however have the ability to create views and store procedures. None of the tables are a direct many to many relationship. The M-M does come into effect with the type of data that I wish to compare in a report.

Take this current report I just started to make for example. I need to show the information of a part in production such as qty in WIP and qty planned for WIP. Thats a simple one to many relationship of INVENTORY_MSTR INNER JOIN JOB_HEADER and doing conditional totaling of the QTY_ORDER for each Job.

Now I need to show Total Open Orders for a part so I can see what we have in production, what is giong into production, and see how that measures up to orders that are due. INVENTORY_MSTR and ORDER_LINES is another one to many relationship, but if i need to see total open orders and total wip for a part, ORDER_LINES -> INVENTORY_MSTR -> JOB_HEADER is a many to many relationship.

The obvious cure for this is to create one report that gathers production info, and a subreport that gathers open order info and link them based on part number. Then I could pass the Sum of order qty for that part as a shared variable to the main report (which is what i've done in numerous situations now). Using subreports have slowed down reporting time and as far as I'm aware I am unable to do summary and Top/N sorting based on any field that would use that shared variable. A very big bummer.

If you have any advice on substitutes for subreports I would be very interested in hearing it.

 

[demoman]

Pervasive Hmmmm, you have my sympathy. I also have a legacy pervasive app (Timberline) which I have to deal with. Hope yours is better than mine. Timberline does not use the Pervasive ODBC driver (has its own proprietary one). I cannot tell you (or perhaps I can) how much fun it is working with this technology.

I built a screen grid/report, and several other production reports, for my MRP system, with the same situation you are describing....Requirements vs Actual (and planned) production. As I recall, I created two views, one requirements, one production and aliased the columns with common names. Then I did an 'append view', creating a single table (view) to report from. I also added a virtual column and assigned it 1 (sales), 2 (production). I was then able to show the data in date order. Problem is, I doubt is you can do this in Pervasive.

I looks like what you are doing is the right approach. By putting the distinct part numbers between sales and production, that should create OTM on each side. Unfortunately, I cannot answer crystal subreport question. I just hope I did not blow the deal for you by someone else thinking the question had been answered. Sorry for misunderstanding your original post.

 

[kwalters110]

Pervasive Hmmmm, you have my sympathy.

Thanks for your sympathy. Our MRP system "upgraded" to use pervasive recently and I am alittle less than impressed with its SQL capabilities.

Then I did an 'append view', creating a single table (view) to report from.

That sounds like a lot like a UNION ALL. And I've done that before to compare Open Orders with Order history. Pervasive allows me to run Union queries against the server but it will not allow me to create a View that UNION's 2 tables. So I have to manually insert my UNION statement in Crystals ShowSQL window. Which becomes a real pain when i need to fix something. But I never thought of using the UNION to bring Orders in with Production. I like that idea and could very well be the solution to my problem.

I was hoping I could get away with a subquery was a scalar SELECT item using a sql expression, but it seems Pervasive doesn't like that either (see thread318-1080152 for more details about what i wanted to accomplish). Thank you so very much for your input.