I am having a problem with Crystal Reports and I hope someone can help me out.
The report was created with Crystal Reports 8.5 using Pervasive SQL 9 for the database. The report is a sales order that has multiple lines. Each line on the sales order can have zero or more lines of processing. The sales order lines are the details of the report, and the processing was not displayed when the report was first created. I added a subreport to show the processing for each line.
When I first added the subreport, the data was not showing for the subreport even though there was processing data for the lines. I was able to fix the problem by unchecking the 'Use Indexes or Server For Speed' checkbox under Report Options. This worked for awhile, but as our client added more data, the report became intolerably slow.
I have determined that it is this subreport that is slowing down the printing of the report, and I would like to speed it up, but when I select the 'Use Indexes or Server For Speed' checkbox, the subreport is blank for all lines. The subreport is pretty simple. There is the main table called so_processing and another table called processing_charges that is linked to the first through a field called process_id. The so_processing table originally had the following indexes:
key0: branch_id, so_no, line_no, process_index
key1: branch_id, quote_no, line_no, process_index
Each sales order or quote can have multiple lines and each line can have multiple processes. The sales order and quote lines are stored in the same table (sales_order_detail). All sales orders can have 0 for the quote_no and all quotes can have 0 for the so_no, so the above indexes are not unique. The subreport links were the following fields from the sales_order_detail table in the main report to the so_processing table in the subreport:
branch_id, so_no, line_no
The first thing I did to try to fix the problem was to add the following unique index to the table:
key2: branch_id, so_no, quote_no, line_no, process_index
and changed the subreport links to be:
branch_id, so_no, quote_no, line_no
That didn't work, so I did thought maybe if the unique index was first, it would force it to use that index, so I rearranged the indexes to be:
key0: branch_id, so_no, quote_no, line_no, process_index
key1: branch_id, so_no, line_no, process_index
key2: branch_id, quote_no, line_no, process_index
That still didn't work, so now I'm stumped. So my question is: Why would selecting the 'Use Indexes or Server For Speed' checkbox cause the subreport to not pull in the data when it does pull the data in with the box unchecked?
Thanks,
Greg
The report was created with Crystal Reports 8.5 using Pervasive SQL 9 for the database. The report is a sales order that has multiple lines. Each line on the sales order can have zero or more lines of processing. The sales order lines are the details of the report, and the processing was not displayed when the report was first created. I added a subreport to show the processing for each line.
When I first added the subreport, the data was not showing for the subreport even though there was processing data for the lines. I was able to fix the problem by unchecking the 'Use Indexes or Server For Speed' checkbox under Report Options. This worked for awhile, but as our client added more data, the report became intolerably slow.
I have determined that it is this subreport that is slowing down the printing of the report, and I would like to speed it up, but when I select the 'Use Indexes or Server For Speed' checkbox, the subreport is blank for all lines. The subreport is pretty simple. There is the main table called so_processing and another table called processing_charges that is linked to the first through a field called process_id. The so_processing table originally had the following indexes:
key0: branch_id, so_no, line_no, process_index
key1: branch_id, quote_no, line_no, process_index
Each sales order or quote can have multiple lines and each line can have multiple processes. The sales order and quote lines are stored in the same table (sales_order_detail). All sales orders can have 0 for the quote_no and all quotes can have 0 for the so_no, so the above indexes are not unique. The subreport links were the following fields from the sales_order_detail table in the main report to the so_processing table in the subreport:
branch_id, so_no, line_no
The first thing I did to try to fix the problem was to add the following unique index to the table:
key2: branch_id, so_no, quote_no, line_no, process_index
and changed the subreport links to be:
branch_id, so_no, quote_no, line_no
That didn't work, so I did thought maybe if the unique index was first, it would force it to use that index, so I rearranged the indexes to be:
key0: branch_id, so_no, quote_no, line_no, process_index
key1: branch_id, so_no, line_no, process_index
key2: branch_id, quote_no, line_no, process_index
That still didn't work, so now I'm stumped. So my question is: Why would selecting the 'Use Indexes or Server For Speed' checkbox cause the subreport to not pull in the data when it does pull the data in with the box unchecked?
Thanks,
Greg