EXCEL Templates

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 format Considerations:   HTML/XML Advantages:     Full styling and formatting Drill-downs Customized sheet names Distributable   Heavy styling = large file size No formulas XHT file extension 4 Creating the Complete Spreadsheet Solution Available Excel formats  HOLD FORMAT EXL2K FORMULA  Standard Excel output extension format Considerations:   HTML/XML Advantages:  Full styling and formatting    Drill-downs Customized sheet names Distributable   Heavy styling lends to large file size XHT file extension Extended processing  Formulas = ready for analysis 5 Creating the Complete Spreadsheet Solution Available Excel formats  HOLD FORMAT EXL2K PIVOT  Unique  Fully Excel output extension functional Excel PivotTable Considerations:   Advantages:    Mini data cube on your desktop Large file size Special syntax rules Limited styling Distributable Ready for analysis  6 Creating the Complete Spreadsheet Solution Available Excel formats  HOLD FORMAT EXL97  Deprecated  Designed Excel output specifically for Excel 97  HTML based (No XML or CSS) Considerations:     Advantages:   Styling, drill-down Distributable Heavy styling = large file size No formulas No CSS based features Limited numeric formatting 7 Creating the Complete Spreadsheet Solution Available Excel formats  HOLD FORMAT EXCEL  Deprecated  Binary Excel output format Considerations:     Advantages:  Opens in all versions of Excel No styling No formulas No drill-downs No headings/footings   Extremely light weight Distributable 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"