Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Where database for Business Objects XI Enterprise?

Status
Not open for further replies.
tmunson99

tmunson99

MIS
Jun 18, 2004
62
US
I'm fairly new using Business Objects having come from SQL Reporting Services. The system I'm using was already installed prior to me using it. Where is the database for Business Objects XI Enterprise stored? I'd like to create a query that will list all the reports on the server as well as user and security group access. SQL Reporting Services creates a SQL database with this data. I can't seem to find anythign on this for Crystal other than it's hard to get to.
 
Sort by date Sort by votes
Hi,
Where it is depends on what you chose at install time ( SqlServer, MSDE, etc)...

Accessing data stored in it is not very intuitive as it is stored in a highly unusual format..

Use the Query Builder ( accessed form the Admin Launchpad)
to list all Reports and much info about them, but security data will need a custom set of queries...

I have developed some code to do that ( it uses 2 asp pages so you can select what folder's rights you want to see and it will handle folders with sub-folders as well..
- It is limited to folder level to prevent it from becoming a huge query result set if you have lots of reports/users/groups)


DisplayFolders.asp
Code: 
 <%@ Language=VBScript %>
<HTML>
<HEAD>
<title>BOE XI Reports MetaData Page</title>
</HEAD>
<BODY>
<%
Const APS = <YourCMS>
Const UserID = "<Userwith Sufficient rights>"
Const Password= "<userspassword>"
Const Aut = "secEnterprise"
Function Logon(ByRef IStore)
    Dim SessionManager 
    Dim Result
    Result = FALSE
    Set SessionManager = Server.CreateObject("CrystalEnterprise.SessionMgr")
    If Err.Number = 0 then
        Dim Sess
        Set Sess = SessionManager.Logon(UserID, Password, APS, Aut)
        If Err.Number = 0 then 
          Set IStore = Sess.Service ("", "InfoStore")
          Set Session("IStore") = IStore
          Result = TRUE
        End If
    end if
   Logon = Result
End Function

Function ViewFolders (Report) 
        Response.Write("<TR><TD>")
        Response.Write("<input type='radio' name='WhatType' value='F'> Folder Has Subfolders<br>")
        Response.Write("<input type='radio' name='WhatType' value='R'> No Subfolders, just Reports<br>")
        Response.Write("</TD><TD>")
        Response.Write("<SELECT class='dropdown' NAME='FolderID' Size=25 >")
        For indx = 1 to Report.Count 
	      	Response.Write("<OPTION VALUE='" & Report.Item(indx).ID &"'>" & Report.Item(indx).Title & "</OPTION>")	
	      Next        
	Response.Write("		</SELECT>")
	 
    
  
End Function

Sub Main
   Dim Indx
   Dim Indx2
   Dim IStore
   Dim Result1
   Dim Result2
    ' Set up the InfoStore Object needed for all Queries aginst CI_INFOOBJECTS   
   Logon IStore
 ' Create a Table to organize the display of the data in a readable format
    Response.Write("<FORM NAME='Test' action='ShowRightsWithForm.asp' method='post'>")
    Response.Write("<TABLE >")
   Set Result1 = IStore.Query("Select SI_ID, SI_NAME ,SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Folder' And SI_INSTANCE=0 AND SI_PARENT_FOLDER = 0 order by SI_NAME" )     
      ViewFolders(Result1) 
   'Response.Write("Folder # 1 is " & Result1.Item(1).ID )
      Response.Write("<BR>")
 Response.Write("</TABLE >")
 Response.Write("<input type='submit' value='Run Test '>")
 Response.Write("</FORM >")
  End Sub

Main

%>
</BODY>
</HTML>

It calls this:( you can shorten it by eliminating the Style components)

ShowRightswithForm.asp
Code: 
 <%@ Language=VBScript %>
<HTML>
<HEAD>
<title>BOE XI Reports MetaData Page</title>
<link rel="stylesheet" type="text/css" href="rsc/style/template_style.css"/>
<style type="text/css">
body {
	font-family:Verdana, Arial, Helvetica, sans-serif;
	font-size:12px;
	color:#5F5F5F;
	text-decoration:none;
	scrollbar-arrow-color:#003366;
	scrollbar-base-color:#C0C0C0;
	scrollbar-darkshadow-color:#6699cc;
	scrollbar-face-color:#6699cc;
	scrollbar-highlight-color:;
	scrollbar-shadow-color:;
}

table {
	font-family:Verdana, Arial, Helvetica, sans-serif;
	font-size:12px;
	color:#5F5F5F;
	text-decoration:none;
}

td {
	vertical-align:top;
    }
    
.dropdown {
	font-size:11px;
    }
  
.marginTable {
	margin-left: 10px;
	margin-right: 10px;
}

.heading {
	font-size:18px;
	font-weight:700;
	text-align:center;
	color:#003366;
}

.leftnav {
	font-family:arial,helvetica,sans-serif;
	font-weight:bold;
	font-size:12px;
	color:white;
	text-decoration:none;
}

a:hover.leftnav {
	text-decoration:underline;        
}

.leftnavHeading {
	font-family:arial,helvetica,sans-serif;
	font-weight:bold;
	font-size:12px;
	text-align:center;
	background-color:#99ccff;
	color:green;
	text-decoration:none;
}

a:hover.leftnavHeading {
	text-decoration:underline;        
}

.leftnavHeadingLeftAlign {
	font-family:arial,helvetica,sans-serif;
	font-weight:bold;
	font-size:12px;
	text-align:Left;
	/*background-color:#99ccff;  */
	color:#003366;
	text-decoration:none;
}


.tableHeading {
	font-weight:700;
	color:#003366;
	font-size:14px;
}
.tableHeadingGreen {
	font-weight:700;
	color:green;
	font-size:14px;
}

.tableHeadingLeft {
	font-weight:700;
	text-align:left;
}


.tableHeadingLink {
	font-weight:700;
	color:#5F5F5F;
	text-align:center;
	text-decoration:none;        
}

a:hover.tableHeadingLink {
	font-weight:700;
	color:#5F5F5F;
	text-align:center;
	text-decoration:underline;        
}

.tableLink {
	font-weight:200;
	font-size:14px;
	color:#003366;
	text-align:left;
	text-decoration:none;        
}

a:hover.tableLink {
	font-weight:200;
	color:#6699cc;
	text-align:left;
	text-decoration:underline;        
}

.navigationPage {
	font-size:14px;
	font-weight:700;
	text-align:left;
	color:#003366;
	text-decoration:none;
}

a:hover.navigationPage {
	font-size:14px;
	font-weight:700;
	text-align:left;
	color:#6699cc;
	text-decoration:underline;
}

.emailLink {
	font-weight:200;
	color:#5F5F5F;
	text-align:left;
	text-decoration:underline;        
}

a:hover.emailLink {
	font-weight:200;
	color:#333333;
	text-align:left;
	text-decoration:underline;        
}
</style>
</HEAD>
<BODY>
<%
Const APS = <YourCMS>
Const UserID = "<Userwith Sufficient rights>"
Const Password= "<userspassword>"
Const Aut = "secEnterprise"
Function Logon(ByRef IStore)
    Dim SessionManager 
    Dim Result
    Result = FALSE
    Set SessionManager = Server.CreateObject("CrystalEnterprise.SessionMgr")
    If Err.Number = 0 then
        Dim Sess
        Set Sess = SessionManager.Logon(UserID, Password, APS, Aut)
        If Err.Number = 0 then 
          Set IStore = Sess.Service ("", "InfoStore")
          Set Session("IStore") = IStore
          Result = TRUE
        End If
    end if
   Logon = Result
End Function

Function ViewReportRoles (ReportID)
    ' Get the Report object for which to view the security roles, 
    ' display the report name, and get the Report plugin interface.
     Set MyStore = Session("IStore") 
     Set Reports = MyStore.Query("Select top 1 * From CI_INFOOBJECTS Where SI_ID='"& ReportID & "'")
     Set Report = Reports.Item (1)
     Response.Write("<TH class=tableHeading> The Following Users have permissions  </TH>")
    ' Get the principals for the Report object.
    Set RptSecurityInfo = Report.SecurityInfo
    Set RptSecurityPrincipals = RptSecurityInfo.ObjectPrincipals
    ' Loop through all of the report's security principals and 
    ' display the name and role of each principal.
    ceRoleAdvanced = 0
    ceRoleNoAccess = 1
    ceRoleView = 2
    ceRoleSchedule = 3
    ceRoleViewOnDemand = 4
    ceRoleFullControl = 5  
    For SecLoop = 1 to RptSecurityPrincipals.Count
        Set SecPrincipal = RptSecurityPrincipals.Item (SecLoop)
        Role = SecPrincipal.Role       
        Select Case Role
            case ceRoleAdvanced 
                RoleDescription = "Advanced" 
            case ceRoleNoAccess : 
                RoleDescription = "No Access" 
            case ceRoleView 
                RoleDescription = "View"
            case ceRoleSchedule 
                RoleDescription = "Schedule" 
            case ceRoleViewOnDemand 
                RoleDescription = "View on demand" 
            case ceRoleFullControl 
                RoleDescription = "Full Control" 
        End Select
        Response.Write "<TR><TD>" & SecPrincipal.Name &" - " & RoleDescription & "</TD></TR>"
    Next
  
End Function

Sub Main
   Dim Indx
   Dim Indx2
   Dim IStore
   Dim Result1
   Dim Result2
   Dim tyFlg
   Dim IdIn
   tyFlg = Request.Form("WhatType")
   IdIn = Request.Form("FolderID")
  ' Set up the InfoStore Object needed for all Queries aginst CI_INFOOBJECTS   
   Logon IStore
 ' Create a Table to organize the display of the data in a readable format
    Response.Write("<TABLE >")
  'See if a Parent Folder is wanted of just 1 folder with its reports
   If tyFlg = "F" then
  'If Folder then this section will cycle through all sub-folders of that parent and create
  'output for all the reports in each sub-folder ( NOTE - Can run for a very long time with large #s of reports)
   Set Result1 = IStore.Query("Select SI_ID, SI_NAME , SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Folder' And SI_INSTANCE=0 AND SI_PARENT_FOLDER='" & IdIn & "' order by SI_NAME" )     
  For indx2 = 1 to Result1.Count
  Response.Write("<tr><td style=color=red><u><h4> Folder Name is " & Result1.Item(indx2).Title & "</h4></u></td></tr>")
  Set Result2 = IStore.Query("Select SI_ID, SI_NAME , SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' And SI_INSTANCE=0 AND SI_PARENT_FOLDER='" &  Result1.Item(indx2).ID & "' order by SI_NAME" )     
     For indx = 1 to Result2.Count
     Response.Write("<tr><td style=color=blue> Report #  " & indx & " is " &   Result2.Item(indx).Title & "</TD></TR>")
      ViewReportRoles(Result2.Item(indx).ID)
     next
     Response.Write("<BR>")
 next
' If just 1 Folder's Reports are requested, this section will handle that...( NOTE: Many Reports = slow return )
elseif tyFlg = "R" then
	Response.Write("<tr><td style=color=red><u><h4> Report Information:</h4></u></td></tr>")
  Set Result2 = IStore.Query("Select SI_ID, SI_NAME , SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' And SI_INSTANCE=0 AND SI_PARENT_FOLDER='" &  IdIn & "' order by SI_NAME" )     
     For indx = 1 to Result2.Count
     Response.Write("<tr><td style=color=blue> Report #  " & indx & " is " &   Result2.Item(indx).Title & "</TD></TR>")
      ViewReportRoles(Result2.Item(indx).ID)
     next
     Response.Write("<BR>")
 else
 	Response.Write("Bad, Bad result")
 end if
  End Sub

Main

%>
</BODY>
</HTML>


The queries and how they are constructed should also show you how InfoStore based queries are set up..

Hope it helps..


[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
Thank you for the post and the functions. I'd like to start with the reports list. Where is the Admin Launchpad? I have CMC and CCM. I don't see anywhere to build a query.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

jchewsmith
  • Locked
  • Question
Delivery Rules for Publications
Replies
0
Views
142
jchewsmith
jchewsmith
BeeBear2
  • Locked
  • Question
whats the main differences between 2011 and 2015 Crystal Enterprise
Replies
1
Views
124
hilfy
hilfy
zigstick
  • Locked
  • Question
BOXI Enterprise / Link to a file in a folder 1
Replies
2
Views
136
zigstick
zigstick
zigstick
  • Locked
  • Question
Linked OLE word doc not refreshing when run in Enterprise. 1
Replies
5
Views
166
zigstick
zigstick
Creamac
  • Locked
  • Question
search Enterprise based on scheduled dates
Replies
5
Views
113
Creamac
Creamac

Part and Inventory Search

Sponsor

Back
Top