yeah … thats right… objects used in sub-queries are not exposed to the datamodel. I have scratched my head many times to get that piece of information but to no avail 
Can anyone plz tell if this is taken care of in the higher version?
s_arnool (BOB member since 2002-09-19)
For completeness, the object model only understands constants (keyed in values) and query panel prompts (as opposed to universe objects containing prompts) as operands (the right hand side of the condition). It has no “visibility” when a sub-query or another object is used as an operand.
Dwayne Hoffpauir 
(BOB member since 2002-09-19)
Have you noticed that in 6.5 a lot of this is provided in the product? (Impact Analysis)
It seems to work programatically and does very much what all of these add-ins do.
It’ll be interesting to see exactly what it comes up with…
Has anyone used it in anger yet?
philmorris 
(BOB member since 2002-11-12)
I was wondering if anyone knows how to tweak this to see the where clause of the documents? I am trying to find all documents that use a condition with a certain object? Any ideas?
maprice (BOB member since 2002-10-23)
Are you looking for a Solution for Webi or full client documents?
Full client it is possible since 5.x all the way to 6.5.x
Webi is another beast. It was not exposed in the SDK before 6.x
In 6.5.1 it is there but not very stable and a great solution to crash your Webi server.
All the cases are based on SDK. You may use the Webi log files it you have Webi docs only.
ClaireB 
(BOB member since 2002-08-09)
Do you have access to tech support? One of my 2004 conference presentations was entitled “Automate Universe Maintenance with the Designer SDK” and it includes a utility that will list the objects used in a series of reports. Technically the utility uses the Reporter SDK, but you probably don’t care about that 
. The presentation and utility(ies) are posted on the tech support site.
Dwayne Hoffpauir (BOB member since 2002-09-19)
Is there a way to retrieve the SQL code of each universe object using VBA/SDK, or can this only be done by querying the Business Objects repository directly?
Andreas (BOB member since 2002-06-20)
I believe the answer is yes and no. Yes, it can be done, but no, not so easily.
If you load a dataprovider called curDP you can use the curDP.sql to obtain the code in the loaded DP. Then if all you want is the select statement, use a mid() function to retrieve the partial line of code. Otherwise, if you are looking to clean SQL code from the curDP.sql that is retrieved, I have a simple subrountine I can provide you.
Otherwise, I believe you can use something like the ManagerO or the modified ManagerO for SQL Server.
Hope this helps. Have a great evening.
MayhewM (BOB member since 2003-10-22)
Are you wanting to find the individual object SQL for objects used in a report, or are you wanting to document all of the SQL for all objects in a universe?
Dwayne Hoffpauir (BOB member since 2002-09-19)
Dwayne, how about both please? 
Andreas (BOB member since 2002-06-20)
Fair enough! This presentation from the 2004 User Conference includes an Excel-based utility to document a universe, including the select and where statements for objects. This presentation includes a Reporter-based utility that documents (almost) all objects used in a series of reports. A bit of cross-referencing of the two should get you what you need.
Dwayne Hoffpauir (BOB member since 2002-09-19)
Dwayne, thank you for digging up the links, I will check them out over the weekend 
Andreas (BOB member since 2002-06-20)
Dwayne, I dont have access to the Tech Support area of BO however I would really like to use this utility. Would you mind emailing this to me?(avcable@pacbell.net)
Thanks,
Andrew
acable (BOB member since 2004-10-01)
Hi All,
I am using the code which given Dave. But, for one of the report I am getting below error.
Run-time error ‘-2147417848 (80010108)’:
Automation error
The object invoked has disconnected from its clients
Can anybody will let me know the cause for this ??
Thanks in advance,
Thanks again,
TSK…
tsk_9 (BOB member since 2005-04-06)
Hi All 
I had a quick look back at this thread an realised it’d been a while since I’d had a good look.
I’ve since been using yet another version of the non-VBA DP version of this utility - originally Dave Rathburns - that I tweaked a little.
It has a couple of little improvements:
Uses For Each loops which seem more stable and execute more quickly
Fetches document variables and formulae alike
Makes all output ‘CSV Safe’ to prevent read-in errors.
Toggles Interactive mode to improve visual feedback
I hope this helps out…
' Objects Used utility
' Orignal by Dave Rathburn
' Tweaked and expanded by Phil Morris - IT Performs
' Code is provided as-is,
' No warranty of any kind is implied nor will any
' liabilities be accepted for any problems losses etc.
' incurred by the use of this code.
' License: Beerware
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
Global Const qc As String = """"
Sub ObjectsUsed()
Dim strCurrentFile As String
Dim docMyDocument As Document
Dim dpMyDataProvider As DataProvider
Dim qryMyQuery As Query
Dim resMyResult As Result
Dim varMyVariable As DocumentVariable
Dim cndMyCondition As Condition
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, l, n As Integer ' loop counters
Dim x As Integer ' function result value
Dim strAllQV As String ' String to contain the array of known query objects, for filtering variable results
' 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) & "\ObjectsUsed\"
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"; ", "; "Class Name"; ", "; "Object Name"; ", Clause"
' Do while there are reports we have not looked at
Do While strCurrentFile <> ""
If strCurrentFile <> ThisDocument.Name & ".rep" Then
' Suppress any unwanted messages
Application.Interactive = False
Set docMyDocument = Application.Documents.Open(strDocPath & strCurrentFile, True, True)
If docMyDocument.Name <> "" Then
For Each dpMyDataProvider In docMyDocument.DataProviders
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
strAllQV = ""
For Each qryMyQuery In dpMyDataProvider.Queries
For Each resMyResult In qryMyQuery.Results
Call PrintToFile(strUniverseName _
, strCurrentFile _
, resMyResult.Class _
, resMyResult.Object _
, "QObject" _
)
strAllQV = strAllQV + "|" + resMyResult.Object
Next resMyResult
For Each cndMyCondition In qryMyQuery.Conditions
Call PrintToFile(strUniverseName _
, strCurrentFile _
, cndMyCondition.Class _
, cndMyCondition.Object _
, "Condition" _
)
Next cndMyCondition
Next qryMyQuery
End If
Next dpMyDataProvider ' Next data provider, if any more
' this is a loop to output variables and formulae in the document.
For Each varMyVariable In docMyDocument.DocumentVariables
If InStr(1, strAllQV, "|" + varMyVariable.Name) = 0 _
Or varMyVariable.Name = "" Then
If varMyVariable.Name = "" Then
Call PrintToFile(strUniverseName _
, strCurrentFile _
, "Formula" _
, varMyVariable.Formula _
, "Formula" _
)
Else
' NOW USE PRINT SUB Print #OutNum, qc; strUniverseName; qc; ", "; strCurrentFile; ", "; docMyDocument.DocumentVariables.Item(k).Name; ", "; docMyDocument.DocumentVariables.Item(k).Formula; ", "; "Variable"
Call PrintToFile(strUniverseName _
, strCurrentFile _
, varMyVariable.Name _
, varMyVariable.Formula _
, "Variable" _
)
End If
End If
Next varMyVariable
End If
' Go interactive again in the hope that we might see progress
Application.Interactive = True
docMyDocument.Close
End If
'skipme:
' Get next document, if there is one.
strCurrentFile = Dir
Loop
Close OutNum
' 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
k = MsgBox("Document Analysis Complete!", vbOKOnly, "Document Structure Macro")
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) & "\ObjectsUsed.log"
Open strErrorLog For Append As #ErrNum
Print #ErrNum, Now; " > "; ErrorString$
Close #ErrNum
End Sub ' LogError
'*********************************************************************
' This subroutine is used to format and output to the main file
' It accepts 1 string parameter for each of the 5 output columns
'*********************************************************************
Sub PrintToFile(ByVal ov_Universe$, ByVal ov_Report$, ByVal ov_Class$, ByVal ov_Object$, ByVal ov_Clause$)
' First pass all of the columns through the CSV safe function
ov_Universe$ = MakeCSVSafe(ov_Universe$)
ov_Report$ = MakeCSVSafe(ov_Report$)
ov_Class$ = MakeCSVSafe(ov_Class$)
ov_Object$ = MakeCSVSafe(ov_Object$)
ov_Clause$ = MakeCSVSafe(ov_Clause$)
' Now output to the file
Print #OutNum, ov_Universe$; ","; ov_Report$; ","; ov_Class$; ","; ov_Object$; ","; ov_Clause$
End Sub ' PrintToFile
'*********************************************************************
' 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
'*********************************************************************
' This function is used to convert output strings to a safe format for
' the output CSV file.
' This includes surrounding the main string with double quotes,
' to ensure any commas contained within are taken literally
' Changing any incidences of double quotes into 2, to mean literal "
'*********************************************************************
Function MakeCSVSafe(term)
Dim m As Integer
Dim bs As String
bs = qc 'initialise build string with a double quote
If InStr(1, term, qc) Then
For m = 1 To Len(term)
If Mid$(term, m, 1) = qc Then
bs = bs + qc + qc
Else
bs = bs + Mid$(term, m, 1)
End If
Next m
Else
bs = bs + term
End If
bs = bs + qc
MakeCSVSafe = bs
End Function
philmorris (BOB member since 2002-11-12)
Hi,
In fact if you want to analyze the SQL for a report and get the list of used fields, it will be difficult to analyze the SQL. Many times it contains fields from different tables and aliases, enclosed by various delimiters, and using complex functions like decode, case… with different SQL syntaxes. You will need to use a very smart SQL analyzer (I found very good open source ones) to achieve good results.
The only reliable way in our opinion is to fetch the universes objects used by the report, then open the universe, get the select of the objects and analyze the select SQL to get the used table.field.
It’s a little long way, but it works. We just analyze it and started to implement it for the metadatas features of the future version of our version managment tool.
Hope this help
jp.golay 
(BOB member since 2002-06-17)
Dwayne I could not find the above utility for documenting a report (including formulas, variables, constants etc…) Can you please point me to correct link… I searched for hours on tech support could not find it…
Thanks in Advance.
BO_Chief (BOB member since 2004-06-06)
If you hover over the hyperlink in Dwayne’s post, you’ll see that the old Tech Support site link was to article 2812.
If click the link, it takes you to the general search of the new tech support site. Enter “Article 2812” into the search bar. That should bring up the presentation, which still has “Article Number 2812” in its summary.
Anita Craig (BOB member since 2002-06-17)
The utility lists universe objects used in data providers, not the entire document itself. The utility mentioned previously can now be found here on BOB.
Dwayne Hoffpauir (BOB member since 2002-09-19)
Thanks for coming back to this thread … Dwayne…and Anita
BO_Chief (BOB member since 2004-06-06)