Query to return all objects and last run-times from e-portfolio 8.5

KING901 · Mar 24, 2005

Unfortunatly still on e-portfolio 8.5, but we are looking to upgrade to XI however we have lost control of the reports currently and are trying to get a list of reports, with there locations, total instances and last run-time, I am not fully familier with the objects but have a rough understanding.

Has anyone done this, if so have you got some code that I could look at using???

Regards

jrennatc · May 4, 2005

I use the code below as a standalone csp page on the production server, it returns a grid of all reports when they were created and the last time they were run. I'm using it on CE 9, but you should be able to modify it for CE 8.5.

<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(username, password, APS_Name, "secEnterprise")
Dim IStore
Set IStore = Sess.Service ("", "InfoStore")
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>ID</TD><TD>Name</TD>" & _
"<TD>Last Run TS</TD><TD>Last Update TS </TD>" &_
"<TD>Path</TD><TD>Instances</TD> " & _
"<TD>Recurring Instances</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 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 rsRow2
counter = 0
For each rsRow2 in tempobj
counter = counter + 1
Next
HTMLTable = HTMLTable & "<TD>" & counter & "</TD>"

'**************************************************************************
'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></TR>"

Next

HTMLTable=HTMLTable&"</table>"

Response.Write HTMLTable

%>
</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 to return all objects and last run-times from e-portfolio 8.5

KING901

Programmer

jrennatc

Programmer

Similar threads

Part and Inventory Search

Sponsor