Repeated use of same table trouble

[abatblack]

I am tying to write a report that connects overdue purchase orders (PO) to any sales orders (SO) that they relate to.
The MRP system creates 2 tables of supply and demand references (PSAD.ID's and ID Links) that can link through up to five levels of components in the manufacturing process.
My report has to use the two tables repeatedly to follow the path from PO to SO. The thread can terminate at any level with a SO result.
However once a result has been found (a SO) the report cannot prevent itself continuing to search for further entries along the maximum length of possible thread.
Obviously i can suppress any unwanted data found after a result is achieved but this often causes the report to run very slowly and often finds a result that seems to have no solution. This causes crystal to lock up and can only be killed using task manager. It is as if i need to control the links between tables to terminate after a result is found.

I've tried to use subreports to limit the continuing search to only those entries that have not found a result but: at any stage the thread may split into more than one route, each of which must be pursued. I can only return one result from my subreport (the last one in the list), so i am missing much important data.

As simple as I can explain this:

Purchase Order PO.XYZ (for Component C1) >>>> used in Works Orders W1,W2,W3.
W1 >>> used to make Component C2 >>> needed for SO.123 and SO.234
W2 >>> C2 >>> SO.345
W3 >>> C3 >>> W4 (Component C4) and W5
W4 >>> C5 >>> SO.456
W5 >>> C5 >>> SO.567

Or in terms of the tables:
PSAD table----------------Link table-------------------------PSAD table---------------------------------Link Table etc, etc
ID01 (orderNo1)>>>>>>demandID/supplyID >>>>>> ID02(orderNo2)/ID03(orderNo2) >>>> repeats as before


There are a few possible trip ups as any oversupply at any stage goes into stock and has a zero demandID causing a lengthy search for a nothing.

I seem stuck between applying too many record selection filters and it returns nothing and it getting stuck in an endless search through millions of entries with no end. Any suggestions please.


I'm using Crystal Reports 2011 and SQL.DB files.

 

[PeterDimitrov]

Are you using SQLServer ?

I suppose you have tables for:
POs (something like tblPurchaseOrder)
components (tblComponent)
works (tblWork)
SOs (tblSO)

and tables for the relation:
component <-> po(rtblComponentPO),
component <-> work (rtblComonentWork)
component <-> SO (rtblComonentSO)

Is this correct?

http://www.R-Tag.com

Tools for Crystal reports and SSRS.

 

[abatblack]

Thanks for the response.

SQLServer - yes.

All tables you mentioned - yes.

I havn't used these tables in the report I am struggling with - this really only involves the supply and demand references and the table that links these references together.
All the tables you list do obviously relate to the elements that are referred to in the supply and demand table.

 

[Madawc]

What you're after is a system that starts from the demand and then finds other details, if needed. Without knowing details of the files, I can't say if that's possible. But I do have some suggestions you may find useful:

a) Crystal allows the same table to be added more than once, each extra instance being called an Alias and treated as if it were completely separate. So you can link A to B to A-Alias, conditionally.

b) Left outer lets you link A to B but still have records from A that have no occurance on B. Note that if you also do selection tests on B, you must allow for nulls and test for them first.

c) Sometimes it's best to collect all possible data, then group with detail lines suppressed and totals found.

d) The main report can have several Crosstabs that group different data in different ways. (Learn to use them if you don't know them already, they often come in useful.

e) Crystal lumps all of the tables and most of the selection tests into a single giant select statement, which you can view using Database > Show SQL Query

f) If you can write and test your own SQL commands with several SELECT statements and perhaps grouping and deletion, this can be added to the Crystal as an SQL Command.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference except for SQL Commands in some of the early versions.





Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[PeterDimitrov]

You can use CTE (

http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx)

. This will allow you to use recursion on the server side and to prepare a recordset will all related records. Your sql could be in a view stored procedure or crystal reports command . CTE cannot be created using Database expert.

http://www.R-Tag.com

Tools for Crystal reports and SSRS.