Need to get a list of data sources for impact analysis

eo · Nov 18, 2005

Hi

CR10, CE10, MSSQL, Oracle, etc

We have 950 reports, and 48 data sources. Every time we make a change somewhere it is a huge exercise (or more commonly professional guess work) to establish the impact analysis if a major change is made to a data source (such as the change of a data type on a SQL table.

Is there a way (even a third party tool) which will tell us which data sources (more specifically tables, views, etc) relate to which reports. The Report Datasources Administration Tool in the CE10 CMC is not nearly detailed or summarised enough.

Anyone have any ideas?

EO
Hertfordshire, England

Turkbear · Nov 18, 2005

Hi,
I have developed an asp page ( based on some CE sample code snippets) that will do that for any given report ID - you can call it with a loop to use all the IDs in your system ( but, since it is very detailed ( covers subreports, database info and all tables and their aliases is any), using it for lots of reports at once can take a long time and be very resource hungry..But..It will work.
The code is below, you can skip all the Style stuff if you want, but is does make it pretty...

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 = "Any Account with view on demand to all reports"

Const Password= "passwordforthataccount"

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>

Hope it helps..

To Paraphrase:"The Help you get is proportional to the Help you give.."

synapsevampire · Nov 18, 2005

I take the following approach, run an application such as Report Miner, which states the report names and the tables used.

Unfortunately most of these types of Crystal Reports applications seem to be brain dead when it comes to the columns used, such a pity, so I use a GREP type tool against a directory of Report Definition files to find out which reports use specific columns if need be.

Now you can also run something like Embarcadero's RStudio, which reverse engineers a database to get further information about relationships, underlying structures, etc.

btw, I always prosyletize NOT using tables in reporting, instead expose Views, that way when something basic like a data type changes, you can CAST/CONVERT it ni the View and youer myriad of reports are never impacted.

You may get an argument from your dba, but there are plenty of dba's out there...and since they hadn't suggested this as an alternative, perhaps it's worth considering anyway. It costs them a tiny amount of extra work, and saves the client side developers ton$ of hour$. I know, they don't care because they don't own the bottom line...

I've heard several Oracle dba's complain that too many objects are being created and that this hinders performance, to that I refer you to my previous solution to argumentative dba's, especially if they don't have a BETTER solution.

-k

ajayshah · Nov 19, 2005

I use report analyzer. Gives lots of information in different ways. Found very useful particullary to report tables/views used by reports.

Ajay

IdoMillet · Nov 19, 2005

See:

http://www.cortexsystems.com.au/ramain.htm

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

eo · Nov 21, 2005

Hi

Thanks for all responses, I will explore them all. TurkBear, please can you advise which sections of your ASP needs changing...I am a novice with HTML...

Am I correct in assuming its:

Code:

<link rel="stylesheet" type="text/css" href="rsc/style/template_style.css"/>
<style type="text/css">

...to a bespoke stylesheet

Code:

Const APS = "YOURCMS"
Const UserID = "Any Account with view on demand to all reports"

Const Password= "passwordforthataccount"

Const Aut = "secEnterprise"

...unique logon details

Is there any other area?

Many thanks,

EO
Hertfordshire, England

eo · Dec 1, 2005

I am reviewing some suggested third party tools for impact analysis. But only get very vague answers when asking them if their applications can work directly with reports stored in CE. Some claim they do, but I am not convinved as to my knowledge in CE reports are stored as meaningless report id's (such as C333-5672164-5436427165.rpt)
I need to access the actual report names such as example.rpt.
Does anyone know where the actual report objects are stored in CE10. Is it the fileStore, in which case my fears are true, or does the file store only store instances, and actual report objects/ templates are stored elsewhere.
Thanks,

EO
Hertfordshire, England

Turkbear · Dec 1, 2005

Hi,
That should do it..The stylesheet is not even used in this code but I am a lazy cut-and-paster so I used the code from another page to set up the <HEAD> section..

To Paraphrase:"The Help you get is proportional to the Help you give.."

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!

Need to get a list of data sources for impact analysis

eo

MIS

Turkbear

Technical User

synapsevampire

Programmer

ajayshah

Programmer

IdoMillet

Instructor

eo

MIS

eo

MIS

Turkbear

Technical User

Similar threads

Part and Inventory Search

Sponsor