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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Report to list all reports on Crystal Enterprise

Status
Not open for further replies.
JonHeath

JonHeath

Technical User
Feb 23, 2004
14
GB
Hi all,

I would like to write a report to simply list all the reports we have uploaded to Crystal Enterprise. (Version 10 service pack 3).

However I cannot seem to find the table that this information is stored in. I have recently turned auditing on, but this only generates records when a report is viewed or a new one is created.

Can anyone please help me find a table that just has all the report titles in, or possibly another way to do this? Many thanks.
 
Sort by date Sort by votes
Go to your Query Builder and enter this query:

SELECT SI_DESCRIPTION FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' AND SI_INSTANCE = 'FALSE'

It's not the best output, but it's about all you can get without building a csp page that reads the *objects* in the database. You cannot use standard SQL to read the info in the APS/CMS database.

(This query works with v9. I think it should work for v10.)
 
Upvote 0 Downvote
Depending on your version of CE-10 you might also be able to turn on the AUDITING tools and run a report against the Auditing tools.
 
Upvote 0 Downvote
For CE9, I used a csp page that outputs to a table detailing the report name, report path, how many instances, and the average runtime. I believe CE10 will use similar syntax. The code could be altered to ouput in XML format which a report could be created against.

<HTML>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=charset=UTF-8">
<BODY>
<%
Dim SessionManager
Set SessionManager=Server.CreateObject("CrystalEnterprise.SessionMgr")
Dim Sess
Set Sess = SessionManager.Logon(login, password,CEserver, "secEnterprise")
Dim IStore
Set IStore = Sess.Service ("", "InfoStore")
Dim rundate
Dim rundate2
rundate = DateAdd("d",-1,Date)
rundate2 = DatePart("yyyy",rundate) & "." & DatePart("m",rundate) & "." & DatePart("d",rundate)
Dim holdprop


'Next we create a query

Dim Rs: Set rs = IStore.Query("SELECT SI_ID, SI_NAME, SI_UPDATE_TS, SI_CREATION_TIME FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' " &_
"AND SI_INSTANCE = 0")

HTMLTable="<table border=0 width = ""100%""><TR><TD><B>ID</B></TD><TD><B>Name</B></TD>" & _
"<TD><B>Last Run TS</B></TD><TD><B>Last Update TS </B></TD>" &_
"<TD><B>Path</B></TD><TD><B>Instances</B></TD> " & _
"<TD><B>Recurring Instances</B></TD> " & _
"<TD><B>Avg Runtime</B></TD></TR>"

'and then loop thru the result set of Rs
For Each rsRow in Rs

'Add the instance name and details to the table.
'Ensure that each name is a link to the viewer.

HTMLTable=HTMLTable & _
"<TR valign=top><TD>" & rsRow.Properties("SI_ID") & "</A></TD>" & _
"<TD>" & rsRow.Properties("SI_NAME") & "</A></TD>" & _
"<TD>" & rsRow.Properties("SI_UPDATE_TS") & "</A></TD>" & _
"<TD>" & rsRow.Properties("SI_CREATION_TIME") & "</TD>"

'**************************************************************************
'Get the path
'**************************************************************************
Dim path
path = ""
Dim parentfolderid
parentfolderid = rsrow.ParentID

Dim tempobj
Set tempobj = iStore.Query("SELECT SI_ID, SI_NAME, SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_ID=" & parentFolderID)
parentfolderid = tempObj.Item(1).ParentID
path = "/" + tempObj.Item(1).Title + path

Do While parentFolderID <> 0
Set tempObj = iStore.Query("SELECT SI_PATH, SI_NAME FROM CI_INFOOBJECTS WHERE SI_ID=" & parentFolderID)
path = "/" + tempObj.Item(1).Title + path
parentFolderId = tempObj.Item(1).parentid
Loop

path = "Home" & path
HTMLTable = HTMLTable & "<TD>" & Server.HTMLEncode(path) & "</TD>"

'**************************************************************************
'Get a count and avg runtime of the instances
'**************************************************************************

Set tempobj = iStore.Query("SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID = " & rsRow.Properties("SI_ID") & _
" AND SI_INSTANCE = 1 AND SI_RUNNABLE_OBJECT = 0" )
Dim counter
Dim runtimetotal
Dim runtime
Dim rsRow2
counter = 0
runtime = 0
runtimetotal = 0

For each rsRow2 in tempobj
counter = counter + 1
runtimetotal = runtimetotal + (rsRow2.Properties("SI_ENDTIME") - rsRow2.Properties("SI_STARTTIME"))
Next
HTMLTable = HTMLTable & "<TD>" & counter & "</TD>"

'Convert total runtime to minutes
runtimetotal = runtimetotal *60 *24

'Calculate runtime
If counter <> 0 Then
runtime = runtimetotal/counter
End If

'**************************************************************************
'Get a count of the recurring instances
'**************************************************************************

Set tempobj = iStore.Query("SELECT * FROM CI_INFOOBJECTS WHERE SI_PARENTID = " & rsRow.Properties("SI_ID") & _
" AND SI_INSTANCE = 1 AND SI_RECURRING = 1" )
counter = 0
For each rsRow2 in tempobj
counter = counter + 1
Next
HTMLTable = HTMLTable & "<TD>" & counter & "</TD>"
HTMLTable = HTMLTable & "<TD>" & runtime & "</TD></TR>"


Next

HTMLTable=HTMLTable&"</table>"

Response.Write HTMLTable

%>
</BODY>
</HTML>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

hpl2001
  • Locked
  • Question
BI4/Crystal 2016 upgrade issue with scheduled reports
Replies
1
Views
122
hilfy
hilfy
lavadan
  • Locked
  • Question
Export to excel with drop down data
Replies
2
Views
131
hilfy
hilfy
smmaruf82
  • Locked
  • Question
How to Fixed 4 Column in Crystal Report Crosstab
Replies
1
Views
133
lbass
lbass
allyne
  • Locked
  • Question
Hello, I have a main report and a
Replies
1
Views
116
kray4660
kray4660
allyne
  • Locked
  • Question
RE: Sub Reports with parameters 1
Replies
3
Views
123
allyne
allyne

Part and Inventory Search

Sponsor

Back
Top