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.


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


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")


graphics/ccc.gif SI_PROGID=CrystalEnterprise.Report")

For i = 1 to infoObjects.Count

 Set infoObject = infoObjects(i)

 Response.write infoObject.Properties("SI_NAME") & "
" Next %>

Figure 34.1. The output of Listing 34.11 shows all reports held in Crystal Enterprise.


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 " & _


 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:


Part I. Crystal Reports Design

Creating and Designing Basic Reports

Selecting and Grouping Data

Filtering, Sorting, and Summarizing Data

Understanding and Implementing Formulas

Implementing Parameters for Dynamic Reporting

Part II. Formatting Crystal Reports

Fundamentals of Report Formatting

Working with Report Sections

Visualizing Your Data with Charts and Maps

Custom Formatting Techniques

Part III. Advanced Crystal Reports Design

Using Cross-Tabs for Summarized Reporting

Using Record Selections and Alerts for Interactive Reporting

Using Subreports and Multi-Pass Reporting

Using Formulas and Custom Functions

Designing Effective Report Templates

Additional Data Sources for Crystal Reports

Multidimensional Reporting Against OLAP Data with Crystal Reports

Part IV. Enterprise Report Design Analytic, Web-based, and Excel Report Design

Introduction to Crystal Repository

Crystal Reports Semantic Layer Business Views

Creating Crystal Analysis Reports

Advanced Crystal Analysis Report Design

Ad-Hoc Application and Excel Plug-in for Ad-Hoc and Analytic Reporting

Part V. Web Report Distribution Using Crystal Enterprise

Introduction to Crystal Enterprise

Using Crystal Enterprise with Web Desktop

Crystal Enterprise Architecture

Planning Considerations When Deploying Crystal Enterprise

Deploying Crystal Enterprise in a Complex Network Environment

Administering and Configuring Crystal Enterprise

Part VI. Customized Report Distribution Using Crystal Reports Components

Java Reporting Components

Crystal Reports .NET Components

COM Reporting Components

Part VII. Customized Report Distribution Using Crystal Enterprise Embedded Edition

Introduction to Crystal Enterprise Embedded Edition

Crystal Enterprise Viewing Reports

Crystal Enterprise Embedded Report Modification and Creation

Part VIII. Customized Report Distribution Using Crystal Enterprise Professional

Introduction to the Crystal Enterprise Professional Object Model

Creating Enterprise Reports Applications with Crystal Enterprise Part I

Creating Enterprise Reporting Applications with Crystal Enterprise Part II

Appendix A. Using Sql Queries In Crystal Reports

Creating Enterprise Reporting Applications with Crystal Enterprise Part II

Special Edition Using Crystal Reports 10
Special Edition Using Crystal Reports 10
ISBN: 0789731134
EAN: 2147483647
Year: 2003
Pages: 341

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net