Last week, I was asked to produce three new versions of a report that listed account type totals, showing three different categories of branch. The data was collected by an SQL Command - much too complex for Crystal selection. What I did was adapt the SQL to select different branches in each of the three new reports.
It was then suggested that they might as well run as a single report, so I made two of them into subreports. I noticed that it might be possible to get the same result with a single SQL that set a flag for the branch-type, but this seemed like more work than was justified for a monthly report that ran for about an hour. Besides, no actual account was being selected more than once. So far, so good.
What happened next was that an SQL expert found a way to greatly speed up the SQL Command. This applied to the original report as well as the new reports, so I bundled them as a main report for all accounts and three subreport for the three branch categories. This took just 12 minutes all told – my own SQL had been correct but subtly inefficient.
This much was OK, but the report now reads every account twice, once in the main report and once in each of the subreports. Is there some way to preserve the temporary files from the SQL Command in the main report and re-use it in an SQL Command in a subreport? We are using Microsoft SQL 8 and Crystal 10, though an upgrade is planned.
I am aware that you can show the same data different ways using Crosstab, but the report is too complex for Crosstab. Also SQL will do its own reporting and re-sorting of files, but Crystal output is wanted.
PS. The SQL Command is also known sometimes known as a Stored Procedure, I have done this myself in the past. But strictly a Stored Procedure is something in the Crystal Repository. My SQL Commands are developed in SQL's Query Analyser and then pasted into the Crystal via the Database Expert.
![[yinyang]](/data/assets/smilies/yinyang.gif "[yinyang] [yinyang]")
Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10
It was then suggested that they might as well run as a single report, so I made two of them into subreports. I noticed that it might be possible to get the same result with a single SQL that set a flag for the branch-type, but this seemed like more work than was justified for a monthly report that ran for about an hour. Besides, no actual account was being selected more than once. So far, so good.
What happened next was that an SQL expert found a way to greatly speed up the SQL Command. This applied to the original report as well as the new reports, so I bundled them as a main report for all accounts and three subreport for the three branch categories. This took just 12 minutes all told – my own SQL had been correct but subtly inefficient.
This much was OK, but the report now reads every account twice, once in the main report and once in each of the subreports. Is there some way to preserve the temporary files from the SQL Command in the main report and re-use it in an SQL Command in a subreport? We are using Microsoft SQL 8 and Crystal 10, though an upgrade is planned.
I am aware that you can show the same data different ways using Crosstab, but the report is too complex for Crosstab. Also SQL will do its own reporting and re-sorting of files, but Crystal output is wanted.
PS. The SQL Command is also known sometimes known as a Stored Procedure, I have done this myself in the past. But strictly a Stored Procedure is something in the Crystal Repository. My SQL Commands are developed in SQL's Query Analyser and then pasted into the Crystal via the Database Expert.
Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10