Reason

Sometimes it makes sense to pass some SQL Views through the whole ETL-Process to e.g. use the View “AS-IS” within a NPrinting Report. To achieve this behaviour, wenn need 2 dynamic steps:

1. Extract the Views to pass through by the name based upon the schema.
2. Create a TableBox for each table which has been passed through the final qvw.

Solution

Step 1

Extract the views based upon a prefix:

/**
* KNOW HOW by Philipp Frenzel
* www.frenzel.net
*/

/**
* STEP 1
* Load the Schema and the relevant tables
*/
SqlTables:
SQL SELECT *
FROM domain.INFORMATION_SCHEMA.TABLES
WHERE
 1 = 1
 AND TABLE_NAME LIKE 'VwPrefix%' 
ORDER BY 
 TABLE_NAME ASC;

/**
* now you look the tables to load each table
**/

LET varTableName = '';
LET varCounter = 0;

// check if a dynamic table exists
IF NoOfRows('SqlTables') > 0 THEN

FOR varCounter TO NoOfRows('SqlTables')

  LET varTablename = peek('TABLE_NAME',varCounter,'SqlTables');
  //... here goes your code for loading and saving the table

NEXT;

END IF;

Step 2

' Frenzel GmbH 2017
' @author Philipp Frenzel
' @version 1.0.0
' AutoGenerate TableObjects

SUB MyAutoTableGenerator
'msgbox("Schritt 0")

SET MasterTableBox = ActiveDocument.GetSheetObject("MASTERTABLE")
'msgbox("Schritt 1")

ReportTableNamePrevious = ""

FOR rowIter = 1 TO MasterTableBox.GetRowCount - 1
'msgbox("Schritt 2")

SET ReportTableName = MasterTableBox.getCell(RowIter,0)
SET ReportTableNameField = MasterTableBox.getCell(RowIter,1)
'msgbox(ReportTableName.Text)

IF ReportTableName.Text <> ReportTableNamePrevious THEN
'msgbox("Schritt 2.0 :: Create Sheet")
ReportTableNamePrevious = ReportTableName.Text

'SET checkSheet = ActiveDocument.getSheetById(ReportTableName.Text)

'IF checkSheet IS Nothing THEN
'nothing
'ELSE
' ActiveDocument.getSheetById(ReportTableName.Text).Remove
'END IF

' adding a new sheet to the current application
'SET newSheet = ActiveDocument.CreateSheet
'SET sheetProperties = newSheet.GetProperties

'sheetProperties.Name = ReportTableName.Text
'sheetProperties.SheetId = ReportTableName.Text
'newSheet.setProperties sheetProperties

SET dynTableBox = ActiveDocument.getSheetObject(ReportTableName.Text)

IF (dynTableBox IS Nothing) THEN
SET dynTableBox = ActiveDocument.Sheets("SH01").CreateTableBox
END IF

SET tp = dynTableBox.getProperties
tp.Layout.Frame.Name.v = ReportTableName.Text
tp.Layout.Frame.ObjectId = ReportTableName.Text
dynTableBox.SetProperties tp

countTableColumns = dynTableBox.GetColumnCount-1 

END IF

varMatch = 0

'MsgBox(countTableColumns)

FOR ColIter = 0 TO countTableColumns
SET colObject = dynTableBox.GetField(ColIter)
'msgBox(colObject.Name)
IF colObject.Name = ReportTableNameField.Text THEN
'msgbox ("Found a match")
varMatch = 1
END IF
NEXT
IF varMatch = 0 THEN
dynTableBox.AddField ReportTableNameField.Text
END IF

NEXT

END SUB