excel templates n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
EXCEL Templates PowerPoint Presentation
Download Presentation
EXCEL Templates

Loading in 2 Seconds...

play fullscreen
1 / 55

EXCEL Templates - PowerPoint PPT Presentation


  • 690 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'EXCEL Templates' - ostinmannual


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
excel templates

EXCEL Templates

Renee Teatro

Information Builders

excel templates agenda
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
creating the complete spreadsheet solution out of the box
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
creating the complete spreadsheet solution available excel formats
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
creating the complete spreadsheet solution available excel formats1
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
creating the complete spreadsheet solution available excel formats2
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
creating the complete spreadsheet solution available excel formats3
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
creating the complete spreadsheet solution available excel formats4
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
creating the complete spreadsheet solution excel report bursting
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
creating the complete spreadsheet solution excel compound reports
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
webfocus release 7 available excel formats
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
creating the complete spreadsheet solution the best fit for the situation
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
creating the complete spreadsheet solution the best fit for the situation1
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
webfocus release 7 promote user productivity excel templates
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.
webfocus release 7 excel templates benefits
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
webfocus release 7 excel templates
WebFOCUS Release 7 Excel Templates

Data entered manually

Data automatically refreshed

Replaced sheet

webfocus release 7 excel templates how it works
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

webfocus release 7 microsoft mhtml format
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
webfocus release 7 creating an excel ready worksheet
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
webfocus release 7 excel template example existing xls file sheet 11
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
webfocus release 7 excel template must have minimum of 2 worksheets
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

webfocus release 7 excel template incorrect template name or extension
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

webfocus release 7 excel template mvs ssctl requirements
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

creating the complete spreadsheet solution compound templates
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

creating the complete spreadsheet solution compound templates3
Creating the Complete Spreadsheet SolutionCompound 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