Query Language Reference

This section provides a reference to the Crystal Enterprise query language. The Crystal Enterprise query language is similar to the Structured Query Language (SQL). Using the InfoStore's Query Method and a simple SELECT statement, you can retrieve InfoObjects (for example, reports or users) from the APS InfoStore, and then use the SDK to gain access to the properties that were queried for. For further details, see Accessing properties from a query result.

Example

QueryResult = IStore.Query("Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_ID = 123")

A typical query is comprised of a SELECT statement that includes query language property names and the APS InfoStore categories CI_INFOOBJECTS and/or CI_SYSTEMOBJECTS. See Syntax. The properties that you can use to build your query are divided into four types:

• InfoObject properties
• ProcessingInfo properties
• SchedulingInfo properties
• System properties

Note:    

• To use the Report Object, you usually only need the information in the ProcessingInfo object, so querying for the SI_PROCESSINFO Property should be enough.
• To schedule objects, it may be enough to retrieve the SI_SCHEDULEINFO Property.
• To set security rights, it is usually enough to query for SI_ID Property.
• The parser will ignore or misspelled properties and will return empty record sets when you query on them.

Tip:    Crystal Enterprise includes a sample Query Builder that you can use to test your queries. The link to this tool is located on the Crystal Enterprise Launchpad.

A clear understanding of the Crystal Enterprise query language is essential; indeed, an inefficient query may have a serious impact on the overall performance of your system. For example, a query that retrieves all reports and instances that exist on the APS can be extremely costly, especially if you have a large-scale system. For details on optimizing your queries, see Performance issues.