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
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.
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.
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:
Given these properties, the following list shows some sample queries that use these properties.
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 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.
<% 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.
<% 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.
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