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”);
}
%>
|
