1 / 55

EXCEL Templates

EXCEL Templates. Renee Teatro Information Builders. 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.

ostinmannual
Download Presentation

EXCEL Templates

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. EXCEL Templates Renee Teatro Information Builders

  2. EXCEL TemplatesAgenda • Overview of the available Excel Format options • Excel Report Bursting • Excel Compound Reports • Excel Templates • Review example using Developer Studio • Compound Excel Templates

  3. Creating the Complete Spreadsheet SolutionOut 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

  4. Creating the Complete Spreadsheet SolutionAvailable Excel formats • HOLD FORMAT EXL2K • Standard Excel output • HTML/XML format Advantages: • Full styling and formatting • Drill-downs • Customized sheet names • Distributable Considerations: • Heavy styling = large file size • No formulas • XHT file extension

  5. Creating the Complete Spreadsheet SolutionAvailable Excel formats • HOLD FORMAT EXL2K FORMULA • Standard Excel output extension • HTML/XML format Advantages: • Full styling and formatting • Drill-downs • Customized sheet names • Distributable • Formulas = ready for analysis Considerations: • Heavy styling lends to large file size • XHT file extension • Extended processing

  6. Creating the Complete Spreadsheet SolutionAvailable Excel formats • HOLD FORMAT EXL2K PIVOT • Unique Excel output extension • Fully functional Excel PivotTable Advantages: • Mini data cube on your desktop • Distributable • Ready for analysis Considerations: • Large file size • Special syntax rules • Limited styling

  7. Creating the Complete Spreadsheet SolutionAvailable Excel formats • HOLD FORMAT EXL97 • Deprecated Excel output • Designed specifically for Excel 97 • HTML based (No XML or CSS) Advantages: • Styling, drill-down • Distributable Considerations: • Heavy styling = large file size • No formulas • No CSS based features • Limited numeric formatting

  8. Creating the Complete Spreadsheet SolutionAvailable Excel formats • HOLD FORMAT EXCEL • Deprecated Excel output • Binary format Advantages: • Opens in all versions of Excel • Extremely light weight • Distributable Considerations: • No styling • No formulas • No drill-downs • No headings/footings

  9. Creating the Complete Spreadsheet SolutionExcel 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

  10. Creating the Complete Spreadsheet SolutionExcel 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

  11. WebFOCUS Release 7Available 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

  12. Creating the Complete Spreadsheet SolutionThe 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

  13. Creating the Complete Spreadsheet SolutionThe 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

  14. WebFOCUS Release 7 - Promote User ProductivityExcel 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.

  15. 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

  16. WebFOCUS Release 7 Excel Templates Data entered manually Data automatically refreshed Replaced sheet

  17. WebFOCUS Release 7Excel 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 WFTMPLTSHEETNUMBER 1

  18. 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

  19. 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

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

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

  22. 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

  23. WebFOCUS Release 7 Excel Template – Example – Create New Procedure

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

  25. WebFOCUS Release 7 Excel Template – Example - Select CenturySales

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

  27. WebFOCUS Release 7 Excel Template – Example – Create DEFINE field

  28. WebFOCUS Release 7 Excel Template – Example – DEFINE in Field List

  29. WebFOCUS Release 7 Excel Template – Example – Add Sortfields to Report

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

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

  32. WebFOCUS Release 7 Excel Template – Example – Run Report

  33. WebFOCUS Release 7 Excel Template – Example – Adjust BYDISPLAY Feature

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

  35. WebFOCUS Release 7 Excel Template – Example – Adjust BYDISPLAY Feature

  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 Report Heading

  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 – Add Excel Template

  43. WebFOCUS Release 7 Excel Template – Example – Run Report – Sheet 1

  44. WebFOCUS Release 7 Excel Template – Example – Run Report – Sheet 2

  45. WebFOCUS Release 7 Excel Template – Example – Run Report – Sheet 3

  46. WebFOCUS Release 7 Excel Template – Example – Run Report – Sheet 4

  47. 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

  48. 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

  49. 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

  50. Creating the Complete Spreadsheet SolutionCompound 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

More Related