BusinessObjects Board

Report Sql scan

sdk development
#1

Hi
I have like 100 reports to scan for the SQL (behind each report).
Is there any way to generate a listing of the SQL of all 100 reports at once ?

I am trying to avoid to go through each of the report manually…

I appreciate your time

BOLearner (BOB member since 2005-01-20)

#2

Do you need to do Execution plan for all the reports? If so, you need to open and refresh all the reports manually.

Please add some words on your post to supports you…

Regards

Siva.M

looksmee :uk: (BOB member since 2006-02-08)

#3

Not as a generic function, but take a look at this utility … List objects used in a series of reports. It uses VBA, and could be modified to record the .SQL property for each data provider instead of the object class and name.

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

#4

Thanks Dwayne

I do not know much about VBA coding, but i am taking help from some other friends, who knows that…

Thanks for sharing the code. I will update if my new code can get me the SQL of all reports

BOLearner (BOB member since 2005-01-20)

#5

In VBA code, DataProviders have a .SQL property. This can be printed out.

I modified the ‘VBA to List the Objects’, to show the DataProviders SQL.

This is what it looks like - you will need to tweak it a bit.

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 = "Z:\enter here the path of the report(s)\"
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.SQL; ","; 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 " & myFileName$ & " not found.”)

End Function ’ File Exist

shabir :canada: (BOB member since 2007-03-01)