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!

Crystal Report on the Crystal Enterprise database 1

Status
Not open for further replies.
almaler

almaler

Programmer
Nov 24, 2003
48
US
Hello Everyone,

We are preparing to upgrade to Business Object XI and are in "clean-up" mode. I am needing to produce a Crystal Report that lists all reports that are published and what folder they are under. The closest thing I have found to getting the results I want is the use of the Query Builder.
I can type in SELECT * FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report') and it will give me details of each report, BUT it lists on the parent folder ID, not the name. I have not been able to find a link to any other table where the folder NAME appears. Can anyone offer any assistance? Thanks so much!
 
Sort by date Sort by votes
Hi, Try something like this:
Code: 
 <%@ Language=VBScript %>
<% Option Explicit
%>
<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;
	color:green
    }
   

th {
font-weight:700;
	color:blue;
	text-align:center;
	text-decoration:none;  
	     
	}
</style>
</HEAD>


<BODY>


<%

Const APS = [i]"<yourCMS/APS>"[/i]

Const UserID = [i]"<UserwithViewOnDemand/FullControl">[/i]

Const Password= [i]"<password for that user>"[/i]

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

Sub ShowReports(Rid,FolderName,IStore,FolderParent)
    Dim indx,Result3,Istr,Result4,FldrPar,indx2
    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("<Table><TH> Folder: " & FolderName & " Within Folder  " & FldrPar & "</TH><TR>")
    else
  	 Response.Write("<Table><TH> Folder: " & FolderName & "</TH><TR>")
    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
	   Response.Write("<TD>" & Result3.Item(indx).Title & "</TD><TR>")
	  Next
    Response.Write("</TR></TABLE>")
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

%>
</BODY>
</HTML>

This will list each Folder ( and indicate its parent, if any) and then list each report in that folder..

Here is a sample of the output from our development system:
Code: 
[COLOR=blue]
Folder: ABB Staff Reports Within Folder FinDev
[/color][COLOR=green] 
Detail by Core Activity 
Error Summary by Office 
Management Activity to Office 
Plan Delivery Detail 
Plan Delivery Detail Summary 
Salary v Non-Salary Expended vs Planned 
Salary vs Non-Salary Dollar Summary 
Salary vs Non-Salary Dollars and Percent 
Summary by Core Activity 
Unallocated Dollar Error Activity Summary 
[/color]




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
Wow - what do I do with the code that you provided me? Do I save that as a file somewhere? I don't know what to do with it. Thank you so much.
 
Upvote 0 Downvote
Hi
OH, sorry..

Create it as an .asp page ( I call mine show_reports.asp - clever eh!)..( be sure to modify it to use your CE info, like the APS/CMS)

Place it in some virtual directory on your CE server's web site..

then

should do it..



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
Thanks!! I'll give it a try and report back..
 
Upvote 0 Downvote
OK, I copied this file and put it in \\Program Files\Crystal Decisions\Enterprise 10\Web Content\Enterprise10\ePortfolio\en\show_reports.asp

Doesn't that mean that I should be able to access it via a URL like
Can anyone think of a reason why I get "Page Cannot Be Displayed"?
 
Upvote 0 Downvote
Just to make sure that I have the CMS name set correctly. When I launch the CMC and look under "Servers", I have input the value that appears under MACHINE NAME. Is that correct? Or should it be machine name.cms?
 
Upvote 0 Downvote
Hi,
Depends,
What shows as System when you launch the CMC?

That is the CMS identification to use..



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
When I launch the CMC, system is the fully qualified domain name of the server. I had just the server name. I will change it and try again.
 
Upvote 0 Downvote
That was it! It is working! Thank you!!!!
 
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
129
hilfy
hilfy
smmaruf82
  • Locked
  • Question
How to Fixed 4 Column in Crystal Report Crosstab
Replies
1
Views
140
lbass
lbass
allyne
  • Locked
  • Question
Hello, I have a main report and a
Replies
1
Views
122
kray4660
kray4660
DanEvansJr
  • Locked
  • Question
Crystal Report to Adobe pdf - font issue
Replies
1
Views
160
DanEvansJr
DanEvansJr
joshidharmesh
  • Locked
  • Question
Crystal report formula field query
Replies
3
Views
186
kray4660
kray4660

Part and Inventory Search

Sponsor

Back
Top