Where-used sort file in Crystal Reports (BOM module)

[Algernon83]

I'm trying to make a variant of the where-used report from the Bill of Materials module using Crystal Reports.

According to the technical reference, there should be a "sort file" named BMW_CompWhereUsedSortFile, which is indexed on the ComponentItemCode. However, this doesn't appear in the table listing in Crystal Reports. So, I have to use BM2_BillMaterialsDetail. This table does show me where each component is used -- but since it's not indexed on ComponentItemCode, it takes over a minute for each item, and my report has over 300 items, and I have to generate it overnight.

There must be a quick way to generate this report. MAS 200 generates a where-used report in less than a second. How can I make a Crystal Report that shows me where a component is used, and generates quickly?

 

[BigLouie]

Single-Level Where-Used Report
BM1
BM2
BM4
BMX
IM1
IM2

Indented Where-Used Report
BM1
BM2
BM4
BMX
IM1
IM2

Summarized Where-Used Report
BM1
BM2
BM4
BMX
IM1
IM2

If you do not use Options you might not need all the files. These are the tables used in each report.

 

[Algernon83]

Thank you, Louie -- However, I'm still at a loss. On my server, the BM4 and BMX tables are empty. Even a test report that only uses BM2 and selects a specific ComponentItemCode takes 20 seconds to return a result. It seems as if MAS 200 is using an index that I can't get to.

PS -- How were you able to see the list of tables used in the reports?

 

[dfeller]

In your Crystal Report, in the Report options, turn off "use indexes or server for speed".

What tables do you have in your report and how are they linked?

BigLouie got the table list from the TRSG.

Dawn

 

[Algernon83]

I'm afraid taking off indexes only made the report slower.

The full report uses bm2->bm1->im1->im2, with the select clause:

({BM2_BillMaterialsDetail.ComponentItemCode} = {?ItemCode} and
{BM1_BillMaterialsHeader.Revision} = {BM1_BillMaterialsHeader.CurrentRevision} and
{IM1_InventoryMasterfile.ProductLine} = {?ProductLine} and
{IM2_InventoryItemWhseDetl.WhseCode} = {?WarehouseCode})

However, I'm concentrating right now on a test report that uses only BM2 with the select clause:

{BM2_BillMaterialsDetail.ComponentItemCode} = {?ItemCode}

This alone takes 20 seconds, which leads me to believe I'm doing something wrong.

 

[BigLouie]

How many items do you have? Do you do any grouping based on IM1 or IM2?

 

[Algernon83]

I don't group on anything. Since it's the sum of the quantity of these items that I'm really interested in, I tried grouping on ComponentItemCode and checking "group on server", but there was no difference in response time. In fact the SQL query didn't change. Perhaps the MAS 200 ODBC driver doesn't support grouping on the server.

BM2 has 31,229 records.
BM1 has 5,117 records.
IM1 has 14,529 records.
IM2 has 17,854 records.

 

[dfeller]

It looks like your linking is incorrect.

BM2 --> BM1 by BillNumber and Revision
BM1 --> IM1 by BillNumber to ItemNumber
(if you're pulling data from inventory master based on Bill number)

Why do you have IM2 in there?

Dawn

 

[Algernon83]

IM2 is in there because part of the goal is to check stock in a specific warehouse. Let me explain, as I should have in the first post.

The goal is a report that lists, for each raw component, the number in stock in a particular warehouse, as well as the number in subassemblies (which can be taken apart if we need another of the raw part). So I need IM1 to check if a BOM in which the component is used is a reversible subassembly (by the product line), and IM2 to look in the specific warehouse.

I have discovered since yesterday that the MAS ODBC driver actually can group on the server, but Crystal Reports 8.5 doesn't generate the SQL syntax to do this. So what I'm working on right now is a query, using OpenOffice Base, that will pull all of the appropriate data into one query, joined and grouped properly, without a separate subreport to check how much of each item is in subassemblies. If this generates the data quickly and correctly, I'll have to look into a fix for Crystal Reports to generate the correct SQL syntax.

 

[BigLouie]

I did something similar for a customer once but what I did was use Microsoft Access. I wrote SQL Specific Pass Through Queries reading MAS. Then I wrote some append queries to populate tables. Then I wrote some code to flush the tables and repopulate. Instead of a bunch of linked tables in Crystal I ended up with one data set in Access. Created a form with a button in Access. When the button was pushed everything ran and in about 4-5 minutes out came the report.