Crystal Reports Web Server


Server-Side Processing

Can choose to have report program do grouping on the server. (Some of the SQL report processing is pushed onto the server).

Uses SQL pass-through technology to send an SQL statement to an ODBC driver and retrieve initial data set. Much of report processing run on server. Server side report processing only works for reports which have been sorted and grouped within the report. Also only functions for reports with SQL data sources; query based reports would not run on the server. Non-ODBC Oracle reports could be a problem. Does this also imply that stored procedure Oracle executed reports are executing everything in the Oracle database stored procedure that by default is done by the Crystal Reports web server ? If this is the case then as long as SQL statements are in Oracle stored procedures then the Crystal Reports web server is irrelevant.

What precisely is meant by grouping and sorting ?

Therefore SQL processing is not done on the server in general or all SQL processing is done on the server and the following happens. Crystal reports requests the server to execute an SQL statement without grouping and sorting. Then the grouping and sorting is done by the client or the server depending on the Crystal Reports setup. This implies that an SQL statement in a stored procedure containing a group-by clause may cause odd results in Crystal Reports. As a consequence of the requirements of running of the report on the Crystal Reports server any SQL grouping or sorting commands in Oracle stored procedures could cause the report to run on the client anyway since the grouping and sorting constraints are not within the Crystal Reports .rpt file. Is this a problem ? Probably !!!

Crystal does server-side processing under the following conditions.

Question ? Is anything at all allowed when requiring server-side processing ? Seems to be severely restrictive.

Hidden section report drill-down requires constant server-client connection. The connection is obviously required. However, this feature should be tested for performance. Since Oracle does not require transaction commits on select statements (so I am told) then there should theoretically be no possiblilty of locking contention problems.

Use Indexes or Server for Speed option forces an order by clause to be added. This is also interesting. Generally an order clause is comprised of parts of a where clause. Also the order clause needs to only contain the parts of the where clause not consistent with any available index. In other words the order clause should only ever contain what is required for sorting in addition to or in contradiction to the contents of the where clause. This is because order clause causes the creation of a secondary, temporary table in memory or a temporary processing area. Thus the order clause is only ever executed upon the results of an SQL statement after the where clause (record filter) has been applied. Examine the following example. Will Crystal Reports comply with the most efficient optimisations of SQL statements ? Probably not !!!

EXAMPLE TABLE - PRIMARY INDEX on FIELD1 and FIELD2
FIELD1INTEGER1
FIELD2INTEGER2
FIELD3INTEGER 
FIELD4INTEGER 

The following SQL statement would use the primary index most efficiently.

select field1, field2, field3, field4 from table where field1 = ??? order by field2

The following would not since it would be resorting what is not required to be resorted since there is only a single value available after the execution of the where clause.

select field1, field2, field3, field4 from table where field1 = ??? order by field1, field2

This is a very simplistic example and in thus negligible. However, use of Crystal Reports tends to produce humungous SQL join statements. The above could become important in the case of large SQL join statements. Large join statements are common when using Crystal Reports since Crystal Reports does not really allow any other way. Crystal Reports is in general more of a power-user tool rather than a development tool. Crystal Reports handles complexity extremely badly.

In general changes to the report to allow for server-side report processing generally forces changes to the Crystal Reports SQL statement to be executed on the server in order to attempt to comply with all the previously specified Crystal Reports Web Server execution restrictions.

SQL Expressions

Can enter SQL expressions into a report as an insert. Then these created SQL expressions can be inserted into a report as a database field would be. By SQL expression one would assume something such as max(thisField) and not select * from <whatever>.

On-Demand Subreports

Subreports which remain hidden until requested by the user. These subreports seem to be controlled by execution of additional SQL statements against the database. In general these reports start out hidden and appear as object frames. When the object frame is selected on-demand then these subreports are executed.

Compiled Reports and Distribution

Reports can be compiled and distributed in exe and dll files. The manual states that this is useful for distribution to client machines without a Crystal Reports installation. There is no mention whatsoever in terms of a speed increase when distributing as run-time rather than development.

Groups in Crystal Reports

The manual mentions custom group creation. However, it appears that these groups are generally wholly dependant on the value of some field value. These field values can be grouped into Crystal Report defined values but the fundamental concept still exists as follows. A group is directly related to the group by clause in the SQL statement which is the basis of a Crystal report. For instance, try the following experiment. Try to create a group in a Crystal Report without the use of a database field. Also try to create a group with a database field but perhaps within a single SQL result set with all the same values in that group field. The reason for these required experiments is that quite often the group-by and having SQL statement clauses are misunderstood and used for the wrong reasons. This will not necessarily result in errors but could cause unneccessary processing time. Note that group by and having clauses are applied to an SQL statement in the same way that where and order by clauses are in terms of processing time overhead. In some cases, particularly those which are not dependant on SQL results, an unlinked subreport may be a better option.

Charting

Charting in Crystal Reports looks very good. It may not be expedient to produce a Crystal Reports chart on a web page in Crystal Reports when only the chart is required. However, the Crystal Reports charts are 3-dimensional and they do look very good indeed.

Chart layouts.

Chart types.

Data which can be used for drawing charts.

Mapping

This could be useful but sounds good. Can include maps in reports. Can use to analyse data and show trends. For instance, create a map showing sales by region.

Sounds good and looks very good. However, could involve long download times.

Sub-Reports

A report within a report. A subreport must have its own record selection criteria. Does this mean it must have an SQL statement ? Also a subreport can not contain another subreport. Use subreports under the following circumstances.

Unlinked Reports

Data is not coordinated with primary calling report. Can even use multiple tables or a different data source. What about headers only for instance ? Can it be used to standardise reports ? I doubt it !

Linked Reports

In this case data is coordinated between primary and subreport. The data in the subreport is entirely dependant upon the data in the primary calling report.

Production of Browser-based Reprots

How Reports are Produced

There are three options for how reports are run in a browser.

  1. ISAPI/NSAPI (Explorer/Netscape application programming interface) - uses the Crystal Reports Web Server.

  2. ASP - does not used the Crystal Reports Web Server but runs through the ASP COM wrapper. This uses the automation server. Allows for better security with encoding of security into ASP.

  3. CGI

What Reports are Produced With

Crystal reports can be produced and downloaded in HTML, DHTML, as ActiveX objects or Java Applets. All this functionality is very useful. The ActiveX and Java Applet versions are much more functionally capable than both HTML and DHTML.