Querying the Crystal Enterprise Repository

So far this chapter has covered the process of establishing and maintaining a Crystal Enterprise session. After that is done, the next logical step is to perform some kind of action on something stored in Crystal Enterprise. Some examples of this might be listing all reports in the system, listing all report instances in a folder called "Sales," or viewing a report called "District Forecast." As it turns out, all things stored in Crystal Enterprise are stored as objects.

Starting at the most granular level, the InfoObject is an abstraction of an object that is persisted in the Crystal Enterprise system. An InfoObject contains information about itself, such as its name, description, type, and so on. Examples of the different types of InfoObjects that can exist on a Crystal Enterprise system are

• Reports (Crystal Report)
• Report instances
• Analytical reports (Crystal Analytical Report)
• Folders

For a discussion of InfoObjects, p. 519

An InfoObjects collection is, not surprisingly, a collection of InfoObject objects. After an InfoObjects collection is obtained, you can enumerate through it to get each InfoObject contained within.

The InfoStore object is the key object that enables retrieval, scheduling, and modification of reports as well as creation of new InfoObjects collections. To retrieve an InfoObjects collection from the InfoStore object, the Query method is called, passing in a SQL-like query statement.

InfoStore Queries

The high-level syntax of this statement is as follows:

SELECT [Properties] FROM [Table] WHERE [Condition]

Properties begin with SI_, for example, SI_ID, SI_NAME. They describe the objects properties. Generally, CI_INFOOBJECTS is the table that is used for all queries. The WHERE Condition can be used to filter to a collection of objects coming back.

NOTE

To get the InfoStore object, the user must be logged on first and the application must have reference to the EnterpriseSession object. The users rights determine what InfoObjects can be accessed by the InfoStore object.

The following list describes some of the more useful properties that can be queried for:

• SI_ID: The unique identifier of the object
• SI_NAME: The name associated with the name, a report title for a report, or a folder name for a folder
• SI_PARENTID: The unique identifier of the parent object, a reports parent object is the folder object that contains the report, a folders parent object is its parent folder, and so on
• SI_PROGID: The type of object; some examples of valid values are CrystalEnterprise.Report, CrystalEnterprise.Folder, and CrystalEnterprise.Pdf
• SI_OWNER: The name of the user who owns that object

Given these properties, the following list shows some sample queries that use these properties.

• SELECT SI_ID FROM CI_INFOOBJECTS WHERE SI_NAME = "Budget" retrieves the ID of a report named Budget
• SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_OWNER = "Neil" retrieves all reports in the system that are owned by Neil
• SELECT SI_NAME, SI_PROGID FROM CI_INFOOBJECTS WHERE SI_PARENTID = 456 AND SI_PROGID = "CrystalEnterprise.Report" retrieves all report objects stored in the folder with an ID of 456

NOTE

A very useful tool provided with Crystal Enterprise enables system administrators and developers to directly query the system without writing code. The Query Builder is available under the Client Samples section of the Crystal Enterprise Desktop Launchpad. To try it out, start the Query Builder and enter a query into the query window. After it is entered, click the Submit Query button. Notice that the logon information supplied to the Query Builder application affects the returned resultset. Try using a specific user account with restrictions to a particular folder or object. Note that information for those objects is not returned in the query.

Effectively querying the Crystal Enterprise repository is one of the most crucial pieces related to application performance when a large number of objects are present in the Crystal Enterprise system. Clearly, using SELECT * FROM CI_INFOOBJECTS is not an ideal query. Just as with any other relational database, using efficient queries to retrieve data is a best practice and often can be the culprit when experiencing poor Crystal Enterprise application performance.

Listing Reports and Folders

Listing 34.12 queries Crystal Enterprise for all reports. After it has the collection of reports back, it loops through each one and prints out its name into the resulting ASP page. Figure 34.1 shows the output of this ASP page.

Listing 34.12. Listing Reports from Crystal Enterprise

<% Set sessMgr = Server.CreateObject("CrystalEnterprise.SessionMgr") Set sess = sessMgr.Logon("Ryan", "123", "CMS1", "secEnterprise") Set iStore = sess.Service("","InfoStore") Set infoObjects = iStore.Query("SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_PROGID=CrystalEnterprise.Report") For i = 1 to infoObjects.Count Set infoObject = infoObjects(i) Response.write infoObject.Properties("SI_NAME") & "

" Next %>

On a relatively empty system, listing all reports in a single list might be feasible; however, in larger implementations, showing all reports is generally not a great idea for a couple of reasons. There might be too many for a user to search though, and it is an expensive operation to bring back all report objects, where perhaps the user only wanted to look at a few of them in the list. Some more efficient ways of listing reports are to show them in their folder structure. Listing 34.13 starts at the root folder in the system and shows only objects at that level. A hyperlink is used to refresh the page with a different folder level and thus drill-down into that folders contents.

Listing 34.13. Providing a Folder-by-Folder Report Listing

<% Note: for simplicity, no session handling is done, the logon operation is performed on each page hit Set sessMgr = Server.CreateObject("CrystalEnterprise.SessionMgr") Set sess = sessMgr.Logon("Ryan", "123", "CMS1", "secEnterprise") Set iStore = sess.Service("","InfoStore") parentID = Request.QueryString("ParentID") If parentID = "" Then parentID = "0" End If Set infoObjects = iStore.Query("SELECT SI_ID, SI_NAME, SI_PROGID FROM " & _ "CI_INFOOBJECTS WHERE SI_PARENTSI_NAME") objectID = infoObject.Properties("SI_ID") If infoObject.Properties("SI_PROGID") = "CrystalEnterprise.Folder" Then Response.Write "<a href= ree.asp?parent>" & _ objectName & "</a>

" Else Response.Write objectName & "

" End If Next %>

With some creativity, using only the ID, Name, ParentID, and ProgID properties, a great number of user interfaces can be produced. They might show reports by folder, by name, by type, and so on.

Retrieving Report Instances

As previously covered in this book, when Crystal Enterprise has finished processing a report, the resulting report with its snapshot of data is stored as an instance in the Crystal Enterprise system. To retrieve these instances, its necessary to query the system.

Like a folder and its content, hierarchically, a report and its instances have a parent-child relationship. Its possible to retrieve the instances of a report by querying for objects with SI_PARENTID property equal to the report objects ID.

Given a report with ID of 234, entering the following query retrieves its instances:

SELECT SI_ID FROM CI_INFOOBJECTS WHERE SI_PARENTID = 234