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:
- Extract the Views to pass through by the name based upon the schema.
- Create a TableBox for each table which has been passed through the final qvw.
Solution
Step 1
Extract the views based upon a prefix:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
/** * 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
' 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 |