Query Builder - rights to reports

peaches1240 · Mar 20, 2006

I've been tasked with documenting our Crystal Enterprise system setup (version 10). One request in particular is a list of reports and the users who have access to each. Is it possible to use Query builder to get the information? I don't relish the thought of looking at each report.

Any help is appreciated.

Turkbear · Mar 21, 2006

Hi,
On the Administrator Launchpad page is a link to the Object Rights Table -- Take a look at what it can do..

To Paraphrase:"The Help you get is proportional to the Help you give.."

peaches1240 · Mar 21, 2006

I've looked at that before but it's cumbersome to use. I was hoping for a SQL that would give me a list.

Thanks,

peaches1240 · Mar 24, 2006

With many thanks to TurkBear in thread782-1171682 I was able to write a program that will give me a list of reports along with user security for each. I found the security information in CE COM Developers Library Guide. I've included the code here in case others are interested.

<%@ Language=VBScript %>
<% Option Explicit%>
<HTML>
<HEAD>
<title>CE 10 Reports Security List</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;
color:green
}

th {
font-weight:700;
color:blue;
text-align:center;
text-decoration:none;
}
</style>
</HEAD>
<BODY>
<%
Const APS = "your server"
Const UserID = "user with full control rights"
Const Password= "password"
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

Dim filesys, filetxt
Set filesys = CreateObject("Scripting.FileSystemObject")
Set filetxt = filesys.CreateTextFile("c:\ReportSecurity.txt", True)

Response.Write " <H3>" & _
"Report List with User Security - CE 10" & _
"</H3>"
filetxt.WriteLine "Report List with User Security - CE 10" & vbcrlf

Sub ShowReports(Rid,FolderName,IStore,FolderParent)
Dim indx, Result3, Istr, Result4, FldrPar, indx2, Report
Dim RptSecurityInfo, RptSecurityPrincipals, SecurityInfo
Dim ceRoleAdvanced, ceRoleNoAccess, ceRoleView, ceRoleSchedule
Dim ceRoleViewOnDemand, ceRoleFullControl, SecLoop, SecPrincipal
Dim Role, RoleDescription

If FolderParent <> 0 then
Set Result4 = IStore.Query("SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Folder' and SI_ID = " & FolderParent)
for indx2 = 1 to Result4.Count
FldrPar = Result4.Item(indx2).Title
Next
Response.Write" " & FolderName & " Within Folder " & FldrPar & " "
filetxt.WriteLine vbcrlf & FolderName & " Within Folder " & FldrPar
else
Response.Write(" " & FolderName & " ")
filetxt.WriteLine vbcrlf & FolderName
End If

Set Result3 = IStore.Query("SELECT SI_NAME, SI_ID, SI_DESCRIPTION " & _
"FROM CI_INFOOBJECTS " & _
"WHERE SI_PROGID='CrystalEnterprise.Report' " & _
"AND SI_INSTANCE=0 " & _
"AND SI_PARENT_FOLDER=" & Rid & _
"ORDER BY SI_NAME")

for indx = 1 to Result3.count
Set Report = Result3.Item(indx)
Response.Write " " & _
Result3.Item(indx).Title & vbTab & _
" "
filetxt.Writeline vbcrlf & Result3.Item(indx).Title

Set RptSecurityInfo = Report.SecurityInfo
Set RptSecurityPrincipals = RptSecurityInf

bjectPrincipals

' 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 SecPrincipal.Name & " - " & RoleDescription & " "
filetxt.WriteLine SecPrincipal.Name & " - " & RoleDescription
Next

Next

End Sub

Sub Main
Dim IStore,Result2,FolderName
Dim itm
Logon IStore
Set Result2 = IStore.Query("SELECT SI_ID, SI_NAME,SI_PARENT_FOLDER FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Folder' ORDER BY SI_NAME")
for itm = 1 to Result2.Count
ShowReports Result2.Item(itm).ID, Result2.Item(itm).Title,IStore,Result2.Item(itm).Properties.Item("SI_PARENT_FOLDER")
Next
End Sub

Main

filetxt.close
%>
</BODY>
</HTML>

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!

Query Builder - rights to reports

peaches1240

Technical User

Turkbear

Technical User

peaches1240

Technical User

peaches1240

Technical User

Similar threads

Part and Inventory Search

Sponsor