	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize

	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: Analyzers Bestsellers CR Schedulers CR UFLs CR Viewers DataBase Tools Graphics International Mail UFLs ReCrystallizePro
	Add'l: About us Contact Us cViewSUITE Ppt Support
	CrystalReports on Steroids

Use an On Demand Subreport to mimic a drill down

We had a finance report for a department to display the actual results compared with the budget. All the relevant data were available in the account master file, but we needed the report to drill down to display the transactions for selected master codes.

The traditional approach of a master - transaction drill down report resulted in a lengthy report, both in the time needed to join the tables together and the large number of records processed by the report.

The report was being delivered on request via a Crystal Enterprise server. We might have some patience while designing a report. We don’t even care how long it takes when using a report scheduler.

However, users have limited tolerance when waiting for a report to display in a Web browser. The speed of the report was a critical acceptance factor with the department managers.

Redesigning the report to display only the master records created a very fast report. The drill down effect was mimicked by using an on demand subreport. Only a few general ledger codes were going to be viewed at a lower level.

The drill down subreport had all the record selection information linked into subreport parameters, so it was also very fast. By splitting the work between a lean master report and a powerful on demand subreport, we got an amazing improvement in response time.

If you need to identify the performance issues in your Crystal Reports, get a copy of Report Analyzer. This will be one of the best purchase decisions you've ever made, because you will not only help your company but also protect your job.

You can also use a subreport on demand. One of our customers was testing one of our User Function Libraries. This function library builds up and then writes a disk file with some data. Our XML, Grid and Disk Output UFLs all do this.

You can do all the file writing in one formula or you can build the data up across multiple formulas using data from each section of the report.

The final formula writes the data to the disk file. The DiskLinesFinish function did this for him in a formula in the report header section.

Our client found a way to control this, by having a simple on-demand subreport with this final formula. This way he could view the report, check it had the correct record count and field values, and then save it to disk by clicking on the on demand subreport.

But which part of the report do you do in the main report, and which parts do you want in subreports? We suggest the following approach:

The main report must do the primary record selection and grouping. You cannot group or select data based on values in the subreport. The subreport is then used complete the missing values–essentially "fill in the blanks."

One extension to this approach is to use a subreport to retrieve a value, and then use conditional formatting in the main report that uses the subreport information. But there are limitations to this:

The subreport has to take up some vertical space in the report.
Subreports in suppressed sections do not get processed, so any shared variables expect from them will not be calculated.

Why is there so much interest in subreports? This concept is not new to Crystal Reports. It's been in practice for a long time, in fact. Subreports are common database tools that serve as intermediaries when you can’t create a report directly from multiple data sources.

In Access, for example, doing anything across multiple queries or tables requires building a subreport. Crystal Reports doesn’t need the same degree of subreport "propping up" that Access does, but it still makes handy use of subreports.

Suppose you have two databases, where each stores the same information—such as employee names, addresses, start dates, and so on. But, the one uses the date format YYYY-MM-DD and the other uses the date format MM-DD-YY.

Hmm. Small problem, here. Suppose one table store numbers in a true number field and the other stores them as string variables or text. The only way to resolve such incompatibilities is with a subreport. You create one subreport to standardize the data in one database to the format of the data in the other. Or, if you’re feeling particularly masochistic, you create a subreport for each database, coming up with an entirely new format.

Subreports can be linked (they update data as those data change in the source) or unlinked (they contain static data). You would want a linked report if you want "real time" information. You would want a static report under any of these conditions:

You simply lack the space for the larger file created by a linked file.
You won’t have access to the source file, thus you cannot keep the files linked.
Your report focuses on a specific period. For example, if you are reporting last year’s sales and it’s June now, you don’t want new data.

Among linked reports, a increasingly popular option is the on-demand subreport. It doesn’t process until you ask it to. It can appear as just a link or some other element (such as a graphic that says, "Update data now.") This has all kinds of benefits which will become apparent as you use it.

You simply lack the space for the larger file created by a linked file.
You won’t have access to the source file, thus you cannot keep the files linked.
Your report focuses on a specific period. For example, if you are reporting last year’s sales and it’s June now, you don’t want new data.

A note on linked subreports

Linked subreports are really useful. Link a field or formula from the main report and get the subreport to select records based on that field. But what if your subreport needs the linked parameter for something else? You might want to use it in a calculation, or for conditional grouping, sorting or section suppress.

If you don’t use the link for record selection you may find that the link is removed when you exit the subreport link form.

The solution is to create the parameter in the subreport prior to creating the link. When you create a link to an existing parameter in the subreport, it is saved when you exit the subreport link form.

This article is copyright 1999 - 2005 Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.