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!

A report of reports?

Status
Not open for further replies.
SueSp

SueSp

MIS
Jul 26, 2004
39
US
Is there such a thing as a report of all reports and their associated databases and fields being used?

I would like to somehow view all my .rpt's and see what databases they are using as well as what fields.

Thanks.
 
Sort by date Sort by votes
Or you can 'roll your own' like this
I call it view_tables.asp:
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;
    }
    
.dropdown {
	font-size:11px;
    }
  
.marginTable {
	margin-left: 10px;
	margin-right: 10px;
}

.heading {
	font-size:18px;
	font-weight:700;
	text-align:center;
	color:#003366;
}

.leftnav {
	font-family:arial,helvetica,sans-serif;
	font-weight:bold;
	font-size:12px;
	color:white;
	text-decoration:none;
}

a:hover.leftnav {
	text-decoration:underline;        
}

.leftnavHeading {
	font-family:arial,helvetica,sans-serif;
	font-weight:bold;
	font-size:12px;
	text-align:center;
	background-color:#99ccff;
	color:#003366;
	text-decoration:none;
}

a:hover.leftnavHeading {
	text-decoration:underline;        
}

.leftnavHeadingLeftAlign {
	font-family:arial,helvetica,sans-serif;
	font-weight:bold;
	font-size:12px;
	text-align:Left;
	/*background-color:#99ccff;  */
	color:#003366;
	text-decoration:none;
}


.tableHeading {
	font-weight:700;
	color:#003366;
	font-size:14px;
}
.tableHeadingGreen {
	font-weight:700;
	color:green;
	font-size:14px;
}

.tableHeadingLeft {
	font-weight:700;
	text-align:left;
}


.tableHeadingLink {
	font-weight:700;
	color:#5F5F5F;
	text-align:center;
	text-decoration:none;        
}

a:hover.tableHeadingLink {
	font-weight:700;
	color:#5F5F5F;
	text-align:center;
	text-decoration:underline;        
}

.tableLink {
	font-weight:200;
	font-size:14px;
	color:#003366;
	text-align:left;
	text-decoration:none;        
}

a:hover.tableLink {
	font-weight:200;
	color:#6699cc;
	text-align:left;
	text-decoration:underline;        
}

.navigationPage {
	font-size:14px;
	font-weight:700;
	text-align:left;
	color:#003366;
	text-decoration:none;
}

a:hover.navigationPage {
	font-size:14px;
	font-weight:700;
	text-align:left;
	color:#6699cc;
	text-decoration:underline;
}

.emailLink {
	font-weight:200;
	color:#5F5F5F;
	text-align:left;
	text-decoration:underline;        
}

a:hover.emailLink {
	font-weight:200;
	color:#333333;
	text-align:left;
	text-decoration:underline;        
}
</style>
</HEAD>
<BODY>
<%

Const APS = [i]"<InsertYour CMS/APS name here>"[/i]

Const UserID = [i]"<Username that has at least ViewOnDemand>"[/i]

Const Password= [i]"<password>"[/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

Function OpenReport(Report, IStore)
   Dim ReportDoc
   Set ReportDoc = Server.CreateObject("CrystalClientDoc.ReportClientDocument")
   ReportDoc.EnterpriseSession = IStore.EnterpriseSession
   ReportDoc.Open Report
   Set Session("OpenReport") = ReportDoc
   Set OpenReport = ReportDoc
End Function

Function GetAvailableDatabaseTables(ReportDoc)
   Dim pTable
   Dim pTables
   Set pTables = Server.CreateObject("CrystalReports.Tables")        
     With ReportDoc.Database
        For Each pTable in .Tables
            pTables.Add pTable
        Next
     End With
    Set GetAvailableDatabaseTables = pTables
End Function


Function GetDatabaseInformation(Report)
     With Report.DatabaseController
        Dim CI,Id
        Dim CIs
        Set CIs = Report.DatabaseController.GetConnectionInfos(Nothing)
        For Each CI In CIs
            Response.Write("<FONT color=blue> UserName: " & CI.UserName & "<BR>")
                     
            For Each Id In CI.Attributes.PropertyIDs 
               
                If (Not IsObject(CI.Attributes.Item(Id))) Then
                	If (Id = "QE_DatabaseType") Then
                    Response.Write "Database Connection Type: " & CI.Attributes.Item(Id) & "<BR>"
                 End if
                if (Id = "QE_ServerDescription") Then
                      Response.Write "Database Instance: " & CI.Attributes.Item(Id) & "</FONT><BR>"
                   End If
                Else
                    'The item is another property bag that you will have to examine.
                End If
            Next
        Next
    End With

End Function

Function QuerySubreportNames(Report,rname)
     Dim Rptname
     Dim SubCount 
     RptName = rname
     
   
    Dim SubReportName,STable
    SubCount = Report.SubreportController.QuerySubreportNames.Count
     If SubCount > 0 then
   	 
     For Each SubreportName In Report.SubreportController.QuerySubreportNames
        Response.Write "<BR><FONT color=#0000CC>Subreport </FONT><u><FONT color=#9900FF>" & SubreportName & "</FONT></u> Uses:<BR>"
   
        For Each STable In Report.SubreportController.GetSubreportDatabase(SubreportName).Tables
         if STable.Name <> STable.Alias then
          Response.Write "<FONT color=green> " & STable.Name & " Aliased as " & STable.Alias & "</FONT><BR>"  
           else
         	Response.Write "<FONT color=green> " & STable.Name & "  and the alias is the same</FONT><BR>"
        end if     
        
       Next 
    
        GetDatabaseInformation(Report)        
     Next
    
    else
    		Response.Write("<b>" & RptName & "  has NO Subreports</b>")
   end if 	


End Function


Sub PrintForm(AvailableTables)


  Response.Write("<TR><TD>")

    %>
  
   <%
        Dim pTable
        For Each pTable in AvailableTables
        if pTable.Name <> pTable.Alias then
       Response.Write "<BR>Table Name is <FONT color=#33CC00> " & pTable.Name  & " </FONT>And it is aliased as:<FONT color=#0099CC> " & pTable.Alias  & "</FONT>" 
      else
      	Response.Write "<BR>Table Name is <FONT color=#33CC00>  " & pTable.Name & "</FONT> and the alias is the same"
      end if
        Next
        Response.Write("<BR>")
    %>
    
    <%

End Sub


Sub Main
   Logon IStore
   Rid = Request.QueryString("ReportID")
   Set Result = IStore.Query("Select SI_ID, SI_NAME, SI_PARENT_FOLDER From CI_INFOOBJECTS Where SI_ID = " & Rid)
   Set Report = OpenReport(Result.Item(1),IStore)
   ReportName  = Result.Item(1).Title
   Response.Write("<table border=2 width=100% align=center cellspacing=0 cellpadding=0>")
   Response.Write("<TH style='tableheading'><b>The Report named<FONT color=red> " & ReportName  & "</FONT> has these tables</TH>")
   Dim Rid,Rname
   Dim IStore, DocManager, Report, ReportName
   Dim Result, AvailableTables, FieldsOnReport,SubTables

   Set AvailableTables = GetAvailableDataBaseTables(Report)
  
   PrintForm AvailableTables
      
   GetDatabaseInformation(Report)  
  Response.Write("</TABLE>")
  Response.Write("<table border=2 width=100% align=center cellspacing=0 cellpadding=0>")
   Response.Write("<TH style='table'><b><FONT color=red>" & ReportName  & " </FONT>has these SubReports</TH>")
   Response.Write("<TR><TD style='tablelinkGreen'>")
   QuerySubreportNames Report,ReportName
    Response.Write("</TABLE>")
  End Sub

Main
%>
</BODY>
</HTML>

Pass the Report ID # to this page like this maybe:
Code: 
[URL unfurl="true"]http://yourserver/somevirtualdir/view_tables.asp?ReportID=23408[/URL]
and it will detail table/datasource usage for it ( including subreports, if any)

You can modify this to use all the reports published, but it can really tax your resources if more than 50 exist.


[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
Turkbear.

This looks very promising. I should have said though we are only using CE10 Embedded. No infor store or anything...

Do you have something similar for that?

Thanks!
 
Upvote 0 Downvote
Hi,
Nope..I do not know the capabilities ( and limitations) of that version..



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
Darn! :)

What you have there looks exactly like what I need/want.

Maybe I'll see if I can tweak it some.

Thanks a ton.

btw Turkbear, are you by chance a hobbiest woodworker?
 
Upvote 0 Downvote
Hi,
Nope, my only real hobby ( besides loafing) is Golf..




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
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
119
hilfy
hilfy
allyne
  • Locked
  • Question
Hello, I have a main report and a
Replies
1
Views
113
kray4660
kray4660
allyne
  • Locked
  • Question
RE: Sub Reports with parameters 1
Replies
3
Views
120
allyne
allyne
smmaruf82
  • Locked
  • Question
How to Fixed 4 Column in Crystal Report Crosstab
Replies
1
Views
130
lbass
lbass
n2nuk
  • Locked
  • Question
Migrating reports from CR server 2013 to CR Server 2016
Replies
6
Views
210
n2nuk
n2nuk

Part and Inventory Search

Sponsor

Back
Top