Troubleshooting

When looking at reducing the "length" of your queries—that is, keeping the number of records returned to a minimum—you'll need to employ the use of the WHERE and TOP N clauses. These standard SQL constructs work pretty much the way you would expect.

The WHERE clause is used to filter the records returned from the query. Ideally, if you only need to obtain information about a single object, use a WHERE clause such as

WHERE SI_ID=123

If properties for a single object are needed, try to use the WHERE clause to filter on the SI_ID (ID) or SI_NAME (name) of the object. If all reports at a certain level of the folder structure need to be brought back, use the SI_PARENTID property in the WHERE clause. The following sample query returns all reports at the root level of the folder structure:

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_PARENTID=0

The other common type of operation is to bring back all objects of a certain kind, for example, all reports, users, events, and so on. This is where the SI_PROGID property should be used in the WHERE clause, as shown in the following example that returns a list of all usernames:

SELECT SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID='CrystalEnterprise.User'

In addition to using a WHERE clause to filter the query, a big performance gain can be made by only filtering on indexed properties. The following properties are indexed in the Crystal Enterprise repository database:

• SI_CUID

• SI_GUID

• SI_HIDDEN_OBJECT

• SI_ID

• SI_INSTANCE_OBJECT

• SI_NAME

• SI_NAMEDUSER

• SI_NEXTRUNTIME

• SI_OWNERID

• SI_PARENTID

• SI_PLUGIN_OBJECT

• SI_PROGID

• SI_RECURRING

• SI_RUID

• SI_RUNNABLE_OBJECT

• SI_SCHEDULE_STATUS

• SI_UPDATE_TS

These indexed properties are used as actual fields in the Crystal Enterprise repository. All other properties are stored in a single binary data field in the repository. This means if a query is passed to the InfoStore with a WHERE clause using an indexed property, that WHERE clause can be used in the actual repository database query. This is the ideal situation. Alternatively, if a non-indexed property such as SI_DBNEEDLOGON is used, the query against the repository database must be run without a WHERE clause, thus bringing back extra records and then the InfoStore component must make a pass through the records and filter out the unnecessary ones. If there are large numbers of reports, folders, user, or other similar types of objects present in Crystal Enterprise, filtering on a non-indexed property can result in a performance bottleneck.

The TOP N clause is used for limiting the results of a query. The N would be replaced by a number, for example, TOP 10 returns the top 10 records. If no ORDER BY clause is used, the TOP N clause simply returns the first N values it finds. This can be useful for preventing a large query that would affect performance. An example of this is allowing the user to search for a report by name or keyword. If the system were to have several thousand reports, then depending on the search term used, a search could potentially bring back far too many results. This is analogous to performing a Google search for a word like "crystal." It would bring back too many results for a user to really be able to review.

By default, if a TOP N clause is not used, the InfoStore limits the results to 1,000. Effectively, the InfoStore inserts a TOP 1000 clause into all queries. You can determine if the list of objects returned has been truncated due to a TOP N by comparing the number of objects in the result collection (InfoObject's Count property) with the real number of records resulting from the query (InfoObjects's ResultCount property). If the Count is less than the ResultCount , the object collection has been truncated.

If there is a valid reason to run a report and return more than 1,000 objects, you can specify an arbitrary number in the TOP N clause such as TOP 5000 . If you want to change the default limit of 1,000 to a different number, lower or higher, modify the InfoStoreDefaultTopNValue Registry key values found in the following location:

HKLM\Software\Crystal Decisions.0\Enterprise\CMS\Instances\

MACHINENAME

.cms

The MACHINENAME is the name of the Crystal Enterprise CMS server. This needs to be configured for each instance of the CMS.

The other way the TOP N clause can be used is to rank the objects being returned. This is done by using an ORDER BY statement. The following query returns the name and start time of all scheduled jobs ordered by the start time in descending order.

SELECT SI_NAME, SI_STARTTIME FROM CI_INFOOBJECTS WHERE

     SI_PROGID='CrystalEnterprise.Report' AND SI_INSTANCE_OBJECT=1

     ORDER BY SI_STARTTIME DESC

This query essentially provides a log of scheduled reports. However, you can imagine how this list could get very large with a large system with many reports and many schedules. By adding the TOP 25 clause, only the last 25 scheduled reports would be returned.

The final principle to InfoStore query optimization is to reduce the number of overall queries to the database. Typically this is done by using some little-known InfoObject properties that can answer a question and eliminate the need for an additional query to find out that answer.

A typical scenario is to call a recursive function to display reports in the system. Typically the process is started by running a function like the following:

SELECT SI_ID, SI_NAME, SI_PROGID FROM CI_INFOOBJECTS WHERE SI_PARENTID=0

This would return a collection of objects that could be a report, folder, or favorites folder that exist as children of the object with an ID of 0, which is the root folder. After that, a function could be recursively called that would list objects in each subfolder. Sometimes this statement would run and that folder would have no children objects. In this case, that query could have been avoided if the SI_CHILDREN property was brought back. This property is a number representing the number of children objects that exist for a given InfoObject. If the value were 0, there would be no need to run a query to retrieve the children. This property also works for reports and instances. A report with four instances returns an SI_CHILDREN value of four. It's also a nice feature to show this number in the user interface to indicate how many objects are below a certain level.

Another property that is useful for reducing additional queries is the SI_INSTANCE_OBJECT property. It is a Boolean value that indicates whether the current object is a report instance. Because this is an indexed property, it could be used in the WHERE clause to limit the results to only report instances or perhaps the opposite : only report objects.

Object Identifiers

Thus far in this book, the SI_ID property has been used as the unique identifier of a report. This is valid, but the ID is technically only unique to the current Crystal Enterprise deployment. You might find that a report when moved from the development to production environment will have a different SI_ID . Depending on how your application works, this might be a concern. In a very generic application, the only ID hard-coded anywhere should be the ID of the root folder in Crystal Enterprise, which is always 0. However, sometimes the ID is hard-coded or perhaps stored somewhere outside of Crystal Enterprise. If this is the case, the SI_CUID property can be used as a unique identifier. This property is a globally unique identifier within a Crystal Enterprise cluster, so that when reports are moved from one deployment to another, the SI_CUID property stays the same. Keep in mind that this value is a string instead of a number and contains alphanumeric data, so it needs to be handled appropriately. To see what the SI_CUID values are for your current reports, run the following statement in the Query Builder:

SELECT SI_NAME, SI_CUID FROM CI_INFOOBJECTS WHERE

     SI_PROGID='CrystalEnterprise.Report'

Hierarchical Properties

So far the InfoObject properties that have been discussed have been simple string, date, or numeric properties. There are some types of properties that exist in the InfoStore that need to provide more than a single value. For example, to return a list of parameters (prompts) defined in the report, instead of having properties such as SI_PROMPT1 , SI_PROMPT2 , SI_PROMPT3 , and so on there is simply an SI_PROMPTS property that contains information for multiple prompts. Hierarchical properties generally map to an object in the Crystal Enterprise object model, in this case, the ReportParameters collection.

As an example, the SI_FILES property returns a list of files that are stored with an InfoObject. To retrieve the hierarchical properties, SI_FILES must be included in the SELECT clause:

SELECT SI_NAME, SI_FILES FROM CI_INFOOBJECTS WHERE

    SI_PROGID='CrystalEnterprise.Report'

Its values can be accessed via the Files collection attached to the InfoObject. Obviously there is more than one piece of data retrieved to make up the collection, but that is handled behind the scenes. Consult the documentation for a list of query properties and which object collections they map to.

Report-Specific Properties

When dealing with report objects specifically , there are two additional categories of properties that are available. They are processing information and scheduling information. Notice they map to the ISchedulingInfo and IProcessingInfo interfaces from the previous chapter on scheduling. These properties are accessed by the SI_PROCESSINFO and SI_SCHEDULEINFO prefixes, respectively. The following example retrieves the name and record selection formula via the SI_NAME and SI_RECORD_FORMULA properties:

SELECT SI_NAME, SI_PROCESSINFO.SI_RECORD_FORMULA FROM CI_INFOOBJECTS WHERE

    SI_PROGID='CrystalEnterprise.Report'

These properties do need to be prefixed or they will not work. There is a full list of these processing info and scheduling info properties in the Crystal Enterprise documentation. Look for the Query Language Reference section. The following is a list of some of the more useful properties.

Processing Info ( SI_PROCESSINFO ):

• SI_LOGON_INFO : A hierarchical property containing the logon credential information for a report

• SI_PROMPTS : A hierarchical property containing the definition and metadata for each parameter defined in a report

• SI_RECORD_FORMULA : The record selection formula for a report

Scheduling Info ( SI_SCHEDULEINFO ):

• SI_STARTTIME : The start time for a scheduled job

• SI_ENDTIME : The end time for a scheduled job

• SI_OUTCOME : The resulting status for a scheduled job, this maps to values in the CeScheduleOutcome enumeration

• SI_SUBMITTER : The username of the user who submitted the scheduled job

Advanced Searching

Based on the material provided so far, you should be able to add searching capability to your application by using the WHERE clause to limit results returned. As an example, the following query would return all reports that are called Sales Report:

SELECT SI_ID FROM CI_INFOOBJECTS WHERE SI_NAME='Sales Report'

Although this is useful, the user would need to know the exact name of the report, which somewhat defeats the purpose of the search. This section describes some advanced methods for providing more robust searching within a Crystal Enterprise-based application.

Up until this point in the coverage of InfoStore queries, all conditions used in the WHERE clause have used the = operator. In fact there are several additional types of operators:

• Not equal to: !=

• Greater than: >

• Less than: <

• Greater than or equal to: >=

• Less than or equal to: <=

• Textual wildcard match: LIKE

• Negative textual wildcard match: NOT LIKE

• In a list of values: IN

• Not in a list of values: NOT IN

• Between the range of two values: BETWEEN

These operators are type-sensitive as you would expect from standard database query operators. As an example, the LIKE operator works against string items only, and the equality operators work only against numeric data. Most of these are self-explanatory but some require some additional information.

The LIKE operator is a great way to handle searching for reports by a keyword or partial report name. Its syntax is as follows :

<property> LIKE <pattern>

The pattern can contain the following wildcard characters :

• % matches any string of zero or more characters (not case-sensitive). The condition SI_NAME LIKE '%budget%' returns all reports with the word budget in them. The condition SI_USERFULLNAME LIKE 'Lisa%' returns all usernames that begin with Lisa.

• _ matches any single character. The condition SI_NAME LIKE 'Team _' matches Team A, Team B, Team C, and so on.

• [] matches any single character between a specified range. The condition SI_NAME LIKE '200[1-3] Budget' matches reports with the names 2001 Budget, 2002 Budget, and 2003 Budget.

• [^] performs a negative match on any single character between a specified range. The condition SI_NAME LIKE '200[^3] Forecast' matches reports with the names 2001 Forecast, 2002 Forecast, and so on, but not 2003 Forecast.

As you can see, the LIKE operator is quite powerful and can be used to provide some robust searching. This becomes important as the number of reports, folders, and users grows.

The IN operator is useful for querying for reports from a known list of IDs or names. The syntax for the IN operator is

<property> IN (<values>, <value>, ...)

Some examples of using the IN operator are shown here:

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_ID IN (223, 732, 442, 334, 743)

SELECT SI_ID FROM CI_SYSTEMOBJECTS WHERE SI_PROGID='CrystalEnterprise.User'

    AND SI_NAME IN ('jsmith', 'sbecker', 'mblouin')

Finally, the BETWEEN operator is a quick way to express a range condition. The syntax is as follows:

<property> BETWEEN <value> and <value>

Some examples of using the BETWEEN operator are listed here:

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_ID BETWEEN 250 and 260



SELECT SI_ID FROM CI_INFOOBJECTS WHERE SI_PROGID='CrystalEnterprise.Folder'

    AND SI_CHILDREN BETWEEN 1 AND 20

Custom InfoObject Properties

You've probably figured out so far that when InfoStore queries are run, any properties listed in the SELECT clause are available from the InfoObject's Properties collection. This is a properties() method in the Java object model and a Properties collection in the COM and .NET object models. Assume the following query was run against the InfoStore:

SELECT SI_NAME, SI_DESCRIPTION FROM CI_INFOOBJECTS WHERE

    SI_PROGID='CrystalEnterprise.Report'

At this point, the following JSP code could be used to access the SI_DESCRIPTION property:

IInfoObject infoObject = (IInfoObject) results.get(0);

IProperties props = infoObject.properties();

IProperty prop = props.getProperty("SI_DESCRIPTION");

out.println(prop.getValue());

You might have noticed that there is an add method on the IProperties interface. This means you can add any number of properties and subproperties yourself. The following code adds a property called Project to the report, so that reports can be tied to specific projects.

IInfoObject infoObject = (IInfoObject) results.get(0);

IProperties props = infoObject.properties();

IProperty newProp = props.add("Project", "Project X", IProperty.DIRTY);

iStore.commit(results);

After this custom property is set, a developer can retrieve this property at any time to display to the user. The new property 'Project' can be used in the InfoStore query like this:

SELECT SI_NAME, Project FROM CI_INFOOBJECTS WHERE SI_ID=289

This new property can even be used in the WHERE clause to perform a filter as shown here:

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE Project='Project ABC'

Note that custom properties are not indexed so are not optimal to filter on, but can be useful when used appropriately.