Session No. E180 Building A Spreadsheet Front End Database Backend Application in PowerBuilder

1. Session No. E180 Building ASpreadsheet Front End Database BackendApplication in PowerBuilder • Kelley Willis • Project Leader • kwillis@mgm.com • Nagui Fam • Senior Programmer/Analyst • nfam@mgm.com • MGM • TV Distribution Division

2. The User’s Request Define the Application Problem: • Want to view ‘event’ dates and status for products, in territories, for different media • Need to be able to define subsets of data • Need to be able to specify the display dimension of the three axes (Product, Territory and Media Type) • Need to be able to do data input/update from same display • Need to maintain the look and feel of manually created report in Excel Spreadsheet

3. Technical Problems • Dimension axes selections form an imaginary display cube • How to define data selection and selected 3D axes • How to reflect these in a datawindow • How to populate the ‘event’ dates in the datawindow • How to do db updates • …and, of course, some n-tier architecture for the future...

4. Demo • The Selection Tab • ‘Drag and Drop’ defines selections and axes, excludes selections from other dimensions once made • ‘Custom Configurations’ allow pre-fab selections to be saved and retrieved, creating uniformity of reporting for different working groups. Uses ‘blobs’ to save datawindow contents • The Maintenance Tab • Three dimension display sliced out of the imaginary cube • Multiple header rows • Multiple columns/rows per header • Column numbers depends on data selection – no limits

5. Comparison

6. Flow Diagram User Selection Criteria DB Selection Analyser SQL Builder Datawindow Source Code Builder Datawindow Template Source data Datastore Data Manager Spreadsheet Datastore Cell / Data Mapping Datastore GetFullState /SetChanges SetFullState /Getchanges Spreadsheet Datawindow

7. Template Datawindow

8. Template Datawindow Source Code • Source Code Link

9. User Selection • Column, Row and Page Data Structure Arrays • Each structure array carries: • Type • Key Identifier • Description • Axes Orientation (Row, Column or Page)

10. User Selections Analyzer • Number of header rows • Number of header cells per header row • Number of columns per header cell • Number of total columns • Number of detail rows • Number of pages • Column, Row and Page axis data types selected • Order of display

11. Cell Position Manager Number of total columns Number of header rows Number of header cells per header row Column, Row and Page axis data types Width Y Position X Position Display order and Orientation Manager Source Code Builder DW Template Source Code Data Extraction Manager Header Data Reader Header Declaration Column Column Header 1st Column 1st Column Header Column Declaration 1st Column Declaration Post Header Declaration DW Initialization Column and Header ID manger Source Code Assembler Source Code String

12. Spreadsheet Datastore Source Code • Link To Spreadsheet Datastore Source Code

13. SQL Builder • Dynamic SQL statement builder • Based on user selection • Dynamically modifies Source Data datastore SQL Select

14. Source Data Datastore • Example • SQL select built dynamically from user selection • Retrieves saved user selections - if any

15. Cell / Data Mapping • Example • Built dynamically while building the spreadsheet datastore source code • Maintains a linear representations for cell axes data types

16. Data Manager • Inserts into the Spreadsheet datastore, the required number of rows based on user data selection • Establishes the association between a cell and its corresponding data in the Data Source datastore via the cell mapping datastore • Writes and reads cell’s content • Reflects cell changes to the Data Source datastore • Initiates Data Source datastore retrieval and update • Verifies data integrity

17. Spreadsheet Datawindow Dynamically generated from “Template” datawindow

18. Building ASpreadsheet Front End Database BackendApplication in PowerBuilder • Open Discussion

19. DW Initialization • release 8; • datawindow(units=0 timer_interval=0 color=16777215 processing=1 HTMLDW=no print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.prompt=no print.buttons=no print.preview.buttons=no row.resize=1 grid.lines=0 )

20. Header Declaration • header(height=72 color="553648127" )

21. Post Header Declaration • summary(height=0 color="536870912" ) • footer(height=0 color="536870912" ) • detail(height=72 color="553648127" )

22. First Column Declaration • table(column=(type=char(255) updatewhereclause=yes name=col1 dbname="col1" )

23. Column Declaration • column=(type=char(10) updatewhereclause=yes name=col2 dbname="col2" values=" /Confirmed CONF/Pending PEND/No Rights NR/Not Viable RNV/Planned PLAN/" )

24. First Column Header • text(band=header alignment="2" text="" border="6" color="0" x="9" y="4" height="64" width="105" html.valueishtml="0" name=col1_t visible="1" resizeable=1 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="80269524" )

25. First Column • column(band=detail id=1 alignment="2" tabsequence=32766 border="6" color="0" x="9" y="4" height="64" width="105" format="[general]" html.valueishtml="0" name=col1 visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=no edit.imemode=0 edit.displayonly=yes font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="80269524" )

26. Column Header • text(band=header alignment="2" text="" border="6" color="0" x="123" y="4" height="64" width="105" html.valueishtml="0" name=col2_t visible="1" resizeable=1 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="80269524" )

27. Column • column(band=detail id=2 alignment="2" tabsequence=10 border="0" color="0" x="123" y="4" height="64" width="105" format="[general]" html.valueishtml="0" name=col2 visible="1" ddlb.limit=0 ddlb.allowedit=no ddlb.case=any ddlb.nilisnull=yes ddlb.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )