How to List Out Dimensions / Measures of Reports in BOE 3.X

Introduction

A common question when assessing your BusinessObjects landscape is “which Dimensions and Measures are being used in which reports?” If you are in the 4.X environment, your best bet for pulling this information would be to leverage the RESTful Web Service SDK to query for that information.

If you haven’t used the RESTful Web Services before, you may find my blog post on how to use Python to communicate with those services to be of value. If however you are still on the 3.X platform your best bet is to opt for the Java SDK. This document will show and explain in detail the Java code required to pull that information.

From a technical perspective, dimensions and measures are considered types of “Expressions” in Business Objects and will be referred to in this document as such.

Pre-Requisities

This document assumes some general programming knowledge, particularly with Java. Knowing basic HTML will also help you understand the output of the code.

You will also need to have a working Java environment for BOE development. Admittedly configuring that environment can be a difficult task and documentation is sparse. Although not technically for the 3.X environment, you can reference section 5.1.2 of the SAP BusinessObjects Web Intelligence Report Engine Java SDK developer guide for instructions on how to configure your environment.

Pulling the Information

Once you have your development environment configured, you’ll create a JSP and use all of the following code. I’ll walk you through this line by line for the time being, but you may copy / paste the code in its entirety at the end of this document.

Start off your JSP by declaring the contentType and importing all of the packages required for this analysis:

<%@ page contentType="text/html; charset=utf-8" %> 
<%@ page import = "com.crystaldecisions.sdk.framework.*" %> 
<%@ page import = "com.crystaldecisions.sdk.occa.infostore.*" %> 
<%@ page import = "com.businessobjects.rebean.wi.*" %> 
<%@ page import = "java.util.*" %>

With that out of the way, go ahead and begin the main code block with your logon credentials. Note that putting your password in plain text is not recommend practice if you plan on actually deploying or sharing this code. The purpose of this post is to just get you up and running as soon as possible. If you are looking to deploy a real application, you should separate the logon process into another JSP. While slightly dated, the Java Report Engine SDK Samples post gives plenty of examples showing you how to implement this.

<% 
    // Login information used to establish session 
    String username = "USER_NAME"; 
    String password = "PASSWORD"; 
    String cmsName  = "YOUR_CMS:6400" ; 
    String authType = "secEnterprise";   

Next we’ll also want to declare some variables that we will use before getting to the meat of the code:

    // Variable declarations 
    ReportEngine        boReportEngine      = null; 
    DocumentInstance    boDocumentInstance  = null; 
    IEnterpriseSession    boEnterpriseSession = null;
    String resultString = "";

With everything imported, credentials identified and variables declared we are ready to start the heavy lifting. We’ll put all of this in a try…catch…finally block to make sure that we properly handle any type of errors.

Starting off at the top of the try block, we are first going to use the credentials we provided above to login to the system and create a session. Once we have a session established, we’ll create an InfoStore object which is responsible for interacting with the components of your Business Objects environment.

 try {     
        // Logon to the enterprise 
        boEnterpriseSession = CrystalEnterprise.getSessionMgr().logon( username, password, cmsName, authType); 
           
        // Retrieve the IInfoStore object 
        IInfoStore boInfoStore = (IInfoStore) boEnterpriseSession.getService("InfoStore");   

To actually make use of the InfoStore, we will need to query the repository using the special BOE query syntax. If you are unfamiliar with how to build those queries, I would advise checking out this post on BusinessObjects Query build – Basics. In the below code, I’m simply querying for objects by their CUID, but you can modify this to match your requirement. After establishing the query, we’ll use the InfoStore to execute and retrieve the results in a collection of InfoObjects:

        // Build query to retrieve the appropriate InfoObjects. Note that the this sample code uses the Webi Report 
        // Engine so providing CUIDs for other object types will throw exceptions during execution, but you 
        // could still support them through modifying this sample to use more than just the Webi Report Engine 
        String query = "SELECT SI_CUID FROM CI_INFOOBJECTS WHERE SI_CUID IN ('A_CUID_1', 'A_CUID_2')"; 
         
        // Execute the query 
        IInfoObjects boInfoObjects = (IInfoObjects) boInfoStore.query(query); 

Now that we have the InfoObjects, we need to create a report engine that will allow us to open and interact with that collection. For the purpose of simplicity in this demo, we will assume that all of the InfoObjects you have queried for are Web Intelligence documents and thus we will use the Web Intelligence report engine. If you’d like to support other document types (like say FullClient / Deski) you could modify this code to use a different engine(s).

        // Instantiate a Web Intelligence report engine. If you want to support other object types, you will 
        // need to modify this 
        ReportEngines boReportEngines = (ReportEngines) boEnterpriseSession.getService("ReportEngines"); 
        boReportEngine = boReportEngines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE); 
         

Our end goal is to print out a table showing each report, the type of expression being retrieved and the name of the expression. That said, start by appending the appropriate HTML to start the table and define your column headers to the resultString variable we created before:

        // Start an HTML table element with column headers 
        resultString += "<table><tr><th>Report CUID</th><th>Qualification</th><th>Expression</th></tr>"; 

Next we are going to loop over all of the InfoObjects we retrieved earlier, open them up to view the “dictionary,” and from that pull out the expressions contained within. We’ll then loop over the expressions, and for each one create a table row that gives us the report name, the type of expression (dimension / measure) and the name of the expression. Once we’ve gotten what we need out of a given document, we’ll close it out and move on to the next one, repeating that process until all information has been accounted for:

        // IInfoObjects interface subclasses java.util.Collection, so we can use an iterator instead of 
        // constructing a for loop  
        Iterator<?> report_it = boInfoObjects.iterator(); 
        while (report_it.hasNext()) 
        { 
            IInfoObject report = (IInfoObject) report_it.next(); 
             
            // Create opendoc params to skip the refresh on open; Not required, but may drastically improve performance 
            OpenDocumentParameters odpm = new OpenDocumentParameters(); 
            odpm.setIgnoreRefreshOnOpen(true); 
            boDocumentInstance = boReportEngine.openDocument( report.getID(), odpm ); 
             
            // With the document open, get the dictionary containing all of the expressions 
            ReportDictionary reportDict = boDocumentInstance.getDictionary(); 
             
            // Fetch the report CUID; We'll write this to the output soon... 
            String reportCUID = report.getCUID(); 
            for (int i=0; i < reportDict.getChildCount(); i++) { 
             
                ReportExpression rexpr = reportDict.getChildAt(i); 
                 
                // Append a unique HTML table row entry for each report expression 
                resultString += "<tr><td>" + reportCUID + "</td>" +  
                                "<td>" + rexpr.getQualification().toString() + "</td>" +  
                                "<td>" + rexpr.getName() + "</td></tr>"; 
           } 
            
           // We've gotten all the info we need, so close the doc before moving to the next 
           boDocumentInstance.closeDocument(); 
        } 

At this point all of the heavy lifting is done. The below code provides the closing brace for the try block, gives you a catch block that you could fill in with any type of error handling, and provides a finally block to make sure that any open document, report engine, and session gets closed out:

   } 
    catch (Exception e) 
    { 
        // Put any type of error handling here 
    } 
      
    finally {   
        // Always close any open documents, report engines and sessions 
        if( boDocumentInstance != null ) { 
               boDocumentInstance.closeDocument(); 
               boDocumentInstance = null; 
        } 
           if( boReportEngine != null ) { 
             boReportEngine.close(); 
            boReportEngine = null; 
        } 
          if( boEnterpriseSession != null ) { 
               boEnterpriseSession.logoff(); 
               boEnterpriseSession = null; 
        } 
    } 

With all of that done, the last piece provides the closing table tag needed by the resultString variable before printing it out:

    // Close out HTML table tag and print out result 
    resultString += "</table>"; 
    out.println(resultString); 
%>

At this point you should have a completely working JSP. Run the JSP and visit it on your server. You should get a result that looks something as follows:

From there you can copy/paste the results into a spreadsheet editor and enjoy for further analysis.

Suggested Further Reading

If you would like to get a better understanding of the objects at play, I would strongly advise reviewing the BOE and Report Engine APIs. Be sure to look for the version applicable to your environment. If you are using 3.1, you can access these via the below links:

• SAP BusinessObjects Report Engine Java API 3.1
• BusinessObjects Enterprise Java SDK 3.1

Complete Code:

<%@ page contentType="text/html; charset=utf-8" %> 
<%@ page import = "com.crystaldecisions.sdk.framework.*" %> 
<%@ page import = "com.crystaldecisions.sdk.occa.infostore.*" %> 
<%@ page import = "com.businessobjects.rebean.wi.*" %> 
<%@ page import = "java.util.*" %> 
  
<% 
    
    // Login information used to establish session 
    String username = "USER_NAME"; 
    String password = "PASSWORD"; 
    String cmsName  = "YOUR_CMS:6400" ; 
    String authType = "secEnterprise";  

    // Variable declarations 
    ReportEngine        boReportEngine      = null; 
    DocumentInstance    boDocumentInstance  = null; 
    IEnterpriseSession    boEnterpriseSession = null;    
    String resultString = ""; 
         
    try {     
        // Logon to the enterprise 
        boEnterpriseSession = CrystalEnterprise.getSessionMgr().logon( username, password, cmsName, authType); 
           
        // Retrieve the IInfoStore object 
        IInfoStore boInfoStore = (IInfoStore) boEnterpriseSession.getService("InfoStore");   
         
        // Build query to retrieve the appropriate InfoObjects. Note that the this sample code uses the Webi Report 
        // Engine so providing CUIDs for other object types will throw exceptions during execution, but you 
        // could still support them through modifying this sample to use more than just the Webi Report Engine 
        String query = "SELECT SI_CUID FROM CI_INFOOBJECTS WHERE SI_CUID IN ('A_CUID_1', 'A_CUID_2')"; 
         
        // Execute the query 
        IInfoObjects boInfoObjects = (IInfoObjects) boInfoStore.query(query); 
     
        // Instantiate a Web Intelligence report engine. If you want to support other object types, you will 
        // need to modify this 
        ReportEngines boReportEngines = (ReportEngines) boEnterpriseSession.getService("ReportEngines"); 
        boReportEngine = boReportEngines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE); 
         
        // Start an HTML table element with column headers 
        resultString += "<table><tr><th>Report CUID</th><th>Qualification</th><th>Expression</th></tr>"; 
         
        // IInfoObjects interface subclasses java.util.Collection, so we can use an iterator instead of 
        // constructing a for loop  
        Iterator<?> report_it = boInfoObjects.iterator(); 
        while (report_it.hasNext()) 
        { 
            IInfoObject report = (IInfoObject) report_it.next(); 
             
            // Create opendoc params to skip the refresh on open; Not required, but may drastically improve performance 
            OpenDocumentParameters odpm = new OpenDocumentParameters(); 
            odpm.setIgnoreRefreshOnOpen(true); 
            boDocumentInstance = boReportEngine.openDocument( report.getID(), odpm ); 
             
            // With the document open, get the dictionary containing all of the expressions 
            ReportDictionary reportDict = boDocumentInstance.getDictionary(); 
             
            // Fetch the report CUID; We'll write this to the output soon... 
            String reportCUID = report.getCUID(); 
            for (int i=0; i < reportDict.getChildCount(); i++) { 
             
                ReportExpression rexpr = reportDict.getChildAt(i); 
                 
                // Append a unique HTML table row entry for each report expression 
                resultString += "<tr><td>" + reportCUID + "</td>" +  
                                "<td>" + rexpr.getQualification().toString() + "</td>" +  
                                "<td>" + rexpr.getName() + "</td></tr>"; 
           } 
            
           // We've gotten all the info we need, so close the doc before moving to the next 
           boDocumentInstance.closeDocument(); 
        } 
    } 
    catch (Exception e) 
    { 
        // Put any type of error handling here 
    } 
      
    finally {   
        // Always close any open documents, report engines and sessions 
        if( boDocumentInstance != null ) { 
               boDocumentInstance.closeDocument(); 
               boDocumentInstance = null; 
        } 
           if( boReportEngine != null ) { 
             boReportEngine.close(); 
            boReportEngine = null; 
        } 
          if( boEnterpriseSession != null ) { 
               boEnterpriseSession.logoff(); 
               boEnterpriseSession = null; 
        } 
    } 
    // Close out HTML table tag and print out result 
    resultString += "</table>"; 
    out.println(resultString); 
%>