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 derfloh 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
Joined
Nov 24, 2003
Messages
48
Location
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
Hi,
Not sure whay that would be the case..Try in some other virtual directory ( or even in inetpub/Then just ( if in

should do it..assuming the CMS etc are set correctly..


[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
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

D
  • Locked
  • Question Question
Crystal Report Server 8 Updates
Replies
0
Views
296
DayKoff
D
hpl2001
  • Locked
  • Question Question
BI4/Crystal 2016 upgrade issue with scheduled reports
Replies
1
Views
581
hilfy
hilfy
smmaruf82
  • Locked
  • Question Question
How to Fixed 4 Column in Crystal Report Crosstab
Replies
1
Views
620
lbass
lbass
allyne
  • Locked
  • Question Question
Hello, I have a main report and a
Replies
1
Views
603
kray4660
kray4660
DanEvansJr
  • Locked
  • Question Question
Crystal Report to Adobe pdf - font issue
Replies
1
Views
797
DanEvansJr
DanEvansJr

Part and Inventory Search

Sponsor

Back
Top