Report fields in a XIR2 report using VBA Macro Code

[system]

Hi

I want to get the report fields available in a report using SDK.
Is there any methods available in BO SDK to retrieve the report fields.

Please suggest me regarding this.

Thanks in Advance

Cheers
Priyanical

---

priyanical (BOB member since 2007-10-27)

[system]

Get report fields means capture all objects, universe as well as report variables and display in a message box.
Or access report variables to update/modify.
What exactly you are looking for

.

---

haider (BOB member since 2005-07-18)

[system]

Actually we are lookin for capturing all objects, universe as well as report variables and display in a message box.

---

priyanical (BOB member since 2007-10-27)

[system]

Do a search on BOB in the SDK forum
search keywords : capture objects
Found one here

120×120 10.4 KB

I’ve used Dave’s code to identify the list of objects used in a report (and if I could figure out how to link to the download, I would place that link here). I know almost nothing about VBA so it’s a miracle that I got Dave’s code to work for me. My question is “How would I modify the code to obtain the object’s SQL as well?” I am mainly interested in the Select but some of our objects have Wheres also (and I guess the conditions have Wheres instead of Selects!). TIA KSG …

Also check the downloads section

.

---

haider (BOB member since 2005-07-18)

[system]

Thanks Haider

one more doubt i have. Using ActiveDocument.DocumentVariable.count we can get the count of Variables available in a report.
In same note is there any method/function is available in SDK to find the formulas and constants used in a report.

Please suggest me how to retrieve the formulas and constants present in a report

---

priyanical (BOB member since 2007-10-27)

[system]

Try this

 Private Sub Document_BeforeRefresh(Cancel As Boolean)
Dim V_Cnt As Integer
Dim i As Long

V_Cnt = ThisDocument.DocumentVariables.Count

For  i = 1 To V_Cnt
 
  MsgBox ThisDocument.DocumentVariables.Item(i).Formula
  
Next i   
   
End Sub  

.

---

haider (BOB member since 2005-07-18)

[system]

@priyanical

Please suggest me how to retrieve the formulas and constants present in a report

The .DocumentVariables collection includes ALL variables … data provider objects (.IsDataProviderObject property = True), named formulas (.Name property is filled in), as well as unnamed formulas and constants (.Name property is empty).

---

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

[system]

Heres a snippet from the process we have that loops through and documents our report structures – it probably does way more than you would need…but it should give you an idea for whats available.

  Dim cellsubstructure, BlockSubStructure As ReportStructureItem
          Dim piv As Pivot
          Dim N As Integer
          Dim docvar As DocumentVariable
          Dim strStructureDesc As String
          Dim strSQL As String
          
          
          
30        Select Case RepStructureItem.Type
              'PROCESS TABLE
              Case boTable
40                strType = "TABLE"
50                strStructureDesc = ""
60                Set BlockSubStructure = RepStructureItem
70                Set piv = BlockSubStructure.Pivot
                  
80                For N = 1 To piv.BodyCount ' this represents the number of body objects in the block
90                    Set docvar = piv.Body(N)
                      
100                   If docvar.Name > "" Then
110                       If strStructureDesc = "" Then
120                           strStructureDesc = docvar.Name & " | "
130                       Else
140                           strStructureDesc = strStructureDesc & docvar.Name & " | "
150                       End If
                      
160                   Else
170                       If strStructureDesc = "" Then
180                           strStructureDesc = docvar.Formula & " | "
190                       Else
200                           strStructureDesc = strStructureDesc & docvar.Formula & " | "
210                       End If

220                   End If
                  
230               Next N
                  
240               strStructureDesc = Trim(strStructureDesc)
250               If Right(strStructureDesc, 1) = "|" Then
260                   strStructureDesc = Left(strStructureDesc, Len(strStructureDesc) - 1)
270               End If
                  
280               strStructureDesc = "Table [" & BlockSubStructure.Name & " ] " & " Created by Change Mgt. Import Process on " & CStr(Now()) & "." & Chr(13) & strStructureDesc
290               strStructureDesc = Replace(strStructureDesc, "'", "''")

          
          
                'PROCESS CROSSTAB
300           Case boCrosstab
310               strType = "CROSSTAB"
320               strStructureDesc = ""
330               Set BlockSubStructure = RepStructureItem
340               Set piv = BlockSubStructure.Pivot
350                   strStructureDesc = "Crosstab [ " & BlockSubStructure.Name & "] Body content --" & Chr(13)
                  
360               For N = 1 To piv.BodyCount ' this represents the number of body objects in the block
370                   Set docvar = piv.Body(N)
                      
380                   If docvar.Name > "" Then
390                       If strStructureDesc = "" Then
400                           strStructureDesc = docvar.Name & " | "
410                       Else
420                           strStructureDesc = strStructureDesc & docvar.Name & " | "
430                       End If
440                   Else
450                       If strStructureDesc = "" Then
460                           strStructureDesc = docvar.Formula & " | "
470                       Else
480                           strStructureDesc = strStructureDesc & docvar.Formula & " | "
490                       End If
500                   End If
510                   strStructureDesc = Trim(strStructureDesc)
520               Next N
                  
530               If Right(strStructureDesc, 1) = "|" Then
540                   strStructureDesc = Left(strStructureDesc, Len(strStructureDesc) - 1)
550               End If
                  
560               strStructureDesc = strStructureDesc & Chr(13) & "Crosstab [ " & BlockSubStructure.Name & "] Column content --" & Chr(13)
                  'PROCESS COLUMNS
570               For N = 1 To piv.ColumnsCount ' this represents the number of body objects in the block
580                   Set docvar = piv.Columns(N)
                      
590                   If docvar.Name > "" Then
600                       If strStructureDesc = "" Then
610                           strStructureDesc = docvar.Name & " | "
620                       Else
630                           strStructureDesc = strStructureDesc & docvar.Name & " | "
640                       End If
650                   Else
660                       If strStructureDesc = "" Then
670                           strStructureDesc = docvar.Formula & " | "
680                       Else
690                           strStructureDesc = strStructureDesc & docvar.Formula & " | "
700                       End If
710                   End If
                      
720                   strStructureDesc = Trim(strStructureDesc)
730               Next N
                  
740               If Right(strStructureDesc, 1) = "|" Then
750                   strStructureDesc = Left(strStructureDesc, Len(strStructureDesc) - 1)
760               End If
                  
                  'PROCESS ROWS
770               strStructureDesc = strStructureDesc & Chr(13) & "Crosstab [ " & BlockSubStructure.Name & "] Row content --" & Chr(13)
                  
780               For N = 1 To piv.RowsCount ' this represents the number of body objects in the block
790                   Set docvar = piv.Rows(N)
                      
800                   If docvar.Name > "" Then
810                       If strStructureDesc = "" Then
820                           strStructureDesc = docvar.Name & " | "
830                       Else
840                           strStructureDesc = strStructureDesc & docvar.Name & " | "
850                       End If
860                   Else
870                       If strStructureDesc = "" Then
880                           strStructureDesc = docvar.Formula & " | "
890                       Else
900                           strStructureDesc = strStructureDesc & docvar.Formula & " | "
910                       End If
920                   End If
930                   strStructureDesc = Trim(strStructureDesc)
940               Next N
                  
950               If Right(strStructureDesc, 1) = "|" Then
960                   strStructureDesc = Left(strStructureDesc, Len(strStructureDesc) - 1)
970               End If
                  
980               strStructureDesc = "Crosstab [" & BlockSubStructure.Name & " ] " & " Created by Change Mgt. Import Process on " & CStr(Now()) & "." & Chr(13) & strStructureDesc
990               strStructureDesc = Replace(strStructureDesc, "'", "''")
                'PROCESS CHART/GRAPH
1000          Case boChart
1010              strType = "GRAPH/CHART"
1020              strStructureDesc = ""
1030              Set BlockSubStructure = RepStructureItem
1040              Set piv = BlockSubStructure.Pivot
1050              strStructureDesc = "Chart/Graph [ " & BlockSubStructure.Name & "] body content --" & Chr(13)
                  
1060              For N = 1 To piv.BodyCount ' this represents the number of body objects in the block
1070                  Set docvar = piv.Body(N)
                      
1080                  If docvar.Name > "" Then
1090                      If strStructureDesc = "" Then
1100                          strStructureDesc = docvar.Name & " | "
1110                      Else
1120                          strStructureDesc = strStructureDesc & docvar.Name & " | "
1130                      End If
1140                  Else
1150                      If strStructureDesc = "" Then
1160                          strStructureDesc = docvar.Name & " | "
1170                      Else
1180                          strStructureDesc = strStructureDesc & docvar.Formula & " | "
1190                      End If
1200                  End If
1210                  strStructureDesc = Trim(strStructureDesc)
1220              Next N
                  
1230              If Right(strStructureDesc, 1) = "|" Then
1240              strStructureDesc = Left(strStructureDesc, Len(strStructureDesc) - 1)
1250              End If
1260              strStructureDesc = "Chart/Graph [" & BlockSubStructure.Name & " ] " & " Created by Change Mgt. Import Process on " & CStr(Now()) & "." & Chr(13) & strStructureDesc
1270              strStructureDesc = Replace(strStructureDesc, "'", "''")
1280      End Select

---

jresendez (BOB member since 2004-05-03)