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!

Which reports use this table? 1

Status
Not open for further replies.
wiplash

wiplash

MIS
Jun 29, 2004
99
US
We're using BOXI and have developed reports using Crystal Reports 10 and 11.

I have a table name and need to know which reports use that particular table.

Is this possible?

Thanks
 
Sort by date Sort by votes
Here are 2 scripts that will
1:retreive all reports in your system ( or you can specify a folder if desired) and, by clicking on the report id,
2:call the second page that will show all tables and database information for that report and any subreports it contains..( It is pretty as well)

Main Page:
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>
<Script Language="JavaScript">
	function GetReport(Rid){
		 if (Rid > "") {
     location = "/CeViewer/view_tablesDB.asp?ReportID=" + Rid;
  }
}
</Script>
</HEAD>


<BODY>


<%

Const APS = "<YOURCMS>"

Const UserID = "<Username>"

Const Password= "<password>"

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 MakeWebPage(Reports)
   Dim pRpt
   Dim pRpts
   Dim MyStore  
   Response.Write("<HTML>")
   Response.Write("<BODY")
   Response.Write("<FORM Name='main'>")
   Response.Write("<TABLE>")
   Response.Write("<TH class=tableHeading>Reports</TH>")
   Response.Write("<TR class=tableHeadingGreen>")
   Response.Write("<TD>ID</TD><TD>Title</TD><TD>Description</TD></TR><TR>")
    Set MyStore = Session("IStore") 
    Response.Write("</FORM>")
    Response.Write("<TD>There are " & Reports.Count & " Reports </TD></TR><TR>")
     for each pRpt in Reports
     Response.Write("<TD> <A href='" & "JavaScript:GetReport(" & pRpt.ID & ")' >" & pRpt.ID & "</a></TD><TD>" & pRpt.Title & "</TD><TD>" & pRpt.Description & "</TD></TR><TR>")
        Next  
  Response.Write("</TR></TABLE></BODY></HTML>")
  End Function



Sub Main
   Logon IStore
   Set Result = IStore.Query("Select SI_ID,SI_NAME,SI_DESCRIPTION From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and SI_INSTANCE = 0 order by SI_NAME" )  
   Dim IStore
   Dim Result
   MakeWebPage(Result)
  
  End Sub

Main
%>
</BODY>
</HTML>

It calls this ( view_tablesDB.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 = "<YOURCMS>"

Const UserID = "<Username>"

Const Password= "<password>"

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>

By using some variation of these queries and display steps you should be able to check for particular tables..

Hope it helps...


[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
123
hilfy
hilfy
allyne
  • Locked
  • Question
RE: Sub Reports with parameters 1
Replies
3
Views
124
allyne
allyne
n2nuk
  • Locked
  • Question
Migrating reports from CR server 2013 to CR Server 2016
Replies
6
Views
219
n2nuk
n2nuk
hwolfgram
  • Locked
  • Question
Crystal Reports
Replies
2
Views
83
PeterDimitrov
PeterDimitrov
kdavis6416
  • Locked
  • Question
Find email-addressess with CSP Query Builder and other detail information about reports
Replies
0
Views
56
kdavis6416
kdavis6416

Part and Inventory Search

Sponsor

Back
Top