Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE

Can a DataMart SQL query be used to enhance a DocFactory Report?

Can a DataMart SQL query be used to enhance a DocFactory Report?

Problem:

Can a DataMart SQL query be used to enhance a DocFactory Report?

Resolution:


  • Product Name: CaliberRM
  • Product Version: All
  • Product Component: DocFactory, DataMart
  • Platform/OS Version: All

This article extends the functionality of the VBA macro in KB Article below, to display the results in a table.

"Is it possible to add a DataMart SQL Query to enhance a DocFactory Report?"

To achieve this a table requires to be created using word API calls and the table populated by the Datamart Query. The VBA function below shows the techique.

Function returninfo(ByVal id_number As Integer)
"Function to return trace information from the Datamart extract

Dim db As DAO.Database
Dim rs As DAO.Recordset

"Path to DataMart extracted Database
Set db = DBEngine.OpenDatabase("C:\Program Files\Borland\CaliberRM\RMMSA.mdb")

"SQL to return required information
sqlString = "SELECT Trace.Trace_Element_Project_Name, RequirementInfo_1.Type, Trace.Trace_Element_ID, Trace.Trace_Element_Name, RequirementInfo_1.Description FROM (Trace INNER JOIN RequirementInfo ON Trace.Root_Element_ID = RequirementInfo.Requirement_ID) INNER JOIN RequirementInfo AS RequirementInfo_1 ON Trace.Trace_Element_ID = RequirementInfo_1.Requirement_ID WHERE (((RequirementInfo.Type) Like "*Business*") And ((Trace.Root_Element_ID) = " + Str(id_number) + ") And ((Trace.Direction) = "To") And ((Trace.Depth) = 1)) ORDER BY Trace.Root_Element_Project_Name, RequirementInfo.Type;"
Debug.Print sqlString


returnstring = ""
"query the database
Set rs = db.OpenRecordset(sqlString)
"Find out how many records
no_of_records = rs.RecordCount
Debug.Print rs.RecordCount
"add table
Dim mytable As Table

If rs.RecordCount > 0 Then
"create table with the correct number of columns and rows(one more than record count)
Set mytable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=no_of_records + 1, NumColumns:=5, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
"set up table column widths
mytable.Columns(1).Width = 100
mytable.Columns(2).Width = 100
mytable.Columns(3).Width = 75
mytable.Columns(4).Width = 100
mytable.Columns(5).Width = 275

"make first row background colour greyish
mytable.Rows(1).Cells.Shading.BackgroundPatternColor = wdColorGray10
"make all cell bold text for row 1
mytable.Rows(1).Range.Bold = True
"Add table headings
mytable.Cell(1, 1).Range = "Project Name"
mytable.Cell(1, 2).Range = "Requirement Type"
mytable.Cell(1, 3).Range = "ID Number"
mytable.Cell(1, 4).Range = "Requirement Name"
mytable.Cell(1, 5).Range = "Requirement Description"
"Create string to add to document
countrow = 2
"add information to table
Do Until rs.EOF
mytable.Cell(countrow, 1).Range = rs![Trace_Element_Project_Name]
mytable.Cell(countrow, 2).Range = rs![Type]
mytable.Cell(countrow, 3).Range = Str(rs![Trace_Element_ID])
mytable.Cell(countrow, 4).Range = rs![Trace_Element_Name]
mytable.Cell(countrow, 5).Range = rs![Description]
returnstring = returnstring + tempstring
rs.MoveNext
countrow = countrow + 1
Loop
Debug.Print returnstring

End If
"Clean up objects
rs.Close
db.Close
Set rs = Nothing
Set con = Nothing
Set mytable = Nothing
"Return information
returninfo = returnstring
End Function

To demonstrate how this technique works the attached template can be run against the Sample Database and the ???Address Book project???. To achieve this follow this procedure:

  1. Perform a DataMart Extraction i.e. Extractor -c c:\config.ini -d RMMSA
  2. Launch DocFactory
  3. Choose the attached template
  4. Choose the current baseline
  5. Open the produced report
  6. Select TOOLS | MACRO | MACROS
  7. Select the "FindInfo" macro


DataMarttable.dot
Old KB# 30437

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Version history
Revision #:
1 of 1
Last update:
‎2013-02-15 20:47
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.