Hi,
I know there is a utility report available on BOB’s downloads which can give you the list of objects in a set of reports. Is there a utility available which can give those objects SQL with them as well? 
salam 
(BOB member since 2005-01-27)
Hi,
What did you want to know exactly ? SQL of these objects or complete SQL of the report ?
Regards
I know how to get the SQL for report. What I want is the list of objects in the report and the SQL associated with these object’s select clause.
salam (BOB member since 2005-01-27)
Assuming you want the SQL of each individual object, you don’t need a new utility to do that. Simply add and extra bit to that utility, the object sql is one of the available bits of info…I can’t remember the code but at the moment you might have something like
VBAobjectvariable.some_property_that_is_outputIf you remove “.some_property_that_is_output” then key a full stop “.” you get a popup memu of the different properties available…one of them should be the object’s SQL.
Nick Daniels 
(BOB member since 2002-08-15)
Following is the only code which i can view from that utility’s macro…
Option Explicit
Sub ListObjectsUsed(dpInterface As DpVBAInterface)
Dim dpCube As DpVBACube
Dim Doc As Document
Dim DProv As DataProvider
Dim Qry As Query
Dim Res As Result
Dim Cond As Condition
'DataRow is used to "stage" the record before adding to the cube
Dim DataRow(1 To 11) As String
Dim PathName As String
Set dpCube = DefineCube(dpInterface.DpVBACubes(1))
PathName = ThisDocument.Path & "\"
DataRow(1) = Application.Variables("BOSECURITYDOMAIN").Value
DataRow(2) = Dir(PathName & "*.rep")
Do While DataRow(2) <> ""
If PathName & DataRow(2) <> ThisDocument.FullName Then
Set Doc = Application.Documents.Open(PathName & DataRow(2), _
True, True)
For Each DProv In Doc.DataProviders
'only process universe-based data providers
If DProv.GetType = "DPQTC" Then
DProv.Load 'processes much faster
'capture items that are common to the data provider
DataRow(3) = DProv.Name
DataRow(4) = DProv.Universe.DomainName
DataRow(5) = DProv.Universe.ShortName & "-" & _
DProv.UniverseName
'process each query tab
For Each Qry In DProv.Queries
'capture items common to the query tab
DataRow(6) = Qry.Name
DataRow(7) = Choose(Qry.Operator + 1, _
"None", "Union", "Intersect", "Minus")
'process each result object
For Each Res In Qry.Results
DataRow(8) = "Result"
DataRow(9) = Res.Class
DataRow(10) = Res.Object
DataRow(11) = ""
'add the record to the cube
Call dpCube.DpVBAColumns.AddLine(DataRow)
Next Res
'process each condition
For Each Cond In Qry.Conditions
DataRow(8) = "Condition"
DataRow(9) = Cond.Class
DataRow(10) = Cond.Object
'because of the different types of operands,
'build a comment to describe the operand
If Cond.OperandCount = 0 Then
DataRow(11) = "Pre-defined condition"
Else
If Cond.OperandType(1) <> "" Then
DataRow(11) = "Operand1 is a " & _
Cond.OperandType(1)
Else
DataRow(11) = "Operand1 is an object " & _
" or sub-query"
End If
If Cond.OperandCount = 2 Then
If Cond.OperandType(2) <> "" Then
DataRow(11) = DataRow(11) & _
"; Operand 2 is a " & Cond.OperandType(2)
Else
DataRow(11) = DataRow(11) & "; Operand2 " & _
" is an object or sub-query"
End If
End If
End If
'add the record to the cube
Call dpCube.DpVBAColumns.AddLine(DataRow)
Next Cond
Next Qry
DProv.Unload
End If
Next DProv
Doc.Close
End If
DataRow(2) = Dir
Loop
CleanUp:
MsgBox "Complete!", vbInformation
Exit Sub
ErrorHandler:
MsgBox Err.Source & " - " & Err.Number & ": " & Err.Description, _
vbCritical, "Failure in ListObjectsUsed()"
Resume CleanUp
End Sub
Function DefineCube(dpCube As DpVBACube) As DpVBACube
Dim dpCol As DpVBAColumn
Set DefineCube = dpCube
DefineCube.DpVBAColumns.SetNbColumns (11)
Set dpCol = DefineCube.DpVBAColumns(1)
dpCol.Name = "Security Domain"
Set dpCol = DefineCube.DpVBAColumns(2)
dpCol.Name = "Report Filename"
Set dpCol = DefineCube.DpVBAColumns(3)
dpCol.Name = "Data Provider"
Set dpCol = DefineCube.DpVBAColumns(4)
dpCol.Name = "Universe Domain"
Set dpCol = DefineCube.DpVBAColumns(5)
dpCol.Name = "Universe Name"
Set dpCol = DefineCube.DpVBAColumns(6)
dpCol.Name = "Query Tab"
Set dpCol = DefineCube.DpVBAColumns(7)
dpCol.Name = "Query Set Operator"
Set dpCol = DefineCube.DpVBAColumns(8)
dpCol.Name = "Object Type"
Set dpCol = DefineCube.DpVBAColumns(9)
dpCol.Name = "Class Name"
Set dpCol = DefineCube.DpVBAColumns(10)
dpCol.Name = "Object Name"
Set dpCol = DefineCube.DpVBAColumns(11)
dpCol.Name = "Comment"
End Function
Now where do I have to make change? 
Sorry i’m a NULL at VBA 
salam (BOB member since 2005-01-27)
Well, that is a different macro from the one I was referring to, but do you see the bit where it says:
DataRow(10) = Res.Object Well you could add
DataRow(11) = Res.????? Now, because of the way it is written, I don’t think you can just type Res. and get a popup menu…but worth ago - someone with more VBA will chip in…
Nick Daniels (BOB member since 2002-08-15)
If some VBA guy can chip in that will be a bonus. But can you please provide the link to the utility which you were talking about???
salam (BOB member since 2005-01-27)
Here it is…see the bit where it is defining the variables before outputting them to a file e.g
dpMyDataProvider.Columns.Itemwell if you play round with this e.g. remove everything so you just have dpMyDataProvider then key “.” then see what options you have then try dpMyDataProvider.Columns then key “.” see what options you have…you’ll get it…
Nick Daniels (BOB member since 2002-08-15)
Thanks Smith and Nick. I’ll play around with the VBA to see how it works. 
salam (BOB member since 2005-01-27)
I have written a modified version of the posted script that includes a freehand SQL data provider that queries the repository. That query returns the universe name, class name, object name, and object SQL. By linking the two data providers (the existing one that creates the VBA output + the freehand SQL) I can provide the SQL code for each object. The SQL code for the object is not one of the attributes available via VBA, which is why I went that route.
Unfortunately, that was one of the items that was a victim of my latest laptop hard-drive crash, so I cannot post it. :x But that is the approach that I have used in the past.
Dave Rathbun 
(BOB member since 2002-06-06)
An other way is to use managero as the other dataprovider as suggest Dave.
Regards