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 ?
Sorting - data already retrieved from the database is re-sorted. This re-sorting may have to be performed in the Crystal Report .rpt file. So much for stored procedural SQL containement.
Grouping - data is summarised into groups generally as part of an SQL statement. This may be handled internally by a database like Oracle. However, how would crystal reports and ODBC deal with it when Crystal Reports is effectively required to deconstruct the SQL statement in order to rebuild into its own set based structure ?
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.
Perform Grouping on Server option must be enabled (options dialog box).
Report must use some form of grouping.
The details section atleast must be hidden. Only hidden sections are processed by the server. This is confusing. How is this going to help ?
Formula fields are usually client processed. All records are transferred prior to formula field result calculation. Use SQL expressions if possible. What exactly is meant by an SQL expression in this case? What is the difference between a formula field and an SQL expression ? This is interesting. An SQL expression is an SQL function applied to a number of records, for instance, the MAX function which will find a maximum value of a field over a number of records. This is a problem in itself. Use of SQL expression functions in SQL statements traditionally tend to slow database access substantially. Generally SQL expression functions should never be used against a database directly. A secondary cursor or VB Recordset would be much better suited. Crystal Reports probably creates some type of an implicit cursor so the Crystal Reports manual is completely contradictory to what I was taught. However, what was true 10 years ago may be false today.
Running totals are also produced on the client-side.
Must contain no average or distinct requirements.
No Top N values are allowed.
No specific value grouping is allowed. Then what was all that about grouping and sorting required in the Crystal Report .rpt file ?
Date and time fields cause problems. Can only group on day or seconds not any other parts of dates or times.
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 | ||
|---|---|---|
| FIELD1 | INTEGER | 1 |
| FIELD2 | INTEGER | 2 |
| FIELD3 | INTEGER | |
| FIELD4 | INTEGER | |
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.
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>.
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.
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.
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 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.
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.
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.
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 !
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.
There are three options for how reports are run in a browser.
ISAPI/NSAPI (Explorer/Netscape application programming interface) - uses the Crystal Reports Web Server.
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.
CGI
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.