IBM Cognos Analysis For Microsoft Excel Version 10.2.2 .
IBM Cognos Analysis for Microsoft ExcelVersion 10.2.2User Guide
NoteBefore using this information and the product it supports, read the information in “Notices” on page 159.Product InformationThis document applies to IBM Cognos Analysis for Microsoft Excel Version 10.2.2 and may also apply tosubsequent releases.Licensed Materials - Property of IBM Copyright IBM Corporation 2009, 2014.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM Corp.
ContentsIntroduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viiChapter 1. What's new? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1New features in version 10.2.2 .New features in version 10.2.0 .Changed features in version 10.2.0New features in version 10.1.1 .New features in version 10.1.0 .Changed features in version 10.1.0.123345Chapter 2. Cognos Analysis for Microsoft Excel . . . . . . . . . . . . . . . . . . 7IBM Cognos Office . . . . . . . . . . . . . . . . . . . . .The Cognos Analysis for Microsoft Excel user interface . . . . . . . . .Getting started with Cognos Office . . . . . . . . . . . . . . .Starting IBM Cognos Office . . . . . . . . . . . . . . . . .Customize IBM Cognos Office . . . . . . . . . . . . . . . .Logging on to an IBM Cognos server . . . . . . . . . . . . . .Clearing the cache . . . . . . . . . . . . . . . . . . . .Update server URLs . . . . . . . . . . . . . . . . . . .Customizing Cognos Analysis for Microsoft Excel . . . . . . . . . .Setting options for IBM Cognos Analysis for Microsoft Excel . . . . . .Set options for exploration sheets . . . . . . . . . . . . . . .Using application settings to optimize your system performance . . . .System optimization with large data sets . . . . . . . . . . . .Controlling data compression . . . . . . . . . . . . . . . .Chunking . . . . . . . . . . . . . . . . . . . . . . .Setting limits on expanding items . . . . . . . . . . . . . . .Working with dimensionally modeled relational and OLAP data . . . .Prompted metadata in packages used for Cognos Analysis for Microsoft ExcelUnderstanding the differences between crosstab, list, and cell-based analysisRecommendations for when to use crosstab, list, or cell-based analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .reports. . . . . 7. 8. 10. 11. 12. 15. 16. 16. 17. 17. 18. 20. 20. 20. 21. 21. 22. 23. 23. 24Chapter 3. Exploring data. . . . . . . . . . . . . . . . . . . . . . . . . . . . 25Creating a crosstab or list exploration . . . . . . . .Specifying the package for your crosstab or list explorationAdding objects to rows and columns . . . . . . . .Selecting measures . . . . . . . . . . . . . .Removing measures from a crosstab exploration . . . .Inserting a hierarchy in a crosstab . . . . . . . . . .Find the items that you need . . . . . . . . . . .Searching for items in the source tree . . . . . . . .Insert items . . . . . . . . . . . . . . . . .Inserting items into a crosstab report from multiple levels ofInsert and display all the items of a level . . . . . .Drill Down and Drill Up . . . . . . . . . . . . .Limit the items in your exploration . . . . . . . . .Custom filters . . . . . . . . . . . . . . .Filter values using context . . . . . . . . . . .Suppressing empty cells in a crosstab or list exploration .Creating a calculation . . . . . . . . . . . . . .Custom sets . . . . . . . . . . . . . . . . .Creating a custom set by using items in a drop zone . .Creating a custom set using items in the source tree . . .Nest rows or columns . . . . . . . . . . . . . .Nest items . . . . . . . . . . . . . . . . . Copyright IBM Corp. 2009, 2014.a. . . . . . . . . . . . . . . . . . . . . . . . . . . .dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26272728292930303132333334343839404141424343iii
Rename a column or row . . . . . . .Reorder columns or rows . . . . . . .Sort rows by values or names . . . . .Show top or bottom results . . . . . .Insert blank or user-defined columns or rowsSwap rows and columns in a crosstab . .Set crosstab options. . . . . . . . .Set list options for a worksheet . . . . .Best practices for working with crosstabs .Best practices for working with lists . . .in. . . . . . . . .a list . . . . . . . . . . .44444545464647474849Chapter 4. Sharing your analysis . . . . . . . . . . . . . . . . . . . . . . . . 51Work with Microsoft Office documents . . . . . . . . . . . . . . . . .Refreshing data in IBM Cognos Analysis for Microsoft Excel workbooks . . . . .Clear cells of content . . . . . . . . . . . . . . . . . . . . . .Convert dynamic data to static data . . . . . . . . . . . . . . . . .Publishing an exploration sheet to IBM Cognos Connection . . . . . . . . .Open, save, and download Microsoft Excel workbooks . . . . . . . . . . .Run an exploration in Report Studio, Analysis Studio, or Cognos Workspace Advanced .Publishing an exploration to IBM Cognos Connection . . . . . . . . . . . .5151515252535555Chapter 5. Using cell-based report formatting . . . . . . . . . . . . . . . . . . . 57Use cell-based reports . . . . . . . . . . . . . . . . .Cell-based reporting . . . . . . . . . . . . . . . .Troubleshooting cell-based analysis . . . . . . . . . . .Convert a crosstab to a cell-based analysis . . . . . . . . .Creating a cell-based analysis without using exploration . . . .Expand items. . . . . . . . . . . . . . . . . . .Drill items . . . . . . . . . . . . . . . . . . . .Displaying the source package . . . . . . . . . . . . .Changing the server and package . . . . . . . . . . . .Best practices for working with Microsoft Excel features in cell-based. . . . . . . . . . . . . . . . . . .analysisChapter 6. Flex views for complex Cognos TM1 reportsFlex views . . . . . . . . . . . . . .Creating a flex view from an exploration sheetCreating a flex view from the source tree . .Replacing or adding members in a flex view .57575859606262626363. . . . . . . . . . . . . . 65.65656666Chapter 7. Contributions to a plan on a Cognos TM1 server . . . . . . . . . . . . . 69Edit data in an exploration sheet . . . . . . .Editing data in exploration sheet cells . . . .Data spreading methods . . . . . . . . .Adding and viewing comments in cells . . . . .Writeback mode and sandboxes . . . . . . .Writeback Mode . . . . . . . . . . . .Toolbar options . . . . . . . . . . . .Sandboxes . . . . . . . . . . . . . .Subsets . . . . . . . . . . . . . . . .Creating a subset by using items in an explorationCreating a subset using items in the source tree .6969707676767878818282Chapter 8. Integrated Cognos TM1 features . . . . . . . . . . . . . . . . . . . . 83Cognos TM1 TurboIntegrator processes . . . . . . . . . . . . . . .Running TurboIntegrator processes within Cognos Analysis for Excel . . . .Cognos TM1 Worksheet functions in IBM Cognos Analysis for Microsoft Excel . .Enable the addin required to work with Cognos TM1 Worksheet functions . .Using Cognos TM1 Worksheet functions in Cognos Analysis for Microsoft ExcelDBR . . . . . . . . . . . . . . . . . . . . . . . . . .DBRA . . . . . . . . . . . . . . . . . . . . . . . . .ivIBM Cognos Analysis for Microsoft Excel Version 10.2.2: User Guide.83838384848586
DBRW .SUBNMVIEW . 86. 87. 88Chapter 9. Examples and use cases . . . . . . . . . . . . . . . . . . . . . . . 89Working with items in a crosstab . . . . . .Working with relational crosstabs . . . . .Sets . . . . . . . . . . . . . . .Crosstab layouts . . . . . . . . . . .Create a nested crosstab report in a crosstab .Example - evaluate revenue from specific orderWorking with items in a list . . . . . . . .Example - create a list report . . . . . . . . . . . . . . . .methods. . . . .8990909092939495Chapter 10. Try it yourself exercises . . . . . . . . . . . . . . . . . . . . . . . 97Create a crosstab report that uses an indirect filter to update data and charts . . . . .Analyze data in a crosstab . . . . . . . . . . . . . . . . . . . . . .Balance sheet reports . . . . . . . . . . . . . . . . . . . . . . . .Creating the balance sheet . . . . . . . . . . . . . . . . . . . . .Populating current assets . . . . . . . . . . . . . . . . . . . . . .Populating other assets . . . . . . . . . . . . . . . . . . . . . .Populating current liabilities . . . . . . . . . . . . . . . . . . . .Populating other liabilities . . . . . . . . . . . . . . . . . . . . .Cleaning up the balance sheet . . . . . . . . . . . . . . . . . . . .Explorations using filters . . . . . . . . . . . . . . . . . . . . . .Creating context filters . . . . . . . . . . . . . . . . . . . . . .Creating custom filters . . . . . . . . . . . . . . . . . . . . . .Creating a custom filter from a list exploration . . . . . . . . . . . . . . .Use sample relational data to create a complex list exploration with a calculated columnAdd values to the worksheet and create filter expressions . . . . . . . . . .Create a crosstab using a custom set of members . . . . . . . . . . . . . .Creating subsets in crosstabs and flex views . . . . . . . . . . . . . . . .Creating a crosstab . . . . . . . . . . . . . . . . . . . . . . .Working in the subset editor . . . . . . . . . . . . . . . . . . . .Creating flex views . . . . . . . . . . . . . . . . . . . . . . . 97. 98. 99. 99. 99. 100. 100. 101. 101. 101. 102. 102. 103. 103. 104. 105. 106. 106. 107. 108Chapter 11. Automating Cognos Analysis for Microsoft Excel. . . . . . . . . . . . 111Setting up the Microsoft application for automationLogging automation activities and errors . . . .IBM Cognos API functions . . . . . . . . .HttpLogonCredentials . . . . . . . . .Logon . . . . . . . . . . . . . . .ClearAllData . . . . . . . . . . . .RefreshAllData . . . . . . . . . . . .RefreshAllDataAndFormat . . . . . . . .UnlinkAllData . . . . . . . . . . . .Publish . . . . . . . . . . . . . .Logoff . . . . . . . . . . . . . . .TraceLog . . . . . . . . . . . . . .SuppressMessages . . . . . . . . . . .ClearCache . . . . . . . . . . . . .UpdateServerUrl . . . . . . . . . . .Example - code for processing within VBA . . .Example - code for processing outside VBA . . .Macro files . . . . . . . . . . . . . .Script files . . . . . . . . . . . . . .Running IBM Cognos TM1 TurboIntegrator functions. . . . . . . . . . . . . . . . . . . . . 111. . . . . . . . . . . . . . . . . . . . . 112. . . . . . . . . . . . . . . . . . . . . 112. . . . . . . . . . . . . . . . . . . . . 113. . . . . . . . . . . . . . . . . . . . . 114. . . . . . . . . . . . . . . . . . . . . 114. . . . . . . . . . . . . . . . . . . . . 115. . . . . . . . . . . . . . . . . . . . . 115. . . . . . . . . . . . . . . . . . . . . 116. . . . . . . . . . . . . . . . . . . . . 116. . . . . . . . . . . . . . . . . . . . . 117. . . . . . . . . . . . . . . . . . . . . 117. . . . . . . . . . . . . . . . . . . . . 117. . . . . . . . . . . . . . . . . . . . . 118. . . . . . . . . . . . . . . . . . . . . 118. . . . . . . . . . . . . . . . . . . . . 119. . . . . . . . . . . . . . . . . . . . . 119. . . . . . . . . . . . . . . . . . . . . 120. . . . . . . . . . . . . . . . . . . . . 120. . . . . . . . . . . . . . . . . . . . . 121Chapter 12. Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . 123Troubleshooting a problem . 123Contentsv
Searching knowledge bases . . . . . . . .Getting fixes. . . . . . . . . . . . .Contacting IBM Support. . . . . . . . .Exchanging information with IBM . . . . .Sending information to IBM Support . . .Receiving information from IBM Support . .Subscribing to Support updates . . . . . .Common errors . . . . . . . . . . .Configuration Issues . . . . . . . . .Processing issues . . . . . . . . . .Security Issues . . . . . . . . . . .Cognos Office Numbered Error Messages . .Cognos Analysis for Microsoft Excel numbered. . . . . . . . . . . . .error. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .messages .125125126127127127128129129134136136138Appendix A. Sample reports and packages . . . . . . . . . . . . . . . . . . . 141The Sample Outdoors Company . . . . . . . .The Sample Outdoors Group of Companies . . . .Employees . . . . . . . . . . . . . . .Sales and marketing . . . . . . . . . . . .Sample Outdoors database, models, and packages . .Samples in the GO Data Warehouse (analysis) packageEmployee Satisfaction Workspace. . . . . . .Return Quantity by Order Method . . . . . .Return Quantity by Product Line Chart . . . .Revenue Data Workspace . . . . . . . . .Samples in the GO Data Warehouse (query) package .Baselines . . . . . . . . . . . . . . .Bursted Sales Performance Report . . . . . .Samples in the Sales and Marketing (Cube) package .Revenue by Product Brand . . . . . . . . .Samples in the GO Sales (analysis) package . . . .Sales Summary . . . . . . . . . . . . .Samples in the GO Sales (query) package . . . . .Horizontal Pagination . . . . . . . . . .No Data . . . . . . . . . . . . . . 48148148148Appendix B. Accessibility features . . . . . . . . . . . . . . . . . . . . . . . 149Keyboard navigation . . . . . . . . .Interface information . . . . . . . . .Increase font size for future sessions. . .View explorations in Windows high contrastVendor software . . . . . . . . . .IBM and accessibility . . . . . . . . . . . . . . .mode . . . . .149153154154154154Appendix C. Rebranding Cognos Office Components . . . . . . . . . . . . . . . 155Resource Files . . . . . . . . .Rebranding or Localizing Cognos OfficeEdit the Resource (.resx) Files . . .Compile the Updated Resource FilesTest Your Work . . . . . . . . . . . .Components . . . . . . . . . . . . .155156157158158Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163viIBM Cognos Analysis for Microsoft Excel Version 10.2.2: User Guide
IntroductionIBM Cognos Analysis for Microsoft Excel is a Microsoft Excel-based tool thatprofessional report authors use to build sophisticated, multiple-sheet,multiple-query reports against multiple databases.AudienceThis guide assumes that you are familiar with IBM Cognos products, such as IBMCognos Business Intelligence, IBM Cognos for Microsoft Office, and IBM CognosTM1 . You should also be familiar with Microsoft Office applications, such asMicrosoft Excel.Finding informationTo find product documentation on the web, including all translateddocumentation, access IBM Knowledge Center sibility featuresAccessibility features help users who have a physical disability, such as restrictedmobility or limited vision, to use information technology products. IBM CognosAnalysis for Microsoft Excel has accessibility features. For information on thesefeatures, see the accessibility section in this document. For more information aboutthe accessibility features in IBM Cognos Analysis for Microsoft Excel, seeAppendix B, “Accessibility features,” on page 149IBM Cognos HTML documentation has accessibility features. PDF documents aresupplemental and, as such, include no added accessibility features.Forward-looking statementsThis documentation describes the current functionality of the product. Referencesto items that are not currently available may be included. No implication of anyfuture availability should be inferred. Any such references are not a commitment,promise, or legal obligation to deliver any material, code, or functionality. Thedevelopment, release, and timing of features or functionality remain at the solediscretion of IBM.Samples disclaimerThe Sample Outdoors Company, Great Outdoors Company, GO Sales, anyvariation of the Sample Outdoors or Great Outdoors names, and Planning Sampledepict fictitious business operations with sample data used to develop sampleapplications for IBM and IBM customers. These fictitious records include sampledata for sales transactions, product distribution, finance, and human resources.Any resemblance to actual names, addresses, contact numbers, or transactionvalues is coincidental. Other sample files may contain fictional data manually ormachine generated, factual data compiled from academic or public sources, or dataused with permission of the copyright holder, for use as sample data to developsample applications. Product names referenced may be the trademarks of theirrespective owners. Unauthorized duplication is prohibited. Copyright IBM Corp. 2009, 2014vii
viiiIBM Cognos Analysis for Microsoft Excel Version 10.2.2: User Guide
Chapter 1. What's new?This section contains a list of new and changed features for this release. It alsocontains a cumulative list of similar information for previous releases. It will helpyou plan your upgrade and application deployment strategies and the trainingrequirements for your users.For information about upgrading, see the IBM Cognos Analysis for Microsoft ExcelInstallation Guide.New features in version 10.2.2IBM Cognos Analysis for Microsoft Excel 10.2.2 has feature enhancements inseveral key areas.Expandable flex viewsYou can now add a row or column to a flex view by typing the name of a memberin a cell next to the last row header or column header.For more
User Guide 2 IBM Cognos Analysis for Microsoft Excel Version 10.2.2: User Guide
IBM Cognos Analysis for Microsoft Excel 10.2.0 has feature enhancements in several key areas: v IBM Cognos Analysis for Microsoft Excel is now integrated with IBM Cognos TM1 data sources. Use IBM Cognos Analysis for Microsoft Excel with IBM Cognos TM1 data sources to enter and write back values to TM1 cubes. For more information, see
2 IBM Cognos Business Intelligence Version 10.1.1: Using IBM Cognos Series 7 and IBM Cognos Business Intelligence in the Same Environment. Chapter 2. Set up Interoperability Between IBM Cognos Series 7 and IBM Cognos BI Set up interoperability before and during migration to provide continuity to your
IBM Cognos ReportNet Model Upgrade 367 Verifying the Model in IBM Cognos ReportNet 367 Opening and Upgrading the Model 368 Verifying and Repairing the Upgraded IBM Cognos ReportNet Model 370 IBM Cognos 8.1 or 8.2 Model Upgrade 374 Verifying the Model in IBM Cognos 8.1 or 8.2 374 Opening and Upgrading the IBM Cognos 8.1 or 8.2 Model 375
With the release of IBM Cognos Business Intelligence V10.1, the IBM Cognos Platform delivered a new 64-bit, in-memory query processing . mode with dynamic query capabilities. IBM Cognos Business Intelligence Dynamic Query is an optional mode included with the Cognos Business Intelligence query service, which can be implemented
Componentes de Cognos Mobile instalados en un sistema . . 33 Componentes de Cognos Mobile instalados en distintos sistemas . . 34 IBM Cognos Analytics con otr os pr oductos de IBM Cognos . . 34 Pr oductos de IBM Cognos que inter operan con IBM Cognos Analytics . . 34 Capítulo 3.
found in the Cognos Connection User Guide, a link to which can be found on the Resources tab in the IIT Cognos portal. 2. About the Cognos Portal The IIT Cognos Portal (aka "Cognos Connection") allows users to access Cognos reports and launch the various Cognos tools for authoring reports and queries based on data stored
WebSphere security and IBM Cognos 8 directly or - more common – for single signon between IBM WebSphere Portal and IBM Cognos 8. 1.2 Applicability While for creation of this document IBM Cognos 8 BI MR2 and IBM WebSphere 6.0.2 were used the technique described in here applies to all versions of IBM Cognos 8.
There are no new features in this release of IBM Cognos Real-time Monitoring Dashboard. New features in version 10.1.1 Listed below are new features for version 10.1.1. Closer integration between IBM Cognos Real-time Monitoring Dashboard and IBM Cognos Business Insight When working with IBM Cognos Real-time Monitoring dashboard objects in IBM
