Skip to Content

There have been scenarios where there is a need to retrieve the information about the access levels applied on a universe for all usergroups/users and have the results in an excel file. This information can not be retrieved using the query builder and can be easily retrieved if you are using the BusinessObjects Enterprise Platform SDKs. However, the Business Objects SDKs do not give you an API call to have these kind of results exported to an excel file.

Using the features of poi jar files, it is possible to include the code for having the results in excel file.

Below is the code for exporting the access levels of universe for all usergroups in an excel file. The code has been designed to get information for the Effective principals. The version poi jars used for compiling this code is 2.5.1-final-20040804

Note:

The part of exporting the results to an excel file doesnot come under the support of BusinessObjects as you would be using third party jars with BusinessObjects jar files to have the results exported to excel.

For other scripts and information on how to run these scripts see here:

http://scn.sap.com/people/shawn.penner/blog/2013/06/04/scripts-and-samples

Universe Access Level for all UserGroups

<%@ page import=”java.util.*,
com.crystaldecisions.sdk.exception.SDKException,
com.crystaldecisions.sdk.occa.infostore.*,
com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.plugin.desktop.report.CeReportRightID,
com.crystaldecisions.sdk.plugin.desktop.user.*,
com.crystaldecisions.sdk.plugin.desktop.usergroup.*,
java.sql.*”%>
<%@ page import=”org.apache.poi.hssf.usermodel.HSSFSheet”%>
<%@ page import=”org.apache.poi.hssf.usermodel.HSSFWorkbook”%>
<%@ page import=”org.apache.poi.hssf.usermodel.HSSFCell”%>
<%@ page import=”org.apache.poi.hssf.usermodel.HSSFRow”%>
<%@ page import=”java.io.*” %>
<%@ page import=”org.apache.poi.hssf.usermodel.HSSFPrintSetup”%>

<html>
<head></head>
<body>
<table border=”2″ width=”50%” cellpadding=”0″ style=”border-collapse: collapse” bordercolor=”#111111″ width=”100%” id=”AutoNumber1″>
<tr>
<th width=”20%”>UserGroupName </th>
<th width=”20%”>Universe Name</th>
<th width=”20%”>Roles/AccessLevels</th>
</tr>

<%
    String userName = “Administrator”;//Administrator User account
    String password = “Password1”;//Administrator Password
    String cmsName =”localhost:6400″;//CMS Name
    String cmsAuthType =”secEnterprise”;

                IInfoObject usergroup;
                IInfoObject universe;
    IEffectiveRole roles=null;

               
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet(“new sheet”);
                HSSFRow rowhead = sheet.createRow((short)0);
                rowhead.createCell((short) 0).setCellValue(“UserGroup”);
                rowhead.createCell((short) 1).setCellValue(“Universe Name”);
                rowhead.createCell((short) 2).setCellValue(“Roles/AccessLevels”);
                               
                int index=1;
               
    try
    {
        IEnterpriseSession  es = CrystalEnterprise.getSessionMgr().logon( userName, password, cmsName, cmsAuthType);

                IInfoStore iStore = (IInfoStore)es.getService(“”, “InfoStore”);

                //Code to query for the user to test access rights.
               
               
         

         String query = “SELECT  SI_ID,SI_NAME FROM  ci_systemobjects WHERE  SI_kind=’usergroup’ “;
         IInfoObjects infoobjects = iStore.query(query);
        String universeQuery = “SELECT  SI_ID,SI_NAME FROM ci_appobjects WHERE SI_Kind=’universe'”;
         IInfoObjects universes = iStore.query(universeQuery);
               
          for(int i=0;i<infoobjects.size();i++)
           {
             for(int j=0;j<universes.size();j++)
              {
                usergroup = (IInfoObject) infoobjects.get(i);
                String userGroupName=usergroup.getTitle();
                                                               
                universe = (IInfoObject)universes.get(j);
                String universeName=universe.getTitle();      
                                                               
               ISecurityInfo2 universeSecInfo = universe.getSecurityInfo2();
               IEffectivePrincipals ePrincipals=universeSecInfo.getEffectivePrincipals();
  
              IEffectivePrincipal ePrincipal=ePrincipals.get(usergroup.getID());
               
              if(ePrincipal !=null)
               {
      
                IEffectiveRoles eRoles=ePrincipal.getRoles();
                if(eRoles != null)
                {
                 Iterator it=eRoles.iterator();
                                                               
                 if((!it.hasNext()))
                 {
     out.println(“<tr><td>” +userGroupName+”</td>”);
     out.println(“<td>” +universe.getTitle()+”</td>”);
                 out.println(“<td>No Access on this Universe!!!!</td></tr>”);
                 }
                 else
                 {
                                                               
                 HSSFRow row = sheet.createRow((short)index);
                 row.createCell((short) 0).setCellValue(userGroupName);
                 row.createCell((short) 1).setCellValue(universeName);
                 while(it.hasNext())
                 {
                  roles=(IEffectiveRole)it.next();
      out.println(“<tr><td>” +userGroupName+”</td>”);
     out.println(“<td>” +universe.getTitle()+”</td>”);
                  out.println(“<td>”+roles.getTitle()+”</td></tr>”);
                  out.println(“<tr><td></td><td></td>”);
                  HSSFRow row1 = sheet.createRow((short)index);
                  row1.createCell((short) 2).setCellValue(roles.getTitle());
                  index++;
                 }
                 }
                 }
                 }
                 }                        
                 }
                }
                catch(Exception e)
    {
       out.println(e);
    }
                finally
                {
                                FileOutputStream fileOut = new FileOutputStream(“c:\\UniverseAccessLevels.xls”);
                                wb.write(fileOut);
                                fileOut.close();
                                out.println(” Excel file created successfully”);
               
                }
%>

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply