How to write a subreport into an existing SQL Command?

[beacon5]

Hi everybody,

[Crystal Reports 2008]

I have a report that connects rows in a table based on a common field, an order number. However, there's a field, called Draft/Final, that indicates whether a specific form has been completed or not. The problem is that the table is the data source for four (4) different forms and there's only one Draft/Final field.

So, what I decided to do was add a subreport, pass the order number, determine whether the last form (I'll call it Form D) has been completed or not, pass that value back to the main report, then use that to determine how to display the record for the next to last form (Form C).

However, because I'm using a subreport and passing a value in a formula, I can't use that formula in the record selection to eliminate unnecessary records because it has to be evaluated later. So, my question is: since I used SQL Commands in the main report and the subreport to improve performance and because I want to eliminate records based on the return value in the subreport, can I write the SQL command for the subreport into the main report?

I thought that I'd seen this done before, but wasn't sure if it was with SQL commands or with SQL expressions.

My end game is to show all Form C's that have been completed, then check to see if Form D has been completed for that order number. If it has, I want that record to drop off the list. If it hasn't then I want it to continue to stay on the report until it's completed.

Thanks,
beacon

 

[lbass]

Please identify the actual values returned in the Form field (if not, C, D, etc.). If the values are not sequential, then what other field is used to order them? Can I assume your data looks something like this?

Order ID Form Draft/Final
123 A Draft
123 B Draft
123 C Draft
123 C Final
123 D Draft

-LB

 

[beacon5]

Technically, the form isn't specifically identified in the table. There are fields that appear on each form that are specific to only that form, and that's how I can tell which form was completed.

The table looks something like this (Keep in mind that Form A and Form B also show on the same row, although there won't be a value in their fields if those forms haven't been completed...I used the SQL command to eliminate these records from the get-go):

FrmC_Ord_Num FrmC_Date FrmC_Time FrmC_Service FrmD_Ord_Num FrmD_Date FrmD_Time FrmD_Service Draft_Final_Value
-----------------------------------------------------------------------------------------------------------------------------------------------------------
123 3/1/12 10:00AM Psychology Final
123 123 3/5/12 8:30AM Psychology Final
234 3/9/12 11:00AM Psychiatry Final


On my report, I'm using only the Form C fields plus the Draft/Final field in the SQL Command for the main report. I'm using only the Form D fields plus the Draft/Final field in the SQL Command on the subreport. The subreport has a formula that determines whether or not a Form D has been started or not, and whether or not it's been completed.

In the example above, the formula on the subreport would pass "Final" back to the main report for order #123 because Form C is completed and Form D is completed. The formula would pass back "No Record" for order #234 because a Form D hasn't been started yet for that order number. If the Form D had been started for order #234, but it was in "Draft", then the formula would pass "Draft" back to the main report.

The problem I'm having with the subreport setup is that I need for the records on the main report that have a completed Form D to drop off the report once the associated Form D is completed. I can't eliminate the records in the record selection based on the value passed back from the formula because it has to be evaluated later since it's on the subreport. I know I could suppress the records, but the performance of the report is already very slow even though I'm using SQL commands. If the number of records grows without records ever falling off, the report will eventually be unusable.

What I was hoping to be able to do was write what I was doing in the subreport into a subquery in the main report's SQL command and do away with the subreport altogether.

It's not the best setup of the forms, but I don't have any control over that, so I have to make due with what I'm given. To recap, there is one table that houses the fields from four forms and only one form's fields are updated each time for new rows. I'm linking the data on the forms using the order number, but I'm not sure what effect this will have if I'm using a subquery. Also, I don't know if it's even possible to write a subquery that uses the same exact table as the outer query.

Thanks,
beacon