Comments
Transcript
Output Format Differences Overview Guideline
Guideline Output Format Differences Overview Product(s): IBM Cognos 8 Area of Interest: Reporting Output Format Differences Overview 2 Copyright and Trademarks Licensed Materials - Property of IBM. © Copyright IBM Corp. 2009 IBM, the IBM logo, and Cognos are trademarks or registered trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at http://www.ibm.com/legal/copytrade.shtml While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors or technical inaccuracies may exist. IBM does not accept responsibility for any kind of loss resulting from the use of information contained in this document. The information contained in this document is subject to change without notice. This document is maintained by the Best Practices, Product and Technology team. You can send comments, suggestions, and additions to [email protected] Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both. Adobe, the Adobe logo, PostScript, and the PostScript logo are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States, and/or other countries. Output Format Differences Overview 3 Contents 1 INTRODUCTION ............................................................................................ 4 1.1 1.2 1.3 PURPOSE .............................................................................................................. 4 APPLICABILITY ....................................................................................................... 4 EXCLUSIONS AND EXCEPTIONS .................................................................................... 4 2 RENDERING OVERVIEW ............................................................................... 4 3 OUTPUT FORMATS OVERVIEW ..................................................................... 4 3.1 3.2 3.3 3.3.1 3.3.2 3.3.3 3.4 3.5 HTML ................................................................................................................. 4 PDF.................................................................................................................... 4 EXCEL .................................................................................................................. 5 Excel 2000 single sheet........................................................................................5 Excel 2002..........................................................................................................5 Excel 2007..........................................................................................................5 DELIMITED TEXT .................................................................................................... 6 XML ................................................................................................................... 6 4 OUTPUT FORMAT CHARACTERISTICS .......................................................... 6 4.1 4.2 4.3 4.4 4.4.1 4.4.2 4.4.3 4.5 4.6 4.7 4.8 4.9 4.10 4.11 4.12 4.13 4.14 4.15 4.16 4.17 4.18 4.19 4.20 4.21 COLUMN HEIGHT AND WIDTH..................................................................................... 6 ROWS PER PAGE................................................................................................... 12 HORIZONTAL PAGINATION ....................................................................................... 14 PDF PROPERTIES.................................................................................................. 14 Setting PDF Properties ....................................................................................... 14 Run Options...................................................................................................... 14 Rendering PDF Properties .................................................................................. 14 RENDERING STATIC TABLES ..................................................................................... 15 TEXT ALIGNMENT .................................................................................................. 18 INLINE CONTENT STYLE RESTRICTIONS IN EXCEL ........................................................... 19 STYLES .............................................................................................................. 20 DATA FORMATS .................................................................................................... 22 CHARTS ............................................................................................................. 22 HTML OBJECTS ................................................................................................... 22 RICH TEXT ITEMS ................................................................................................. 23 LINEAGE AND GLOSSARY ......................................................................................... 23 HYPERLINK BUTTONS ............................................................................................. 23 BOOKMARKS ........................................................................................................ 23 TABLE OF CONTENTS ............................................................................................. 24 LOCATING IMAGES FOR RENDERING ........................................................................... 24 BASING CONDITIONAL ACTIONS ON OUTPUT FORMAT ..................................................... 24 FUNCTIONS ......................................................................................................... 24 PERCENT SIZING OF BORDER, PADDING AND MARGIN ..................................................... 24 WORD WRAPPING ................................................................................................. 24 APPENDIX A............................................................................................................ 25 Output Format Differences Overview 4 1 Introduction 1.1 Purpose This document discusses the differences between various report output types from reports rendered within the IBM Cognos 8 environment. 1.2 Applicability The differences outlined in this document apply to: • IBM Cognos 8.4 build 27.6-0 • IBM Cognos 8.3 build 81.20 1.3 Exclusions and Exceptions The Document Type Definition(DTD) provided within Appendix A is only applicable to IBM Cognos 8.4 build 27.6-0. 2 Rendering Overview IBM Cognos 8 uses HTML as the standard rendering model, working to render other formats as closely as possible to that norm while accommodating the specific characteristics and behaviours of each output type. For example, HTML uses a flow model where pages generally re-size automatically for different screen sizes and resolutions, PDF uses a page model where objects are statically positioned on an x-y coordinate and Excel which uses a cell based model. 3 Output Formats Overview The available output types available within IBM Cognos 8 are: HTML, PDF, Excel 2007, Excel 2002, Excel 2000 single sheet, Delimited Text and XML. 3.1 HTML The HTML format is designed primarily for online viewing allowing full interactivity such as drill down and drill through typical of browser based applications. When run interactively, separate HTML pages are created and the user pages up/down through them. When run as a scheduled report, a single large HTML page is created and the user can scroll up/down through the report. 3.2 PDF This format is designed for online viewing, printing and email distribution using a fixed sized page based rendering model. Portrait/landscape orientation can be varied page by page as required. Output Format Differences Overview 5 3.3 Excel This format is designed to allow formatted reports to be viewed in a spreadsheet form. As Excel uses a cell based rendering model there are restrictions on what report objects can be rendered successfully in this format. For a complete list of issues, see the chapter entitled “Limitations When Producing Reports in Microsoft Excel Format” of the Professional Authoring User’s guide. The Excel 2000 and 2002 formats are created as HTML based files, an Excel format designed by Microsoft in an early attempt to integrate desktop and web applications. These formats have known issues with usability, performance and rendering quality. The Excel 2007 uses the new XML based Excel format .XLSX native to Excel 2007. This format alleviates all of the usability and performance issues of the HTML and is in fact even generally smaller than the binary (.xls) format. And, as this is the native format for Excel 2007, much better control over the formatting is available resulting in an improved appearance. 3.3.1 Excel 2000 single sheet In order to render multiple sheets in Excel using the HTML based format, multiple HTML pages must be used. Excel 2000 is a limited HTTP client and therefore we are unable to render multiple HTML pages in a secure environment. This format was designed to operate within that restriction. It should be noted that all page breaks are ignored when rendering to this format in order to ensure only a single sheet is rendered. As Microsoft no longer supports Excel 2000, conformance support for that application has been terminated. So, while this format is still supported for use in newer versions of Excel (2003, 2007), the version of Excel it was designed for is not. This output format was flagged as deprecated in IBM Cognos 8.3 and will be removed in a future release. 3.3.2 Excel 2002 This format will render as many sheets as required for the data using the HTML based format with its requisite issues. Again, the Excel 2007 format is strongly recommended. 3.3.3 Excel 2007 The Excel 2007 format can be read by all Microsoft supported versions of Excel. Excel 2003 requires the Microsoft Compatibility Pack. For more information, see the Proven Practices document “IBM Cognos 8.3 - Excel 2007” found at: http://www.ibm.com/developerworks/data/library/cognos/cognosprovenpractices.ht ml under the Reporting\Workshops section. Output Format Differences Overview 6 3.4 Delimited Text This format is intended to allow the transfer of data to desktop tools such as spreadsheets or databases. By its very nature the delimited text format has no capacity to preserve layout and style information such as font, color and so on. Therefore, this format focuses largely on the data. The list attributes that are preserved are column order and header text. This format can be customized as documented in the Administration and Security Guide Modify Properties for the CSV Output Format section. 3.5 XML This format is intended to allow the transfer of data to XML based tools. This is a pure data format that does not preserve formatting. This output type can also be used as a data source within IBM Cognos 8 Framework Manager. 4 Output Format Characteristics 4.1 Column Height and Width Column heights and widths may differ between the HTML, PDF and Excel outputs. For example, a list (with default dynamically sized columns) will flow into different screen sizes due to the HTML flow model. The list rendered on a wide screen will use the available space. Output Format Differences Overview Re-sizing the browser will result in the browser automatically re-sizing the table, resulting in more rows word wrapping to fit the available space as circled below. 7 Output Format Differences Overview If the browser window it too small, then scroll bars will appear as illustrated in the following screen capture. 8 Output Format Differences Overview 9 When this report is rendering to PDF, the same HTML-like algorithms are applied using the author specified page size (8 ½ x 11" , A4, etc.) as per the screen capture below. But, unlike HTML, once the column width has been determined, it is fixed and PDF Reader browser zoom levels and scroll bars are used to view and navigate the report. This same list rendered in Excel 2007 will use the ‘page size’ specified in the report to determine the maximum width for very wide columns. Output Format Differences Overview 10 When we get to multiple lists, Excel’s cell based approach imposes interesting challenges. Given the scenario illustrated below. One might expect the two lists to have independently sized columns as it would in a HTML output type. The column widths differ between the first and second list. In PDF, this is also the case. Again, the column widths differ between the first and second list. Output Format Differences Overview 11 But, in Excel the column width is fixed for the height of the sheet as illustrated below. Each Column (A, B and C) has a single width resulting in the Quantity column appearing wider than the data requires as the column needs to be wide enough to accommodate the product description. This example allows us to examine another Excel 2007 rendering characteristic. That is, when data is word wrapped to the next line, a cell span is used – that is, the Excel cell is made to extend over two or more rows. This is readily apparent in the Product Description item. A close-up view is shown below. With Excel 2000 and 2003, columns widths are determined by Excel. With Excel 2007, the column width is set by IBM Cognos BI and is limited to the PDF page width. This is done on a column by column basis, not for the entire report. Thus, a report with 3 wide columns would be (at least) 3 times as wide as the PDF page width. Output Format Differences Overview 12 4.2 Rows Per Page When the rows per page value is not set, IBM Cognos 8 will use different values for different formats to maximize the utility of each output type. When the rows per page value is not set, interactive HTML will default to 20 detail rows per page. Output Format Differences Overview 13 When the rows per page value is not set, PDF will render to fill each page to allows the user to consume the maximum information in the fewest pages. Space will be reserved for headers and footer automatically to allow the report to fit the page. When the rows per page value is not set, Excel will render to fill the sheet, spilling over to two or more sheets as required. This fits the Excel model of a single column header per sheet making integration of the data into financial or similar models easier. IBM Cognos 8 will split results across sheets when there are either more than 64K rows or 256 columns, even when dealing with Excel 2007 which is capable of handling larger sheets. By design, settings the rows per page also has different impact on the various formats. In HTML, a large rows per page setting can minimize page down activities. In PDF, we ignore rows per page settings larger than fit the designated page size but smaller settings can be used to force additional page breaks. In Excel we use the page size to split the report across multiple sheets. Output Format Differences Overview 14 4.3 Horizontal Pagination Horizontal pagination is supported in PDF only and allows wide reports to span multiple pages. Horizontal pagination is controlled as a property of list and crosstab objects. When horizontal pagination is disabled, the page will be report will be shrunk uniformly across the height and width to fit the full width of the report onto a single page. If frames are nested, horizontal pagination is applied to one frame only, either the one specified or the outer frame if horizontal pagination is enabled on two or more frames. 4.4 PDF Properties 4.4.1 Setting PDF Properties The xml report specification allows the PDF properties, page size and orientation, to be set at both the report and page level. IBM Cognos Query Studio and IBM Cognos Analysis Studio, which each support only a single page definition set the PDF properties on the report object. IBM Cognos Report Studio allows the author to set the PDF properties for the report using the File menu PDF Properties option or on each page via the PDF Page Set properties. The PDF properties set on the page level override those set at the report level. 4.4.2 Run Options The PDF properties can also be set on the run options when executing a report to override the report level PDF properties only but not the page level properties. IBM Cognos Connection allows the PDF properties to be set in the Report tab of the report properties and when running a report via the advanced options. In IBM Cognos Report Studio the run options can be set via the Run … Run Options… menu. 4.4.3 Rendering PDF Properties PDF properties are ignored when rendering for HTML, Excel 2000 and Excel 2003, Delimited Text, and XML. PDF properties, as the name suggests, are applied when rendering PDF. PDF properties are also applied when rendering Excel 2007 to determine column width and text wrapping. The properties are not applied to the page itself (i.e. when printing from Excel these properties are not automatically set by IBM Cognos BI). Output Format Differences Overview 15 4.5 Rendering Static Tables Static tables are tables created in IBM Cognos Report Studio, not lists and crosstabs which also happen to use HTML tables. Static tables render differently between output types to achieve the best layout for each type. The example below consists of a single column table with three rows. Each row contains a text item and a chart. The text items are circled in red and read Chart 1, Chart 2 and Chart 3 respectively. Output Format Differences Overview When this report is run in HTML, the entire report is rendered as a single page. If the author wanted multiple pages, then they would need to create two pages in IBM Cognos Report Studio with the appropriate content. 16 Output Format Differences Overview As PDF is a print oriented format, IBM Cognos 8 fits the table into the page size, breaking on the table row/column boundaries. The Table Property Pagination can be used to determine if cell contents are split between pages or not. 17 Output Format Differences Overview 18 In Excel, the table will appear similar to the HTML output type, placing the content on a single sheet. 4.6 Text Alignment In the previous examples the text circled in red on a previous page appears at the lower left of the table cell in HTML and PDF but at upper left in Excel. This is because Excel has no concept of a large table cell like HTML and PDF. In addition, there is no cell to align it to; therefore, it appears in its natural position before the chart. Output Format Differences Overview 19 4.7 Inline Content Style Restrictions in Excel Excel does not support inline properties such as borders and colors. In the following example there are three items in the same list column. • Product line code • A text object “: “ • Product line We have formatted Product line code with a border and Product line with a red background color: Both HTML and PDF support the inline block properties. The result in an HTML output type would represent the following screen capture. The PDF output would display as illustrated below. Excel does not support the inline block properties so they are not rendered to that output type. Instead, the cell contents are merged and the inline content styling is lost. The result would represent the following image. Output Format Differences Overview 20 4.8 Styles The vast majority of styles are supported in all output types. However, there may be differences in how they appear in each output. For example, borders may have a slightly different appearance in HTML, PDF and Excel as seen in section 2.8 of this document. There are some styles IBM Cognos 8 does not support in PDF. Those are the Text Flow and Justification: Writing Mode (Top to Bottom, Left to Right). In HTML, this property can be displayed by the following screen capture. When rendered as a PDF output type, this property is ignored completely. The resultant list would display as follows. In Excel 2007, we are currently unable to properly calculate the cell height. As a result the column header will be truncated. Output Format Differences Overview 21 Output Format Differences Overview 22 4.9 Data Formats All data is rendered to HTML and PDF as strings so all data formatting is done by IBM Cognos BI. For example, currency values might be delivered to HTML or PDF as strings such as $123.45, or ¥ 12345 or 123.45 €. However, when rendering to Excel, in order to preserve Excel’s ability to perform calculations on the values, we render numeric and date values as numbers and dates. Most, if not all, numeric formatting supported by IBM Cognos 8 can be rendered in Excel. Dates, however, may cause issues as IBM Cognos 8 supports date formats that Excel does not. The Short, Medium, Long and Full date formats have no meaning in Excel Therefore, while a best effort is made to preserve date formatting, it is not always possible to do so and there may be differences in presentation of date values between HTML/PDF and Excel. 4.10 Charts IBM Cognos 8 has its own charting engine that renders a chart as an image for display in HTML and PDF. In order to preserve the capabilities of Excel, the native Excel charting capabilities are used. This will result in differences in presentation of charts between HTML/PDF and Excel. A very noticeable case is repeating pie charts. The IBM Cognos BI charting engine will plot several pies within the same chart. Excel requires separate charts for each pie. There is a difference in how charts are created in Excel 2000/2002 and Excel 2007. For Excel 2000/2002, the data is stored as part of the chart object. There are limitations as to how much data can be stored in the chart object; this may cause issues when attempting to render very large charts. The Excel 2007 rendering stores the chart data in a hidden sheet thereby avoiding these issues. To see the hidden sheets in Excel 2007, right click on a sheet (tab) and select Unhide and show pages with names like data_Page1_1_1. 4.11 HTML Objects HTML objects are rendered to HTML output only. Output Format Differences Overview 23 4.12 Rich Text Items Rich text items are in effect limited HTML objects which allow a restricted set of HTML styles to be rendered in non-HTML formats such as PDF and Excel. The rich text object would generally be used when there is HTML based data in the query database which you wish to report on. The Rich Text Item has the same general definition as a HTML item. However it only supports a the following restricted set of well formed XHTML: • div • span • ul (unordered list) • ol (orderered list) • li (list item) • tables (table, tr and td elements) • images The full DTD is available within Appendix A. 4.13 Lineage and Glossary Lineage and glossary are supported in HTML output only. 4.14 Hyperlink Buttons Hyperlink buttons operate in HTML, PDF, and Excel 2007. 4.15 Bookmarks Bookmarks allow you to create links to locations in saved HTML, PDF and Excel 2007. Bookmarks are not supported in interactive HTML. Output Format Differences Overview 24 4.16 Table of Contents The table of contents allows you to add a book-like table of contents to a report. The TOC operates in PD and Excel 2007 and is not rendered in other formats. 4.17 Locating Images for Rendering Images must be accessible by the browser to render them in HTML output. The images must be accessible by the IBM Cognos BI server in order to render them in PDF and Excel output. 4.18 Basing Conditional Actions on Output Format Sometimes specific report content is to be rendered in specific output types. For example, consider a report which has a Table of Contents page. As the TOC object will not render in any format other than PDF and Excel 2007, we want to remove the entire TOC page when rendering other formats. We can do this by making rendering dependant on the result reportOutput( ) function. We could for example, set the page Render Variable property to an Boolean expression to ReportOutput () in ( 'PDF', 'spreadsheetML') 1 . This expression will be false for all other formats and the page not rendered. 4.19 Functions The pageCount( ) and pageNumber( ) function will return ? when used in interactive HTML and will return the number of logical pages or current logical page number respectively in Excel 2000 single sheet. The horizontalPageNumber( ) and horizontalPageCount ( ) functions will always return 1 in saved HTML and Excel as horizontal pagination is supported in PDF only. 4.20 Percent Sizing of Border, Padding and Margin Percent sizing of border, padding and margin is not supported in PDF and Excel output types. 4.21 Word Wrapping The rules that determine where line break due to word wrap differ between the output types. PDF line breaks are inserted by IBM Cognos 8 based on the latest ISO standards and are as such subject to occasional updates as the standard matures. The browser vendors and Excel may follow different standard for determining line break and word warp. Output Format Differences Overview 25 Appendix A <!-Copyright (C) 2008 Cognos ULC, an IBM Company. All rights reserved. Cognos (R) is a trademark of Cognos ULC, (formerly Cognos Incorporated). --> <!ENTITY nbsp " "> <!ENTITY iexcl "¡"> <!ENTITY cent "¢"> <!ENTITY pound "£"> <!ENTITY curren "¤"> <!ENTITY yen "¥"> <!ENTITY brvbar "¦"> <!ENTITY sect "§"> <!ENTITY uml "¨"> <!ENTITY copy "©"> <!ENTITY ordf "ª"> <!ENTITY laquo "«"> <!ENTITY not "¬"> <!ENTITY shy "­"> <!ENTITY reg "®"> <!ENTITY macr "¯"> <!ENTITY deg "°"> <!ENTITY plusmn "±"> <!ENTITY sup2 "²"> <!ENTITY sup3 "³"> <!ENTITY acute "´"> <!ENTITY micro "µ"> <!ENTITY para "¶"> <!ENTITY middot "·"> <!ENTITY cedil "¸"> <!ENTITY sup1 "¹"> <!ENTITY ordm "º"> <!ENTITY raquo "»"> <!ENTITY frac14 "¼"> Output Format Differences Overview <!ENTITY frac12 "½"> <!ENTITY frac34 "¾"> <!ENTITY iquest "¿"> <!ENTITY Agrave "À"> <!ENTITY Aacute "Á"> <!ENTITY Acirc "Â"> <!ENTITY Atilde "Ã"> <!ENTITY Auml "Ä"> <!ENTITY Aring "Å"> <!ENTITY AElig "Æ"> <!ENTITY Ccedil "Ç"> <!ENTITY Egrave "È"> <!ENTITY Eacute "É"> <!ENTITY Ecirc "Ê"> <!ENTITY Euml "Ë"> <!ENTITY Igrave "Ì"> <!ENTITY Iacute "Í"> <!ENTITY Icirc "Î"> <!ENTITY Iuml "Ï"> <!ENTITY ETH "Ð"> <!ENTITY Ntilde "Ñ"> <!ENTITY Ograve "Ò"> <!ENTITY Oacute "Ó"> <!ENTITY Ocirc "Ô"> <!ENTITY Otilde "Õ"> <!ENTITY Ouml "Ö"> <!ENTITY times "×"> <!ENTITY Oslash "Ø"> <!ENTITY Ugrave "Ù"> <!ENTITY Uacute "Ú"> <!ENTITY Ucirc "Û"> <!ENTITY Uuml "Ü"> <!ENTITY Yacute "Ý"> <!ENTITY THORN "Þ"> <!ENTITY szlig "ß"> <!ENTITY agrave "à"> <!ENTITY aacute "á"> <!ENTITY atilde "ã"> <!ENTITY auml "ä"> <!ENTITY aring "å"> 26 Output Format Differences Overview <!ENTITY aelig "æ"> <!ENTITY ccedil "ç"> <!ENTITY egrave "è"> <!ENTITY eacute "é"> <!ENTITY ecirc "ê"> <!ENTITY euml "ë"> <!ENTITY igrave "ì"> <!ENTITY iacute "í"> <!ENTITY icirc "î"> <!ENTITY iuml "ï"> <!ENTITY eth "ð"> <!ENTITY ntilde "ñ"> <!ENTITY ograve "ò"> <!ENTITY oacute "ó"> <!ENTITY ocirc "ô"> <!ENTITY otilde "õ"> <!ENTITY ouml "ö"> <!ENTITY divide "÷"> <!ENTITY oslash "ø"> <!ENTITY ugrave "ù"> <!ENTITY uacute "ú"> <!ENTITY ucirc "û"> <!ENTITY uuml "ü"> <!ENTITY yacute "ý"> <!ENTITY thorn "þ"> <!ENTITY yuml "ÿ"> <!ELEMENT div (#PCDATA | div | span | ul | ol | img | table)*> <!ATTLIST div class CDATA #IMPLIED style CDATA #IMPLIED xml:space (default | preserve) "preserve" > <!ELEMENT span (#PCDATA | span)*> <!ATTLIST span class CDATA #IMPLIED style CDATA #IMPLIED xml:space (default | preserve) "preserve" > 27 Output Format Differences Overview 28 <!-For the CSS style, the "list-style-type" attribute supports "circle", "disc", and "square" only. The default is "disc". supported. The "list-style-image" attribute is also --> <!ELEMENT ul (li+)> <!ATTLIST ul class CDATA #IMPLIED style CDATA #IMPLIED > <!-For the CSS style, the "list-style-type" attribute supports "decimal" only. --> <!ELEMENT ol (li+)> <!ATTLIST ol class CDATA #IMPLIED style CDATA #IMPLIED > <!ELEMENT li (#PCDATA | div | span | ul | ol | img | table)*> <!ATTLIST li class CDATA #IMPLIED style CDATA #IMPLIED xml:space (default | preserve) "preserve" > <!ELEMENT img EMPTY> <!ATTLIST img class CDATA #IMPLIED style CDATA #IMPLIED src CDATA #IMPLIED > <!-NOTE: cellpadding="0" will be automatically inserted when rendered. --> Output Format Differences Overview <!ELEMENT table (tr+)> <!ATTLIST table class CDATA #IMPLIED style CDATA #IMPLIED > <!ELEMENT tr (td+ | th+)> <!ATTLIST tr class CDATA #IMPLIED style CDATA #IMPLIED > <!ELEMENT td (#PCDATA | div | span | ul | ol | img | table)*> <!ATTLIST td class CDATA #IMPLIED style CDATA #IMPLIED colspan CDATA #IMPLIED rowspan CDATA #IMPLIED > <!ELEMENT th (#PCDATA | div | span | ul | ol | img | table)*> <!ATTLIST th class CDATA #IMPLIED style CDATA #IMPLIED colspan CDATA #IMPLIED rowspan CDATA #IMPLIED > 29