BusinessObjects Board

List Objects Used in a set of Documents

Author: Dave Rathbun
Author Notes:

Objects Used

' "ObjectsUsed"
' Dave Rathbun, Integra Solutions
' www.IntegraSolutions.net
' provided "as is", no warranty implied
' This macro does not by design to anything to the reports that it reads. It does write
' to a CSV file that will be - by default - located in the "UserDocs" folder as specified by
' a user preference setting. The data provider for the document must point to this file. As of
' this revision, the user must do this step manually.

' This code may be used for personal or corporate use, but may not
' be sold, redistributed, or posted on web sites without permission
' from the author.

Option Explicit                             ' Force explicit declaration of all variables

' GLOBAL CONST *******************************************************
' Standard File I/O numbers assigned here
Global Const ErrNum As Integer = 1
Global Const OutNum As Integer = 2

Sub ObjectsUsed()
    Dim strCurrentFile As String
    Dim docMyDocument As Document
    Dim dpMyDataProvider As DataProvider
    Dim dpColumn As Column
    Dim strUniverseName As String
    Dim strUniverseDomain As String
    
    Dim strNextItem As String   ' used to parse comma listed table names
    Dim strDocPath As String    ' Path to use to look for files
    Dim strObjList As String    ' Path for output file
    Dim strErrorLog As String   ' Path for error log

    Dim i, j, k As Integer      ' loop counters
    Dim x As Integer            ' function result value
    
    ' Start directory list of .rep files in the standard file location. If you want to load files
    ' from a different directory, you would hard code the value below. Make sure that a hard-coded
    ' path includes the trailing back slash!
    
    strDocPath = Application.GetInstallDirectory(boDocumentDirectory) & "\Integra Solutions\"
    strObjList = strDocPath & "ObjectsUsed.csv"
    strErrorLog = strDocPath & "ObjectsUsed.log"
    
    If FileExist(strObjList) Then
        Kill (strObjList)
    End If
    If FileExist(strErrorLog) Then
        Kill (strErrorLog)
    End If
    
    strCurrentFile = Dir(strDocPath & "*.rep")
    
    Open strObjList For Append As OutNum
    Print #OutNum, "Universe"; ", "; "Report Name"; ", "; "Object Name"; ", X"
    Close #OutNum
    
    ' Do while there are reports we have not looked at
    Do While strCurrentFile <> ""
     
      If strCurrentFile <> ThisDocument.Name &amp; ".rep" Then

        Set docMyDocument = Application.Documents.Open(strDocPath &amp; strCurrentFile)
        
        If docMyDocument.Name <> "" Then
        
            For j = 1 To docMyDocument.DataProviders.Count
            
                Set dpMyDataProvider = docMyDocument.DataProviders.Item(j)
                If dpMyDataProvider.GetType = "DPQTC" Then  ' This is a dataprovider from a universe
                
                    ' Get the universe associated with the data provider
                    strUniverseName = dpMyDataProvider.UniverseName
                    
                    ' Next step will be to parse the data provideres and write out to
                    ' an output file the comma separated list of objects used
                    Open strObjList For Append As OutNum
                    
                    For k = 1 To dpMyDataProvider.Columns.Count
                    
                        Print #OutNum, strUniverseName; ", "; strCurrentFile; ", "; dpMyDataProvider.Columns.Item(k).Name; ", "; "X"

                    Next k
                    
                    Close OutNum
                    
                End If
            
            Next j  ' Next data provider, if any more
            
        End If
        
        docMyDocument.Close
        
      End If
        
      ' Get next document, if there is one.
      strCurrentFile = Dir
   Loop
   
   ' If this document includes a link to the CSV file as a data provider,
   ' then the following lines can be used to refresh the list of objects.
   ' ThisDocument.Activate
   ' ThisDocument.Refresh

End Sub

'*********************************************************************
' This subroutine is used to log entries to a specified log file. The
' only error that is not logged here is a failure to write to the log
' file...
'*********************************************************************
Sub LogError(ErrorString$)

    Dim strErrorLog As String
    strErrorLog = Application.GetInstallDirectory(boDocumentDirectory) &amp; "\ObjectsUsed.log"
    Open strErrorLog For Append As #ErrNum
    Print #ErrNum, Date; " "; Time(); " > "; ErrorString$
    Close #ErrNum

End Sub ' LogError

'*********************************************************************
' This function is used to check for the existance of a file. It returns
' a fake "boolean" flag using the BusinessObjects constants TRUE and FALSE.
' If the file is found, the function returns TRUE. Otherwise, the function
' returns FALSE.
'
' If any file name is not found, an entry is made to the LogFile.
'*********************************************************************
Function FileExist(myFileName$)

  ' First Check to ensure that myFileName is a valid file

  On Error GoTo NotFound            ' provide alternate error handling

  Open myFileName$ For Input As 255 ' attempt to open file
  On Error Resume Next              ' if success, restore default error handling
  Close 255                         ' close file (it was only a test, afterall)

  FileExist = True
  
  Exit Function
  
NotFound:
  On Error Resume Next
  FileExist = False
  LogError ("File " &amp; myFileName$ &amp; " not found.")
  
End Function ' File Exist

BOB Downloads (BOB member since 2003-05-05)

Please Give the link to list objects used.rep
Report from repository
getting the full path of a var
Clean up Formula
Webi / Infoview Report Documentation
Query from Bo repository to know if an object is used or not
Macro to get objects and rows used in report?
Impact Analysis of Reports
List of objects with their SQL
Impact Analysis on All Reports
Viewing SQL generated by Business Objects
Report Documentation
Report listing all objects of BO Universe and name of report
List of objects used in report..
Find Universe objects which are not used in reports
Reports metadata.
Listing unused object of the universe
BO XI 3-SDK-Java get schema, table, columns used in report
How to display reports which contain certain object
Repository Table Linking Objects to Documents ?
Disabling Universe From All Groups
How can I print out the backend details of a report?
Data Provider Links
What reports belong to what Universe?
Repository Data Extraction Question
List of objects by report
Querying CorpDocs' SQL?
BO 5.1.2 file specs for. REP files
List all documents that use a specific universe object
Lists of objects used - how to include condition objects
Locating Objects in Report
Where to Start - SDK?
List of BO Report Names based on the table name
Document on a report
BO Freeware?
How do I list the report tab names in all corporate docs
BO repository SQL
Find Impact on Reports
Views/Table used in Reports
Alphabetized Index to BOB's Downloads
Which reports use a particular universe?
Extract Object Names, Object Definitions used in current rep
List of Objects used in report
Which Reports Use Which Objects
Searching Report Files by Universe
How to get metadata details from report? Modify Dave's code?
determining reports to be affected by universe modification
Is there a way to find 'Who is running this SQL'
report metadata
using BO repository tables via SQL

How do I implement this code?? When I tried to run it it gives me the compile error. on the first line that starts with Global.

Compile Error: Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of oibject modules

Thanks
KC


SKC (BOB member since 2008-11-06)

Place the code in its own module, not in the ThisDocument module. You can find a similar example here … List objects used in a series of reports. The second example puts the results in a full client document, whereas the first uses a text file, but otherwise are similar.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

:wink: These macros work great. I am interested in a macro that will document the field usage within the full client report tabs. For example, Tab 1 Table 1 Column 1 contains heading of “Account Number” and column contains the field named Account Number. Tab 1 Table 1 Column 2 contains heading of name, and column contains the Account Name (the field name or variable name used). I think you get the idea. I cannot see anyting posted that does this.


nell :us: (BOB member since 2005-09-08)

As always people want more and more :slight_smile:

The macro provided works really well, however is there a simple edit that can be made so that it lists personal dataproviders?

The reason I ask is that I need to produce a summary list of all deski reports that use excel data sources.

Any advice would be much appreciated - alas my vb is terrible

Thanks


JB101 (BOB member since 2009-06-15)

How can we modify the code to include wqy files in this…? please help


desmataks (BOB member since 2010-04-26)