EXCEL Templates by alllona

VIEWS: 3,869 PAGES: 55

									    EXCEL Templates




                            Renee Teatro
                      Information Builders
                      F.I.R.E. Conference
                            October 2007


1
    EXCEL Templates
    Agenda
      Overview of the available Excel Format options
      Excel Report Bursting
      Excel Compound Reports
      Excel Templates
         Review example using Developer Studio
      Compound Excel Templates




2
    Creating the Complete Spreadsheet Solution
    Out of the box…
     Fully formatted spreadsheets,
        Stop-lighting, numeric/date format translation, etc.

     Real-time drill-down within a spreadsheet

     Automatic PivotTable generation

     Native Excel Formulas

     Compound reports

     Population of existing spreadsheets


3
    Creating the Complete Spreadsheet Solution
    Available Excel formats
          HOLD FORMAT EXL2K
             Standard     Excel output
             HTML/XML       format


       Advantages:                        Considerations:
          Full styling and formatting       Heavy styling =
                                              large file size
          Drill-downs
                                             No formulas
          Customized sheet names
                                             XHT file extension
          Distributable




4
    Creating the Complete Spreadsheet Solution
    Available Excel formats
          HOLD FORMAT EXL2K FORMULA
             Standard     Excel output extension
             HTML/XML       format


       Advantages:                           Considerations:
          Full styling and formatting          Heavy styling lends to
                                                 large file size
          Drill-downs
                                                XHT file extension
          Customized sheet names
                                                Extended processing
          Distributable
          Formulas = ready for analysis


5
    Creating the Complete Spreadsheet Solution
    Available Excel formats
          HOLD FORMAT EXL2K PIVOT
             Unique    Excel output extension
             Fully   functional Excel PivotTable


       Advantages:                               Considerations:
          Mini data cube on your desktop           Large file size
          Distributable                            Special syntax
                                                     rules
          Ready for analysis
                                                    Limited styling




6
    Creating the Complete Spreadsheet Solution
    Available Excel formats
          HOLD FORMAT EXL97
             Deprecated     Excel output
             Designed     specifically for Excel 97
             HTML   based (No XML or CSS)


       Advantages:                      Considerations:
          Styling, drill-down             Heavy styling = large file size
          Distributable                   No formulas
                                           No CSS based features
                                           Limited numeric formatting


7
    Creating the Complete Spreadsheet Solution
    Available Excel formats
          HOLD FORMAT EXCEL
             Deprecated   Excel output
             Binary   format


       Advantages:                          Considerations:
          Opens in all versions of Excel      No styling
          Extremely light weight              No formulas
          Distributable                       No drill-downs
                                               No headings/footings



8
    Creating the Complete Spreadsheet Solution
    Excel Report Bursting
        HOLD FORMAT EXL2K BYTOC
           Unique Excel output extension
           Bursts data into multiple worksheets

       Advantages:
          Organizes the data into
           individual worksheets
           based on sort values
          Utilizes existing HTML
           Table of Contents syntax




9
     Creating the Complete Spreadsheet Solution
     Excel Compound Reports
         SET COMPOUND=OPEN/CLOSE (NOBREAK)
            Multi-report output in a single workbook

        Advantages:
           Ability to combine data from
            multiple sources
           Mix and match Excel
            formats
           Utilizes existing PDF
            compound report syntax
           Distribute multiple reports in
            a single workbook


10
     WebFOCUS Release 7
     Available Excel formats
         HOLD FORMAT EXL2K TEMPLATE
            Populates existing
            Excel files
        Advantages:
           Leverage spreadsheets
            already in use within
            your organization
           WebFOCUS can drive
            complex Excel
            applications
           More Excel functionality
            and styling for end
            users


11
     Creating the Complete Spreadsheet Solution
     The best fit for the situation
      Typical situations:
         Novice users requiring
          data for WHAT-IF analysis
               EXL2K FORMULA

         Advanced users requiring a data
          set for high level of analysis
                PivotTable

         A snapshot of data for review/filing
               EXL2K


12
     Creating the Complete Spreadsheet Solution
     The best fit for the situation
      Typical situations cont…
         Users requiring scheduled automatic delivery of data
                    EXL2K
                    EXCEL
                     Advanced macro based application requiring
                      a steady stream of operational data
                            EXL2K TEMPLATE

                     Highly styled report for presentation
                           HTML or PDF
                           EXL2K TEMPLATE


13
     WebFOCUS Release 7 - Promote User Productivity
     Excel Templates
       Ability to populate your own custom Excel workbook with data from
        WebFOCUS
          WebFOCUS will merge Excel output with an existing Excel
           “template”

       Benefits
          Leverage existing spreadsheets already
              in use within your organization
             Allows WebFOCUS to integrate with complex Excel workbooks
              that may contain macros, graphs, or complete VB applications.
             Allows developers to supply ad hoc or end users with more
              advanced Excel functionality out of the box such as filters,
              subtotals, page/print settings, etc.
             Allows more advanced styling for Excel based reports.



14
     WebFOCUS Release 7
     Excel Templates - Benefits
      Centralized Excel Templates
      Refreshed with data from anywhere in the enterprise
      Lock/Protect Cells
      Reduces errors
          WebFOCUS can be your “data engine”
             Eliminate manual data entry
          Automate your Auditing
             Create charts, functions, comparison formulas, etc.
             Utilize Excel’s Formula Auditing
      Speed Financial Reporting
      Improve Consistency

15
     WebFOCUS Release 7
     Excel Templates
     Data entered manually
                             Data automatically
                              refreshed




            Replaced sheet




16
     WebFOCUS Release 7
     Excel Templates – How It Works
      The Template
         Create an Excel workbook and designate one worksheet that will
             be replaced with a WEBFOCUS report
            Save the workbook as a “Single File Web Page” (Web Archive)
               Extension will be “.mht”
            Must be stored in EDAPATH or APP PATH

      The WebFOCUS procedure
         Output format is EXL2K [FORMULA]
         Special syntax that references the template file
             and individual worksheet

     ON TABLE PCHOLD FORMAT EXL2K TEMPLATE WFTMPLT SHEETNUMBER 1




17
     WebFOCUS Release 7
     Microsoft MHTML format
      Web Archive or Single File Web Page

         Encapsulation of worksheet files in MIME format

         Supported in Office 2002 (XP) and 2003

         Used to generate and distribute any multi-file Excel report
            Compound Reports
            Bursted reports
            PivotTables
            Templates


18
     WebFOCUS Release 7
     Creating an Excel ready worksheet
        Avoid functional pitfalls that negate built in Excel features

            Absolutely no blank lines or cells
              SET BY DISPLAY=ON

            If you must have a Heading or Footing
              Add blank line after Heading
                 and before Footing

            No Subheads, Subfoots, Subtotals, etc
              Use Excel’s built in Subtotal feature



19
     WebFOCUS Release 7
     Excel Template – Example – Existing XLS File – Sheet 2




20
     WebFOCUS Release 7
     Excel Template – Example – Existing XLS File – Sheet 1




21
     WebFOCUS Release 7
     Excel Template – Example – Existing XLS File – Sheet 1
        Change the worksheet format to the Excel file type, mht.
           This will enable WebFOCUS to populate the datasheet.
        Save the file to the appropriate APP directory




22
     WebFOCUS Release 7
     Excel Template – Example – Create New Procedure




23
     WebFOCUS Release 7
     Excel Template – Name Procedure & Select Report Painter




24
     WebFOCUS Release 7
     Excel Template – Example - Select CenturySales




25
     WebFOCUS Release 7
     Excel Template – Example – Create DEFINE field




26
     WebFOCUS Release 7
     Excel Template – Example – Create DEFINE field




27
     WebFOCUS Release 7
     Excel Template – Example – DEFINE in Field List




28
     WebFOCUS Release 7
     Excel Template – Example – Add Sortfields to Report




29
     WebFOCUS Release 7
     Excel Template – Example – Add verb objects to Report




30
     WebFOCUS Release 7
     Excel Template – Example – Add verb objects to Report




31
     WebFOCUS Release 7
     Excel Template – Example – Run Report




32
     WebFOCUS Release 7
     Excel Template – Example – Adjust BYDISPLAY Feature




33
     WebFOCUS Release 7
     Excel Template – Example – Adjust BYDISPLAY Feature
                  SET BYDISPLAY=ON




34
     WebFOCUS Release 7
     Excel Template – Example – Adjust BYDISPLAY Feature




35
     WebFOCUS Release 7
     Excel Template – Example – Add Report Heading




36
     WebFOCUS Release 7
     Excel Template – Example – Add Report Heading




37
     WebFOCUS Release 7
     Excel Template – Example – Add Report Heading




38
     WebFOCUS Release 7
     Excel Template – Example – Add Excel Template




39
     WebFOCUS Release 7
     Excel Template – Example – Add Excel Template




40
     WebFOCUS Release 7
     Excel Template – Example – Add Excel Template




41
     WebFOCUS Release 7
     Excel Template – Example – Add Excel Template




42
     WebFOCUS Release 7
     Excel Template – Example – Run Report – Sheet 1




43
     WebFOCUS Release 7
     Excel Template – Example – Run Report – Sheet 2




44
     WebFOCUS Release 7
     Excel Template – Example – Run Report – Sheet 3




45
     WebFOCUS Release 7
     Excel Template – Example – Run Report – Sheet 4




46
     WebFOCUS Release 7
     Excel Template – Must have minimum of 2 Worksheets
          Microsoft Templates:
          http://office.microsoft.com/en-us/templates/default.aspx
     TABLE FILE CAR
     PRINT
           BODYTYPE AS 'Bodytype'
     BY
           HIGHEST COUNTRY AS 'Country'
     BY
           CAR AS 'Car Name'
     BY
           MODEL AS 'Model'
     ON TABLE SUBHEAD
     "CAR MANAGEMENT LIST"
     " "
     WHERE COUNTRY NE 'ENGLAND';
     ON TABLE SET PAGE-NUM OFF
     ON TABLE NOTOTAL
     ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'EXCEL_CUSTOMER MANAGEMENT LIST11' SHEETNUMBER 1
     …

     (FOC3290) EXL2K: Template file is not a valid WebArchive file
     (FOC009) INCOMPLETE REQUEST STATEMENT BYPASSING TO END OF COMMAND




47
     WebFOCUS Release 7
     Excel Template – Incorrect Template name or extension
        Right template name but .mht file not created and picked up or
        Incorrectly spelled or non-existent template name referenced
     TABLE FILE CAR
     PRINT
           BODYTYPE AS 'Bodytype'
     BY
           HIGHEST COUNTRY AS 'Country'
     BY
           CAR AS 'Car Name'
     BY
           MODEL AS 'Model'
     ON TABLE SUBHEAD
     "CAR MANAGEMENT LIST"
     " "
     WHERE COUNTRY NE 'ENGLAND';
     ON TABLE SET PAGE-NUM OFF
     ON TABLE NOTOTAL
     ON TABLE PCHOLD FORMAT EXL2K TEMPLATE ‘WRONG NAME' SHEETNUMBER 1
     …
      (FOC3289) EXL2K TEMPLATE FILE: Error opening file
      (FOC009) INCOMPLETE REQUEST STATEMENT
      BYPASSING TO END OF COMMAND




48
     WebFOCUS Release 7
     Excel Template – MVS SSCTL Requirements
        Make sure the .MHT file is transferred in ASCII format
        MEMBER in a PDF – Attributes of dataset: RECFM VB and LRECL 512.
     BROWSE CSSRXT.MHT.DATA(TWOTEST) Line 00000000 Col 001 080 ********************************* Top of Data
     **********************************
     MIME-Version: 1.0 X-Document-Type: Workbook
     Content-Type: multipart/related; boundary="----=_NextPart_01C6A5D9.73C5D8B0"
     This document is a Single File Web Page, also known as a Web Archive file. If y

     ------=_NextPart_01C6A5D9.73C5D8B0
     Content-Location: file:///C:/A565A114/twosheet.htm
     Content-Transfer-Encoding: quoted-printable
     Content-Type: text/html; charset="us-ascii"

     <html xmlns:v=3D"urn:schemas-microsoft-com:vml"
     xmlns:o=3D"urn:schemas-microsoft-com:office:office"
     xmlns:x=3D"urn:schemas-microsoft-com:office:excel"
     xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:html=3D"http://www.w3.org/TR/REC-html40"
     ...
       Allocate the dataset containing the EXL2K Template(s) to the DDNAME MHT
       Add allocation to reporting server startup JCL or dynamically in the request
            DYNAM FREE FILE MHT
            DYNAM ALLOC FILE MHT DS CSSRXT.MHT.DATA SHR REU
            TABLE FILE BIGCAR PRINT CAR MODEL SEATS
            BY COUNTRY
            ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'TWOTEST' SHEETNUMBER 1
            END


49
     Creating the Complete Spreadsheet Solution
     Compound Templates
     FILEDEF HOLD1 DISK hold1.mht

     TABLE FILE CENTORD
     SUM LINEPRICE
     BY PRODNAME ACROSS YEAR
     ON TABLE SET PAGE-NUM OFF
     ON TABLE SET BYDISPLAY ON
     ON TABLE HOLD AS HOLD1 FORMAT EXL2K TEMPLATE myTemplt
       SHEETNUMBER 1
     END
     TABLE FILE CENTINV
     SUM QTY_IN_STOCK BY PROD_NUM BY PRODNAME
     ON TABLE SET PAGE-NUM OFF
     ON TABLE SET BYDISPLAY ON
     ON TABLE PCHOLD FORMAT EXL2K TEMPLATE HOLD1 SHEETNUMBER 2
     END



50
     Creating the Complete Spreadsheet Solution
     Compound Templates




51
     Creating the Complete Spreadsheet Solution
     Compound Templates




52
     Creating the Complete Spreadsheet Solution
     Compound Templates
     APP HOLD SESSION
     APP FI COMPXLSH COMPXLSH.MHT
     TABLE FILE CAR
     HEADING
     "Report 1: W Germany"
     PRINT DCOST BY COUNTRY BY CAR
     IF COUNTRY EQ 'W GERMANY'
     ON TABLE HOLD AS COMPXLSH FORMAT EXL2K TEMPLATE 'COMPXLS_SINGLE' SHEETNUMBER 1
     ON TABLE SET STYLE *
     type=report, color=red, $
     type=data, backcolor=yellow, $
     type=heading, color=blue, $
     END
     TABLE FILE CAR
     HEADING
     "Report 2: England"
     PRINT RCOST BY COUNTRY BY CAR BY MODEL
     IF COUNTRY EQ ENGLAND
     ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'COMPXLSH' SHEETNUMBER 2
     ON TABLE SET STYLE *
     type=report, color=yellow, backcolor=black, style=bold, $
     END




53
     Creating the Complete Spreadsheet Solution
     Compound Templates




54
 55
55

								
To top