Linking Sub-Reports & Command Statements

[bluraz]

I am using Crystal XI and working with a SQL database. I have one report that includes 4 subreports. The report's purpose is to be a "snapshot" of production. The reason for the many subreports is because I am pulling data from different transaction types (i.e. Receipts, Shipments, Dispatches) and from different date types (i.e. Creation Date/Time & Completed Date/Time).

I have written the various reports in command statements, and then tried to link them by the date parameter (Start & End Date).

First Problem: I linked one sub-report to the main by the date parameter. When I try to run the report, it prompts me to input two date ranges. Is there a way to fix this within the command statements?

Second Problem: In addition to linking reports by date range parameters, two of the subreports are real-time totals. Therefore, I am doing a "While Printing Records, Share Total" from the subreport to the main. This causes way too many records to be pulled. Do I have any other options when trying to share subreport totals within a main report?

Let me know if you would like to see any code.

 

[beltmanjr]

First Problem: I linked one sub-report to the main by the date parameter. When I try to run the report, it prompts me to input two date ranges. Is there a way to fix this within the command statements?

To be honest I'm not certain what you have done, but it sounds amazing. So, am I correct in thinking that you have actual subreports in your main report?

Or do you have several command statements (I assume you mean the views you create using the database expert) in your main report.

Or do you have subreports in your main report with in both the subreports and main reports or just in either subreports or main reports the command statements?

I assume something like the latter however, that wouldn't necessarily cause a subreport to ask for the parameters unless there is something wrong with your subreport links.

In normal cases the subreport parameters either come from you manually or from values linked on by the main report. So if these are available in the main report, make sure to link them. If not, you could think about making them available...or remove them as parameter in the subreport.

Second Problem: In addition to linking reports by date range parameters, two of the subreports are real-time totals. Therefore, I am doing a "While Printing Records, Share Total" from the subreport to the main. This causes way too many records to be pulled. Do I have any other options when trying to share subreport totals within a main report?

Here I am not certain if there are any limitations to using a shared variable for total. Completely depending on where and how and how often you call your subreport it may be an idea to have a shared total in your subreport, display it in the main report and reset its value to 0 straight after.

With XI you could even specify that it must first run the formula to display the shared total before resetting it however, placing a reset formula in a group header or page header and the total in a footer (completely depending on your report) has always been an easy way.

 

[bluraz]

Thanks for the advice but I ended up getting this resolved.

First of all I created command (sql) statements, and used the "Add Command" feature in Crystal to set up the various sub-reports. My problem with the date thing, was that I was linking the reports by the "?Pm-?1 -Start_Date". I changed this to link to the actual parameter, "?1 -Start_Date" which was included in my command statements. This eliminated the two date prompts and works.

Second, I ended up using a getDate function in my command and just looked at the last 7 days for the total I was needing. This limited my search enough to eliminate the 1,000's of records being pulled.

Thanks again.