Query Tutorial 4p54y

TRAINING SERVICES

Cognos 8 BI Ad Hoc Querying Instructor Guide

COGNOS

8

BI

AD

HOC

QUERYING

Cognos 8 BI Ad Hoc Querying

Copyright (C) 2005 Cognos Incorporated.

C88150

Portions of Cognos(R) software products are protected by one or more of the following U.S. Patents: 6,609,123 B1; 6,611,838 B1; 6,662,188 B1; 6,728,697 B2; 6,741,982 B2; 6,763,520 B1; 6,768,995 B2; 6,782,378 B2; 6,847,973 B2.

Part Number: 47996 Published December 2005

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. Cognos does not accept responsibility for any kind of loss resulting from the use of information contained in this document. This document shows the publication date. The information contained in this document is subject to change without notice. Any improvements or changes to either the product or the document will be documented in subsequent editions. U.S. Government Restricted Rights. The software and accompanying materials are provided with Restricted Rights. Use, duplication, or disclosure by the Government is subject to the restrictions in subparagraph (C)(1)(ii) of the Rights in Technical Data and Computer Software clause at DFARS 252.227-7013, or subparagraphs (C) (1) and (2) of the Commercial Computer Software - Restricted Rights at 48CFR52.22719, as applicable. The Contractor is Cognos Corporation, 15 Wayside Road, Burlington, MA 01803. This software/documentation contains proprietary information of Cognos Incorporated. All rights are reserved. Reverse engineering of this software is prohibited. No part of this software/documentation may be copied, photocopied, reproduced, stored in a retrieval system, transmitted in any form or by any means, or translated into another language without the prior written consent of Cognos Incorporated. Cognos and the Cognos logo are trademarks of Cognos Incorporated in the United States and/or other countries. All other names are trademarks or ed trademarks of their respective companies. Information about Cognos Products and Accessibility can be found at www.Cognos.com

IP-2

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

PREFACE

Contents

PREFACE.............................................................................................................................. IP-1 CONTENTS ............................................................................................................................IP-3 COURSE OVERVIEW ..............................................................................................................IP-7 COURSE OUTLINE .................................................................................................................IP-8 INSTRUCTIONAL MATERIALS ..............................................................................................IP-10 GENERAL SETUP AND INSTRUCTOR PREPARATION ..............................................................IP-12 WORK WITH THE VMWARE WORKSTATION ........................................................................IP-13 DOCUMENT CONVENTIONS .................................................................................................IP-16 POWERPOINT TIPS ..............................................................................................................IP-17 VMWARE KEYBOARD SHORTCUTS .....................................................................................IP-18 GET THE CLASS STARTED ...................................................................................................IP-20 POST-CLASS AGENDA .........................................................................................................IP-21 SUBMIT AND LOCATE ADDITIONAL PRODUCT INFORMATION ...........................IP-22 COGNOS PRODUCT HELP .....................................................................................................IP-23 SETUP INSTRUCTIONS .....................................................................................................SI-1 IMPORTANT SETUP INSTRUCTIONS FOR THIS COURSE ...........................................................SI-3 INSTALL MICROSOFT SQL SERVER 2000 ..............................................................................SI-4 MICROSOFT SQL SERVER 2000 SP3 .....................................................................................SI-7 MICROSOFT SQL SERVER KB815495...................................................................................SI-8 ENSURE THAT THE PROPER PATCHES FOR SQL SERVER 2000 ARE INSTALLED ......................SI-9 CREATE THE COGNOS 8 CONTENT STORE ...........................................................................SI-10 RESTORE THE SAMPLE DATABASES ....................................................................................SI-11 SET UP WEB ALIASES ..........................................................................................................SI-13 CONFIGURE COGNOS 8........................................................................................................SI-15 CREATE S IN THIRD-PARTY AUTHENTICATION PROVIDERS.........................................SI-17 DEPLOY COGNOS 8 SAMPLES AND SOLUTIONS ...................................................................SI-18 PUBLISH POWERCUBE AND ADD DATA SOURCES ...............................................................SI-19 SET SECURITY ON POWERCUBE...........................................................................................SI-21 RESTRICT ACCESS TO ISTRATIVE TOOLS ..................................................................SI-22 ADD SPECIFIC S TO QUERY GROUP .....................................................................SI-23 ADD SPECIFIC S TO AUTHORS GROUP .................................................................SI-24 REMOVE EVERYONE FROM COGNOS ROLES ........................................................................SI-25 ENSURE SIMPLE FILE SHARING IS OFF .................................................................................SI-26 STOP DEFAULT SMTP VIRTUAL SERVER ............................................................................SI-27 INTRODUCTION.......................................................................................................................1 COURSE OBJECTIVES .................................................................................................................3

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

IP-3

COGNOS

8

BI

AD

HOC

QUERYING

OVERVIEW OF COGNOS 8 BI ........................................................................................... 1-1 OBJECTIVES ........................................................................................................................... 1-3 REVIEW THE PROCESS FOR DECISION MAKING ...................................................................... 1-4 CORPORATE PERFORMANCE MANAGEMENT SYSTEM ............................................................ 1-5 COGNOS 8 BI - INTRODUCTION .............................................................................................. 1-6 EXAMINE THE STUDIOS IN COGNOS 8 BI ................................................................................ 1-7 OVERVIEW OF COGNOS 8 BI FEATURES ................................................................................ 1-8 REVIEW OPEN DATA ACCESS................................................................................................. 1-9 WORK WITH POWERCUBE DATA IN COGNOS 8 BI................................................................ 1-10 ANALYZE DATA FROM MULTIPLE DATA SOURCES .............................................................. 1-11 WHAT IS DIMENSIONALLY MODELED RELATIONAL METADATA?........................................ 1-12 QUERY OLAP DATA SOURCE .............................................................................................. 1-13 ACCESS ENTERPRISE PLANNING (EP) DATA SOURCES ......................................................... 1-14 IDENTIFY DATA SOURCE TYPES IN THE STUDIOS ................................................................. 1-15 DEMO 1: NAVIGATE DATA IN DIFFERENT STUDIOS .............................................................. 1-16 USE DEEP, LARGE DATA COMPARATIVE ANALYSIS ............................................................ 1-24 DESIGN INFORMATIVE EXECUTIVE DASHBOARDS................................................................ 1-25 PERFORM SELF-SERVICE REPORTING................................................................................... 1-26 MANAGE THE EVENT LIFECYCLE ......................................................................................... 1-27 EXAMINE INTEGRATED METRICS ......................................................................................... 1-28 EXTENDED DATA TRANSFORMATION SERVICES IN DATA MANAGER................................... 1-29 PORTAL INTEGRATION AND SUBSCRIPTION .......................................................................... 1-30 SUMMARY ............................................................................................................................ 1-31 INTRODUCTION TO COGNOS CONNECTION ............................................................. 2-1 OBJECTIVES ........................................................................................................................... 2-3 WHAT IS COGNOS CONNECTION ............................................................................................ 2-4 DISCUSS ROLES AND RESPONSIBILITIES ................................................................................. 2-5 DEFINE REPORTS ................................................................................................................... 2-6 RUN A REPORT ....................................................................................................................... 2-7 SCHEDULE REPORTS .............................................................................................................. 2-8 SCHEDULE A SINGLE REPORT................................................................................................. 2-9 SCHEDULE MULTIPLE REPORTS ........................................................................................... 2-10 VIEW AND MANAGE SCHEDULES ......................................................................................... 2-11 VIEW THE RUN HISTORY OF REPORTS .................................................................................. 2-12 VIEW A REPORT ................................................................................................................... 2-13 VIEW REPORT OUTPUT VERSIONS ......................................................................................... 2-14 DEMO 1: RUN A REPORT AND RENDER THE OUTPUT IN A DIFFERENT LANGUAGE ............... 2-15 DEMO 2: SCHEDULE A REPORT ............................................................................................ 2-19 DEMO 3: SCHEDULE MULTIPLE REPORTS ............................................................................. 2-23 DISTRIBUTE REPORTS .......................................................................................................... 2-29 CREATE REPORT VIEWS ....................................................................................................... 2-30 SPECIFY PROPERTIES OF AN ENTRY...................................................................................... 2-31 SPECIFY HOW CONTENT APPEARS ....................................................................................... 2-32 DEMO 4: CREATE A REPORT VIEW AND SET PREFERENCES.................................................. 2-33 ACCESS EXTERNAL FILES WITH LINKS ................................................................................. 2-38 ORGANIZE CONTENT WITH FOLDERS ................................................................................... 2-39 ORGANIZE CONTENT WITH SHORTCUTS ............................................................................... 2-40 ORGANIZE ENTRIES ............................................................................................................. 2-41 SEARCH FOR ENTRIES .......................................................................................................... 2-42 DEMO 5: ORGANIZE CONTENT IN COGNOS CONNECTION ..................................................... 2-43 GROUP CONTENT INTO A SINGLE VIEW................................................................................ 2-48 EXAMINE PORTAL PAGE CONTENT ...................................................................................... 2-49 DEMO 6: PERSONALIZE A PORTAL........................................................................................ 2-50 ACCESS SHARED PAGES ....................................................................................................... 2-54 IP-4

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

PREFACE

DEMO 7: ACCESS A SHARED PAGE ....................................................................................... 2-55 ENABLE COMMUNICATION BETWEEN PORTLETS .................................................................. 2-58 DEMO 8: ENABLE COMMUNICATION BETWEEN PORTLETS ................................................... 2-59 SUMMARY ............................................................................................................................ 2-63 INTRODUCTION TO QUERY STUDIO ............................................................................ 3-1 OBJECTIVES ........................................................................................................................... 3-3 EXAMINE QUERY STUDIO ...................................................................................................... 3-4 LIST THE PACKAGES AVAILABLE FOR REPORTING ................................................................. 3-5 UNDERSTAND REPORT TYPES ................................................................................................ 3-6 OPEN AD HOC REPORTS ........................................................................................................ 3-7 ADD DATA TO AD HOC REPORTS ........................................................................................... 3-8 VIEW DATA BY USING CHARTS ............................................................................................. 3-9 SAVE AD HOC REPORTS....................................................................................................... 3-10 DEMO 1: EXPLORE QUERY STUDIO ...................................................................................... 3-11 SUMMARY ............................................................................................................................ 3-15 CREATE REPORTS IN QUERY STUDIO ......................................................................... 4-1 OBJECTIVES ........................................................................................................................... 4-3 CREATE AN AD HOC REPORT ................................................................................................. 4-4 DISPLAY DATA WITH LIST REPORTS ...................................................................................... 4-5 CREATE GROUPED LIST REPORTS .......................................................................................... 4-6 SORT DATA IN AD HOC REPORTS .......................................................................................... 4-7 DEMO 1: CREATE AND ENHANCE AN AD HOC REPORT .......................................................... 4-8 SPECIFY COLUMN ORDER IN AD HOC REPORTS ................................................................... 4-13 EXPAND AND COLLAPSE DATA GROUPS .............................................................................. 4-14 DISPLAY CROSSTAB REPORTS .............................................................................................. 4-15 DEMO 2: CREATE AN AD HOC CROSSTAB REPORT............................................................... 4-16 DISPLAY DATA GRAPHICALLY WITH CHARTS ...................................................................... 4-19 SPECIFY A CHART TYPE ....................................................................................................... 4-20 ENHANCE THE DISPLAY OF CHARTS..................................................................................... 4-22 ADD CALCULATED DATA TO AD HOC REPORTS .................................................................. 4-23 CREATE CALCULATED COLUMNS......................................................................................... 4-24 ADD SUMMARIZED DATA TO AD HOC REPORTS .................................................................. 4-25 CREATE NEW REPORT ITEMS WITH CUSTOM GROUPS .......................................................... 4-26 DEMO 3: CALCULATE DATA IN AN AD HOC REPORT............................................................ 4-27 EXAMINE DIMENSIONAL DATA............................................................................................. 4-30 CHANGE THE FOCUS OF A REPORT ....................................................................................... 4-31 VIEW RELATED DATA ........................................................................................................... 4-32 DEMO 4: NAVIGATE DIMENSIONAL DATA............................................................................ 4-33 DEMO 5: INSERT A CALCULATED COLUMN AND DISPLAY A CHART..................................... 4-36 VIEW DATA IN AD HOC REPORTS ........................................................................................ 4-39 PRINT AD HOC REPORTS ...................................................................................................... 4-40 SUMMARY ............................................................................................................................ 4-41 WORKSHOP 1: CREATE A RANKED REPORT ......................................................................... 4-43

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

IP-5

COGNOS

8

BI

AD

HOC

QUERYING

ENHANCE REPORTS IN QUERY STUDIO...................................................................... 5-1 OBJECTIVES ........................................................................................................................... 5-3 HIGHLIGHT REPORT ITEMS USING CONDITIONAL STYLES ...................................................... 5-4 DEMO 1: USE CONDITIONAL FORMATTING ............................................................................ 5-5 FILTER DATA IN AD HOC REPORTS ........................................................................................ 5-9 FILTER DATA BASED ON REPORT DETAILS .......................................................................... 5-10 FILTER DATA BASED ON GROUP SUMMARIES ...................................................................... 5-11 FILTER DATA BASED ON INDIVIDUAL RECORDS .................................................................. 5-12 SPECIFY FILTER CRITERIA WITH PROMPTS ........................................................................... 5-13 USE ADVANCE FILTERS ....................................................................................................... 5-14 DEMO 2: FILTER AND CALCULATE DATA IN AN AD HOC REPORT ........................................ 5-15 DEMO 3: USE A PROMPT TO FILTER DATA ........................................................................... 5-20 DEMO 4: COMBINE FILTERS ................................................................................................. 5-24 SPECIFY ADVANCED OPTIONS.............................................................................................. 5-29 DEMO 5: RUN A REPORT USING ADVANCED OPTIONS ......................................................... 5-30 FORMAT A REPORT .............................................................................................................. 5-34 DEMO 6: FORMAT A REPORT ................................................................................................ 5-35 APPLY A REPORT TEMPLATE ................................................................................................ 5-39 DEMO 7: APPLY COMPANY REPORTING STANDARDS TO A QUERY STUDIO REPORT ............ 5-40 SUMMARY ............................................................................................................................ 5-44 WHAT'S NEXT....................................................................................................................... 1-1 END-TO-END WORKSHOP ................................................................................................A-1

IP-6

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

PREFACE

Course Overview Course Overview Cognos 8 BI Ad Hoc Querying is a one-day, instructor-led course designed for business and professional authors who want to learn how to create, modify and organize ad hoc reports. The course covers how to use different report capabilities and the appropriate use of each, how to graphically display the data, and how to create reports with the same look and feel through the use of templates. The course also covers basic istrative tasks, such as scheduling reports and delivering reports in different formats (HTML, PDF, Excel, etc.).

Intended Audience This course is intended for business and professional authors that want to learn about the features offered in Cognos 8 BI Ad Hoc Querying.

Topics Covered Topics covered in this course include: •

view the different studios in Cognos 8 and highlight key capabilities of Cognos 8 BI

•

use Cognos Connection to schedule, manage, and organize content

•

create and modify reports and charts

•

enhance reports by highlighting exceptions, filtering data and formatting a report

•

customize ad hoc reports using templates

Course Prerequisites Participants should have: •

knowledge of your business requirements

•

experience using the Windows operating system

•

experience using a Web browser

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

IP-7

COGNOS

8

BI

AD

HOC

QUERYING

Course Outline The following table outlines the high-level topics for each module, and the number of slides, demos and workshops included, as well as the estimated teaching time. Module 1: Overview of Cognos 8 BI Topics

•

Review Corporate Performance Management

•

Introduce Cognos 8 BI

•

Examine the different studios in Cognos 8 BI

•

Identify the different data sources within the studios

•

Highlight key capabilities of Cognos 8 BI

Slides

Demos

23

1

Workshops

0

Est. Time

45 mins.

Module 2: Introduction to Cognos Connection Topics

•

Identify the advantages of using Cognos Connection

•

Schedule and manage reports

•

Organize content

•

Include dynamic Web pages

Slides

Demos

Workshops

Est. Time

35

8

0

1.5 hr.

Slides

Demos

Workshops

Est. Time

10

1

0

30 mins.

Module 3: Introduction to Query Studio Topics

•

List the packages available for reporting

•

Examine report types

•

Add and save data to ad hoc reports

•

View data by using charts

IP-8

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

PREFACE

Module 4: Create Reports in Query Studio Topics

•

Create list, grouped list, and crosstab reports

•

List the charts available and their appropriate use

•

Calculate and sort report data

•

Define a custom group to create your own report item

Slides

Demos

Workshops

Est. Time

28

5

1

2 hrs.

Slides

Demos

Module 5: Enhance Reports in Query Studio Topics

•

Highlight report items using conditional styles

•

Focus the scope of a report using filters

•

Format report objects

•

Apply a report template

18

Workshops

7

0

Est. Time

1.5 hrs.

Appendix A: End-To-End Workshop Topics

•

Slides

End-to-end workshop reviewing all topics

NA

Demos

Workshops

0

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1

Est. Time

1 hr

IP-9

COGNOS

8

BI

AD

HOC

QUERYING

Instructional Materials Student Guide The Student Guide contains material that helps to explain features of the product, along with the presentation slides that are presented by the instructor. Student demos and workshops are incorporated in the course to enrich the learning experience through hands-on practice. Demos Demos appear after covering one or more topics or features of the application. While not every product function is demonstrated, participants work with the more important and complex features through a series of tasks. Demo tasks contain a number of steps related to a specific action or feature of the product. Workshops In most of the modules, a supplementary workshop is included. If participants followed the concepts in class without difficulties, they can probably complete the workshop with no additional information. The second section for each workshop contains a task table that identifies each task, where to work in the application, and any applicable hints to help the participants. The third section of the workshop contains screen captures of the expected results. The fourth section contains a step-by-step solution to the workshop. Participants may want to follow these instructions if they are not able to complete the workshop or if they require a little more practice with the application.

VMware Image The VMware image is located on C:\Vm_Images\Courses\C88150 If necessary, you can run Cognos 8 from the host environment. Simply enter a cmd prompt in the Virtual Machine, type IPCONFIG /ALL and obtain the IP Address. Enter this IP Address in the browser of the host environment, as shown below: http:// /cognos8 NOTE: This is only available from the host computer and not available from the classroom workgroup.

IP-10

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

PREFACE

Instructor Guide The Instructor Guide contains the same content presented in the Student Guide, along with additional notes to supplement and add value to the lecture. The information can be generic, non-technical information, such as multiple ways to perform the same command or a more in-depth discussion of a topic. It may also be used to address more technical questions from participants or as supplementary technical discussion, at the discretion of the instructor. It helps to provide the appropriate level of information to a specific audience.

Instructor Data CD The Instructor Data CD contains an executable (EXE) file that can install any or all of the following files. By inserting the CD into your computer and following the prompts as the auto install runs, these files will be installed in C:\Edcognos\C88150. Instructor Slides These files contain the Microsoft PowerPoint slide presentation for each module of the course as presented in the Student Guide: •

StartC88150.ppt

•

Introduction.ppt

•

C8-01-Overview_of_Cognos8_BI.ppt

•

C8-02-Intro_to_Cognos_Connection.ppt

•

C8-03-Intro_to_Query_Studio.ppt

•

C8-04-Create_Rpts_in_Query_Studio.ppt

•

C8-05-Enhance_Reports_in_Query_Studio.ppt

•

C8-A-End_to_End_Workshop.ppt

PDF version of the Instructor Guide These files are the Instructor Guide Microsoft Word documents in PDF. Student Data •

This course has no student data files.

Demos, Workshops, and Workshop Solutions The course is designed to be easily customized for on-site training with customer data. The files contain Microsoft Word files that you can use to modify the demonstrations and workshops as required.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

IP-11

COGNOS

8

BI

AD

HOC

QUERYING

General Setup and Instructor Preparation Pre-Class Agenda To ensure that the class runs smoothly, you should know the answers to the following questions. If you need help in obtaining answers, the customer or customer's sales representative (if the course is scheduled for a client site), or the local office responsible for course logistics. •

Who is the person for class setup?

•

What is the classroom setup? Is there a white board? Is there a flip chart? Is there a computer for the instructor, a PC viewer, overhead projector, and screen?

•

Will the physical environment be set up prior to your arrival (product loaded and PowerPoint files on the computer)?

•

What time does the class start?

•

What hours are available for accessing the teaching site, copying the files to the hard disk, tuning the color on the PC viewer, and so on?

•

What Cognos office is responsible for sending the Student Guides?

•

If the course has been previously taught on the computers you are using, have the Preferences been reset to their defaults, and have student files been deleted?

Prepare to Teach After you have configured the instructor and student computers, consider the following: •

Run through the VMware Image.

•

Run through at least one module in a classroom with a PC viewer.

•

Run through the full course at least once on a computer.

•

Make sure you complete each of the demos before teaching the course so that you become familiar with each step required.

•

Have a set of product reference manuals in the classroom.

•

Make sure that there is a Student Guide for each participant.

IP-12

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

PREFACE

Work with the VMware Workstation This section includes steps to perform the following actions when working with a VM Workstation: •

Start a VMware workstation

•

Open a VMware image

•

Close a VMware image

•

Reset Course Image

•

VMware Image Settings

•

Add addition s to Outlook Express

Start a VMware Workstation. 1. From the Start menu, point to All Programs, VMware, and then click VMware Workstation. VMware Workstation opens. Open a VMware Image. 1. From the File menu click Open and navigate to D:\Vm_images\Courses\ , or from the Favorites list click C88011 2. In the Commands list click Start this virtual machine. The standard Windows logon appears. Note: To maximize the VM screen, press Ctrl+Alt+Enter. If you no longer want the virtual machine screen maximized, press Ctrl +Alt. (See page Error! Bookmark not defined. for more VMware shortcuts.) 3. Log on as name: Student, : Education1!. The VMware image opens. 4. If you are prompted to create a new identifier click OK. Close a VMware Image. 1. From the VMware Windows Start menu click Shut Down. 2. Click OK.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

IP-13

COGNOS

8

BI

AD

HOC

QUERYING

Reset Course Image. Note: You will not have to perform this step yourself after April, 2006. If necessary, complete the following steps to return to the original VMware image: 1. Ensure that your VMware image is shut down. 2. From the VM menu, point to Snapshot, click Snapshot Manager and then click .

3. Click Go To, to revert to this snapshot of the original image. VMware Image Settings See the list below for information about the VMware image settings: •

1,536 MB RAM

•

Bridged

•

Hard disk 12 GB

•

Shared Folders: Enabled Note: For information about the products installed on the VMware image and the way these products have been configured, see the Ree files in the ClassBase, Products, and Courses folders. (Folder locations: D:\VM_image)

IP-14

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

PREFACE

Add Additional s to Outlook Express. If necessary, you can add additional s from the Cognos 8 BI security story to Outlook Express. To add additional s: 1. Open Outlook Express, and then from the Tools menu, click s. 2. Click Import. 3. Navigate to C:\Course_Setup\Outlook_s, select the s you want to add, and then click Open. 4. When you have added the s you require, click Close.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

IP-15

COGNOS

8

BI

AD

HOC

QUERYING

Document Conventions Conventions used in this guide follow Microsoft Windows application standards, where applicable. As well, the following conventions are observed: Bold

Bold style is used in demo and workshop step-by-step solutions to indicate a interface element that is actively selected or text that must be typed by the participant.

Italic

Used to reference book titles.

CAPITALIZATION

All file names, table names, column names, and folder names appear in this guide exactly as they appear in the application. To keep capitalization consistent with this guide, type text exactly as shown.

IP-16

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

PREFACE

PowerPoint Tips Here are valuable keyboard commands you can use to improve your presentation. Command

Key(s)

Advance to next slide

Left-click, Page Down, Space, N, Right or Down Arrow, right-click/Next, Enter

Return to previous slide

Backspace, Page Up, P, Left or Up Arrow, right-click/Previous

Change pointer to a pen

Right-click/Pen or Ctrl+P

Erase drawings on screen

E

Make the screen white

W or ',' (toggle to restore)

Make the screen black

B or '.' (toggle to restore)

Help

?

End the slide show

Esc, Ctrl+Break, '-'

Move between PowerPoint and the product

Alt+Tab or click the application name on the status bar

You can also jump to a specific slide by typing its slide number and pressing the Enter key. However the slide number is not the same as the printed page number because a page may be built from several slides to produce an animation sequence. Important Tips: •

A page containing an animation slide (multiple clicks to complete the slide) will also include an Instructor Guide note indicating the number of clicks needed to complete the slide.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

IP-17

COGNOS

8

BI

AD

HOC

QUERYING

VMware Keyboard Shortcuts Below is a list of shortcuts that can be used when in a VM image. Shortcut

Action

Ctrl-B

Power on.

Ctrl-E

Power off.

Ctrl-R

Reset the power.

Ctrl-Z

Suspend.

Ctrl-N

Create a new virtual machine.

Ctrl-O

Open a virtual machine.

Ctrl-F4

Close the summary/console view for the selected virtual machine. A confirmation dialog appears only if the virtual machine is powered on.

Ctrl-D

Edit the virtual machine's configuration.

Ctrl-G

Grab input from keyboard and mouse.

Ctrl-P

Edit preferences.

Ctrl-AltEnter

Go to full screen mode.

Ctrl-Alt

Return to normal (windowed) mode.

Ctrl-AltTab

Switch among open virtual machines while mouse and keyboard input are grabbed.

Ctrl-Tab

Switch among open virtual machines while mouse and keyboard input are not grabbed. VMware Workstation must be the active application.

IP-18

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

Shortcut

PREFACE

Action

CtrlSwitch among open virtual machines while mouse and keyboard input are Shift-Tab not grabbed. VMware Workstation must be the active application. Ctrl-AltFx

Linux hosts: Switch among open virtual machines while using full screen mode. Fx is a function key corresponding to the virtual machine you want to use. The key combination to use for a virtual machine is shown in the VMware Workstation title bar when that virtual machine is active and in normal (windowed) mode. Windows hosts: For an additional similar functionality, see Using Full Screen Switch Mode.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

IP-19

COGNOS

8

BI

AD

HOC

QUERYING

Get the Class Started Welcome participants to the course. Use the slide show, STARTC88150.PPT, to introduce yourself, the participants, and the agenda for the training (optional). Use this slide to welcome the participants to the course, to introduce yourself, and to mention your background (for example, how long you have been teaching the course, your teaching experience overall, how long you have been working with the product, and so on). Make sure you have customized the slide ahead of time.

Instructor Introduction ƒ Course Name ƒ Instructor Name ƒ Instructor Background

Have the participants take turns introducing themselves with respect to the items listed on this slide. The intent of the slide is to act as an icebreaker and to encourage participation.

Student Introductions ƒ Name ƒ Company ƒ Position ƒ Product Experience ƒ Personal objective(s) for this course

Use this slide to go through the list of istrative items that participants often ask about.

istrative Items ƒ sheet ƒ Smoking ƒ Messages ƒ Telephones ƒ Washrooms ƒ Refreshments ƒ Turn off cell phones and pagers

Class Format ƒ lecture with slides ƒ student guides as reference material

IP-20

ƒ

hands-on demos to learn and practice

ƒ

independent workshop exercises for more practice

Use this slide to explain the class format and emphasize that participants are encouraged to actively perform the hands-on demos while following along with the instructor. Mention that the Student Guide contains copies of the slides and further ing notes for the participants to use as reference material in the future.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

PREFACE

Post-Class Agenda •

Have participants complete the Course Evaluation forms.

•

Hand out certificates.

•

Leave the classroom clean.

•

If you brought any hardware or course media, take them with you when you leave. Erase any files copied to the hard disks of the computers in the classroom. Change the Preferences back to their initial settings.

•

Complete the Instructor form, and return it to the Education Coordinator. Ensure that the Coordinator receives the Course Evaluation forms.

•

If you are at a customer site, thank the course by letter.

•

List any outstanding questions, and ensure that participants receive answers in writing.

•

Report any sales leads to your sales representative.

•

Make notes for yourself about what went well during the course and what needs improvement. When you are preparing for your next teach, you can refer to these.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

IP-21

COGNOS

8

BI

AD

HOC

QUERYING

Submit and Locate Additional Product Information Submit Instructional Designers from the Cognos Education Development team designed these course materials. Your is important and valuable. We are interested in your comments or questions. If you have for a course, you may directly submit it to our online database using the following link: http://ges/ges-. The link to this database can also be found in the Instructor community on myCognos. You may also send general comments or concerns to Global Education Services at [email protected]. In the Instructor Community on myCognos, your suggested course corrections and course-related supplementary information (e.g. additional demos you use, diagrams you created) will be posted under Course Updates, and teaching tips and techniques will be posted under Instructor Tips. This gives other instructors worldwide, immediate use of the information. Instructional designers will also use this information when the course is updated. Access the Instructor Community at http://myCognos. You will also find additional course information (for example, course release status or course development templates) in the Instructor Community, which you may find useful when preparing for courses. Suggestions for significant course enhancements will be reviewed for possible implementation in future course updates.

Additional Product Information You can research product information including issues, and advanced functionality using the Knowledge Base. Access the Knowledge Base through the web at http://trakweb. If you do not already have a name and to access the Knowledge Base, you can send an email request to [email protected]. You can also search the Customer website at www.cognos.com/, for further product information. To obtain a name and , send an email request to [email protected]. Tip: To determine if you already have a name and for the Customer website, visit http://.cognos.com//.

IP-22

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INSTRUCTOR

PREFACE

Cognos Product Help Help type

When to use

Location

Taskoriented

You are working in the product and you need specific task-oriented help.

Cognos Product - Help link

Books for Printing (.pdf)

You want to use search engines to find information. You can then print out selected pages, a section, or the whole book.

Start/Programs/Cognos Product/Documentation

Use Step-by-Step online books (.pdf) if you want to know how to complete a task but prefer to read about it in a book. The Step-by-Step online books contain the same information as the online help, but the method of presentation is different. Cognos on the Web

You want to access any of the following: •

Services and Training Web site

http://.cognos.com

•

Online

http://.cognos.com

•

Cognos Web site

http://www.cognos.com

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

IP-23

COGNOS

IP-24

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI

Setup Instructions

Cognos 8 BI

COGNOS

SI-2

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Important Setup Instructions for This Course Instructor and Student Computer Checklist Use the following checklist when configuring both the instructor and student computers. The total time to set up the instructor computer is 82 minutes, and the total time to set up a student computer is 82 minutes. Setup

Est. Time (per computer)

Install Microsoft SQL Server 2000. See steps on page SI-4.

5 mins

Install Microsoft SQL Server 2000 SP3. See steps on page SI-7.

5 mins

Install Microsoft SQL Server KB815495. See steps on page SI-8.

5 mins

Ensure that the proper patches for SQL Server 2000 are installed. See steps on page SI-9.

2 mins

Perform a default installation of Cognos 8 Business Intelligence Modeling and of Cognos 8 Business Intelligence Server. Install v.8.1.60.27 Nov RTM.

15 mins

Complete

Note: Do not set up the database and do not configure Cognos 8. Create the Cognos 8 Content Store. See steps on page SI-10.

2 mins

Restore the Sample Databases. See steps on page SI-11.

10 mins

Set up Web Aliases. See steps on page SI-13.

2 mins

Configure Cognos 8. See steps on page SI-15.

5 mins

Create s in Third-party Authentication Providers. See steps on page SI-17.

2 mins

Deploy Cognos 8 Samples and Solutions. See steps on page SI-18.

3 mins

Publish a PowerCube and Add Data Sources. See steps on page SI-19.

2 mins

Set security on the Great Outdoors Company PowerCube. See steps on page SI-21.

2 mins

Restrict Access to istrative Tools. See steps on page SI-22.

2 mins

Ensure Simple File Sharing is off. See steps on page SI-26.

1 min

Stop Default SMTP Virtual Server. See steps on page SI-27.

1 min

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-3

COGNOS

8

BI

AD

HOC

QUERYING

Install Microsoft SQL Server 2000 Use these instructions as a guide to install the SQL Server component. Ensure that you are logged onto the machine with a valid , which is the that will be used to ister the machine. Note: If SQL Server 2000 is already installed, go to the section Ensure you have the right patches for SQL Server. 1. 2. 3. 4.

Start the install. Click SQL Server 2000 Components. Click Install Database Server. Click Next.

5. Click Local Computer, and then click Next.

6. Click Create a new instance of SQL Server, and then click Next. 7. Edit the Name, Co., etc., and then click Next.

SI-4

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

8. Accept the License Agreement. 9. Enter the 25-digit CD Key.

10. Click Server and Client Tools, and then click Next. 11. Select Default as the Instance Name, and then click Next.

12. Select a Setup Type of Typical, and then click Next.

13. For Services s, click Use the Local System.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-5

COGNOS

8

BI

AD

HOC

QUERYING

14. Click Next.

15. For Authentication Mode, click Mixed Mode, and type Education1! in the Enter and Confirm boxes. 16. Click Next. 17. Click Next again. The installation continues, and the Microsoft Data Access Components are installed. The complete installation may take a few minutes. 18. Once the install is complete, click Finish.

SI-6

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Install Microsoft SQL Server 2000 SP3 Use these instructions as a guide to install the SQL Server Service Pack 3. Ensure that you are logged onto the machine with a valid , which is the that will be used to ister the machine. 1. 2. 3. 4. 5.

Start the install. Click Next on the Welcome page. Click Yes on the Software Agreement License page. Click Next on the Instance Name page. On the page Connect to server, check The SQL Server system information (SQL Server authentication). Enter the Education1! in the textbox below. Click Next to continue. 6. Check Upgrade Microsoft Search and apply SQL Server 2000 SP3 (required), and then click Continue.

7. Click OK on the Error Reporting page. 8. Click Next on the Start Copying Files page. 9. Reboot the machine when prompted.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-7

COGNOS

8

BI

AD

HOC

QUERYING

Install Microsoft SQL Server KB815495 1. You have to install the next patch if you are inside the Cognos network. This patch is required to eliminate the problem with a virus. 2. Start the program. The name of the file is SQL2000-KB8154958.00.0818-ENU.exe. 3. Click Next on the page Hotfix Installer. 4. Accept the Licensing agreement. 5. Select the Instance that you want to upgrade. The program usually selects the local machine. 6. Click SQL Server Authentication on the page Authentication Mode and type Education1! as the in the text box. 7. Click Install.

SI-8

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Ensure that the Proper Patches for SQL Server 2000 are Installed Use the following instruction to that you have the proper patches installed on the machine. Not having the proper patch level will prevent Cognos 8 to create or use the content store. Also, a patch is mandatory due to previous experience with a Virus. 1. From the Start menu, click All Programs, then click Microsoft SQL Server and then click Query Analyzer. 2. Logon as sa on the database, with the Education1!. 3. From the Query window, type select @@version (make sure there is a space after “select”), and then press the F5 key to run the query. 4. Expand the column to see the version of MS SQL Server. The portion that you are interested in is bolded below. Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1) •

If you have build 8.00.194 then you have to install SP3A and KB815495.

•

If you have build 8.00.769 then you have to install KB815495.

• If you have build 8.00.818 then you have the required patches. 5. Close SQL Query Analyzer.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-9

COGNOS

8

BI

AD

HOC

QUERYING

Create the Cognos 8 Content Store Task 1. Create the Cognos 8 Content Store. 1. From the Start menu, point to All Programs, Microsoft SQL Server, and then click Enterprise Manager. 2. Expand Microsoft SQL Servers and SQL Server Group. 3. Expand <servername>, and then expand Databases. 4. Right–click Databases, and then click New Database. The Database Properties dialog box appears. 5. In the Name box, type cm. 6. In the Collation name list, select Latin1_General_CI_AS, and then click OK. 7. Leave Enterprise Manager open for the next step.

SI-10

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Restore the Sample Databases Task 1. Add the databases. 1. While still in Microsoft SQL Server Enterprise Manager, right-click Databases, and then click New Database. 2. In the Name box, type GOSL, and then click OK. 3. Repeat steps 1 and 2 to add two new databases called GORT, and GOSLDW. Task 2. Restore the databases. 1. Right–click the GORT database, point to All Tasks, and then click Restore Database. 2. In the Restore database dialog box, click From device. 3. Under Parameters, click Select Devices. 4. In the Choose Restore Devices dialog box, click Add. 5. In the Choose Restore Destination dialog box under File name, click the Ellipsis and browse to :\Program Files\cognos\c8\webcontent\samples\ datasources\sqlserver\GORT, and then click OK. 6. Click OK, and then click OK again. 7. In the Restore Database dialog box, click the Options tab. 8. Select the Force restore over existing database check box. 9. In the Restore database files as pane, ensure the Restore as path for both files in the Move to physical file name column points to the location of the GORT database. If you chose the default location when you created the database, the path should read, <SQL Sever location>\MSSQL\Data\GORT.mdf for the first file, and <SQL Sever location>\MSSQL\Data\GORT_log.ldf for the second file.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-11

COGNOS

8

BI

AD

HOC

QUERYING

Please note: The GORT.mdf file may be called GORT_Data.mdf. The result should appear similar to the one shown below:

In the screen capture, the data file is listed first in the logical file name column, followed by the log file. If the positions are reversed (in other words, if the log file is listed first), ensure that each logical file name points to the correct physical file location. 10. Click OK. A message appears saying the restoration was successful. 11. Click OK to close the message. 12. Repeat Task 2 to restore the databases for GOSLDW, and GOSL ensuring the logical files are pointing to the GOSLDW, and GOSL database. 13. Close SQL Server Enterprise Manager.

SI-12

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Set up Web Aliases Task 1. Set up alias for Cognos 8. 1. From the Control , open istrative Tools, and then open Internet Information Services. 2. In Internet Information Services, expand <servername>, and then expand Web Site. 3. Expand Default Web Site, right–click Default Web Site, point to New, and then click Virtual Directory. The Virtual Directory Creation Wizard appears. 4. Click Next. 5. Under Alias, type cognos8, and then click Next. 6. Browse to :\Program Files\ cognos\c8\webcontent, click OK, and then click Next. 7. Clear the Run scripts check box, so only Read is selected, and then click Next. 8. Click Finish. Task 2. Set up virtual directory for cgi–bin. 1. Right–click the cognos8 virtual directory folder, point to New, and then click Virtual Directory. The Virtual Directory Creation Wizard appears. 2. Click Next. 3. Under Alias, type cgi–bin, and then click Next. 4. Browse to :\Program Files\cognos\c8\cgi–bin, click OK, and then click Next. 5. Select the Execute check box and de-select Read and Run scripts and then click Next. For security reasons, you should not leave Read on cgi-bin alias. 6. Click Finish.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-13

COGNOS

8

BI

AD

HOC

QUERYING

Task 3. Set up virtual directory for help. 1. Right–click the cognos8 virtual directory folder, point to New, and then click Virtual Directory. The Virtual Directory Creation Wizard appears. 2. Click Next. 3. Under Alias, type help, and then click Next. 4. Browse to :\Program Files\cognos\c8\ webcontent\documentation, click OK, and then click Next. 5. Clear the Run scripts check box, so that only Read is selected, and then click Next. 6. Click Finish. Task 4. Give browse access to images. 1. Expand the samples folder. 2. Right-click images, and then click Properties. 3. In the images Properties dialog box under the Directory tab, select the Directory browsing check box. 4. Click OK. 5. Close Internet Information Services and istrative Tools.

SI-14

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Configure Cognos 8 Task 1. Configure the NTLM provider. 1. From the Start menu, point to All Programs, Cognos 8, and then click Cognos Configuration. 2. In the Explorer pane of Cognos Configuration, right-click Authentication, point to New resource, and then click Namespace. 3. In the New Namespace dialog box, under Name, type Local NT, and then from the drop-down list under Type, click NTLM. 4. Click OK. The results appear as follows:

5. Click the cell in the Namespace ID row and the Value column, and type Local NT ID, and then press Enter. 6. In the Explorer pane under Authentication, click Cognos. 7. To the right of Allow anonymous access, click True in the Value column, and then click False to change the setting. With Anonymous access now set to False, students will need to to Cognos Connection and provide a name and . 8. Leave Cognos Configuration open.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-15

COGNOS

8

BI

AD

HOC

QUERYING

Task 2. Configure the Cognos 8 Components. 1. In Cognos Configuration, in the Explorer pane under Cognos 8 service, click Cognos 8. 2. In the Maximum memory in MB row, change the value to 512. This property specifies the maximum amount of memory in MB that can be used by the ReportNet dispatcher. The value specified here determines the amount of memory used by the Java Virtual Machine and depends on how much memory is available. If you are using a computer that has a lower amount of RAM, increasing this value will improve performance. 3. Expand Content Manager, and click Content Store. 4. Click the ID and row, and then click the Edit button. The Value - ID and dialog box appears. 5. In the field and the Confirm field, type Education1!. 6. Click OK when the test is complete. 7. Click Save configuration

to save the new configuration.

8. Click Close to close the Cognos Configuration dialog box after the configuration is saved and all items have green check marks beside them. 9. Click Start

to start the Cognos Series 8 service.

10. When all services are started, click Close. 11. Close Cognos Configuration.

SI-16

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Create s in Third-party Authentication Providers Task 1. Create s in the NTLM authentication provider. 1. From the Start menu, click Control , and then click istrative Tools. 2. Click Computer Management. 3. Expand Local s and Groups. 4. Right-click s, and then click New . 5. In the New dialog box, in the name box, type and then in the and confirm boxes, type Education1!. 6. Clear the must change at next logon check box, select the never expires check box, and then click Create. 7. Repeat steps 4 to 6 to create the following three s: id: brettonf, : Education1! id: johnsone, : Education1! id: kunzej, : Education1! id: uragomek, : Education1! id: wrightc, : Education1! 8. Click Close to close the dialog box. 9. In the left pane, click s to ensure that the s have been added. 10. Close Computer Management. 11. Close Control .

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-17

COGNOS

8

BI

AD

HOC

QUERYING

Deploy Cognos 8 Samples and Solutions Task 1. Prepare for deployment. 1. Start Windows Explorer. 2. Navigate to :\Program Files\cognos\c8\webcontent\samples\content. 3. Copy the Cognos_Samples.zip file and Cognos_PowerCube.zip. 4. Navigate to :\Program Files\cognos\c8\deployment, and then paste the zip files. Task 2. Import the samples. 1. Open your browser, and then in the Address box, type localhost/cognos8, and press Enter. 2. In the ID box, type , in the box, type Education1!, and then click OK. 3. From the Cognos 8 welcome screen, click Cognos Connection. 4. From the Tools menu, click Content istration. 5. On the toolbar, click New Import. 6. On the Select a deployment archive page, ensure the Cognos_Samples archive is selected, and then click Next. 7. On the Specify name and descriptions page, click Next. 8. On the Select the public folders content page, click GO Sales and Retailers, Query Studio Templates, GO Sales and GO Data Warehouse. 9. Clear the Disable after import check box beside all of the folders selected, and then click Next. 10. On the Specify the general options page, click Next. 11. On the Review the summary page, click Next. 12. On the Select an action page, click Save and run once, and then click Finish. 13. On the Run with options page, ensure Now is selected , click Run, and then click OK. 14. Repeat steps 4 to 13, selecting Cognos_PowerCube archive for import, and Great Outdoors Company on the Select the public folders content page. 15. Leave your browser open.

SI-18

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Publish a PowerCube and Add Data Sources Task 1. Create data sources. 1. In Cognos Connection, from the Tools menu, click Directory. 2. On the Directory page, click the Data Sources tab. 3. Click New Data Source

.

4. Under Name, type gosales, and then click Next. 5. Under Type, select Microsoft SQL Server (OLE DB), and then click Next. 6. In the Server name box, type localhost, and then under Database name, type GOSL. 7. Under Signon, select the check box.

8. In the ID box, type sa, and in the and Confirm boxes, type Education1!, and then click Finish. 9. Repeat steps 3 to 8 to create the following data sources: a. Data source name = go_data_warehouse, Database name = GOSLDW b. Data source name = goretailers, Database name = GORT Task 2. Create cube data source. 1. Click New Data Source

.

2. Under Name, type Great_Outdoors_Company, and then click Next. 3. Under Type, select Cognos PowerCube, and then click Next. 4. In the Windows location box, type the following C:\Program Files\cognos\c8\webcontent\samples\datasources\cubes\ PowerCubes\En\Great_Outdoors_Company.mdc

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-19

COGNOS

8

BI

AD

HOC

QUERYING

5. Under Testing, click Test the connection, and then click Test. If the test failed, ensure that you typed everything correctly in step 4 and repeat testing your connection. 6. Click Close, and then click Close again. 7. Click Finish. 8. Click Return. 9. Leave Cognos Connection open.

SI-20

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Set Security on the Great Outdoors Company PowerCube 1. Click Set properties beside Great Outdoors Company. 2. Click the Permissions tab. 3. Select the Override the access permissions acquired from the parent entry checkbox. 4. From the list of available entries, select the check box beside Authors. 5. On the Set properties page, ensure that under Grant, only the Read, Write, Execute, and Traverse check boxes are selected. If you had to make any changes, click Apply. 6. Under Option, select the Delete the access permissions of all child entries check box. 7. Click OK. 8. Leave Cognos Connection open.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-21

COGNOS

8

BI

AD

HOC

QUERYING

Restrict Access to istrative Tools 1. In Cognos Connection, from the Tools menu, click Directory. The Directory page shows two namespaces, Cognos and Local NT. 2. Under the Name column, click Cognos, and then beside the System s role, click Set properties. 3. On the Set properties – System s page, click the tab. 4. Click Add, select the Show s in the list check box, and then click Local NT. 5. Select the Person() check box, click the green arrow and then click OK. 6. Select the Everyone check box, click Remove, and then click OK. 7. Leave Cognos Connection open.

SI-22

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Add Specific s to Query Group 1. In Cognos Connection, from the Tools menu, click Directory. The Directory page shows two namespaces, Cognos and Local NT. 2. Under the Name column, click Cognos, and then beside the Query s role, click Set properties. 3. On the Set properties – Query s page, click the tab. 4. Click Add, select the Show s in the list check box, and then click Local NT. 5. Select Corey Wright (wrightc) check box, and click the green arrow to add the s to the Selected entries box. 6. Click OK. 7. Select the Everyone check box, click Remove, and then click OK. 8. Leave Cognos Connection open.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-23

COGNOS

8

BI

AD

HOC

QUERYING

Add Specific s to Authors Group 1. In Cognos Connection, from the Tools menu, click Directory. The Directory page shows two namespaces, Cognos and Local NT. 2. Under the Name column, click Cognos, and then beside the Authors role, click Set properties. 3. On the Set properties – Authors page, click the tab. 4. Click Add, select the Show s in the list check box, and then click Local NT. 5. Select Frank Bretton (brettonf), Jorg Kunze (kunzej), Kazumi Uragome (uragomek) and Estelle Johnson (johnsone) and click the green arrow to add the s to the Selected entries box. 6. Click OK. 7. Select the Everyone check box, click Remove, and then click OK. 8. Leave Cognos Connection open.

SI-24

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Remove Everyone from Cognos Roles 1. In Cognos Connection, from the Tools menu, click Directory. The Directory page shows two namespaces, Cognos and Local NT. 2. Under the Name column, click Cognos. 3. Beside the Analysis s role, click Set properties. 4. On the Set properties – Analysis s page, click the tab. 5. Select the Everyone check box, click Remove, and then click OK. 6. Repeat steps 3 to 5 for the following roles: Controller s Data Manager Authors Metrics Authors Metrics s Planning s 7. Close Cognos Connection.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-25

COGNOS

8

BI

AD

HOC

QUERYING

Ensure Simple File Sharing is Off 1. From the Start menu, click Control , and then click Folder Options. 2. Ensure the View tab is selected, and then deselect the Use Simple File Sharing (recommended) check box.

Instructional Tips Note: Use Simple File Sharing (recommended) may already be deselected.

3. Click OK. 4. Close Control .

SI-26

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SETUP

INSTRUCTIONS

Stop Default SMTP Virtual Server 1. From the Start menu, right-click My Computer, and then click Manage. The Computer Management dialog box opens.

Instructional Tip Note: the Default SMTP Virtual Server might already be stopped.

2. Expand Services and Applications, expand Internet Information Services. 3. Right click Default SMTP Virtual Server and click Stop. 4. Close Computer Management.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

SI-27

COGNOS

SI-28

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Introduction

Cognos 8 BI

COGNOS

Intro-2

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

Course Objectives ƒ In this course, we will: ƒ

view the different studios in Cognos 8 and highlight key capabilities of Cognos 8 BI

ƒ

use Cognos Connection to schedule, manage, and organize content

ƒ

create and modify reports and charts

ƒ

enhance reports by highlighting exceptions, filtering data and formatting a report

ƒ

customize ad hoc reports using templates

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Intro-3

COGNOS

8

BI

AD

HOC

QUERYING

Overview of Cognos 8 BI Introduction to Cognos Connection Introduction to Query Studio Create Reports in Query Studio Enhance Reports in Query Studio End-to-End Workshop

Throughout this course, Query Studio concepts and procedures are grouped into modules that are presented in a logical and structured manner. The hands-on demonstrations and workshops provide the knowledge and skills you will require to create and modify reports in Query Studio. Audience: •

business and professional authors

Prerequisites: •

knowledge of your business requirements

•

experience using the Windows operating system

•

experience using a Web browser

Intro-4

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1

Overview of Cognos 8 BI

Cognos 8 BI

COGNOS

1-2

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

OF

COGNOS

8

BI

Objectives ƒ In this module, we will: ƒ

review Corporate Performance Management

ƒ

introduce Cognos 8 BI

ƒ

examine the different studios in Cognos 8 BI

ƒ

identify the different data sources within the studios

ƒ

highlight key capabilities of Cognos 8 BI

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1-3

COGNOS

8

BI

AD

HOC

QUERYING

Additional Information When you ask the question, "How are we doing?," you are really asking "Am I on or off track?" We can answer that question with a scorecard or dashboard to provide you with an aggregate view of the necessary information.

Review the Process for Decision Making

How are we doing?

Why are we doing it?

What should we be doing?

When you want to find answers to the question "Why?," you are really asking "Why am I on or off track?". You require reports and the ability to analyze to find the answers. Scorecarding & Dashboarding

Reporting & Analysis

The question "What should we be doing?” means "How do we plan to get where we want to be?" We set objectives and targets so that we can get back on course when changes occur.

High-frequency Planning

Single Platform for Decision Making

•

1-4

When decision makers want to find answers, they ask questions in three fundamental areas: •

How are we doing?

•

Why are we doing it?

•

What should we be doing?

•

Most companies use many different tools and systems to provide the answers to these questions. The result of this fragmented approach is a slow, disconnected decision-making process with low end- satisfaction and high costs to the organization.

•

The answers to these three questions need to feed into a single platform to allow for a better decision-making process. Finding this single platform has been a challenge until Cognos 8 Business Intelligence (BI).

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

Corporate Performance Management System PERFORMANCE MANAGEMENT SOLUTIONS

Industry Solutions

Enterprise Performance Solutions

BEST PRACTICES How are we doing?

Why are we doing it?

What should we be doing?

PERFORMANCE MANAGEMENT PLATFORM

Metrics Management

Dashboarding

Query & Reporting

Analysis

Event Management

Planning

PLATFORM SERVICES

Portal

Security

Configuration

istration

Information & Business Modeling

Data Integration

Operational or Star Schema

OLAP

Enterprise Applications

OF

COGNOS

8

BI

Additional Information Having all the critical capabilities to manage performance means that we can look at a single set of metrics across all processes. When we look at a metric, it is based on the same calculation, the same roll up, the same assumptions as the plans and the reports - they are the same numbers. The metrics are completely interrelated so decision-makers can quickly and consistently find answers to critical questions that drive performance. Enterprise applications can cover a wide variety of enterprise software, some of which could be considered relational data sources (for example, Oracle Financials).

Flat, Modern and Legacy

•

The Cognos Corporate Performance Management (M) system lets you answer all three questions from one single platform (Cognos 8 BI).

•

This single platform includes: •

World-class scorecarding and dashboarding (Metric Studio and Report Studio), which answers "How are we doing?"

•

Reporting and analysis (Report Studio, Query Studio, Analysis Studio and Event Studio), which answers "Why are we doing it?"

•

Planning (Contributor, Analyst, Controller), which answers "What should we be doing?"

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1-5

COGNOS

8

BI

AD

HOC

QUERYING

Cognos 8 BI - Introduction ƒ Cognos 8 BI is a complete enterprise business intelligence software solution with integrated reporting, analysis, scorecarding, and event management capabilities.

1-6

•

Cognos 8 BI leverages a modern single architecture using open Web services standards that meet the combined demands of IT and key business stakeholders.

•

Cognos 8 BI takes decision-making to the next level of efficiency, simplicity, and cost effectiveness, which makes your job easier to perform.

Additional Information Cognos 8 BI provides you with business intelligence across multiple applications and data sources, and provides IT with solution control, security, and management.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

Examine the Studios in Cognos 8 BI ƒ Metric Studio helps you manage the performance of your organization by monitoring and analyzing metrics at all levels. ƒ Query Studio helps you quickly answer a focused question. ƒ Analysis Studio performs simple to complex analyses and comparisons of data to discover trends, risks, and opportunities. ƒ Report Studio builds sophisticated reports, including multi-page, multiple-query reports against multiple data sources. ƒ Event Studio notifies s of key operational or performance-related events that may affect their business.

•

In Metric Studio, you can easily track your business by viewing metrics, asg tasks for poorly performing metrics, and linking to reports containing related information.

•

In Query Studio, you can create simple queries and reports, and apply basic formatting and templates.

•

In Analysis Studio, you can analyze large volumes of data from all business dimensions through ranking, complex filtering, and so on, regardless of the data source.

•

In Report Studio, you can create professional reports, including visually engaging dashboards with gauges, charts, maps and other graphical elements. Fully automated drill-through capabilities are available in the reporting interface.

•

In Event Studio, you can create agents that check the data. When an agent detects a particular condition, Event Studio takes action by generating emails, running reports, updating a portal news channel, or communicating with other software.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OF

COGNOS

8

BI

Additional Information Dimensionally modeled relational metadata is data taken from a relational source and modeled as a star schema, as well as having hierarchies applied to allow for drill behaviour. Cognos 8 BI delivers basic drill down and drill up functionality to all s in Query Studio, Analysis Studio, and Report Studio, so they are not restricted to one studio over the other to perform fundamental tasks. Use these studios in conjunction to effectively monitor performance at all levels of your organization.

1-7

COGNOS

8

BI

AD

HOC

QUERYING

Additional Information Cognos 8 BI s normalized and relational OLAP data, OLAP servers, and modern XML-based data sources. All of our end functionality is equally available for all data sources.

Overview of Cognos 8 BI Features ƒ Cognos 8 BI features include:

•

ƒ

open data access

ƒ

integrated enterprise planning and consolidation

ƒ

dimensional reporting and dashboarding

ƒ

deep, large data comparative analysis

ƒ

extended self-service reporting

ƒ

process integration and Event Lifecycle Management

ƒ

actionable scorecards in Metric Studio

ƒ

extended data transformation services in Data Manager

ƒ

integrated portal

Cognos Enterprise Planning and Cognos Controller are integrated with the full Cognos 8 BI platform. This integration enables you to create reports, analyses, and scorecards, based on the data in your plans and your consolidated ledgers. Through dimensional reporting and dashboarding, Cognos extends the market-leading ReportNet with dimensional reporting and dashboarding.

The key capabilities of Cognos 8 BI let you effectively and efficiently query, analyze, and report on your business data.

Enhanced PowerPlay Web functionality for direct exploration and analysis allows for deep, large data comparative analysis. Enhancements in Query Studio to extend the scope of use, such as formatting, conditional formatting, and templates, to provide an extended self service reporting. Event Lifecycle Management provides notification to facilitate required actions to closed-loop decision-making. You can now edit the individual metrics to add action items to facilitate keeping the metric on track.

1-8

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

Report Studio

Cognos Viewer

Analysis Studio

Event Studio

Metric Studio

Data Access

SQL

Relational

MDX

XML

Files

Cubes

ODBC

Composite

Metric Database

More

•

COGNOS

8

BI

Additional Information Framework Manager is the metadata modeling tool at the data access layer that facilitates accurate and predictable access to the underlying data sources.

Review Open Data Access

Query Studio

OF

Cognos 8 BI offers full BI capabilities regardless of how your data is stored. These include reporting for OLAP sources and analysis for relational sources.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Some of the data sources ed include: • Oracle • IBM (DB2, UBD, Informix, Intelligent Minor) • Microsoft (SQL Server, SAS) • Sybase • ODBC • Siebel • SSA • SAP BW Note: You can analyze relational data in Analysis Studio but it must be modeled dimensionally (as discussed later in this module).

1-9

COGNOS

8

BI

AD

HOC

QUERYING

Work with PowerCube Data in Cognos 8 BI ƒ You can work with your PowerCube data in all the studios in Cognos 8 BI. Query Studio

Report Studio

Cognos Viewer

Analysis Studio

Event Studio

Metric Studio

Data Access

Cognos PowerCube

•

1-10

Cognos 8 BI lets you make the most of the multi-dimensional data stored in your organization’s PowerCubes.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

Analyze Data From Multiple Data Sources ƒ Use Analysis Studio to analyze data in dimensional data sources (for example, PowerCubes, MSAS, and SAP BW) or relational data sources that are modeled dimensionally.

OLAP data source

Relational data modeled dimensionally

OF

COGNOS

8

BI

Additional Information With multidimensional analysis, your corporate data is organized in the way you think about your business, so that you spend more time on value added analysis, rather than on data retrieval. You can find answers to your business questions by navigating through the data to determine successes and failures, uncover trends, discover opportunities, and find weaknesses. Multidimensional analysis is a process where you selectively extract and evaluate multiple aspects of your business against your key performance indicators for the purpose of decision . Other industry standard OLAP sources that are ed are:

•

Analysis Studio has native access and extended features for Cognos OLAP sources and other industry standard OLAP sources.

• MSAS (Microsoft Analysis Services) – attributes, named sets, actions

•

Cognos 8 BI allows relational data to be modeled dimensionally in the Cognos 8 metadata modeling tool, Framework Manager. This extends analysis to relational data sources.

• IBM DB2 OLAP – substitution variables, member properties • Essbase – substitution variables, member properties

Additional Information The key concern with ROLAP is performance. Will the database respond fast enough to really analysis? • Typically ROLAP performance requires use of technology called aggregate tables (or materialized views) and an aggregate aware query optimizer • Cognos 8 BI does not provide that technology, but is designed to work with that technology being supplied as part of your database system. Our open data access strategy allows you to choose ROLAP as a storage technology independent of your end interface. High performance in a ROLAP environment usually requires the use of aggregate tables and a query optimizer that chooses which is the best aggregate table to use for each query. Some BI tools include this capability of aggregate awareness. Cognos 8 BI doesn't, but modern releases of Oracle, IBM DB2 and others do, and we work with them. One of the main aspects of Cognos 8 BI is the ability to not only model relational data but to also manipulate the metadata to form hierarchies, dimensions, and levels within Framework Manager. This gives the end drill up and drill down functionality within AS/RS/QS.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

• SAP BW – multi-currency, variables, alternative hierarchies, InfoQuery, InfoCube, MultiProvider, RemoteCube Use ROLAP (relational online analytical processing) if you want to use aggregate and hierarchical awareness in relational sources. ROLAP is a form of OLAP that performs dynamic multidimensional analysis of data stored in a relational database rather than in a multidimensional database (which is considered the OLAP standard). For ROLAP to work effectively, along with a quick response time, the responsibility must be shared between the Framework Manager

1-11

COGNOS

8

BI

AD

HOC

QUERYING

What is Dimensionally Modeled Relational Metadata? ƒ Dimensionally modeled relational data is data you take from a relational data source and model as a star schema.

Additional Information The following features were added to Framework Manager for dimensional modeling of relational data sources. • Regular dimensions include the concepts of hierarchies and levels which the definition of alternate hierarchies on a single dimension. • Measure dimensions provide the ability to define a list of measures as well as a hierarchy to control the rollup of measures. • Scope relationships control rollups by defining the scope of a measure with respect to existing dimensions.

•

Framework Manager is the metadata modeling tool that lets modelers model relational data dimensionally.

•

You can apply hierarchies to allow drill behavior.

•

When working with dimensionally modeled relational data, you can perform OLAP-style querying such as drill up and drill down, navigate hierarchies, and leverage member functions.

•

Member functions allow you to manipulate (that is, getting the children for the parent member).

•

A member is an instance of an object in a level of a dimension. are organized hierarchically.

•

of a level can be further described by a value called an attribute.

1-12

• With the dimension map, you can view the measures and dimensions you have created, and view or modify the scope from a wholemodel perspective. In Cognos 8 BI, all studios can provide the with a dimensional data view on top of relationallystored data as long as the data view has been dimensionally modeled. A member was previously called a category in PowerPlay. For example, Joe Smith is a member of the Employees level. Examples of attributes are: age and gender. Age and gender are attributes of the member Employees.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

Query OLAP Data Sources ƒ Multidimensional Expression (MDX) is the language used to query OLAP data sources.

Original report

OF

COGNOS

8

BI

Instructional Tips You may want to view MDX language in Report Studio to help debug queries. To edit the MDX language, in Report Studio, go to the Query Explorer bar, and then select the query. In the Properties pane, click the ellipsis beside Generated SQL/MDX. The dialog box that appears allows you to edit the text and then validate it. Any changes made to this cannot be reversed. For most purposes, Cognos 8 BI will generate SQL or MDX on your behalf and you will not need to understand them.

Generated MDX

•

SQL is the language used to query relational data sources. MDX is the multidimensional equivalent of SQL. Like SQL, it is a recognized standard.

•

In Cognos 8 BI, you can only view and edit SQL and MDX in Report Studio.

•

If you are querying against an OLAP data source, Cognos 8 BI uses MDX language.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1-13

COGNOS

8

BI

AD

HOC

QUERYING

Access Enterprise Planning (EP) Data Sources

Query Studio

Report Studio

Cognos Viewer

Analysis Studio

Event Studio

Metric Studio

Additional Information Cognos Planning – Analyst is a flexible tool used by financial specialists to define their business models. These models include the drivers and content required for planning, budgeting, and forecasting. The models can then be distributed to managers using the Web-based architecture of Cognos Planning Contributor.

Data Access MDX

SQL

MDX

Views Publish Tables

Cognos Contributor

MSAS

Cognos Analyst

Cognos Planning - Contributor streamlines data collection and workflow management. s have the option to submit information simultaneously through a simple Web or Excel interface. Using an intranet or secure internet connection, s review only what they need to review and enter data where they are authorized.

SQL Metric Database

Published mart

Controller

•

You can integrate a Cognos Enterprise Planning data source with Cognos 8 BI to report on financial data.

•

Cognos 8 BI integrates with Cognos Enterprise Planning data sources to provide enterprise reporting with true dimensional awareness through automatic drill capabilities and hierarchy awareness.

•

You can access your current plan (Cognos Contributor) directly for querying, reporting and analyzing, as well as the published plan.

•

You can also access Cognos Controller consolidated financial data in Cognos 8 BI.

1-14

Use Cognos Controller to enter, consolidate, and analyze your financial data, regardless of the complexity of your organization. With Cognos Controller, you can secure a single, accurate view of key corporate information and shorten consolidation, close, and financial reporting cycles which better planning and forecasting. Cognos Finance and Cognos Contributor data sources can now feed into the data access layer of Cognos 8 BI using MDX language. You can publish data from Analyst and Contributor data sources to relational tables and then feed the data access layer from these tables using SQL. Finally, Controller can also feed the data access layer by going through a cube (namely, MSAS) using MDX or by publishing views using SQL.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

Identify Data Source Types in the Studios Relational model

Dimensional model

OF

COGNOS

8

BI

Instructional Tips A regular dimension consists of descriptive information. A measure dimension consists of measurable values. Relational models can not be used in Analysis Studio. An end can not easily tell if a dimensional model represents a cube or a relational database. This is intentional.

•

A relational model lets s interact with query subjects and query items. There are no visible hierarchies.

•

With a dimensional model, s interact with dimensions, (consisting of hierarchies, levels, and ), and measures. This allows for OLAPstyle querying in the studios.

•

Only dimensional models allow drill up and drill down behavior in analyses and reports.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1-15

COGNOS

8

BI

AD

HOC

QUERYING

Demo 1 ƒ Navigate Data in Different Studios

1-16

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

OF

COGNOS

8

BI

Demo 1: Navigate Data in Different Studios Purpose: As new s to Cognos 8 BI, we will explore various models in Query Studio, Analysis Studio, and Report Studio to familiarize ourselves with the reporting environment. In Report Studio we will create a dashboard report containing key company data. Task 1. Explore a relational data source in Query Studio. 1. Start your browser. In the address box, type http://<servername>/cognos8, and then press Enter.

Public Classroom Because of the use of the VMware image, students will not have to type in the address.

2. On the Log on screen, in the ID box, type brettonf, in the box, type Education1!, and then click OK. 3. On the Welcome page, click Cognos Connection. Cognos Connection starts and we see the studios listed in the top right corner. Notice that there is one single interface to access all studios. 4. Click Query Studio. 5. On the Select a package page, click GO Sales and Retailers. Query Studio starts. We see a relational data source in Query Studio. The data tree on the left displays the available query subjects. Notice the query subject icon . 6. Expand the Orders query subject. The available query items display in the data tree. Notice the query item icon . 7. Under Orders, drag Order number to add it to the query. 8. Under Orders, drag Quantity to the end of the query. This is a measure. Notice the icon

.

9. Collapse the Orders query subject.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1-17

COGNOS

8

BI

AD

HOC

QUERYING

10. Expand Retailers. A list appears containing all the retailer query items in the Insert Data menu, as shown below: Instructional Tips We are expanding Retailers because it is a data item found in multiple packages (for example, Go Sales as well as Go Sales and Retailers). It will be easier to compare how the data appears in multiple studios. Notice that this model could not be opened in Analysis Studio as it is not dimensionally modeled relational data.

11. Drag Retailer name to the end of the query. Notice there are no hierarchies, only query subjects and query items. 12. Click Return, and click No when prompted to save the report. We return to Cognos Connection. Task 2. Explore a dimensionally modeled relational data source in Query Studio. 1. Click Query Studio. 2. In the Select a package page, click GO Sales. Query Studio starts. We are now looking at a relational data source that has been modeled dimensionally. The data tree on the left displays the available namespaces. Notice the namespace icon . 3. Expand the Retailer activity namespace. The available dimensions and measures folders display in the data tree. Notice the dimension icon and the measures folder icon . 4. Expand the Retailer site dimension. The available levels display in the data tree. Notice the level icon

1-18

.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

OF

COGNOS

8

BI

5. Expand the Retailer name level. The available query item displays in the data tree. Notice the query item icon , as shown below:

These query items are attributes to the retailer name level. 6. Drag the Retail name level to add it to the query. Because this is dimensionally modeled relational data, we can drill down on Retailer name. You cannot do this in a relational model. 7. Click Return, and then click No when prompted to save the report. Task 3. Explore an OLAP data source in Query Studio. 1. Click Query Studio. 2. In the Select a package page, click Great Outdoors Company. Observe the dimensional data in Query Studio. The data tree on the left displays the available dimensions and measures. Notice the dimension icon and the measures icon .

Instructional Tips The Great Outdoors Company package is a PowerCube; it contains dimensional data.

3. Expand the Retailer dimension. The available hierarchies display in the data tree. Notice the hierarchy icon .

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1-19

COGNOS

8

BI

AD

HOC

QUERYING

4. Expand Retailer. The available levels display in the data tree. Notice the level icon

.

5. Drag the Retailer name level to add it to the query. 6. Expand Products. The Retailer dimension has multiple hierarchies, but the Products dimension has only one hierarchy and therefore displays the levels at the root of the dimension. 7. Drag Products to the end of the query. 8. Expand Measures. Different dimensional sources allow measures to be organized in different ways. Notice the measures folder icon . A measures folder is used to organize the measures logically, as shown below:

1-20

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

OF

COGNOS

8

BI

9. Drag Revenue to the end of the query. We can drill down on Products and Retailer name, as we did when we were using dimensionally modeled relational data. 10. Click Return, and then click No when prompted to save the report. Task 4. Explore an OLAP data source in Analysis Studio. Now we want to explore an OLAP data source (dimensional data) in Analysis Studio. 1. Click Analysis Studio. 2. In the Select a package page, click Great Outdoors Company.

Instructional Tips Here we will build a simple analysis so that we can show the same PowerCube data being ed to Report Studio. This task shows how we can use the same data in different studios.

We are looking at an OLAP data source in Analysis Studio. The data tree on the left displays the available dimensions. Notice the folders icon. A folder indicates multiple hierarchies are available for the dimension. 3. Expand the Retailer folder. The available hierarchies display in the data tree. 4. Expand the Retailer hierarchy. The available are displayed in the data tree. Notice the member icon . All of the retailers appear in the tree. Notice that the tree contains the same data items as in Query Studio, except that in Query Studio, Products appeared as a dimension icon, and it now appears as the top member of the Products hierarchy in the Products dimension.

5. From the data tree, drag the Retailer hierarchy onto the rows area in the crosstab. © 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1-21

COGNOS

8

BI

AD

HOC

QUERYING

6. From the data tree, expand the Years folder, and then drag the Years hierarchy onto the columns area. 7. In the data tree, expand Measures, and then drag Revenue onto the measure area. We have created a simple analysis. Task 5. Open the analysis in Report Studio and create a dashboard. 1. From the File menu, click Open in Report Studio. We want to see all of the , so we will change a setting in Report Studio to make all visible. 2. From the Tools menu, click Options, select the Show folder check box, and then click OK. 3. In the Insertable Objects pane, expand the Retailer dimension. 4. Expand the Retailer level hierarchy, and then expand the Retailer level. 5. Expand the folder, and then expand Retailer. All of the retailer types we saw in Analysis Studio appear in this tree. We want to create a dashboard report displaying a crosstab and a gauge chart which contain different data. We will look at the year-to-year performance of Retailer types and Product lines. 6. Click the Toolbox tab, and then drag a Chart to the far right of the crosstab. 7. In the Insert Chart dialog box, click Gauge, and then click OK. 8. Click the Source tab, expand Measures, and then drag Quantity sold to the Measure drop zone on the gauge chart. 9. Expand the Products dimension and the Products level hierarchy. 10. Drag the Product line level to the Needles (series) drop zone. 11. Expand the Years dimension, and then expand the Years hierarchy. 12. Drag the Year level to the Gauges drop zone on the gauge chart.

1-22

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

13. On the toolbar, click Run Report

OF

COGNOS

8

BI

.

The report appears in Cognos Viewer containing the information that we originally selected in Analysis Studio, as well as the gauge chart we added, as shown below:

14. Close Cognos Viewer. 15. From the Tools menu, click Options, clear the Show folder check box, and then click OK. 16. Close Report Studio and Analysis Studio without saving. 17. Click Log Off, and then close your browser. Results: We ensured that there are no limitations as to what data we can use in each studio. We explored Query Studio, Analysis Studio and Report Studio. We saw that in Query Studio we add metadata to reports, in Analysis Studio we add to reports, and in Report Studio we can use both methods. In Report Studio we created a dashboard report containing a crosstab and a gauge chart containing different company data.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Additional Information We need to reset the default for Report Studio for future demos; therefore we change the Show folder option here back to what it was prior to Task 5, step 2 of this demo.

1-23

COGNOS

8

BI

AD

HOC

QUERYING

Instructional Tips Anything you create in Analysis Studio (or Query Atudio) can be moved into Report Studio. Through analysis and exploration, you can evolve a large report into a managed report.

Use Deep, Large Data Comparative Analysis ƒ Data in cubes can contain vast amounts of records making an analysis very large.

•

Analysis Studio can control how much of the data in cubes is rendered in the data tree and in the crosstab.

•

By reducing the number of records rendered in an analysis, cube performance improves.

1-24

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

Design Informative Executive Dashboards ƒ You can design informative dashboards using Report Studio’s extensive capabilities.

OF

COGNOS

8

BI

Additional Information Report Studio adds: • maps • gauges • more combination charts • more chart design control • access to cubes • high levels of graphic design control over charts Cognos Viewer adds: • drill up and drill down on text • drill up, drill down and through on charts

•

A dashboard displays multiple aspects of your business in a consolidated view using visual aids (such as gauges, and charts).

•

Dashboards provide s with: •

the ability to drill up and drill down to see higher or lower levels of detail

•

the ability to navigate to other linked targets, including Report Studio reports, Query Studio queries, and Analysis Studio analyses

•

special chart styles, such as gauges, charts, and maps

•

the ability to include multiple charts in a single report, derived from multiple data sources

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1-25

COGNOS

8

BI

AD

HOC

QUERYING

Perform Self-Service Reporting ƒ Cognos 8 BI provides extended formatting, filtering, and calculations for ad hoc reports.

Highlight Exceptional Data

•

Query Studio offers templates and style formatting, multiple summaries in group and footer calculations, and AND/OR filters.

•

You can use this studio when seeking specific answers to your business questions.

1-26

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

Manage the Event Lifecycle ƒ Notify s of key operational or performance events which impact the business.

•

You use Event Studio to define rules to detect and deliver critical business information to key stakeholders within an organization. s can react to the information in a timely manner to improve performance.

•

This is a valuable tool that allows you to automate event detection, allowing information to be sent only to the appropriate people when an event occurs.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OF

COGNOS

8

BI

Additional Information OLAP data sources tend to be static and not live data. Having an event run against static data would not be very relevant as the information the s would receive would be based on 'old' data. It would be best to run against live data when working with Event Studio, although you can run agents on a weekly basis after the cube has been refreshed. This would not be as timely as live data, but it depends on what your needs are.

1-27

COGNOS

8

BI

AD

HOC

QUERYING

Additional Information Metric Studio s objectives, projects, and counter-measures, and distributed application management.

Examine Integrated Metrics

You can create integrated plans, forecasts, reports, analyses, and scorecards that show how your organization is performing, why it is performing that way, and what you can do to improve performance.

ƒ Metric Studio is fully integrated into Cognos 8 BI.

Note: Metric Studio was previously called Cognos Metrics Manager. Metric Studio is a major upgrade from Cognos Metrics Manager.

•

1-28

Metric Studio: • uses the Cognos 8 BI scheduler, content store, security, and report service. • UI appears as a studio or alternate portal. • can pick Cognos 8 BI reports as drill targets and displays. • has actionable scorecards.

Benefits of Metric Studio include: •

the ability to work with multiple applications

•

a flexible calendar

•

navigation features including a tabbed interface, search function, and configurable navigation options

•

graphing, charting, and diagram creation capabilities

•

activity monitoring

•

the ability to include email notifications on changes to metrics in the Watch list

•

actionable scorecards and metrics

The Watch list can be imported as a portlet into Cognos Connection.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

Extended Data Transformation Services in Data Manager ƒ The Data Manager engine is based on the Cognos 8 BI architecture:

•

ƒ

query subject data access (designed using Framework Manager)

ƒ

same data type

ƒ

Unicode

OF

COGNOS

8

BI

Instructional Tips Cognos 8 BI Data Manager was previously called DecisionStream. There have been some customer enhancements to allow easier large project management (such as, improved for unbalanced hierarchical structures).

Cognos 8 BI has integrated data transformation capabilities, making it easier to build data foundations for BI.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1-29

COGNOS

8

BI

AD

HOC

QUERYING

Additional Information Some features might not be covered in this course as they are available only to s.

Portal Integration and Subscription

s can now manage and deploy portal pages, customize the Welcome page, create and modify styles, import WSRP portlets into Cognos Connection, and manage profiles.

•

A portal is a location where s can share and distribute their reports, as well as a place to consolidate all of their pertinent information.

•

Cognos 8 BI offers Cognos Connection as a consolidation tool for all your reporting needs.

•

Cognos 8 BI also provides portlets to enable Cognos 8 BI reports to appear inside enterprise portals (such as Plumtree).

•

Portlets are technology pieces that enable reports or other "content" from one vendor to appear inside portal pages from another vendor.

•

In Cognos 8 BI, you can define, publish, and subscribe to shared portal pages.

•

Cognos 8 BI s WSRP conformant portlets. It also includes 'outof-the-box' portlets to facilitate managing your content.

1-30

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

OVERVIEW

OF

COGNOS

8

BI

Summary ƒ In this module, we have: ƒ

reviewed Corporate Performance Management

ƒ

introduced Cognos 8 BI

ƒ

examined the different studios in Cognos 8 BI

ƒ

identified the different data sources within the studios

ƒ

highlighted key capabilities of Cognos 8 BI

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

1-31

COGNOS

1-32

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2

Introduction to Cognos Connection

Cognos 8 BI

COGNOS

2-2

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Objectives ƒ In this module, we will: ƒ

identify the advantages of using Cognos Connection

ƒ

schedule and manage reports

ƒ

organize content

ƒ

include dynamic Web pages

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-3

COGNOS

8

BI

AD

HOC

QUERYING

What is Cognos Connection? ƒ Cognos Connection is a web page that provides a single access point to all corporate data available in Cognos 8 Business Intelligence (BI). Access data sources and saved content.

Access different studios for report authoring, analysis, scorecarding and event management.

Additional Information s use Cognos Connection to ister servers, optimize performance, set access permissions and modify profiles. They also use it for entry istration, which includes scheduling and distributing reports, analyses, agents and metrics. If you use a customized interface, you may not have access to all the features documented.

•

•

Cognos Connection allows you to quickly access reports created in Report Studio, queries created in Query Studio, analysis created in Analysis Studio, agents created in Event Studio, metrics created in Metric Studio and different data sources called packages. Reports accessed from the Cognos Connection Web portal page can be: •

•

2-4

scheduled to run at a time and frequency that is convenient for your organization

•

generated in multiple formats and languages

•

distributed using shared folders and email

Because of the inclusion of Metrics Manager into the Cognos 8 BI architecture, you can choose Metrics Manager (MM) to be your home page. The top two lines of the MM and Cognos Connection UIs are identical and allow quick access to common UI tools. Instructional Tips You may want to define RSS as a format for syndicating news, as it is used by many Web sites. Before moving on to the next slide, tie the two slides together by mentioning that the ability to perform tasks depend on the s’ roles and responsibilities.

You can personalize Cognos Connection for your own use by: •

creating shortcuts to reports

•

including links (that is, bookmarks)

•

including Web pages called portlets (that is, Real Simple Syndication (RSS) news feeds, stock market pages, or Cognos Watch List to show performance metrics created using Metric Studio)

•

changing the appearance of the content so that it is meaningful to you (that is, change all text to your preferred language)

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Discuss Roles and Responsiblities ƒ Roles represent a collection of s that perform similar tasks, or have a similar status in an organization. ƒ The ability to perform tasks depends on the default permissions described for the predefined Cognos roles. ƒ There are ten predefined Cognos roles. ƒ The use of the predefined Cognos roles is optional. An can create a new role that is named according to s’ businessspecific roles. For example, your company may have a group of s called Finance Report Authors. ƒ The could create a role to reflect this name.

Cognos Role

Responsibility

Consumers

Read and run reports.

Query s

Same as Consumers, but they also use Cognos Query Studio.

Authors

Same as Query s, but they also use Cognos Report Studio.

Report s

ister reports; use Cognos Report Studio and Cognos Query Studio.

Server s

ister servers, dispatchers, and jobs.

Directory s

ister groups, s, s, distribution lists, data sources and printers.

Metrics s

ister Metric packages and tasks in Cognos Connection.

Metrics Authors

Create and edit scorecard applications in Metric Studio.

Metrics s

Monitor performance in Metric Studio.

Portal s

ister the Cognos portlets and third-party portlets in Cognos Connection.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Instructional Tips Throughout the module, emphasize that the ability to perform tasks is dependent on the default permissions described for the predefined Cognos roles. By default, a member of the Consumers role cannot ister content in Public Folders. For example, a member of the Consumers role cannot save a report to Public Folders, however they can save reports to My Folders. An author or an can save reports to both Public Folders and My Folders. Additional Information A key benefit of Cognos 8 is that predefined Cognos roles can be modified to reflect your organization's security infrastructure.

2-5

COGNOS

8

BI

AD

HOC

QUERYING

Define Reports ƒ A report can refer to either: ƒ

a specification that says which information to include in a report

ƒ

the information itself

Instructional Tips Emphasize that in this module, “reports” refers to any of the following: • report studio reports • ad hoc queries • analyses Additional Information A report specification is an XML representation of the queries, prompts, layouts, and styles in a report. You create report specifications using Report Studio or Query Studio, or by writing your own report specifications in XML. You can also create report views, which are references to other reports. Each report view has its own properties, such as prompt values, schedules and results. Report views are useful if you want to share a report specification instead of making copies of it.

•

You can access reports from Cognos Connection.

•

In Cognos Connection, a Query Studio report is also called a query, a Report Studio report is also called a report and an Analysis Studio report is called an analysis.

You can create an ad hoc report using Query Studio or you can use Report Studio to define layouts, styles and prompts for your report. You can also create Analysis Studio reports. Report Studio and Query Studio report specifications can have saved results or you can run a report to produce new results. In PowerPlay, reports are always run against the latest data in the cube. After a report is published to the portal, you can view, run, and open it, view report output, and run reports in various formats. You can distribute reports by saving them, sending them by email, printing them or bursting them. You can create drill-through definitions to create links to target reports using a set of parameter values defined by a drill path and determined by the context of a source report.

2-6

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Run a Report

Additional Information If you click a report with the edit icon, the report opens in the authoring tool, where you can edit it.

ƒ When you run a report, the report retrieves data from a data source

Click a report that has a report run

ƒ You can:

icon to open the report in Cognos Viewer, based on the report format (HTML, PDF, CSV, and so on) specified in the report properties, the Run with options page, or the Preferences page.

ƒ

view live data and edit the report by clicking the report name

ƒ

run a report with options to change the report output format, language, delivery method and prompt values

ƒ

schedule reports to run individually or simultaneously in a job

Run a report to view live data.

Run a report with options.

Schedule a report.

•

Default options are set by the report author.

•

When you run a report with options, you override the default run options.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

If you choose not to view the report now, the report is run in the background. A report is run in the background when it is: • set to run later • has multiple formats or languages • has a delivery method of save, print or email • is a burst report If the Prompt for values box is selected, and the report contains prompts, you will be prompted to provide prompt values. On the Run with advanced options page, if you choose to View the report now, you cannot choose multiple output formats. As well, you can use the Run with advanced options page to: • specify a time for running the report later • choose multiple output formats and languages • add email as a delivery method

2-7

COGNOS

8

BI

AD

HOC

QUERYING

Schedule Reports ƒ You can schedule reports to run: ƒ

at a time that is convenient for you, such as during off hours when demands on the system are low

ƒ

individually or by using a job

ƒ

by minute, hourly, daily, weekly, monthly, or yearly

ƒ After you create a schedule, the report or job runs at the date and time specified.

2-8

•

You coordinate schedules using the Schedule Management tool.

Instructional Tips The Schedule Management tool is covered later in this module.

•

Only one schedule can be associated with each report or job. If you require multiple schedules for the same report, create multiple report views and create a schedule for each report view.

A job is used for multiple reports. Jobs are covered later in this module.

•

Once you create a schedule, you can modify it as necessary.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Schedule a Single Report ƒ You can schedule a single report to run at a later time or at a recurring date and time.

This report runs every Wednesday at 2:57 p.m.

Additional Information You can view the run history of a scheduled report to see how many times the report ran and the success or failure of each run.

•

If you no longer require a schedule, you can permanently delete it or temporarily disable it.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Instructional Tips In the slide example, we are scheduling a report to run every Wednesday at 2:57pm, with no specified end date. Every time the report runs, a report output is generated in HTML format.

2-9

COGNOS

8

BI

AD

HOC

QUERYING

Schedule Multiple Reports ƒ You can set the same schedule for multiple reports by creating a job. ƒ A job identifies a collection of reports, report views, and other jobs. ƒ Steps in a job: ƒ

are scheduled to run together

ƒ

share the same schedule settings

Additional Information When you execute the steps in a job all at once, all the steps are submitted at the same time and the job is successful when all steps are completed successfully. When you execute the steps in a job in sequence, you can specify the order in which the steps run. Each step is submitted only after the preceding step ran successfully.

•

When a scheduled job runs, all the reports in the job run.

•

Jobs are composed of steps. Each job step is a reference to an individual report, job, or report view.

•

The steps in a job can be executed in two ways:

2-10

•

all at once

•

in sequence

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

View and Manage Schedules ƒ You can use the Schedule Management tool to view and manage the schedules you have created.

TO

COGNOS

CONNECTION

Additional Information You can specify that you only want to see activities that occurred in a specific time period; for example, in the last four hours. You can also filter by status. For example, you may only want to view scheduled reports that failed to run.

View the schedule and status.

View the run history.

•

You can view reports of a specified status such as pending, running, or failed. You can then permanently cancel reports that are running or pending, or delay the running of a pending report. This is useful when several reports are pending, but you need a specific report to run immediately. You can then cancel or suspend reports that are scheduled to run first.

You can use the Schedule Management tool to: •

change the properties of a job

•

modify a scheduled report

•

view the run history of a report

•

run a scheduled entry once

•

delete a schedule

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-11

COGNOS

8

BI

AD

HOC

QUERYING

Additional Information The run history is saved when a report runs in the background.

View the Run History of Reports

To view the run history for a single report: 1. Under the Actions column, click the More link beside the report you wish to view. 2. Click View Run History. 3. Under Status, click the status filter to apply.

ƒ You can view the run history of a report to examine: ƒ

when the report was requested

ƒ

the start time and completion time for the report

ƒ

whether the report ran successfully

To view the run history for many reports: 1. In the tools list, click Schedule Management. 2. Click the Activity tab and select the filter options to apply. A list containing all reports and jobs that meet the filter options is shown.

•

You can check whether reports ran successfully or determine how often a report ran.

•

The run history for a report includes information such as the request time, start time, completion time, and whether the report ran successfully.

•

You can run a report in the background without viewing it. When a report runs in the background, a run history is saved.

•

You can filter the items listed on the run history page by the run status. If a report has failed to run, click the Failed link in the Status column to view the reason.

2-12

Consider the example below of when to use the run history: A report in Public Folders is scheduled to run daily and render the most up-to-date information. You access this report and notice that the information is not up to date. (No hyphens when it follows the noun.) Even though you were not the author of the report, you can view its run history to see the request time, completion time and status. If the report Status was not successful, you may want to notify your . If we click the View the output versions for this report icon under the Actions column, we can view the versions that were produced during this run cycle.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

View a Report ƒ Click a report name to view the most recently saved copy of the report in Cognos Viewer. Cognos Connection

CSV PDF HTML

Cognos Viewer

•

A report output is a snap shot of the data that is current only up to the time that the report is saved.

•

When you view a report, you are opening a report that contains a report output. This report opens in Cognos Viewer.

•

Actions available in Cognos Viewer include save, save as report view, email and run the reports, and drill capabilities, view in HTML, PDF, CSV, XML and multiple Excel formats.

TO

COGNOS

CONNECTION

Additional Information You can identify a report that is a saved copy by: • Viewing the icon next to the report name. This icon is derived from the format type of the most recently saved copy of the report, for example HTML • Using the View the output versions of this report icon, under the Actions column In the slide example, there are reports that have saved report outputs. For example: • Expand and Collapse report, which has a format type of CSV • High Margin Product Types, which has a format type of PDF • Low Revenue Products, which has a format type of HTML If you click the name of a report that is the report output, you will view the most recently saved copy of the report. When a report is saved, multiple outputs can be generated at the same time (different formats, languages). You view these other versions by clicking More next to the report under the Actions column, and then clicking View report output versions. You can also click the View previous report output versions icon, which is also next to the report under the Actions column. If you are viewing a report in Excel or CSV format, you can only save the output of the report to the file system. You cannot save it to Cognos Connection from Cognos Viewer. In this case, the only way to save the report output in either of these two formats is to close Cognos Viewer and rerun the report in the background using the Run with options or Run with advanced options pages.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-13

COGNOS

8

BI

AD

HOC

QUERYING

Additional Information You may want to view older data, for example, when you compare two reports with monthly sales volume for a product. The first report contains data before new features were added to the product. The second contains data after new features were added.

View Report Output Versions ƒ You can analyze older data in a report by viewing previous report output versions.

You can also view the report output versions of a report by clicking the More link under the Actions column, and then clicking View report output versions. Clicking the PDF icon allows you to save a PDF version of the report on your hard drive. Previously saved versions of the Low Revenue Products report in PDF and Excel and HTML.

•

When you run a report, the specification is run against the data source so that the report reflects the most recent data. However, this data may not meet your needs. You may want to view older data.

•

You can use the View report output versions page to view all versions of a report output, including the date, format, and language of each report output.

2-14

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Demo 1 ƒ Run a Report and Render the Output in a Different Language

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-15

COGNOS

8

BI

AD

HOC

QUERYING

Demo 1: Run a Report and Render the Output in a Different Language Purpose: Business analysts in North America have created an ad hoc query that would be meaningful to share with business analysts in other regions. Our s in would like to see the report. We will run this report and save the output as an HTML report in French. Public Classroom Because of the use of the VMware image, students will not have to type in the address.

Task 1. Open Cognos Connection. 1. Start your browser, in the address box type http://<servername>/cognos8, and then press Enter. 2. On the Log on screen, in the ID box, type brettonf, in the box, type Education1!, and then click OK. 3. On the Welcome page, click Cognos Connection. Cognos Connection opens. Task 2. Run a query. 1. Click the Public Folders tab, click Go Sales and Retailers, and then click Query Studio Report Samples. 2. From the list of reports, go to Returns by Order Method and click Run with options – Returns by Order Method

2-16

.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

3. From the Run with options dialog box: •

ensure the Format is HTML

•

click Language and select French (Canada)

•

click Save the report

The Run with options dialog box appears, as shown below:

4. Click Run. 5. On the Cognos 8 information screen, click OK. The report is saved in Cognos Connection. 6. Click Refresh

until the View the output versions for this report

appears in the actions tool bar. Notice that the icon next to the report name changed to show the query is now an HTML report.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-17

COGNOS

8

BI

AD

HOC

QUERYING

7. Click the file Return by Order Method. The report opens in Cognos Viewer and appears in French, as shown below:

8. Click Home and leave Cognos Connection open for the next demo. Results: We ran a report from Cognos Connection and rendered the output in a language different from the default language.

2-18

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Demo 2 ƒ Schedule a Report

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-19

COGNOS

8

BI

AD

HOC

QUERYING

Demo 2: Schedule a Report Purpose: Management wants to compare the revenue by order method and sales territory. We already have an Analysis Studio report that makes this comparison, so we will schedule the report to run every two minutes so that the data can be analyzed frequently. To accommodate the needs of various managers, we will render the report outputs in both HTML and PDF formats.

Instructional Tips You may want to mention that it is unrealistic in a business environment to schedule a report to run every two minutes. We scheduled the report to run every two minutes for this demo so that the students can see the results immediately.

Task 1. Schedule the report to run on a regular basis. 1. From the Public Folders tab, click Great Outdoors Company, and then click Analysis Studio Samples. Analyses created in Analysis Studio are displayed as shown below:

2. Beside Revenue by Order Method and Sales Territory analysis, under Actions, click Schedule - Revenue by Order Method and Sales Territory

.

The Schedule - Revenue by Order Method and Sales Territory page appears. Here we can specify when we want the report to run, how frequently we want to create report outputs, and the format and language of these report outputs. 3. In the Frequency section, click the By Day tab, then click the button beside Every 1 minute(s), and type 2. 4. In the Options section, select the Override the default values check box, and then select the PDF check box. Both HTML and PDF are selected.

2-20

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

5. In the Delivery section, ensure that the Save check box is selected, ensure that the button beside Save the report is selected, and then click OK. The report is scheduled to run every two minutes. Because we specified that we wanted to save the report, PDF and HTML report outputs will be created after each two-minute interval. 6. After two minutes have ed, click Refresh

.

Beside Revenue by Order Method and Sales Territory, under Actions, the View the output versions for this report - Revenue by Order Method and Sales Territory icon shows that the Schedule Management tool has generated report outputs. Task 2. View the report outputs and disable the schedule. 1. Beside Revenue by Order Method and Sales Territory, under Actions, click View the output versions for this report - Revenue by Order Method and Sales Territory. The View report output versions - Revenue by Order Method and Sales Territory page appears. We can see that two report outputs have been generated (both in English): one in HTML format, and the other in PDF format.

2. Click PDF

.

The PDF report output opens in Cognos Viewer, as shown below:

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-21

COGNOS

8

BI

AD

HOC

QUERYING

3. Click the Back button on your browser, and then click HTML

.

The HTML report output opens in Cognos Viewer as shown below:

4. Click Return, click Close, and beside Revenue by Order Method and Sales Territory, under Actions, click Schedule. 5. Select the Disable the schedule check box.

6. Click OK. 7. Click Home and leave Cognos Connection open for the next demo. Results: We scheduled a report to run on a regular basis. Report outputs were rendered in both PDF and HTML every two minutes. After viewing the results, we disabled the schedule to prevent the output of further reports.

2-22

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Demo 3 ƒ Schedule Multiple Reports

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-23

COGNOS

8

BI

AD

HOC

QUERYING

Demo 3: Schedule Multiple Reports Purpose: Management now wants to see data about the cost of goods sold across different product lines and product types. They also want to analyze general sales data from all global territories. We already have two reports that include this data. We will now combine them into a job and schedule this job to run every two minutes and render in Excel format so that management can analyze these reports at the same time.

Instructional Tips You may want to mention that it is unrealistic in a business environment to schedule a report to run every two minutes. We scheduled the report to run every two minutes for this demo so that the students can see the results immediately.

Task 1. Create a job containing two reports. 1. On the toolbar in Cognos Connection, click New Job

.

The New Job Wizard opens. 2. In the Name box, type Sales Performance, under Location, click Select another location. 3. Click GO Sales and Retailers, click the button beside Query Studio Report Samples, click OK, and then click Next. 4. In the Select the steps window, under Steps, click Add. 5. Select the High Margin Product Types and Low Revenue Products check boxes, and then click Add

.

The two reports are added to the job.

6. Click OK, and then under Defaults for all steps, click Set. We want to specify options for the entire job. The choices that we make on this screen will apply to both of the reports in the job.

2-24

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

7. Click Report Options, then select the Specify default values for all the reports of this job check box, clear the HTML check box, and then select the Excel 2000 Single Sheet check box. When this job runs, we want it to generate an Excel version of each report. 8. Click OK, and then in the Submission of steps section, click In sequence. We want the report outputs to be generated one after the other, rather than all at once. 9. Click Next, under Action click Save and Schedule, and then click Finish. Now we can schedule how frequently we want the job to run. Each time the job runs, two Excel report outputs (one for each report) will be created. 10. Click the By Day tab, click the button beside Every 1 minute(s), and then in the box, delete 1 and type 2. 11. Under End, click the End by button, and then set the End by time to be 4 minutes later. The Sales Performance job will be executed every two minutes. In the process, the job will generate two report outputs, both in Excel format. 12. Click OK, click the GO Sales and Retailers link if it’s not already selected, and then click Query Studio Report Samples. The Sales Performance job is added to the Query Studio Report Samples subfolder as shown below:

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-25

COGNOS

8

BI

AD

HOC

QUERYING

Task 2. View the report outputs generated by the Sales Performance job. 1. After two minutes have ed, click Refresh. Beside the High Margin Product Types and the Low Revenue Products links, the View the output versions for this report icon shows that the Schedule Management tool has generated report outputs.

2. In the top right corner of Cognos Connection, click the Tools link, and then click Schedule Management. The Schedule Management tool opens. The Activity tab shows the reports and jobs that have been scheduled and the times they were requested, and also indicates whether they executed successfully. We can see that the Sales Performance job was requested and executed successfully.

3. Click the Refresh button, and then beside the first Sales Performance job, under Actions, click View run history details - Sales Performance.

2-26

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

4. Beside the High Margin Product Types report, under Actions, click View outputs - High Margin Product Types. The View report output versions screen appears. We can see that output for this report was generated in Excel. 5. Click the Excel

icon.

6. If the File dialog box appears asking if you want to open or save the file, click Open. The report opens in Microsoft Excel format as shown below:

7. Close Excel. 8. Click Close, then click Close again, and then click the Schedule tab. We can see the Sales Performance job that we created and scheduled previously. We now want to disable the schedule so that it does not run anymore. 9. In the Actions column for Sales Performance, click More, and then click Modify the schedule. The Schedule - Sales Performance screen appears.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-27

COGNOS

8

BI

AD

HOC

QUERYING

10. Select the Disable the schedule check box, and then click OK. The schedule for the job is disabled. No more report outputs will be created by this job until we enable the schedule again or run the job manually.

11. Click Home and leave Cognos Connection open for the next demo. Results: We created a job that includes two reports. We then scheduled this job to run every two minutes and rendered both reports in Excel.

2-28

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Distribute Reports ƒ You can share information with other s by distributing reports. ƒ You can distribute reports by: ƒ

saving them where other s can access them

ƒ

sending them to other s by email

ƒ

printing them

Additional Information If you want to share a report that is currently in My Folders and you do not want to save it to Public Folders, you can: • copy the report • move the report • create a shortcut to the report Instructional Tips To send a report by email, both you and the recipients must have valid email addresses.

•

Public folders typically contain reports that are of interest to many s: •

•

To print the report from Cognos Connection rather than directly from the browser, you must first add a printer. In the top right corner of the Home page, from the Tools menu, click Directory, click the Printers tab, and then specify the location of the printer.

you can create a subfolder on the Public Folders tab to hold reports that everyone can view

Sending a report by email is useful when a group of people do not have direct access to the report authoring tool. You can: •

attach one copy or multiple copies of the report to the email

•

specify multiple formats and languages

•

include a link to the report as an email attachment

•

To obtain a hard copy of the report, you can print a PDF version of it.

•

You can add a printer in Cognos Connection by specifying a name and a network address.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-29

COGNOS

8

BI

AD

HOC

QUERYING

Create Report Views

Additional Information A report view shares the same report specification as the source report, but other properties, such as prompt answers, are independent of the source report. If you consistently use a specific view, format, or language for a report, create a report view in My Folders to easily access your view of the report.

ƒ Create a report view to produce a customized version of a report.

The report view properties page provides a link to the properties of the source report. If the source report was deleted or moved to another location, the report view icon changes to indicate a broken link and the link to the properties of the source report is removed.

•

2-30

Create a report view if you want to keep the underlying report specification but want the choice of another format, language, delivery method or run option.

Instructional Tips Report Authors can create a report view in Public Folders to share a specific view of a report with multiple s. For example, there may be a requirement for German employees to have access to a report saved in German.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

Specify Properties of an Entry ƒ In Cognos Connection, you can set the default properties for an entry.

•

Use an entry’s Set Properties page to set general properties, default run options and permissions.

TO

COGNOS

CONNECTION

Additional Information General Properties include person, name, description, screen tip, run history and report output versions. Default run options determine how the report executes, including format, language, and prompt values. You can set one of the following actions as the default when a report is run: • view the most recent report or run if it has not been previously run (default) • run the report • open the report in the authoring tool that was used to create it (Query Studio, Report Studio, or Analysis Studio) In the slide example, the second tab is called Query because this report was created in Query Studio. If the report was created in another studio, the tab would read Report for Report Studio and Analysis for Analysis studio. Through Permissions, s can specify which s have access to the entry and what actions can be performed on it. You can specify access permissions for all entries in Cognos 8. Some examples of such entries are reports, queries, analyses, packages, agents, metrics, namespaces, groups, s or dispatchers. You can reference entries from different namespaces. The who creates the entry is its owner and sets the default properties of the entry. You can set multilingual properties including name, screen tip and description for each of the entries in the portal.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-31

COGNOS

8

BI

AD

HOC

QUERYING

Specify How Content Appears ƒ You can personalize the way data appears in Cognos Connection by changing your preferences. The portal preference was set to display all content in German.

Additional Information Refer to the Cognos 8 online Help for a list of preferences that may be changed. s can set access permissions on styles to grant/deny access based on s, groups, or roles. They can also set profiles to deploy specific styles to specific s. Preferences are available for all s. However, depending on a ’s permissions, they might not see all of the preference options. You can modify Portal tabs settings if you have read permissions for your . You can add, remove, and modify the sequence of tabs in your portal.

•

You can change your portal to view data in your own language.

•

Your preferences pertain to the style of the interface (UI), the report format, and the language of the content or the output.

•

Preference changes take effect immediately in the current session. They are stored and used for future sessions.

2-32

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Demo 4 ƒ Create a Report View and Set Preferences

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-33

COGNOS

8

BI

AD

HOC

QUERYING

Demo 4: Create a Report View and Set Preferences Purpose: The Global Sales (1) report shows the expected volume of each product type for each quarter of a sales year. We are responsible for creating language-specific versions of this report, to be distributed to our German and French sales offices. As well, we would like to create Excel versions of the report to be distributed to the German and French ing department. To accomplish this, we will create a copy of this report (report view) in My Folders, and then modify the run options of the report so that it produces an Excel and PDF version of the report in French and German. We will then view a report output to ensure that we have achieved the desired results. Task 1. View a report in Cognos Viewer. 1. From the Public Folders tab, click Go Sales and Retailers, and then click Report Studio Report Samples. 2. Click Global Sales (1). Now we would like to see a view of this report in Excel format to see how the report will look for the German and French ing departments. 3. On the toolbar, click the View in Excel 2000 Single Sheet Format

icon.

4. If the File dialog box appears asking if you want to open or save the file, click Open. The report runs and displays in Excel format. The result appears as shown below:

5. Close the Excel window. 6. In the top right corner, click Return to return to the Report Studio Report Samples folder. 2-34

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Task 2. Create a report view. 1. Beside Global Sales (1), under Actions, click More. The Perform an action - Global Sales (1) page appears. 2. Click Create a report view of this report. The Specify a name and description - New Report View Wizard page appears. 3. In the Name box, type Demo 4_Introduction to Cognos Connection, under Location, click Select My Folders, and then click Finish. The report view is saved to My Folders. Task 3. Specify a different language for the report view. 1. Click the My Folders tab, and then click Demo 4_Introduction to Cognos Connection. The report view opens in Cognos Viewer. This view is currently identical to its source report, Global Sales (1). 2. In the top right corner, click Return. We return to the My Folders tab. 3. Beside Demo 4_Introduction to Cognos Connection, under Actions, click Set properties - Demo 4_Introduction to Cognos Connection. The Set properties - Demo 4_Introduction to Cognos Connection page appears. 4. In the Description box, type The Global Sales (1) report shows the expected volume for each product type for every quarter of a sales year. 5. In the Language list, click French, in the Name box, type Vue personnalisée de ventes globales (1), and then in the Description box, type Les ventes globales (1) rapportent à des expositions le volume prévu pour chaque type de produit pour chaque quart d'une année de ventes. If the portal content is viewed in French, this title and description will be used instead of the English version. This distinguishes it from the source report, which does not have values set for French. 6. Click OK, and then in the top right corner, click Preferences. The Set preferences page appears. 7. In the Default view section, click the button beside Details. This displays the details of each entry in the portal rather than showing a list view. 8. In the Content language section, click Use the following language, and then click French.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-35

COGNOS

8

BI

AD

HOC

QUERYING

9. Click OK. The content of the report is set to French. Notice that the title and description of the report view that we created is in French instead of English (which is the language of the other report titles and descriptions). 10. In the top right corner, click Preferences, in the Default view section, click the button beside List, in the Content language section, click English. 11. Click OK. The portal content reverts to English. Task 4. Create multiple outputs of a report. 1. Beside Demo 4_Introduction to Cognos Connection, under Actions, click Run with options - Demo 4_Introduction to Cognos Connection . The Run with options - Demo 4_Introduction to Cognos Connection page appears. 2. Click Advanced options. The Run with advanced options page appears. 3. In the Delivery section, click the button beside Save the report, print it, or send an email, and then ensure that the Save box is selected. 4. In the Time section, ensure that the button beside Now is selected, and then under Formats, clear the HTML check box, and then select the PDF and the Excel 2000 Single Sheet check boxes. 5. Click Select the languages. The Select the languages screen appears. 6. In the Available languages box, Ctrl+click French and German, click the Add icon, and then click OK. We want to generate report outputs in English, French and German. 7. Click Run, and then click OK. The report outputs will be generated at the specified time. Task 5. View report output versions. 1. On the toolbar, click Refresh . 2. Beside Demo 4_Introduction to Cognos Connection, under Actions, click View the output versions for this report - Demo 4_Introduction to Cognos Connection . The View report output versions - Demo 4_Introduction to Cognos Connection page appears.

2-36

Instructional Tips You may have to click Refresh more than once if the reports do not appear the first time.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

3. Click the arrow beside Languages, and then click All languages. The result appears as shown below:

The six report outputs were generated at once. There are three PDF documents and three Excel documents. There are English, French and German versions of each output type. You can also a PDF version of each report using the - PDF

icon.

4. In the French row, click the PDF icon . A French version of the report view opens as a PDF report in Cognos Viewer. From here, we can print the report or save a version to a different location. 5. Click Home and leave Cognos Connection open for the next demo.

Instructional Tips You may have to launch the Adobe Reader program to initialize it before you click the PDF icon.

Results: We created a customized report view of the Global Sales (1) report in My Folders. We then ran the report with options to create PDF and Excel versions of the report in French and German. To ensure that we achieved the desired result, we viewed the French PDF output version of the report.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-37

COGNOS

8

BI

AD

HOC

QUERYING

Access External Files with Links Additional Information Use the New URL wizard to create a new Uniform Resource Locator, also known as a Web link.

ƒ Create links to make external files and Web sites easily accessible from Cognos Connection. New URL

For example, you may want to create a link in My Folders that is specific to you, or an may want to create a link in Public Folders to make it accessible to all s. A URL is a standard way of identifying the location of any external file or Web site. Click the URL to open it in your browser. Clicking the URL does not open a new browser window. Click the Back button to return to Cognos Connection.

•

A Uniform Resource Locator (URL) is a standard way of identifying the location of any external file or Web site.

•

Create URLs to keep the files and Web sites that you most frequently use easily accessible.

2-38

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

Organize Content with Folders ƒ You can organize reports and other content in Cognos Connection by creating folders.

TO

COGNOS

CONNECTION

Additional Information Folders within Public Folders are of interest to and can be viewed by many s. s and Authors organize content in Public Folders to make it accessible to other s. Folders within My Folders can be organized according to your preferences. These are personal and only accessible when you are logged on. You must have write access to a folder to create entries in it.

A new folder is created in My Folders location.

•

Ensure that your folders are logically labeled and organized to make locating reports quick and easy.

Folders can contain entries and are now considered content objects. To create a new folder: 1. Click either the Public Folders or My Folders link. 2. On the portal toolbar, click New Folder. 3. Type the name of the new folder. 4. Optionally, you can add description and tool tip text. 5. Choose where you want the folder to be located. The new folder has the same access permissions as the parent folder in which it was created. In the slide example, we’ve created a subfolder in My Folders called "My New Folder."

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-39

COGNOS

8

BI

AD

HOC

QUERYING

Organize Content with Shortcuts ƒ Create a shortcut to point to another entry in Cognos Connection, such as a report or Web link.

Shortcut to a report is created in My Folders.

•

A shortcut is a pointer to another entry such as a report, report view, folder, job, agent, page, or URL.

•

Use shortcuts to organize information that you use regularly.

•

The main benefit of a shortcut is that updating the source entry updates the shortcuts in all locations.

•

If a source report has been deleted or moved to another location, the shortcut icon changes to indicate a broken link and the properties link to the source report is removed.

2-40

Additional Information In the slide example, we’ve created a shortcut to a Low Revenue Products and it is stored in My Folders so a can access it in either place. If you want to make a new report, it may be easier to make a copy of an existing report, modify it and resave as something else. If you want to run an existing report with a different format, language, or delivery method, create a report view. You cannot update the source entry by clicking the shortcut. Updating the source updates all shortcuts to the entry (for example, the "Shortcut to Low Revenue Products" that we created). You can change access permissions for a shortcut entry, but this will not change the access permissions for the source entry.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

Organize Entries ƒ To organize content in Cognos Connection, you can copy and paste entries to create a replica of the entry in another location in the portal.

COGNOS

CONNECTION

Instructional Tips If an author wants to use a generic report in Public Folders as the basis for another report, they can make a copy of the generic report and modify it without affecting the original report.

Copy contents from Public Folders.

Additional Information Review entries regularly and attempt to group them logically. Consider grouping the entries by type or frequency of use.

Paste contents to My Folders.

You may decide to create a folder hierarchy of nested folders. Ensure that the folder structure is logical and that it s the chosen method of grouping.

•

You can organize folders, reports, analyses, agents, and packages in a meaningful way so that you can easily locate them.

•

You can also rename, move, disable, delete, or specify the order of entries within Cognos Connection.

If you want to see the latest version of the data, you should create a shortcut to the report instead of creating a copy of the report. Disable an entry to prevent s from accessing it. Disabling is useful when s and Authors want to modify an entry in Public Folders. To disable an entry: 1. Beside the entry you want to disable, under actions, click Set properties. 2. On the General tab, select Disable this entry, and then click OK. Delete an entry to permanently remove it from the portal. For example, you may want to remove entries that are outdated.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-41

COGNOS

8

BI

AD

HOC

QUERYING

Search for Entries ƒ You can search for entries in Cognos Connection whose name, description, or both, match the string entered in the search criteria.

•

•

2-42

You can determine if the entry you are looking for: •

contains the specified string

•

starts with the specified string

•

matches the specified string

The search ignores capitalization.

Additional Information You can find types of entries by leaving the search string empty and selecting an entry type in the advanced search criteria. Entries for which you have access permissions are included in the search results. Instructional Tips To perform a search: 1. Select the highest level folder you want to include in your search. You can increase or limit the folders to include in your search by changing the Scope when you enter the search criteria. 2. On the toolbar, click Search. 3. Type the phrase you want to search for. 4. Choose the type of match (contains, starts, or matches). 5. In the Scope box, click the folders you want to include in the search. 6. In the Find text in box, click the field you want to search. 7. In the Type box, click the type of entry to search for. 8. In the Modified box, click the date that the entry was last modified (for example, click In last week if you want to see the entries that were updated in the last week). 9. Click Search. To open an entry returned by the search, click its link.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Demo 5 ƒ Organize Content in Cognos Connection

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-43

COGNOS

8

BI

AD

HOC

QUERYING

Demo 5: Organize Content in Cognos Connection Purpose: Our manager has asked us to re-arrange some of the contents in the Public Folders so the content details are displayed. Management also wants the report view created for Global Sales (1) copied into a subfolder called Reports. To accomplish this task, we will change the interface preferences and create a folder to hold reports. Since we are changing the preferences and folders on Cognos Connection, we will create a shortcut of the Reports subfolder that management asked for and put it into our personal folder tab. We will also add a URL link to the Cognos corporate site to give us quick access to news releases related to Cognos 8. Task 1. Explore the folders. 1. From Cognos Connection, click the Public Folders tab, if not already selected. By default, entries on the Public Folders tab are accessible to all s. 2. Click GO Sales and Retailers.

Instructional Tips Authors can also open Query Studio, Report Studio, Analysis Studio, Event Studio, and Metric Studio to create and manage reports, events, and analyses.

This folder contains subfolders that contain reports previously created in Query Studio and Report Studio. 3. Click the My Folders tab. My Folders are only accessible by a when that is logged on. The folder contains a report called Demo 4_Introduction to Cognos Connection. Task 2. Change general preferences. 1. In the top right corner, click Preferences. 2. Select Details. 3. In the Number of Columns list, select 2 columns, and then in the Automatic refresh rate in seconds box, type 20.

2-44

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

4. Click OK, and then click the Public Folders tab. The portal reflects the options that we specified in the Preferences section. The entries in the Public Folders tab are divided into 2 columns, as shown below:

Task 3. Create a folder to hold reports. 1. Click New Folder

.

2. In the Name box, type Reports, and then in the Description and Screen tip boxes, type Reports created for General Manager. 3. Click Finish. The subfolder is added to Public Folders tab, as shown below:

Task 4. Copy the report view to the Reports folder. 1. Click the My Folders tab, and then select the Demo 4_Introduction to Cognos Connection check box. 2. From the toolbar, click Copy. 3. Click Public Folders, and then click Reports folder.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-45

COGNOS

8

BI

AD

HOC

QUERYING

4. Click Paste. The report is copied to the Public Folders, Go Sales and Retailers, Reports location as shown below:

Task 5. Create a shortcut of the Reports folder. 1. Go back to the location Public Folders > Go Sales and Retailers by clicking on GO Sales and Retailers. 2. Under Reports, click More, and then click Create a shortcut to this entry. The Specify a name and description - New Shortcut Wizard page appears. 3. Under Location, click Select My Folders, and then click Finish. 4. Click My Folders tab. A shortcut to the Reports folder was added to the My Folders. Notice icon that appears beside the folder name. the shortcut 5. Click Shortcut to Reports. The entry Demo 4_Introduction to Cognos Connection is displayed. Task 6. Create a URL link. . 1. From the Shortcut to Reports folder, click New URL 2. In the Name box, type Cognos Corporate Site, and then in the Description and Screen tip boxes, type Web site for Cognos Inc.

2-46

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

3. In the URL box, type http://www.cognos.com, and then click Finish. The URL is added to the Shortcut to Reports folder, as shown below: Instructional Tips The entries displayed Step 3 have a locked icon beside them. Please disregard this icon since it does not have any significance to the demo. You may not see this icon after completing step 3.

4. Click the Cognos Corporate Site link. The Cognos Corporate Web site appears. 5. Click the Back button on the browser to return to Cognos Connection. 6. Click Home and leave Cognos Connection open for the next demo.

Instructional Tips For step 4, students may not be connected to the network and therefore may not be able to see the Cognos Corporate Web site.

Results: We created a subfolder on the Public Folders location to hold the Demo 4_Introduction to Cognos Connection report view. We then created a shortcut of the subfolder, so that the contents exist on the My Folders location too. Finally, we changed how entries in the folders appear on our screen and added a URL link to Cognos corporate Web site.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-47

COGNOS

8

BI

AD

HOC

QUERYING

Group Content into a Single View ƒ Add your own pages to the portal to group different types of content into a single view. The new page appears as a new tab in Cognos Connection.

Additional Information You can edit a page to change its content, layout and style, and access permissions. For example, changes may be required when the portlets initially added to the page are no longer available, or the list of s who can access the page has changed. You can delete pages in Cognos Connection if you have the required access permissions for the pages. Deleting a page may affect your portal tabs.

•

You choose the information that is most important to you and make it available from personalized pages.

•

After you create the page, you can edit it to modify its content, specify its layout and style, and set access permissions.

2-48

You can modify the content of an instance of a portlet in a page. You will retain the custom settings even if the resets the portlet. If other s have access to the page that contains this portlet instance, they will also see the changes. However, if the locks the portlet, your configured content will not be available until the portlet is unlocked.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

Examine Portal Page Content ƒ Cognos Connection pages consist of a collection of dynamic portlets that provide customized content. This page contains five portlets.

TO

COGNOS

CONNECTION

Additional Information Each page can include portlets from the following groups: Cognos Content: Cognos Navigator: Select Cognos 8 content: Cognos Search: Use the search tool to find Cognos 8 content and open it. Cognos Viewer: The content selected in Cognos Navigator or Cognos Search. Cognos Extended Applications: Custom content created by extended applications toolkit or the SDK. Metrics Manager: Cognos Watch list. You can create pages with scorecards to help complement reports and content. Also you can Integrate MM into 3rd party portals – IBM, WebSphere, SAP EP, and Plumtree portals.

•

A portlet is a component of a portal Web page that provides access to a specific information source or application, such as news updates, technical , or an email program.

•

You can add Cognos Content and Cognos Utility portlets to your portal pages. You can also add any other portlets that have been imported by your (that is, Cognos Watch list, RSS news feeds).

•

Page content can include:

Cognos Utility: Bookmark Viewer: active URLs that represent specified Web sites. Image Viewer: Images HTML Viewer: Specified Web pages RSS Viewer: A really simple syndication (RSS) news feeds represented by URL addresses. RSS is a format summarizing news and is used by many Web sites.

•

Cognos BI content from Cognos Series 7 PowerPlay Web, Cognos Query, Upfront, and Visualizer

Third Party Portlets: Non-Cognos portlets that conform to the WSRP standard.

•

Really Simple Syndication (RSS) news feeds, Web sites, and stock market and quotes

•

metrics and reports

When s log on to Cognos Connection for the first time, they can access the pages an has made available. Pages are cached in a Cognos Connection session. When the state of a page changes during the current session, s who have access to the page are affected. For example, when the page is deleted or disabled, it cannot be accessed any longer and its tab is deleted or not functional.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-49

COGNOS

8

BI

AD

HOC

QUERYING

Demo 6 ƒ Personalize a Portal

2-50

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Demo 6: Personalize a Portal Purpose: We want to create a new portal page to add content that is relevant for us. We will then personalize our page by adding it as a new tab on our portal for quick access to it. We will also change its appearance and add some Cognos navigation and viewing portlets to our page. Task 1. Create a page. 1. Click Log Off, and then click Log on again. 2. On the Log on screen, in the ID box, type uragomek, in the box, type Education1!, and then click OK. 3. If the Cognos 8 Welcome page appears, click Cognos Connection. 4. Click New Page

on the toolbar.

The New page wizard opens. 5. In the Name box, type Resources, and then under Location, click Select another location. 6. Click GO Sales and Retailers button, and then click OK. Public Folders/GO Sales and Retailers will appear under Location. 7. Click Next. 8. Under Number of columns, click 2 columns

.

Two columns appear on our screen. Task 2. Add and modify portlets in the new page. 1. Change the left column width to 30%. 2. Under the left column, click Add. 3. Click Cognos Utility. 4. Select the Bookmarks Viewer check box, and then click Add so that the Bookmarks Viewer portlet will appear in the selected entries. 5. Under Available entries in the top left, click Portlets. 6. Click Cognos Content, and then select the Cognos Navigator and Cognos Search check boxes. 7. Click Add so that the Cognos Navigator and the Cognos Search portlets appear in the selected entries, and then click OK. 8. Change the right column width to 70%, and then under the right column, click Add. © 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-51

COGNOS

8

BI

AD

HOC

QUERYING

9. Click Cognos Content and select the Cognos Viewer check box. 10. Click Add to add the Cognos Viewer portlet to the selected entries, and then click OK. 11. Click Next. We have finished adding the portlets. Now, we will complete the wizard by adding this page to a tab. Task 3. Add the page to the portal tab. 1. On the Set page style – New page wizard dialog box, under Title, type Resources, and then click Next. 2. On the Select an action – New page wizard dialog box, select the Add this page to the portal tabs and View the page check boxes, and then click Finish.

Instructional Tips There is another way to add a page to the portal tab. In Cognos Connection, locate the page you wish to add as a tab, then under Actions, click the add page button . The page will appear as a tab.

The Resources tab now appears as the last tab, as shown below:

Task 4. Modify the portlets. We want to edit the Bookmarks Viewer to contain a link so that we can access the Cognos Web site directly from our portlet. As well, we want to modify the Cognos Navigator to change its appearance to make it easier to view.

Public Classroom For this demo we are only modifying the Bookmarks Viewer and the Cognos Navigator portlets, but note that any portlets available to you can be edited.

1. On the Resources tab, in the Bookmarks Viewer title bar, click Edit . 2. On the Set the properties page, in the Title box, type Web Links. 3. In the Bookmarks area, in the first URLs box, type http://www.cognos.com, and in the Aliases box, type Cognos site, and then click OK. The Cognos Web site is now added to our bookmarks area. 4. In the Cognos Navigator portlet, click Edit. 5. Under Features to expose in the Navigator views, under Normal mode, clear the Actions check box. 6. Near the bottom of the page, in the Separators list, click Alternating backgrounds.

2-52

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

7. Click OK. In the Cognos Navigation pane that the Action column is no longer visible and the background appears in alternating colors. Task 5. Modify the appearance of the portal. 1. In the top right corner, click Preferences. 2. On the General tab, under Style, click Contemporary. 3. Click Preview. The Contemporary style template appears. 4. Click Close. 5. Clear the Show the Welcome page at startup check box, and then click OK. The portal tab appears with the new style along with the new portlet views, as shown below:

6. Leave Cognos Connection open for next demo. Results: We created a new portal page. We personalized it by adding it as a new tab on our portal and we changed its appearance. We also added some Cognos navigation and viewing portlets to it.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-53

COGNOS

8

BI

AD

HOC

QUERYING

Instructional Tips Note: In ReportNet 1.1, pages were personal items only. Once created, they appeared in a ’s portal tab, which meant that only individual s could see their own pages. The problem with this is that you are not able to share your pages with other s. This is not the case with Cognos 8.

Access Shared Pages ƒ By sharing pages you can share your reports with other authors without having to distribute reports by email or in paper format. ƒ The pages take on the characteristics of content objects. ƒ The pages can be: ƒ copied and shared

Additional Information In Cognos 8 BI, the pages are "real" content objects, the same as folders, reports and URLs.

ƒ secured to select s, groups or roles ƒ navigated to and cosumed from a content listing

If a page is copied, all changes that are made to the source will not be brought over to the copy. As well, all changes made to the copy will not be copied over to the source.

•

To access pages created by other s or to have your page accessed, pages need to be saved to a shared location (for example,Public Folders).

•

Other s who have access to these pages can choose to view them, edit them (if they have write access), or even save a local copy to their personal folder. Any modifications made to the page by the owner of the page is circulated out to all s.

•

s can set security on individual pages to grant or deny access to certain s or groups of s.

Pages can be included in tab bars but instead of being the only way to navigate to the page, these tabs are actually shortcuts to the page. You will get the same experience clicking on the tab bar entry for the page as you would finding it in the content tree and then viewing the page. The page is a consumable piece of content, therefore it can be selected as the entry that you want to put into a Viewer portlet. If your page has a viewer portlet on it and the entry you select is a page itself, then the selected page is embedded on a page. You can assemble compound pages and mix and match dashboards. You can configure it so that s only have read: access, therefore they can only view the page. The can save pages in a shared folder, and assign security permission, so that s can see a subset of those pages.

2-54

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Demo 7 ƒ Access a Shared Page

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-55

COGNOS

8

BI

AD

HOC

QUERYING

Demo 7: Access a Shared Page Purpose: Jorg and Kazumi are report authors and managing directors in their designated countries. They want to share information with each other. Kazumi has set up a page that Jorg can access. As Jorg, we will access Kazumi’s page and will place it as a tab on our main page to make it easily accessible. Task 1. Access a shared page. 1. Click Log Off, and then click Log on again. 2. On the Log on screen, in the ID box, type kunzej, in the box, type Education1!, and then click OK. 3. If the Cognos 8 Welcome page appears, click Cognos Connection. We want to view and select the portal tab created by Kazumi. 4. Click Preferences. 5. In the Set preferences dialog box, click Portal Tabs. 6. Select the Public Folders check box. 7. Click Add. 8. Click Public Folders, and then click Go Sales and Retailers. 9. Select the Resources check box. 10. Click Add

.

11. Click OK and then click OK again. The Resources tab will now appear beside My Folders. 12. Click the Resources tab. The Resources portal that Kazumi created appears in Jorg’s portal with the portal style that Jorg has chosen. Task 2. Modify a shared page. We want to set up our portal page with 3 columns. The layout will have our left column set to 30%, the middle column set to 50% and the right column set to 20%. 1. In the top left hand corner, click Tab Menu portal tab.

, and then click Edit this

2. Under Number of columns, click 3 columns. 3. Change the middle column width to 50%. 4. Change the last column width to 20%. We want to have Cognos Search appear in the right column. 2-56

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

5. In the left column, click Cognos Search. 6. To the right of the left column, click Move one item right

.

Cognos Search appears in the middle column. 7. With Cognos Search still selected, click Move one item right to the right of the middle column. 8. Click OK. Cognos Search appears in the last column. As Jorg, we changed the look of Kazumi’s page on our portal. Kazumi’s page will still look the same when she logs on to her portal because these pages are being saved with each ’s individual preferences, as shown below:

Instructional Tips The preference settings from Jorg’s page are not carried over to Kazumi’s page. However, all other layout changes do affect Kazumi’s page. If students seem interested in viewing the differences, then log back on as Kazumi and go to the page.

9. Click Log Off, and leave Cognos 8 open for the next demo. Results: As Jorg, we accessed Kazumi’s page and placed it as a tab on our main page as a reference.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-57

COGNOS

8

BI

AD

HOC

QUERYING

Additional Information You can enable portlet-to-portlet communication on all Cognos Content portlets and Utility portlets.

Enable Communication Between Portlets ƒ Portlet-to-portlet communication allows for actions in one portlet to appear in other portlets.

For example, Cognos Navigator and Search portlets can populate the Cognos Viewer portlet. In Cognos 8 BI, Portal Services allows for portlets to interact with one another. This allows you to keep browsing and consuming all in one browser instance, whereas before you opened content in a new browser window.

The report displayed in Cognos Viewer was selected from the list of reports displayed in Cognos Navigator.

•

•

Any object that is run in the Navigator or Search (including pages) can be displayed in the Cognos Viewer.

You can set up portlet-to-portlet communication so that when you click an entry in one portlet (for example, Cognos Navigator), the output is rendered in another portlet (for example, Cognos Viewer). This saves time as you do not have to look at a generated report outside of your portal page. Having portlet-to-portlet communication allows s a quick way to search, find, and then report on information.

For the Cognos utility portlets, s can direct their output of the Image, Bookmarks, and RSS Viewer to the HTML Viewer. With portlet-to-portlet communication, you can click on any report and have that report run in many Cognos Viewers on many pages in your portal tabs. The report will be displayed in any portlet that has the correct channel name. This functionality applies only to Cognos portlets and works only for portlets of the same group. In each group, one portlet is a target portlet, and the remaining portlets are source portlets. The results of actions in the source portlets are shown in the associated target portlets.

2-58

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Demo 8 ƒ Enable Communication between Portlets

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-59

COGNOS

8

BI

AD

HOC

QUERYING

Demo 8: Enable Communication between Portlets Purpose: We want to be able to navigate and search for reports easily, and view the results within the same page for convenience. To do this, we will configure portlet-to-portlet communication. Task 1. Link the Cognos Navigator portlet to the Cognos Viewer portlet and personalize the appearance. 1. Click Log on again. 2. On the Log on screen, in the ID box, type uragomek, in the box, type Education1!, and then click OK.. 3. If the Cognos 8 Welcome page appears, click Cognos Connection. 4. Click the Resources tab. 5. In the top left hand corner, click Tab Menu portal tab.

, and then click Edit this

We want to have Cognos Search appear in the left column. 6. In the right column, click Cognos Search. 7. Click Move one item left

.

Cognos Search appears in the middle column. 8. With Cognos Search still selected, click Move one item left to the left of the middle column. 9. Under Number of columns, click 2 columns. 10. Change the left column width to 30%. 11. Change the right column width to 70%. 12. Click OK. 13. In Cognos Connection, on the Cognos Viewer title bar, click Edit. We want Cognos Viewer to be the receiver of actions made in the Cognos Navigator portlet; therefore, we will specify the same name in the Cognos Viewer portlet as in the Cognos Navigator portlet. 14. In the Channel name box, type reportviewer. This will enable the portlet to receive actions from other portlets. 15. Under Portlet action, click Run the report. We want to set the window height fairly high to be able to see most of the report in Cognos Viewer. We will limit the height to 450 pixels.

Instructional Tips In the channel name box and the In a destination portlet box, no spaces are accepted. (that is, reportviewer is accepted, but report viewer would not be accepted).

16. Under View options, in the Portlet height in pixels box, type 450 and then click OK. 2-60

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

17. On the Cognos Navigator title bar, click Edit, and then in the In a destination portlet box, type reportviewer. With limited space on our page, we want to limit the number of entries visible in our Cognos Navigator to 4. 18. Under number of entries, type 4 and then click OK. 19. On Cognos Search, click Edit. 20. In the In a destination portlet box, type reportviewer and then click OK. This lets us browse in one page and not keep opening new browser instances. Task 2. Locate a report and render it in Cognos Viewer. 1. In Cognos Search, under Search string, type revenue, and then click Search. 2. Click Low Revenue Products.

Instructional Tips Note: It will take approximately 1 minute for the report to appear. If a message appears saying "Click the button to run the report" in the Cognos Viewer, select the run icon to run the report. This will only need to be done the first time. Click the button to run the report.

The report runs and displays in Cognos Viewer. 3. In the Cognos Search portlet, click Clear the search results. 4. In Cognos Navigator portlet, click Public Folders, and then click Go Sales and Retailers. 5. Click Query Studio Report Samples. 6. Click Expand and Collapse. The report runs and displays in Cognos Viewer, as shown below:

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-61

COGNOS

8

BI

AD

HOC

QUERYING

Task 3. Remove all portal headers. We want to remove all editing icons from our page to make it cleaner. 1. In Cognos Connection, click Edit

to edit the page.

2. Click the Page Style tab. 3. Under Portlet style, select the Hide title bars check box.

Instructional Tips The has the option to hide just the edit buttons, and/or hide the title bars. s can also, hide the borders should they wish. Whatever makes the page cleaner for their personal use.

The Hide Edit buttons on the title bar check box is automatically selected after the Hide title bars check box is selected. 4. Click OK. The titles of the portlets and the edit buttons are no longer visible on the page, as shown below:

5. Click Home and leave Cognos Connection open for the next demo. Results: We navigated, searched for, and viewed report results within the same page by configuring portlet-to-portlet communication.

2-62

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

COGNOS

CONNECTION

Summary ƒ In this module, we have: ƒ

identified the advantages of using Cognos Connection

ƒ

scheduled and managed reports

ƒ

organized content

ƒ

included dynamic Web pages

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

2-63

COGNOS

2-64

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

3

Introduction to Query Studio

Cognos 8 BI

COGNOS

3-2

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

QUERY

STUDIO

Objectives ƒ In this module, we will: ƒ

list the packages available for reporting

ƒ

examine report types

ƒ

add and save data to ad hoc reports

ƒ

view data by using charts

Public Classroom If you intend to teach this module, students should be familiar with: • Cognos Connection Suggested module to reference: • Overview of Cognos 8 BI • Introduction to Cognos Connection

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

3-3

COGNOS

8

BI

AD

HOC

QUERYING

Examine Query Studio ƒ Query Studio is the ad hoc querying tool. ƒ Use Query Studio to:

3-4

ƒ

explore the corporate information that is available to you

ƒ

create ad hoc reports that can be viewed by others

ƒ

customize ad hoc reports that have been saved

ƒ

compare and analyze data

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

List the Packages Available for Reporting ƒ

ƒ

Packages use corporate business rules to define and group the data to be used for business reporting.

Relational

Dimensionally modeled

Packages can be based on models that are: ƒ

relational

ƒ

dimensionally modeled relational (DMR)

ƒ

online analytic processing (OLAP)

ƒ

a combination of the above

•

The modeled data appears as a package (the set of published metadata), that has been created by your and published to Cognos Connection.

•

A relational model contains query subjects and query items.

•

A dimensional model contains dimensions organized in hierarchies and levels.

•

Unlike relational packages, DMR and OLAP packages are dimensionally modeled data and therefore: •

organize the information in a hierarchy, from the highest level of granularity to the lowest

•

allow you to view all levels of granularity by clicking on a item directly on the report

TO

QUERY

STUDIO

Additional Information Framework Manager publishes XML based packages to the Cognos 8 server. The server compiles XML into binary run-time information after each publish. There may be a short delay at first use due to compile time. A model is a physical or business representation of the structure of the data from one or more data sources. A model describes data objects, structure, and grouping, as well as relationships and security. A model, called a design model, is created and maintained in Framework Manager. The design model or a subset of the design model must be published to the Cognos 8 server as a package for s to create and run reports. Instructional Tips DMR and OLAP models are dimensionally modeled and allow you to use drill capabilities, specifically drill up, drill down and drill through. DMR model means that you can take a relational data source and model as a star schema, and then apply hierarchies to allow drill behavior. When working with DMR data, you can perform OLAP-style querying. OLAP models perform dynamic multidimensional analysis of data. Multidimensional analysis is a process where you selectively extract and evaluate multiple aspects of your business against your key performance indicators for the purpose of decision . The different models are discussed in module "Introduction to Cognos 8". The drill capabilities are discussed in module "Create Ad Hoc Reports in Query Studio".

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

3-5

COGNOS

8

BI

AD

HOC

QUERYING

Understand Report Types ƒ Use different report types to look at data in different ways or to answer different business questions. Grouped List Report List Report

Crosstab Report

•

3-6

Each report type has its advantages: •

list reports show detailed data

•

grouped list reports eliminate duplicate rows

•

crosstab reports show a number at the intersection of each row and column (for example, between the United States and the Golf Equipment product line)

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

QUERY

STUDIO

Open Ad Hoc Reports ƒ From Cognos Connection, you can open a report in Query Studio by clicking: ƒ

the link for that report

ƒ

the Open with Query Studio icon in the Actions column

ƒ You can open an existing report to: ƒ

view the most current data in the report

ƒ

make changes to the report

ƒ

use it as the basis of a new report

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Additional Information A report always uses the most recently published version of the package. If the has republished the package since the report was last opened, you will be notified that the report will use the most recent version of the package. You must then save the report to complete the update.

3-7

COGNOS

8

BI

AD

HOC

QUERYING

Instructional Tips Before creating the report, consider the following questions: • What business question(s) do you want to answer? • What type of report will most effectively address these questions? • Who is the target audience? • What data items and calculations do you require in the report to answer your business questions?

Add Data to Ad Hoc Reports ƒ You can add data to an empty report by inserting report items from the package. ƒ You can expand the scope of an existing report by inserting additional report items, or you can focus on specific data by removing unnecessary report items.

Additional Information The grouped list report in the slide example, contains four report items that were added from the package. The report also includes one calculated column (Revenue Percent) that was created by the report author.

3-8

•

The items you add from the package to your report are called report items.

•

Report items appear as columns in list reports, and as rows and columns in crosstab reports. By default, each new report item (column) follows the last report item, but you can reorder the items. You can also click and drag an item from the package to where you want it in the report.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

QUERY

STUDIO

View Data by Using Charts ƒ Use charts to present the report graphically. ƒ A report requires at least one measure and one non-measure to produce a chart. Column Chart (Standard) Pie Chart (100% with 3D visual effect)

Instructional Tips Charts are discussed further in the "Create Reports in Query Studio" module. This chart contains one non-measure (Product line).

This chart contains two non-measures (Product line and Order method).

•

A qualitative data item, such as golf equipment, is referred to as a nonmeasure. A quantitative data item, such as sales, is referred to as a measure.

•

How the data is plotted in the chart depends on the chart type.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Additional Information In the slide example: • the pie chart shows each value in proportion to the whole (in other words, as pieces of a pie) • you can have a maximum of two non-measure in your column chart, such as Product line and Order method • you can include an unlimited number of measures in your chart, such as Quantity

3-9

COGNOS

8

BI

AD

HOC

QUERYING

Save Ad Hoc Reports ƒ Save your reports to: ƒ

preserve any changes to the report structure

ƒ

use as the basis of a new report

ƒ Reports may be saved in your personal folders or in the Public Folder depending on your access rights.

•

When you save a report in Query Studio, you are actually saving the report specification: a set of instructions for extracting and formatting a particular set of data.

•

You do not save a snapshot of the data when you click the Save or Save As button. However, you can generate and save reports in other formats, such as PDF, that reflect the state of the data at a particular time.

3-10

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

QUERY

STUDIO

Demo 1 ƒ Explore Query Studio

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

3-11

COGNOS

8

BI

AD

HOC

QUERYING

Demo 1: Explore Query Studio Purpose: As new authors for The Great Outdoors Company, we need to familiarize ourselves with the Query Studio tool. We will open a report in Query Studio, examine the characteristics of the report, and perform some basic modifications using Query Studio functionality. Task 1. Open and examine the Low Revenue Products report. 1. Start your browser, in the address box type http://<servername>/cognos8, and then press Enter. 2. On the Log on screen, in the ID box, type brettonf, in the box, type Education1!, and then click OK.

Public Classroom Because of the use of the VMware image, students will not have to type in the address in Task 1, Step 1.

3. On the Welcome page, click Cognos Connection. 4. Click the Public Folders tab, click the GO Sales and Retailers package, and then click Query Studio Report Samples. 5. Under Low Revenue Products, click Open with Query Studio - Low Revenue Products. The report opens in Query Studio. The result appears as shown below:

This is an example of a grouped list report. Notice that, in the menu on the left side, the Edit Data section is selected by default.

3-12

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

QUERY

STUDIO

Task 2. Examine the menu functionality. 1. In the menu on the left side, click Insert Data. The report items contained in the GO Sales and Retailers package are shown. Some of these items are organized into folders to make them easier to locate. These items can be added and removed from the report as required. 2. In the menu on the left side, click Change Layout. The various layout options are shown. We can add a chart, set page breaks, and perform other modifications. 3. In the menu on the left side, click Run Report. The various run options are shown. We can run the report with all data, preview limited amount of data, or preview no data. We can also view the report in formats other than HTML, such as a comma-separated values (CSV), or and Excel (XLS) file. 4. Click the Advanced Options link. The advanced query options are shown at the bottom of the screen. We can specify that we want to include footer summaries for measures, such as Revenue in the current report. We can also automatically summarize detail values, suppressing duplicate values in the process. Both of these advanced options are selected by default. 5. Click Cancel, and then in the menu on the left side, click Manage File. The file management options are shown. From here we can save the report, create a new report, or view the report definition. 6. Click the Report Definition link. The expressions for each of the six items in the report are shown at the bottom of the screen. The result appears as shown below:

For example, we can see that the Revenue Percent column is a calculation, while the Product line column is derived directly from a query item in the underlying GO Sales and Retailers package.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

3-13

COGNOS

8

BI

AD

HOC

QUERYING

7. Click the Query Information link. The details of the underlying query are shown. These details are useful for troubleshooting. 8. Click OK. Task 3. Add data to the Low Revenue Products report. 1. In the menu on the left side, click Insert Data. The contents of the package are displayed. 2. Expand the Orders query subject. The query items in the Orders query subject are displayed. This query subject also includes a folder called Returned Items, which contains query subjects specific to returned product data. 3. Double-click Quantity to add it to the report. The Quantity column is added to the right of the Revenue Percent column. 4. On the toolbar, click Save As

.

The Save As page appears. We can add a description that is meaningful to us and save the report in different locations. 5. Click Cancel. 6. In the top right corner, click Cognos Connection. 7. Close Internet Explorer. 8. Click Log off, and then close Cognos Connection. Results: We opened a report in Query Studio, examined the characteristics of the report and performed some basic modifications using Query Studio.

3-14

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INTRODUCTION

TO

QUERY

STUDIO

Summary ƒ In this module, we have: ƒ

listed the packages available for reporting

ƒ

examined report types

ƒ

added and saved data to ad hoc reports

ƒ

viewed data by using charts

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

3-15

COGNOS

3-16

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4

Create Reports in Query Studio

Cognos 8 BI

COGNOS

4-2

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Objectives ƒ In this module, we will: ƒ

create list, grouped list, and crosstab reports

ƒ

list the charts available and their appropriate use

ƒ

calculate and sort report data

ƒ

define a custom group to create your own report item

Public Classroom Suggested modules to reference: • Overview of Cognos 8 BI • Introduction to Cognos Connection • Introduction to Query Studio

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-3

COGNOS

8

BI

AD

HOC

QUERYING

Create an Ad Hoc Report ƒ You can create a new report by inserting items from a package into an empty report.

Additional Information The modeler creates the package in Framework Manager and publishes it to the Cognos Connection portal. Packages can also be deployed by the .

•

You create reports to answer specific business questions, for example how much growth in a product line can be attributed to advertising.

•

Before you can create a report in Query Studio, you must have access to a package that is available in the Cognos Connection portal.

Instructional Tips You can tell that this report uses a package built on a relational model, because of the icons used to identify query subjects and query items. The query subject corresponds to the table name in the data source. The query item represents either a column of qualitative (for example, products name) or quantitative data (or measure, for example revenue) from the database. In the slide example, we have a package called GO Sales and Retailers that contains data that we can use in our ad hoc reports.

4-4

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Display Data with List Reports ƒ Create a list report to show information in a list, such as products or customers. ƒ A list report shows data in rows and columns.

Instructional Tips The slide example shows a list report with three columns. Each column is derived directly from the package.

•

When you add report items to an empty report, you create a list report by default.

•

By default, each column that you add gets its name from the corresponding item in the package. You can rename the columns if you want.

Recall that with a list report every non-zero combination of country and product line is listed, so both the country and the product line are repeated. For example, in the slide, is listed several times, and Golf Equipment is listed several times. However, the combination of – Golf Equipment is only listed once. Items added from the package to your report are called report items. Report items appear as columns in list reports, and as rows and columns in crosstab reports.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-5

COGNOS

8

BI

AD

HOC

QUERYING

Create Grouped List Reports ƒ Group a list report to suppress duplicate values. ƒ You can organize the grouped data by row headings or section headings. Data Grouped by Row Headings

•

Data Grouped by Section Headings

You can only group report items containing text data or non-measure numeric data, such as order numbers. You cannot group by measures, such as Quantity.

Additional Information When you group by row headings, you: • reorder the rows of the selected report so that identical values appear together • suppress the display of duplicate rows • show footer values for each grouped report item that has summarization turned on (such as Product line in the slide example) When you group by section headings, you: • show the value of a selected report item (such as Product line) as the heading of a section • create a chart for each section, if you specified charting Grouping a report by measures (such as individual quantities of each product sold) would produce too many groups that are useless for data analysis.

4-6

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Sort Data in Ad Hoc Reports ƒ You can sort your data in either ascending or descending alphabetical or numeric order. ƒ When sorting grouped data, you can choose to sort the detail values or you can sort the summary values in the footers.

•

•

Before you can sort data, you must first select the column that you wish to sort. You can then sort data by either: •

clicking the Sort button on the toolbar.

•

selecting Sort from the Edit Data section of the menu.

When you select Sort, you can choose how you want the data to be sorted.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Additional Information You may want to sort the data in descending (highest to lowest) order instead of ascending (lowest to highest) order. When you sort on a numeric column, the sort page appears, whether you select the Sort icon or the menu option. When you sort on text, the sort page appears only when you select the menu option. If you select the Sort button, the column will be sorted in alphabetical order.

4-7

COGNOS

8

BI

AD

HOC

QUERYING

Demo 1 ƒ Create, Sort and Group an Ad Hoc Report

4-8

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Demo 1: Create, Sort and Group an Ad Hoc Report Purpose: Management wants to find out more about product sales and returns. Specifically, they want to see the total sales of each product type and how many items in each product type have been returned. They also want to see the product returns grouped by order method. We will create a list report that includes this information, group the data, create a section for each group, and then sort the report. We will then save the report to My Folders, in Cognos Connection. Task 1. Add report items to the report. 1. Start your browser, in the address box type http://<servername>/cognos8, and then press Enter. 2. On the Log on screen, in the ID box, type brettonf, in the box, type Education1!, and then click OK.

Public Classroom Because of the use of the VMware image, students will not have to type in the address in Task 1, Step 1.

3. On the Welcome page, click Query Studio, and then on the Select a package page, click Go Sales and Retailers. Query Studio opens with the Insert Data section of the menu selected. 4. In the menu on the left side, expand Products, and then double-click Product type. Product type is added as the first column of the report. 5. In the menu on the left side, expand Orders, and then double-click Quantity and Revenue to add them to the report. 6. Under Orders, expand the Returned items folder, and then double-click Return quantity to add it to the report. 7. Under Orders, double-click Order method to add it to the report. The result appears as shown below:

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-9

COGNOS

8

BI

AD

HOC

QUERYING

Task 2. Group the report data by Order Method. 1. Click the Order method column header, and then on the toolbar, click Group

.

The data in the list report is grouped by each order method. For example, we can see that 6,854 binoculars were sold by email. 2. Click the Order Method column header, and then on the toolbar, click Create Sections

.

The result appears as shown below:

The report is divided into sections. Each section of the report contains data specific to an order method (such as email). 3. At the bottom of the report, click Page Down. The next page of the report is shown. This page contains data about sales made by fax. 4. At the bottom of the report, click Bottom. The last page of the report is shown. This page contains data about sales made over the Web. 5. At the bottom of the report, click Top. We are returned to the first page of the report. Task 3. Sort the data. 1. In the menu on the left side, click Edit Data. The editing options are shown. 2. Click the Return quantity column header, and then on the toolbar, click Sort. The Sort page appears.

4-10

Instructional Tips The sort page appears because this is a numeric column.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

3. In the Sort Order area, click the button beside Descending (9 to 1), and then click OK. The result appears as shown below:

The data in each section is sorted by return quantity, in descending order (highest to lowest). We can see that, with orders made by email, the Lantern product type experienced the most returns. Some product types, such as First Aid, experienced no returns at all.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-11

COGNOS

8

BI

AD

HOC

QUERYING

Task 4. Save the report. 1. On the toolbar, click Save As. The Save As page appears. 2. In the Name box, type Demo 1_Create Reports in Query Studio. 3. Under Location, click Select My Folders, and then click OK. The report is saved to My Folders. 4. Leave Query Studio open for the next demo. Results: We created a list report that includes this information, grouped the data, created a section for each group, and then sorted the report. We then saved the report to My Folders, in Cognos Connection.

4-12

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Specify Column Order in Ad Hoc Reports ƒ You can delete one or more columns from a report. ƒ You can re-order columns in a report by using cut and paste functionality.

•

When you paste a column that you have cut to the clipboard, the column is moved to the left of the column that you have selected.

Instructional Tips In the slide example, we permanently deleted the Revenue Percent column from the report. If you delete a column that references a filter, you will be prompted to delete the filter as well. If you delete the column without deleting the filter, the data in the report will still be filtered by the criteria you set previously.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-13

COGNOS

8

BI

AD

HOC

QUERYING

Expand and Collapse Data Groups ƒ You can expand or collapse grouped data in a report to show more or less detail. Fully expanded grouped report Grouped report collapsed two levels

Grouped report collapsed one level

Grouped report collapsed three levels

Instructional Tips Collapsing a report removes a level of detail. In the slide example, we collapsed the report one level, which removed the Product name column from the report. Product type became the lowest level. We then collapsed the report a second time, which removed the Product type column, to make the lowest level of detail Product line. When we collapsed the report one last time, we removed all detail from the report. All we can see is the total number of products sold (2,215,354) and the total revenue generated by these product sales ($171,576,387.88). After collapsing the report three times, we cannot collapse it any further. However, if we want to see how Quantity and Revenue break down by Product line, Product type, and Product name, we can click the Expand Group button on the toolbar to see the detail again.

4-14

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Display Crosstab Reports ƒ Create a crosstab report to present data more clearly. ƒ You create a crosstab report by rotating the values in a selected column of a list report to produce column headers. ƒ A crosstab report shows a summary value at the intersection of each row and column.

Additional Information In a crosstab report, the selected column produces the column headers. The remaining columns become the rows of the report.

List Report Before Pivoting Crosstab Report after Pivoting on Country Column

•

You can create a crosstab report to display summary data.

•

You can pivot on more than one non-measure column to create a nested crosstab.

Instructional Tips The report on the right side of the slide example displays the total number of products sold in each country, broken down by product line. The intersection of each row and column displays a summary value. For example, we can add Cities underneath the Country column headers at the top of the report. Adding this report item will further subdivide sales by individual cities in each country. However, if we have data about many cities, the resulting nested crosstab will contain a large amount of data, forcing the to scroll back and forth to see everything. In fact, this type of nested crosstab may contain too much data to be useful for analysis.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-15

COGNOS

8

BI

AD

HOC

QUERYING

Demo 2 ƒ Create an Ad Hoc Crosstab Report

4-16

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Demo 2: Create an Ad Hoc Crosstab Report Purpose: We are approaching the end of the fiscal year. Business analysts at The Great Outdoors want to see which product lines are the least popular, aggregating the data over three years, in order to determine which need more attention in the marketing budget. We have been asked to create a report that includes revenue data summarized at the product line level, rather than the product type level. We have also been asked to summarize the data by order method. To accomplish this, we will create a crosstab report that includes this data. Task 1. Create a new list report and add report items. 1. On the toolbar, click New Report

.

2. In the menu on the left side, under Orders, double-click Order method to add it to the report. 3. In the menu on the left side, under Products, double-click Product line to add it to the report. 4. In the menu on the left side, under Orders, double-click Revenue to add it to the report. The result appears as shown below:

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-17

COGNOS

8

BI

AD

HOC

QUERYING

Task 2. Pivot the data to create a crosstab report.

Instructional Tips This demo does not have to be saved.

1. Click the Product line column header. 2. On the toolbar, click Pivot (creates a crosstab)

.

The result appears as shown below:

By pivoting a column of the list report, we create a crosstab. Each of the values in the pivoted column (Product line) becomes a column, while each of the values in the remaining non-measure column (Order method) becomes a row. The Revenue numbers are located between each row and column intersection (for example, $641,666.40 worth of Outdoor Protection products were sold on the Web). 3. Click Cognos Connection at the top right corner, and click OK to navigate away from this page. Results: We created a crosstab report that includes product revenue data summarized by order method.

4-18

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Display Data Graphically with Charts ƒ Create a chart to present information graphically. ƒ Charts are useful for seeing patterns and trends in data.

•

You must have at least one measure and one non-measure in your report to create a chart.

•

When you create a chart, Query Studio plots data that has been summarized at the highest level of detail.

•

When you create the chart, you can specify that you want to see both the chart and the actual values in the report. In the slide example, we are only viewing the chart.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Instructional Tips In the slide example, the measure is Quantity, and the non-measures are Product line and Country. For example, 103,412 units of camping equipment were sold in . Additional Information You can focus the chart by eliminating unnecessary measures from your report and by reordering the columns.

4-19

COGNOS

8

BI

AD

HOC

QUERYING

Specify a Chart Type ƒ Consider what you want the chart to illustrate before you choose a chart type.

Use a pie chart to highlight proportions rather than actual values.

Use a column chart to show how data changes over time.

Use a bar chart to place less emphasis on time and focus on comparing values.

Your situation

Chart type

You want to show the relationship of parts to the whole.

Pie

You want to highlight values for easy comparison and plot your numbers vertically.

Column

You want to highlight values for easy comparison and plot your numbers horizontally.

Bar

4-20

Instructional Tips In charts based on a dimensionally modeled package, the pointer changes to a hand when you pause over a drillable item, and a tooltip indicates what you are drilling on.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Specify a Chart Type (Cont’d) ƒ Consider what you want the chart to illustrate before you choose a chart type.

Use a line chart to plot many measures.

Use a column line chart to compare two different measures in the same chart.

Use an area chart to show how much the value of a measure changes over time.

Use a radar chart to compare measures using a radial figure.

Your situation

Chart type

You want to view trends by plotting data at equidistant points connected by lines.

Line

You want to emphasize the magnitude of change over time.

Area

You want to compare two different measures.

Column line

You want to compare data by integrating multiple axes into a single radial figure.

Radar

Additional Information The data markers in a line chart take up little space. When you move the pointer over a data marker in a line chart, a screen tip appears containing the label and value of the data marker. When using an area chart, you can select a stacked configuration (covered later in this module) to show the relationship of parts to the whole. In a column line chart, the second quantitative axis appears on the right side of the chart. As a result, the line that tracks the second measure (for example, gross margin) crosses the columns that track the value of the first measure (for example, revenue). The columns that track the first measure are aligned along the bottom axis. A radar chart plots the values of measures along multiple axes. These values are then ed to adjacent axes by connecting lines.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-21

COGNOS

8

BI

AD

HOC

QUERYING

Enhance the Display of Charts ƒ Consider what you want the chart to illustrate before you choose a chart variation.

Use the standard variation to distinguish each measure by a differently colored data marker.

Additional Information When you create standard charts using multiple measures, you can distinguish each measure by the color of its data marker.

Use the stacked and 100% stacked variations to distinguish the value of each measure by a differently colored section in each stack.

For example, a standard column chart that has multiple measures plots the sales for each region to emphasize the actual value that each region achieves in sales.

In the 100% stacked variation, the top of each stack represents 100%.

Use the 3D display to enhance the chart with a three-dimensional visual effect.

Note: In area and radar charts that use a standard configuration and multiple measures, the data markers for some measures can hide others.

Your situation

Chart variation

You want to plot the actual value of each measure from a common baseline.

Standard

You want to plot the relative value that each measure contributes to the total.

Stacked

You want to plot the relative contribution of each measure to the total, and express this value as a percentage.

100% Stacked

You want to plot measures in three dimensions.

3-D Axis

In a stacked chart, you can distinguish each measure by the color of its section in the stack. The top of each stack represents the accumulated totals for each category. For example, a stacked column chart that plots the sales for each region will emphasize the proportion that each region contributes to the total. Note: In line charts that have multiple measures, it is difficult to distinguish between standard and stacked configurations. In a 100% stacked chart, you can distinguish each measure by the color of its section in the stack. The top of each stack represents 100 per cent. For example, a 100% stacked column chart that plots the sales for each region will emphasize the percentage that each region contributes to total sales. 100% stacked charts highlight proportions, not figures. If the actual values are important, use another chart configuration.

4-22

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Add Calculations to Reports ƒ You can perform many types of calculations in Query Studio. For example, you can: ƒ

add calculated columns

ƒ

add a summary row to the footer

ƒ Calculation results are not stored in the underlying data source. Instead, Query Studio runs the calculation each time you run the report.

Additional Information The functions available when creating calculations are restricted by functions available in the data source.

ƒ You can create calculations directly in an ad hoc report, or add predefined calculations from the package.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-23

COGNOS

8

BI

AD

HOC

QUERYING

Create Calculated Columns ƒ You can add value to your analysis by inserting calculations that derive numeric, text, and date values from your report items.

Instructional Tips In the slide example, we are calculating the percentage that each country’s sales of camping equipment contributed to the total. We can see that the United States represents the largest contributor, at 20.71%.

•

Numeric calculations include arithmetic, percentage, or analytic operations, and can use multiple columns.

•

Some examples of text calculations are to remove trailing spaces and to concatenate more than one column.

4-24

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

Add Summarized Data to Ad Hoc Reports ƒ You can use predefined summaries to calculate values in individual report items.

REPORTS

IN

QUERY

STUDIO

Instructional Tips You can only get a count of individual text data values, not an average or sum of these values. Additional Information You have to know what summary function you have applied to a column. It is not indicated in the summary line.

On average, each order in 2004 contained 140,426 items.

Function

Description

Total Count Maximum

Returns the sum of a set of values. Returns the total number of records. Returns the maximum value. When applied to date or time data, this returns the latest value. Returns the minimum value. When applied to date or time data, this returns the earliest value. Returns the average of a set of values. Specifies that the summary is defined within the expression that is used to populate the column. It is expected that the expression itself is an aggregation function and should not require modification to provide summary values. Summarizes values based on the data type. Removes footers. This applies only to footer values, not details.

Minimum Average Calculated

Automatic None

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-25

COGNOS

8

BI

AD

HOC

QUERYING

Create New Report Items with Custom Groups ƒ Create custom groups to produce new report items that contain values that are meaningful to you.

•

•

4-26

You can create a custom group to: •

convert a list of numeric results into broader categories (for example, sales below and above $100,000)

•

reduce the number of values into a smaller, more meaningful sets

You can define a group based on individual items (such as countries in North America), or a range of values (such as sales below and above $100,000).

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Demo 3 ƒ Calculate Data in an Ad Hoc Report

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-27

COGNOS

8

BI

AD

HOC

QUERYING

Demo 3: Calculate Data in an Ad Hoc Report Purpose: In Demo 1, we created an ad hoc report that included data about revenue and returns, organized by product type. Business analysts want to highlight the sales of specific product types in Canada. We will add a calculation that shows the percentage of revenue that each product type contributed to the whole. We will then sort the data to see which product type contributed the greatest to the total revenue. Task 1. Open an existing report and add a new column to the report. 1. In Cognos Connection, click My Folders tab, and click Demo_1 Create Reports in Query Studio. We want to make the report less complex. Therefore, we will remove the Order method sections. 2. Click the Order method: E-mail section header, and then on the toolbar, click Delete. The order method report item is removed from the report, and the report no longer contains sections. Task 2. Add a calculation to the report. 1. Click the Revenue column header, and then on the toolbar, click . Calculate The Calculate page appears. 2. In the Operation Type list, click Percentage, and then in the Operation list, click % of total. 3. In the New item name section, click the radio button beside the empty box. 4. In the box, type Percentage of Total Revenue, and then click Insert. The calculated column is added to the report, and the data is formatted as a percentage.

Instructional Tips If you Ctrl+click more than one column (for example, Revenue and Quantity), you can only choose one column in the Report item box of the Calculate page to create the Percentage of Total Revenue calculation.

Task 3. Sort the calculated column. 1. Click Percentage of Total Revenue header, then on the toolbar, click Sort

.

The calculated column is sorted in ascending order, because it is a numeric column.

4-28

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

2. Click Sort, a second time. This time the calculated column is sorted in descending order. We see that tents make up over 27% of the total revenue, over all years.

3. Click Cognos Connection, and click OK to navigate away from this page.

Instructional Tips This demo does not have to be saved.

Results: We added a calculation that shows the percentage of revenue that each product type contributed to the whole. We then sorted the data to see which product type contributed the greatest to the total revenue.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-29

COGNOS

8

BI

AD

HOC

QUERYING

Examine Dimensional Data ƒ In Query Studio, when working with dimensional data, a metadata view of the dimensional model appears in the data tree.

Dimension

A cube is a physical data source containing a multidimensional representation of data. A cube contains information organized into dimensions and optimized to provide faster retrieval and navigation in reports. Cubes created through Cognos Transformer are also known as PowerCubes.

Hierarchy Level

Measures Dimension

• •

Instructional Tips For example, if you add the root level from the Products dimension, (Products) to your query, a Products item will appear in the column. This item is the parent and rollup of all items in the Products dimension and can be drilled down on to view the next level of detail (Product lines).

Instead of query subjects and query items, dimensional data contains dimensions, hierarchies, and levels. When adding dimensional data to reports, the level you select within a hierarchy or dimension determines the level of detail that will appear in your report.

In the above slide, the underlying data source is a PowerCube. The structure of the data is retained when you create a package from it. Additional Information Dimension - used to reflect the natural hierarchies in your business like time (year, quarter, month) or organization (VP, Director, employee) Hierarchy - a collection of dimensional organized into a tree structure, with each member having one or more parent and an arbitrary number of child . Level – a set of that have common attributes. For example, a geographical dimension might contain levels for country, region, and city. Multiple levels can exist within a level hierarchy, beginning with the root level. The root level is the parent and rollup of all in the first level. It is used to obtain a rollup of all values across the hierarchy and to provide a convenient point to start drilling.

4-30

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

Change the Focus of a Report ƒ When working with dimensional data, you can drill up and drill down within a report. Before Drill Down

After Drill Down

Drill Down on 2004

REPORTS

IN

QUERY

STUDIO

Additional Information Drill Up and Drill down options are only available when working with a dimensional model. You can access the drill options using the toolbar button or the rightclick menu. You can disable drill up and drill down. From the Run Report menu, click Advanced Options, and then clear the Enable drill up and drill down in the report output check box. You can drill down or drill up on both rows and columns at the same time by right-clicking an intersection cell, and then clicking Drill Down or Drill Up.

•

Drilling down lets you narrow the focus of your report by displaying a more detailed level of data.

•

In lists and crosstabs, hyperlinks identify drillable items.

•

You can also expand the focus of your report by drilling up.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

For example, a column chart shows revenue by product line by year. You can drill on a single axis, such as product line or year. You can also drill on both axes, such as Camping Equipment and 2005. In this case, you are drilling on the intersection of Camping Equipment and 2005. Instructional Tips In the above example, we have drilled down on the year 2004, to view more details about each quarter.

4-31

COGNOS

8

BI

AD

HOC

QUERYING

View Related Data ƒ You can navigate from a Query Studio report to another target to view additional information about related data. Query Studio Report Query Studio Report

Target Containing Related Data Go to

•

•

4-32

Instructional Tips The target report only shows data for the Americas, which is why the Revenue for 2004 Camping Equipment is much lower than in the Query Studio report.

Additional Information Drill-through definitions are created in Cognos Connection and allow s to navigate to a specific target from any query, report, or analysis that uses a specific package.

You can navigate to targets such as: •

another Query Studio query

•

a Report Studio report

•

an Analysis Studio analysis

Before you can navigate to another target, you must create a drill-through definition for the package you are using.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Demo 4 ƒ Navigate Dimensional Data

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-33

COGNOS

8

BI

AD

HOC

QUERYING

Demo 4: Navigate Dimensional Data Purpose: We want to create a report to examine product line revenue for 2005. We will create a list report and drill down on Years to examine data for 2005. We will then convert the list to a crosstab, add a chart to the display and then drill down on the chart and crosstab simultaneously. Task 1. Explore dimensional objects and hierarchies. 1. Click Query Studio, and then on the Select a package page, click Great Outdoors Company.

Additional Information Here you are using Drag whereas before you used 'double-click'

The Great Outdoors Company package is based on the information in the Great_Outdoors_Company.mdc, a PowerCube. 2. In the data tree, expand the Years dimension, and the Years hierarchy, and then drag the Year level to the report. 3. Expand the Products dimension, and then drag the Product line level to the right of the Year column. 4. Expand Measures, and then drag the Revenue measure to the right of the Product line column. 5. Click the Year column heading, and then on the toolbar, click Group

.

We want to examine the revenue for the Camping Equipment only. 6. Click Camping Equipment to drill down. The report displays revenue camping equipment data items for all years. Task 2. Convert the list to a crosstab report. 1. Click the Year column header, and then on the toolbar, click Pivot (creates a crosstab)

.

The Year level corresponds to the column headers. Product types (corresponding to camping equipment) are the rows.

4-34

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

2. Click the Cooking Gear row header (not the label), and then on the toolbar, click Drill Up

.

The result appears as shown below:

3. On the toolbar, click Chart

REPORTS

IN

QUERY

STUDIO

Instructional Tips Row header = area around the text in the box. Row label = text

.

4. Click OK to accept the default Column chart. Notice that a chart has been added to the report, above the crosstab. We want to view only data for 2005. We can drill down in the chart. 5. In the chart, click the 2005 column label to drill down. Notice that we drilled down on 2005. We also drilled down in the crosstab and the chart at the same time, as shown below:

6. Leave Query Studio open for the next demo. Results: We created a list report to examine product line data. We drilled down on Years to examine revenue data for 2005. We then converted the list to a crosstab, added a chart to the display, and then drilled down on the chart and crosstab simultaneously. © 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Instructional Tips If you want to drill down on only one dimension, you can click an item in the legend.

Instructional Tips This demo does not have to be saved.

4-35

COGNOS

8

BI

AD

HOC

QUERYING

Demo 5 ƒ Insert a Calculated Column and Display a Chart

4-36

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Demo 5: Insert a Calculated Column and Display a Chart Purpose: We have been asked to modify an already existing report to determine the percentage of revenue that is profit, in January 2005. We will need to add a calculation and to display the results graphically. Task 1. Add a calculation. 1. On the toolbar, click Chart OK.

, click None for Chart type then click

2. On the toolbar, click Swap Rows and Columns

.

Swapping rows and columns on a crosstab allows you to see the same level of detail, but with the rows as columns, and vice versa. If you have a chart, you have the option of swapping rows and columns in the chart as well. 3. Click the Camping Equipment column header (not the label), and then on the toolbar, click Delete. 4. From the Insert Data menu, expand Measures, and then drag Gross Profit to the right of Revenue.

Instructional Tips Notice that when you are editing an existing report, the Edit Data menu is opened.

5. Shift+click the column header Revenue and column header Gross Profit, and then on the toolbar, click Calculate. 6. In the Operation Type list, click Percentage, then in the Operation list, click %, then below Order select Gross profit as a percentage of Revenue. 7. In the New item name section, click in the empty box and type % Profit per Revenue, and then click Insert. You have added a calculated column which indicates the Percentage of gross profit per revenue. Since we do not want to summarize our % Profit per Revenue, we will delete the summary cell. 8. Click the % Profit per Revenue column header. 9. Click Edit Data from the menu. 10. Click Summarize. 11. Clear the "Show Summaries For footers" checkbox and click OK.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-37

COGNOS

8

BI

AD

HOC

QUERYING

Task 2. Create a chart. 1. On the toolbar, click Chart

.

2. In the chart type list, select Column-Line and Standard, keep the other defaults, and then click OK. 3. In the chart, drill down on 2005 Q1. The result appears as shown below: Additional Information We are working with dimensional data again in this model. Note how drill options work with a chart.

By hovering over a data points in the graph, you can see the values that are reflected in the table below. From the graph, we see that in Jan 2005, the Gross profit was $3,063,274.66. 4. Click Cognos Connection, and click OK to navigate away from this page. 5. Log off and close Cognos Connection. Results: We modified an existing report, added a calculation and displayed the results graphically.

4-38

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

View Data in Ad Hoc Reports ƒ You can open a report in Cognos Viewer to look at the most recent data from the data source. ƒ You can also look at the most recent data while you are editing the report in Query Studio. HTML Report in English PDF Report in German

•

When you are editing a report in Query Studio, you can run the report in Cognos Viewer to see how it will look to end s.

•

In the menu on the left side, click Run Report, and then specify whether you want to view the report in PDF, CSV, XML, or XLS format.

•

You can also preview the data in edit mode. You can choose to view all the data, some of the data, or none of the data.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Instructional Tips In the slide example, when you look at a report in Cognos Viewer, you can specify the language and the format. For example, you can examine a German translation of the data in PDF format.

4-39

COGNOS

8

BI

AD

HOC

QUERYING

Print Ad Hoc Reports ƒ Print your report to obtain a paper copy. ƒ You can print a report directly from Query Studio, or from Cognos Connection.

•

4-40

You may also choose to generate and print the report in PDF format.

Instructional Tips In the slide example, we are printing a report directly from the browser. However, instead of selecting the Print option from the File menu, we are right-clicking a frame of the report and then selecting the Print option. By using this method, we avoid printing out a hard copy version of the entire interface.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Summary ƒ In this module, we have: ƒ

created list, grouped list, and crosstab reports

ƒ

listed the charts available and their appropriate use

ƒ

calculated and sorted report data

ƒ

defined a custom group to create your own report item

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-41

COGNOS

4-42

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Workshop 1 ƒ Create a Ranked Report

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-43

COGNOS

8

BI

AD

HOC

QUERYING

Workshop 1: Create a Ranked Report Business analysts at The Great Outdoors are aware that some products sell better than others. However, they want to know which specific products have sold the best over the past three years. In addition, they want to know the percentage that each of these products has contributed to total revenue. The District Manager has asked you, Frank Bretton, the Business Analyst to create a report that shows a ranking of the top selling products, including the revenue that each product generated. Then enhance the report to include the percentage that each product contributed to total revenue. To accomplish this: •

Create a new report in Query Studio that includes Product name and Revenue.

•

Add a calculated column called Rank that returns the ranking of each product. The operation type is Analytic and the operation itself is Rank. Move the Rank column to the far left side of the report and sort the data ascending (lowest to highest values).

•

Add a calculated column called Percentage of Total that returns the percentage of revenue that each product contributed to the whole. The operation type is Percentage and the operation itself is % of total. Move the Rank column to the far left of the report. Sort the data in the Rank column in ascending order.

•

Save the report to My Folders as Wkshp 1_Create Reports in Query Studio.

For more detailed information outlined as tasks, see the Task Table on the next page. For the final query results, see the Workshop Results section that follows the Task Table.

4-44

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Workshop 1: Task Table Task

Where to Work

Hints

1. Add report items to a new report.

Insert Data menu

• Product name, Revenue.

2. Add a calculated column to return the rank of each product.

Calculate page

•

Revenue column.

•

Call the new item Rank.

•

Operation type: Analytic.

•

Operation: Rank.

•

Revenue column.

•

Call the new item Percentage of Total.

•

Operation type: Percentage.

•

Operation: % of total.

•

Move the Rank column to the far left of the report.

•

Sort on the Rank column.

•

My Folders > Wkshp1_Create Reports in Query Studio.

3. Add a calculated column to return a percentage.

Calculate page

4. Save the report.

If you need more information to complete a task, see the Step-by-Step Instructions that follow the Workshop Results section.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-45

COGNOS

8

BI

AD

HOC

QUERYING

Workshop 1: Results When complete, the report appears as shown below:

4-46

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

CREATE

REPORTS

IN

QUERY

STUDIO

Workshop 1: Step-by-Step Instructions Task 1. Add report items to a new report. 1. Start your browser, in the address box type http://<servername>/cognos8, and then press Enter. 2. On the Log on screen, in the ID box, type brettonf, in the box, type Education1!, and then click OK.

Public Classroom Because of the use of the VMware image, students will not have to type in the address in Task 1, Step 1.

3. On the Welcome page, click Query Studio, and then on the Select a package page, click Go Sales and Retailers. Query Studio opens with the Insert Data section of the menu selected. 4. In the menu on the left side, expand Products, and then double-click Product name to add it to the report. 5. In the menu on the left side, expand Orders, and then double-click Revenue to add it to the report. Task 2. Add a calculated column to return the rank of each product. 1. Click the Revenue column header, and then on the toolbar, click Calculate. The Calculate page appears. 2. In the Operation type list, click Analytic, and then in the Operation list, click Rank. 3. In the New item name section, type Rank. 4. Click Insert. Each product is ranked by the revenue it has generated. We can see that Star Dome, with over 14 million dollars in revenue, is the highest-ranking product. 5. Click the Rank column header, and then on the toolbar, click Sort. The data is sorted according to rank. Star Dome is at the top of the list. Task 3. Add a calculated column to return a percentage. 1. Click the Revenue column header, and then on the toolbar, click Calculate. The Calculate page appears. 2. In the Operation type list, click Percentage, and then in the Operation list, click % of total. 3. In the New item name box, type Percentage of Total.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

4-47

COGNOS

8

BI

AD

HOC

QUERYING

4. Click Insert. The calculated column is added to the report. We can see that Star Dome, the highest-ranking product, represents over eight percent of the total sales of all the products. 5. Click the Rank column header, on the toolbar, click Cut, and then click the Product name column header. 6. On the toolbar, click Paste (before the selected report item). The Rank column is moved to the far left of the report. Task 4. Save the report. 1. On the toolbar, click Save As. The Save As page appears. 2. In the Name box, type Wkshp 1_Create Reports in Query Studio. 3. Under Location, click Select My Folders, and then click OK. The report is saved to My Folders. 4. Close Internet Explorer.

4-48

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5

Enhance Reports in Query Studio

Cognos 8 BI

COGNOS

5-2

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Objectives ƒ In this module, we will: ƒ

highlight report items using conditional styles

ƒ

focus the scope of a report using filters

ƒ

format report objects

ƒ

apply a report template

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-3

COGNOS

8

BI

AD

HOC

QUERYING

Highlight Report Items using Conditional Styles ƒ You can define conditional styles to highlight key information in a report.

•

You can use conditional styles with numeric data, text data, and date values.

•

You can use a predefined style, or you can create your own style.

Instructional Tips In the above example, we use conditional styles to highlight all revenue greater than five million in green. Additional Information To create your own style, click the Edit button beside the appropriate range. If you attempt to change a predefined style, Query Studio automatically creates a custom style rather than change the predefined style.

5-4

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Demo 1 ƒ Use Conditional Formatting

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-5

COGNOS

8

BI

AD

HOC

QUERYING

Demo 1: Use Conditional Formatting Purpose: Your manager needs a report that displays revenue by order method. Your manager has asked that revenue greater than $160,000 and revenue less than $50,000 be highlighted so that they are easily identified. Task 1. Open Query Studio.

Public Classroom Because of the use of the VMware image, students will not have to type in the address in Task 1, Step 1.

1. Start your browser, in the address box type http://<servername>/cognos8, and then press Enter. 2. On the Log on screen, in the ID box, type brettonf, in the box, type Education1!, and then click OK. 3. On the Welcome page, click Query Studio, and then on the Select a package page, click Go Sales and Retailers. Query Studio opens with the Insert Data section of the menu selected. Task 2. Add columns to the report. 1. Under Orders, double-click Order method. The Order method column is added to the report 2. Under Products, double-click Product type. The Product type column is added to the report. 3. Under Products, double-click Product name. The Product name column is added to the report. 4. Under Orders, double-click Quantity. The Quantity column is added to the report. 5. Under Orders, double-click Revenue. The Revenue column is added to the report. The result appears as shown below:

5-6

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Task 3. Highlight all revenue greater than $160,000 in green, and less than $50,000 in red. 1. From the menu, click Change Layout. 2. Click the Revenue column header, and then click Define Conditional Styles. The Define conditional styles dialog box appears. 3. In the New value box, type 160000, and then click Insert

.

We now have two ranges: the lower is 160,000 and below, and the upper is above 160,000. 4. In the New value box, type 50000, and then click Insert. Notice three ranges: •

Greater than 160,000

•

Greater than 50,000 and less than or equal to 160,000

•

Less than or equal to 50,000

5. In the highest value range, in the dropdown list, click Excellent (has a default color of green). 6. In the lowest value range, in the dropdown list, click Poor (has a default color of red).

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-7

COGNOS

8

BI

AD

HOC

QUERYING

7. Click OK, and then click in the white space to clear the Revenue column. We can see all values greater than 160,000 highlighted in green, and all values lower than 50,000 highlighted in red, as shown below:

Leave Query Studio open for the next demo. Results: We created a report that displays revenue by order method, and highlighted high and low revenue amounts in different colors so they are easily identified.

5-8

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Filter Data in Ad Hoc Reports ƒ Create a filter to retrieve a subset of records in a report. ƒ Any data that does not meet the criteria of the filter is eliminated from the report. Original report Filter on country

Filtered report

•

You can create custom filters or use filters that are included in the published package (model filters).

•

When you filter on a numeric report item or date report items, you specify a range of acceptable values.

•

When you filter on a non-numeric column, you specify particular values that will be returned when the report is run (for example, Spain and United States only).

•

When you filter data, you have the option to query: •

On missing values.

•

leaving out missing values

•

including missing values

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Additional Information You must have a column selected before you can select the filter option. You can either click the Filter button on the toolbar or select the Filter option from the Edit Data section of the menu. When you filter on dimensional data, you have the ability to see the parent. This is a useful feature if you have duplicate entries, for example, two staff with the same name, reporting to different managers.

5-9

COGNOS

8

BI

AD

HOC

QUERYING

Filter Data Based on Report Details ƒ You can filter the values that appear in the detail rows of your report. This filtering eliminates detail rows that do not meet the specified criteria.

Detail rows with quantities less than or equal to 30,000 are displayed in the filtered report.

•

Filtering on detail rows affects the summary values in your report.

•

If you remove detail rows that contribute to a summary value, the summary value is decreased.

Instructional Tips In the screen capture on the left side of the slide example, the company sold 224,110 camping equipment products. This summary value is made up of three detail values: 103,412 from , 3,562 from Japan, and 117,130 from United States. We created a filter to exclude countries that sold more than 30,000 items in a particular product line. After applying this filter, the and United States rows were removed from the Camping Equipment section of the report, because more than 30,000 items were sold in these countries.

5-10

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Filter Data Based on Group Summaries ƒ You can filter on values in the footers of your ad hoc report. This filtering eliminates groups from your report that do not meet the specified criteria.

If the Product Line group summary is less than or equal to 100,000, then all the detail rows for that product line are displayed in the filtered report.

•

You can filter on the group summaries, such as the total number of camping equipment products sold.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-11

COGNOS

8

BI

AD

HOC

QUERYING

Filter Data Based on Individual Records ƒ You can use a filter based on individual records in the underlying data source, even if the report contains only aggregated data. Original report

Records in the data source that have quantities of 50 or more are summed. Filtered report

Data source

•

A report may contain only aggregated (summed) data, such as the total number of camping equipment products sold in the United States.

•

However, each summary row is the result of aggregating data from individual records in the underlying data source. You can filter on the data in these underlying records.

5-12

Instructional Tips In the slide example, 117,136 camping equipment units were sold. This value is calculated by summarizing the values from individual orders. In the filter, we are specifying that we only want to see data from orders where 50 or more units were purchased at one time. Orders of less than 50 units are removed from the report, and the summary value for the product lines is re-calculated. As we can see from the slide example, the re-calculated summary values are lower in the filtered report.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Specify Filter Criteria with Prompts ƒ Create a prompt if you want to use different criteria for the same report item each time the report is run. ƒ Using prompts is faster and easier than repeatedly changing the filter.

•

Prompts are based on filters.

•

To create a prompt, you:

•

•

select the query item you want to include in the filter

•

indicate that you want to prompt the to select values from the query item included in the filter, every time you run the report

The contents of the report reflect the one or more values selected in the prompt.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Instructional Tips In the slide example, the must select one or more countries to determine what data they want to see in the report. The selected Australia, Canada, and , so the resulting report only contains data for those countries. Additional Information You select items to include in your prompt filter and run the report. The next time the report is run, the items that you selected previously are selected by default in the prompt.

5-13

COGNOS

8

BI

AD

HOC

QUERYING

Use Advanced Filters ƒ You can combine two or more filters to do more complex filtering in your reports using AND/OR conditions.

5-14

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Demo 2 ƒ Filter and Calculate Data in an Ad Hoc Report

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-15

COGNOS

8

BI

AD

HOC

QUERYING

Demo 2: Filter and Calculate Data in an Ad Hoc Report Purpose: In Demo 1, we created an ad hoc report that included data about revenue organized by order method. Business analysts want to highlight the sales of specific products in Canada. We will enhance the existing report by including returned quantities. We will then add a calculation that shows the amount of revenue (as a percentage) that each product contributed to the whole. Lastly, we will create a filter to retrieve only Canadian data. Task 1. Add a new column to the report. 1. Click Insert Data, expand Orders and then expand Returned items. 2. Under Returned items, double-click Return quantity. The Return quantity column is added to the report.

5-16

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

3. Click the Product type column header, and then on the toolbar, click Group

.

The result appears as shown below:

4. Click the Order method column and click Delete. The Order method report item is removed from the report. Recall that the conditional style from Demo 1 is still active. Amounts that are greater than 160,000 are highlighted in green. Since Order method is deleted, the revenue is summed by Product name in the Product type group. Task 2. Add a calculation to the report. 1. Click the Revenue column header, and then on the toolbar, click Calculate

.

The Calculate page appears. 2. In the Operation Type list, click Percentage, and then in the Operation list, click % of total. 3. In the New item name box, type Percentage of Total Revenue, and then click Insert. The calculated column is added to the report as a percentage.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

Instructional Tips If you Ctrl+click more than one column (for example, Revenue and Quantity), you can only choose one column in the Report item box of the Calculate page to create the Percentage of Total Revenue calculation. At this point, you may want to show the model filters that have been included with the package. The model filters are located in the Filters folder at the bottom of the menu on the left side.

5-17

COGNOS

8

BI

AD

HOC

QUERYING

Task 3. Add a filter to include Canadian data only. 1. In the menu on the left side, click Insert Data, if not already selected, and then expand Sales branch address. 2. Double-click Country to add it as a column to the report. 3. Click the Country column header, and then on the toolbar, click Filter

.

The Filter (Pick values from a list) screen appears. 4. In the Show only the following list, select the Canada check box, and then click OK. The report now contains data relating to Canadian sales only. Since we no longer require the data in the Country column, we can delete it. 5. Click the Country column header, and then on the toolbar, click Delete. The Delete Filter screen appears, asking if we want to delete the filter as well as the column. 6. Click No. The Country column is removed from the report, but the filter remains. The result appears as shown below:

We can see the filter near the top of the report.

5-18

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Task 4. Save the report. 1. On the toolbar, click Save As

.

The Save As page appears. 2. In the Name box, type Demo 2_Enhance Reports in Query Studio. 3. Under location, click Select My Folders, and then click OK. The report is saved to My Folders. 4. Leave Query Studio open for the next demo. Results: We enhanced the report by including a return quantity column and a calculated column. The calculation shows the amount of revenue (as a percentage) that each product contributed to the whole. Lastly, we created a filter to retrieve only Canadian data.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-19

COGNOS

8

BI

AD

HOC

QUERYING

Demo 3 ƒ Use a Prompt to Filter Data

5-20

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Demo 3: Use a Prompt to Filter Data Purpose: Your manager is pleased with the report created in Demo 2 that highlighted sales for specific products in Canada. However, management wants a report that will quickly highlight sales data for specific products for any country they are interested in, not just Canada. Task 1. Remove an existing filter and add a prompt to filter data. 1. Click on Country: Canada filter at the top of the report

.

2. From the Filter (Pick values from a list) dialog box: •

select the Prompt every time the report runs check box

•

from the Show only the following list, clear the Canada check box

The result appears as shown below:

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-21

COGNOS

8

BI

AD

HOC

QUERYING

3. Click OK. The report appears as shown below:

Notice that the filter at the top of the report, is not specific to any country. We will be prompted to select a country every time the report runs. Since we can select multiple countries, we will add a country column to the report so that the country specific sales are easily identified. Task 2. Add the county column to the report. 1. In the menu on the left side, click Insert Data, if not already selected, and then expand Sales branch address. 2. Double-click Country. Country column is added to the report. Task 3. Run the report. 1. On the toolbar, click Run with All Data (and re-prompt)

.

The Prompt screen appears, prompting us to specify a country as shown below:

5-22

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

2. Click Italy and then Ctrl+click Belgium. 3. Click Finish. The focus of the report is narrowed down to sales from Italy and Belgium as shown below:

4. Leave Query Studio open for the next demo. Results: We edited a report so that the report allows us to view country specific sales data for any country we chose.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-23

COGNOS

8

BI

AD

HOC

QUERYING

Demo 4 ƒ Combine Filters

5-24

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Demo 4: Combine Filters Purpose: In Demo 2, we created a report that highlighted the sales of specific products in Canada. Management wants to enhance this report so that the report shows sales data for specific order year and month. The order year and month is determined by the person running the report. Management is interested in sales of binoculars in Canada or sales of golf accessories in Canada. Task 1. Add order year, order month and order date to the report. 1. Click Cognos Connection, and click OK to navigate away from this page. 2. From My Folders, click Demo 2_Enhance Reports in Query Studio. The report opens in Query Studio. 3. Click Insert Data and expand Orders. 4. Double-click Order year, then Order month, and then Order date to add columns to the report. The report appears as shown below:

Task 2. Add an order year prompt filter, an order month prompt filter, a product type filter and group selected filters. 1. Click the Order year column, and then on the toolbar, click Filter. The Filter screen appears.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-25

COGNOS

8

BI

AD

HOC

QUERYING

2. Select the Prompt every time the report runs check box, and then click OK. Since a filter on Country already exists, the Combine filters screen appears as shown below:

3. Click OK. 4. Repeat steps 1 through 3 for Order month. 5. Click the Order year column and click Delete. The Delete Filter screen appears, prompting us to delete the associated filter as well as the column. 6. Click No. 7. Repeat steps 5 and 6 for the Order month column delete the order month column. 8. Click the Product type column, and then click Filter. 9. From the Filter dialog box, select Binoculars, and then click OK. The Combine filters screen appears as shown below:

for Country: Canada, Order year and Order 10. Crtl+click Select line month filters, and then click Group. 11. Click OK. When this report is run, it will display data for binocular sales in Canada for the order year and order month we are prompted to enter. This is not exactly what we want, so we will modify the existing filters and then combine them again.

5-26

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Task 3. Modify the filters to include sales of golf equipment in Canada. 1. Click the Edit Data menu, and then click on the Product type column. 2. Click Combine Filters and then click Add a filter line. 3. Select Golf Accessories and then click OK. 4. Ctrl+click Product type: Binoculars and Product type: Golf Accessories filters and then click Group. 5. Change the AND operator between the second grouped filter (binoculars and golf accessories) to an OR operator as shown below:

6. Click OK. The report appears as shown below:

Note: The report displays data for all years and all months. After running the report and selecting the prompt values for year and month, the reported data will change.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-27

COGNOS

8

BI

AD

HOC

QUERYING

Task 4. Run the report. 1. On the toolbar, click Run with All Data (and re-prompt)

.

The Prompt screen appears, prompting us to specify a year and month range. We want to see July statistics for 2004 and 2005. 2. In the order year From box type 2004 and the order year To box, type 2005. 3. In the order month From and To boxes, type 7, and then click Finish. The result appears as shown below:

4. Leave Query Studio open for the next demo. Results: We enhanced the report by adding new filters and combining the filters so that sales in Canada for specific month and year are displayed for binoculars or golf accessories.

5-28

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Specify Advanced Options ƒ When you run a report, you can change the way the data is processed by automatically: ƒ

summarizing measures in the footer

ƒ

summarizing detail values and suppressing duplicates

We are not summarizing detail values. Each sale of every product is shown in the report.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-29

COGNOS

8

BI

AD

HOC

QUERYING

Demo 5 ƒ Run a Report Using Advanced Options

5-30

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Demo 5: Run a Report Using Advanced Options Purpose: As business analysts for the Great Outdoors Company, we want to create a quick report that shows how much revenue E-mail orders generated for 2005. We also want to further our analysis to determine how many E-mail orders were done for January 4, 2005. Task 1. Create a report. 1. In Query Studio, on the toolbar, click New Report prompted to save, click No.

and when

2. In the data tree, expand Orders if not already expanded, and then double-click Order year, Order method, and Revenue. Task 2. Add a year filter for 2005. 1. Click the Order year column header, and then on the toolbar, click Filter. 2. In the From box, type 2005, in the To box, type 2005, and then click OK. The report appears as shown below:

We can see that E-mail orders produced over nine million in revenue for 2005. We now want to how many E-mail orders on January 4, 2005 contributed to total E-mail revenues. Task 3. Add a new column and add a new filter for January 4, 2005. 1. From the Orders list, double-click Order date. 2. Click the Order date column header, and then on the toolbar, click Filter.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-31

COGNOS

8

BI

AD

HOC

3. Click the calendar

QUERYING

next to From Date box and select Jan 4, 2005.

4. Click the calendar next to To Date box and select Jan 5, 2005. 5. Click OK. 6. From the Combine Filters dialog box, click OK. The report appears as shown below:

We can see that on January 4, 2005 E-mail revenue was $10,408.08 of the total revenues for 2005. We can run the report and choose to not suppress duplicates in order to view how the $10,408.08 E-mail orders are broken down. Task 4. Run the report with advance options. 1. In the menu, click Run Report and then click Advanced Options. 2. Clear the Automatically summarize detail value, suppressing duplicates check box as shown below:

5-32

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

3. Click OK. Duplicate values for E-mail orders are not suppressed. The report appears as shown below:

Notice that there were three E-mail orders for January 4, 2005. The summary total for January 4, 2005 is the same as the summary total in task 3 step 6 of this demo. 4. Leave Query Studio open for the next demo. Results: We created a report to examine revenue for order methods by order year. We filtered the report on the year 2005 to identify total revenue generated by E-mail orders. We also added another filter for January 4, 2005 and ran the report again to how many E-mail orders occurred on January 4, 2005.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-33

COGNOS

8

BI

AD

HOC

QUERYING

Format a Report ƒ The formatting options available to you depend on the type of data you want to format. ƒ You can format text, numbers, currency, percentages, scientific expressions, date and time.

Format a percentage column with decimal places and negative signs.

Format text with font, size, weight, style and alignment.

•

Use the Style toolbar or the Change Layout menu to format a report.

•

When inserting a new column in a list report, the column inherits the formatting of the column to its left. If you insert the new column to the left of the first column, it inherits the formatting of the first column.

•

When inserting a new column in a crosstab report, the column inherits the formatting of the column above it. If you insert a new column above the topmost column, it inherits the formatting of the column under it.

5-34

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Demo 6 ƒ Format a Report

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-35

COGNOS

8

BI

AD

HOC

QUERYING

Demo 6: Format a Report Purpose: Management wants a report to examine revenue for order methods by order year 2005. To make the report more visually appealing, we will add a report title and formatting such as font colors and background colors. Task 1. Create a report and format the column titles. 1. In Query Studio, on the toolbar, click New Report and when prompted to save, click No. 2. In the data tree, expand Orders if not already expanded, and then double-click Order year, Order method, and Revenue. 3. Shift+click the Order year and Revenue column headers to select all of the column headers. 4. On the toolbar, click Change Font Styles

.

5. In the Font color list, click Blue. 6. In the Background color list, click Silver.

Instructional Tips If students can’t see the Change Font Styles button on the toolbar, ensure the style toolbar is visible. To make it visible, click Show Style Toolbar located at the end of the Standard Toolbar.

7. In the Size list, click 14 pt. 8. In the Weight list, click Bold. 9. Click OK, and then click the white space to the right of the report. This deselects the column headers so we can see the new formatting, as shown below:

The column headers display the new formatting. We now want to add a title and a filter to the report. Task 2. Add a title and filter to the report. 1. Click Title at the top of the report. 2. In the Title box, type Order Report, and then click OK. The new report title displays at the top of the report. We want to filter the report on the year 2005. 3. Click the Order year column header, and then on the toolbar, click Filter.

5-36

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

4. In the From box, type 2005, in the To box, type 2005, and then click OK. The report displays data only for the year 2005, as shown below:

We will format the new report title and the filter text that displays at the top of the report. Task 3. Format the report title and filter text. 1. On the toolbar, click Change Font Styles. 2. Change the font properties to the following: •

Font color: Default

•

Background color: Default

•

Style: Italic

The Size and Weight are still set to 14 pt Bold. 3. Click Advanced options. 4. Click Apply styles only to the selections below.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-37

COGNOS

8

BI

AD

HOC

QUERYING

5. Select the Title, and Filters check boxes, and then click OK. The report title and filter text are formatted, as shown below:

6. Leave Query Studio open for the next demo. Results: We created a report to examine revenue for order methods by order year. We filtered the report on the year 2005. To make the report more visually appealing, we added a formatted report title, and we also formatted the column headers and the filter text with color.

5-38

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Apply a Report Template ƒ You can apply a template to your report pages and prompt pages to give them the same look.

Additional Information Query Studio uses templates that are authored in Report Studio of Cognos 8. Templates provide a mechanism to easily format a report and images and other objects. The Query Studio templates can be used to customize page headers and footers and to re-style layout objects.

•

Templates make it easy to convert reports to corporate-standard layouts and color schemes.

For items that you can normally format within Query Studio, you can override the template format.

•

The template is applied to the report when you run the report, therefore changes to the template are applied to the report immediately.

To remove a template, in the Apply Template dialog box, click Do not apply a template.

•

Within the page header and footer, many layout objects can be used in a Query Studio template: • Image

• Table

• Date

• Block

• Time

• Text item

• Hyperlink

• Layout calculations, depending on the functions and parameters

• Page number

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-39

COGNOS

8

BI

AD

HOC

QUERYING

Demo 7 ƒ Apply Company Reporting Standards to a Query Studio Report

5-40

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Demo 7: Apply Company Reporting Standards to a Query Studio Report Purpose: Management needs a product line report that will show sales by e-mail, fax and standard mail for 2005. Using the report created in the previous demo, we will add a product line column and filter by order method. Since the report will be viewed at the next sales management status meeting, the report must display the company report layout standards such as company logo. To accomplish this, we will apply a template to the report. Lastly, we will override the column header font color and make it bold and white so that it is easier to read. Task 1. Add another column and filter to the report. 1. Using the report created in the previous demo, click Insert Data if not already selected, expand Products, and then drag Product line to the report, between Order method and Revenue. The new column picks up the formatting of the column to the left of it. 2. Click the Order method column header, and then on the toolbar, click Filter. We want to filter the report on the E-mail, Fax, and Mail order methods. 3. In the Show only the following box, select the E-mail, Fax, and Mail check boxes, and then click OK. 4. From the Combine Filters dialog box, click OK. 5. Change the report title to Product Line Report, and ensure the Show filters check box is selected.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-41

COGNOS

8

BI

AD

HOC

QUERYING

6. Click OK. The report appears as shown below:

We are ready to apply a template to the report. Task 2. Apply a template. 1. From the menu, click Change Layout, and then Apply Template. 2. Click Apply a template, and then click Select a template. 3. Click Query Studio Templates. 4. Click QS Template II, click OK, and then click OK again. Notice that the report has a company logo in the header. The colors and fonts have been changed to that of the template.

If we want to change the format of the column headers, we can override the template. 5-42

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

ENHANCE

REPORTS

IN

QUERY

STUDIO

Task 3. Override the template formatting. 1. Shift+click the Order method and Product line column headers. 2. On the toolbar, click Font Color to clear bolding.

, click White, and then click Bold

3. Click the white space to the right of the report. The column headers are easier to read in the new font style, as shown below:

4. In the top right corner, click Cognos Connection. 5. Close Internet Explorer. Results: We applied a template to a report because the template contains many of the company report formatting standards. We changed the column header font to make it easier to read.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5-43

COGNOS

8

BI

AD

HOC

QUERYING

Summary ƒ In this module, we have:

5-44

ƒ

highlighted report items using conditional styles

ƒ

focused the scope of a report using filters

ƒ

formatted report objects

ƒ

applied a report template

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

What’s Next

Cognos 8 BI

COGNOS

8

BI

AD

HOC

QUERYING

On-Site Training

When you have 8 or more people to train On-Site Training •

2

Cognos offers on-site training, a cost-effective, flexible solution when you have eight or more people to train at one time on a common topic. Courses can be delivered when you need it, customized with your data, and delivered by Cognos experienced instructors.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

WHAT'S

NEXT

Custom Training

Custom Training •

All our classroom and e-Learning courses can be customized to highlight your business, with your data. Creating a custom course that includes personally relevant topics makes it easier to learn, leading to greater productivity.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

3

COGNOS

8

BI

AD

HOC

QUERYING

Certification ƒ The Cognos Customer Certification program recognizes individuals who have obtained superior knowledge and skills with Cognos products.

Free Certification •

4

The Cognos Customer Certification program recognizes individuals who have obtained superior knowledge and skills with Cognos products. Obtaining certification on individual Cognos products allows you the flexibility to certify in your area of expertise.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

WHAT'S

NEXT

For More Information ƒ www.cognos.com -> Services -> Training OR ƒ http://.cognos.com/en/training/

Additional Information •

At Cognos, we recognize that learning is not just something you do when you go on a course - it is an ongoing, continuous process.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

5

COGNOS

6

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

A

End-to-End Workshop

Cognos Confidential

Cognos 8 BI

COGNOS

A-2

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

END-TO-END

WORKSHOP

Workshop 1 ƒ End-to-End Workshop

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

A-3

COGNOS

8

BI

AD

HOC

QUERYING

End-to-End Workshop Management and business analysts at The Great Outdoors have a number of questions that they want to answer. For example, they want to see which sales reps have achieved sales of over $500,000 in 2004. You, Estelle Johnson, a business analyst can help provide answers to these business questions by creating several ad hoc reports. Once you have created these reports, customize and schedule them as necessary. To accomplish this:

A-4

•

Log on to Cognos with ID of johnsone and of Education1!.

•

Customize Cognos Connection by creating a new subfolder in My Folders called Ad Hoc Analysis. Set the home page to My Folders, and then specify grid line separators for the list view.

•

Create a grouped list report that includes Staff name, Revenue, Date hired, Position and Order year. Group the report on Order year and sort the report in ascending order by Revenue. Run the report with no data and with all data.

•

Filter the report to include only those sales reps that have achieved more than $500,000 in sales in 2004.

•

Add a third filter that includes a prompt, which allows the report consumer to see data regarding a selected employee. View information about Anders Nilsson, and then adjust the filter so that it again selects four specific employees with sales over $500,000 in 2004.

•

Format the report data as British pounds, and then change the title to "Sales Rep Revenue Report". Re-order the columns so that Revenue is to the left of Staff name, and then save the report to the Ad Hoc Analysis subfolder as Sales Rep Revenue Report.

•

Create a new report with Product line, Order method and Revenue columns, and then pivot the data on the Product line column.

•

Add a column chart to the report (include both the chart and the report data) and then save the report to the Ad Hoc Analysis subfolder as Product Line Crosstab Report.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

END-TO-END

•

Create a new report with Product line, Product type, and Quantity. Group the report by Product line and average the quantity values for each product type. Sort the data by Quantity in ascending order and then save the report to the Ad Hoc Analysis subfolder as Average Product Sales Report.

•

Generate HTML and PDF report outputs for the Average Product Sales Report in English, French, and German. View the German HTML output and the French PDF output in Report Viewer.

•

Create a job for the three reports called Ad Hoc Reports. Schedule the job to run every Tuesday at noon effective immediately with no end date specified.

WORKSHOP

For more detailed information outlined as tasks, see the Task Table on the next page. For the final results, see the Workshop Results section that follows the Task Table.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

A-5

COGNOS

8

BI

AD

HOC

QUERYING

End-to-End Workshop: Task Table Task

Where to Work

Hints

1. Customize Cognos Connection.

Cognos Connection

•

Click arrow beside Home.

•

Click Preferences.

•

Staff name, Revenue, Date hired, Position and Order year.

•

Group Order Year and Sort Revenue in ascending order.

•

Preview with no data

•

Run with all data.

•

Revenue is over $500,000.

•

Order year is 2004.

•

Select Anders Nilsson.

•

Adjust filter to select Bjorn Winkler, Donald Chow, and Maria Laponder.

•

United Kingdom, pound.

•

Thousands separator.

•

Title is Sales Rep Revenue Report.

•

Move Revenue to the left of Staff name.

•

My Folders > Ad Hoc Analysis > Sales Rep Revenue Report.

•

Product line, Order method, Revenue.

•

Pivot on Product line.

2. Create a grouped list report.

3. Filter data based on revenue and order year.

Query Studio Menu

Filter Page

4. Add a prompt to view sales of a selected sales rep.

Filter

5. Format data in the report.

Edit Data

6. Create a crosstab report.

A-6

Pivot (creates a crosstab) button

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

END-TO-END

Task

Where to Work

Hints

7. Add a chart to the crosstab report.

Chart button

•

Column chart, standard configuration.

•

Include both chart and report data.

•

My Folders > Ad Hoc Analysis > Product Line Crosstab Report.

•

Product line, Product type, Quantity.

•

Group by Product line.

•

Summary for cells is Average and summary for footers is Total.

•

Sort Quantity ascending.

•

My Folders > Ad Hoc Analysis > Average Product Sales Report.

•

Average Product Sales Report.

•

Click Refresh after the reports have started running to view the report outputs.

•

View the German HTML and the French PDF outputs.

8. Create a list report with calculated data.

9. Generate report outputs.

Edit Data menu Advanced link

Run with options button, Advanced options link

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

WORKSHOP

A-7

COGNOS

8

BI

AD

HOC

QUERYING

Task

Where to Work

Hints

10. Create and schedule a job.

New Job wizard

•

Call the job Ad Hoc Reports.

Schedule Management tool

•

Sales Rep Revenue Report, Product Line Crosstab Report, Average Product Sales Report.

•

Under the By Week tab, ensure that only Tuesday is selected.

•

Schedule has no specified end date.

If you need more information to complete a task, see the Step-by-Step Instructions that follow the Workshop Results section.

A-8

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

END-TO-END

WORKSHOP

End-to-End Workshop: Results After formatting, the Sales Rep Revenue Report appears as shown below:

After adding the chart, the Product Line Crosstab appears as shown below:

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

A-9

COGNOS

8

BI

AD

HOC

QUERYING

After calculating the data, the Average Product Sales Report appears as shown below:

A-10

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

END-TO-END

WORKSHOP

End-to-End Workshop: Step-by-Step Instructions Task 1. Customize Cognos Connection. 1. Start your browser, in the address box type http://<servername>/cognos8, and then press Enter. 2. On the Log on screen, in the ID box, type johnsone in the box, type Education1!, and then click OK.

Public Classroom Because of the use of the VMware image, students will not have to type in the address in Task 1, Step 1.

3. On the Welcome page, click Cognos Connection, and then on the Cognos Connection opens. 4. In the top right corner, click Preferences. The Set preferences page appears. 5. In the Separators in list view box, click Grid lines, and then click OK. The portal reflects your changes. 6. Click the My Folders tab, and then click New Folder. The New Folder wizard opens. 7. In the Name box, type Ad Hoc Analysis, and then click Finish. The subfolder is added to My Folders. 8. Click the arrow beside the Home icon (at the top right), and then click Set View as Home. Task 2. Create a grouped list report. 1. In the top right corner, click Query Studio. 2. Click the Go Sales and Retailers package. Query Studio opens. 3. In the Insert Data menu, expand Sales reps, and then double-click Staff name to add it to the report. 4. In the menu, expand Orders, and then double-click Revenue to add it to the report. 5. Under Sales reps, double-click Date hired and Position to add them to the report. 6. Under Orders, double-click Order year to add it to the report. 7. Click the Order year column header, and then on the toolbar, click Group. The data is grouped by Order year values. 8. Click the Revenue column header, and then on the toolbar, click Sort. The Sort page appears.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

A-11

COGNOS

8

BI

AD

HOC

QUERYING

9. Click Ascending (1 to 9), and then click OK. The data in the Revenue column is sorted in ascending order. 10. In the menu on the left side, click Run Report, and then click Preview with No Data. The report is populated with artificial data. 11. In the menu on the left side, click Run with All Data. The report is populated with all the required data. Task 3. Filter data based on revenue and order year. 1. Click the Revenue column header, and then on the toolbar, click Filter. The Filter page appears. 2. Under Show only the following, in the From box, type 500,000, and then click OK. The data is filtered so that only sales reps with at least $500,000 in sales in a given year are shown. 3. Click the Order year column header, and then on the toolbar, click Filter. The Filter screen appears. 4. Under Show only the following, in both the From and To boxes, type 2004, and then click OK. The Combine filters dialog box appears containing a filter for order year 2004. 5. Click OK. Both filters appear at the top of the page. Task 4. Add a prompt to view sales of a selected sales rep. 1. Click the Staff name column header, and then on the toolbar, click Filter. The Filter page appears. 2. In the Show only the following box, select the check box beside Anders Nilsson. 3. Select the Prompt every time the report runs check box, and then click OK. The Combine filters dialog box appears.

A-12

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

END-TO-END

WORKSHOP

4. Click OK. Data for Anders Nilsson appears in the report. The report runs and contains data pertaining to Anders Nilsson only. This sales rep generated $514,498.38 in revenue in 2004. You can test the prompt by re-running the report. 5. On the toolbar, click Run with All Data (and re-prompt). The Prompt page appears. 6. Scroll through the values in the Provide a value box. You can see that Anders Nilsson is pre-selected because of the filter that you created previously. If you click Finish at this point, data for Anders Nilsson will be shown. 7. Ctrl+click Anders Nilsson, Bjorn Winkler, Donald Chow, and Maria Laponder, to select these sales reps. 8. Click Finish. The report displays data for the four employees that they have generated more than $500,000,000 in revenue. Task 5. Format data in the report. 1. Click the Revenue column header, in the menu on the left side, click Edit Data, and then click Format Data. The Format page appears. 2. In the Category list, click Currency, and then in the Currency list, click £ (GBP) - United Kingdom, pound. 3. In the Thousands separator box, click Yes, and then click OK. 4. At the top of the report, click Title. The Edit Title page appears. 5. In the Title box, type Sales Rep Revenue Report, and then click OK. The new title is added to the report. 6. On the toolbar, click Save. The Save As page appears. 7. In the Name box, type Sales Rep Revenue Report, click Select another location, and then click the button beside Ad Hoc Analysis. 8. Click OK twice to save the report to the subfolder.

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

A-13

COGNOS

8

BI

AD

HOC

QUERYING

Task 6. Create a crosstab report. 1. On the toolbar, click New Report, in the menu on the left side, expand Products, and then double-click Product line to add it to the report. 2. Under Orders, double-click Order method and Revenue to add them to the report. 3. Click the Product line column header, and the on the toolbar, click Pivot (creates a crosstab). The data is pivoted to create a crosstab report. Task 7. Add a chart to the crosstab report. 1. On the toolbar, click Chart. The Chart page appears. 2. In the Chart type box, ensure that Column is selected, ensure that Standard is selected, and then click OK. The chart is added to the report. The report data is included below the chart. 3. On the toolbar, click Save. The Save As page appears. 4. In the Name box, type Product Line Crosstab Report, and then click OK. Task 8. Create a list report with calculated data. 1. On the toolbar, click New Report, in the menu on the left side, under Products, double-click Product line and Product type, and then under Orders, double-click Quantity. 2. Click the Product line column header, and then on the toolbar, click Group. The data is grouped by Product line. 3. Click the Quantity column header, in the menu on the left side, click Edit Data, and then click Summarize. The Summarize screen appears. 4. In the right corner, click Advanced, and then in the Summary for cells box, click Average. 5. Click OK. The data in the Quantity column for every product type is averaged, while the data for every product line remains totaled. 6. Click the Quantity column header, and then on the toolbar, click Sort. The Sort screen appears.

A-14

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

END-TO-END

WORKSHOP

7. In the Sort Order section, click Ascending (1 to 9), and then click OK. The data in the report is sorted by the average quantity of each product type sold. 8. On the toolbar, click Save. The Save As screen appears. 9. In the Name box, type Average Product Sales Report, and then click OK. Task 9. Generate report outputs. 1. In the top right corner, click Cognos Connection, click the My Folders tab, then click the Ad Hoc Analysis folder, and then beside the Average Product Sales Report, click Run with options - Average Product Sales Report. The Run with options page opens. 2. Click the Advanced options link, in the Delivery section, click Save the report, print it, or send an email, and then ensure that the Save checkbox and the Save the report button are selected. 3. In the Formats section, ensure that the HTML box is selected, and then select the PDF checkbox. 4. In the Languages section, click Select the languages. The Select the languages page opens. 5. In the Available languages box, Ctrl + click French and German, and then click Add. 6. Click OK, click Run, and then click OK. The report runs and report outputs are generated in HTML and PDF formats. 7. After a few moments, on the Cognos Connection toolbar, click Refresh, and then beside Average Product Sales Report, click View the output versions for this report. 8. Click the downward pointing arrow beside Languages, and then click (All languages). The View report output versions page opens. Six report outputs were generated in three languages and two formats. 9. Click the HTML icon to the left of German. The German HTML report output opens in Cognos Viewer. 10. Click the Return button on your browser, and then click the PDF icon to the left of French. The French PDF report output opens in Cognos Viewer. 11. Click the Return button at the top of the report, and then click Close. © 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

A-15

COGNOS

8

BI

AD

HOC

QUERYING

Task 10. Create and schedule a job. 1. On the Cognos Connection toolbar, click New Job. The New Job wizard opens. 2. In the Name box, type Ad Hoc Reports, and then click Next. 3. Below Steps, click Add. The Select entries page opens. 4. Click the boxes beside Average Product Sales Report, Product Line Crosstab Report, and Sales Rep Revenue Report, click Add, and then click OK. 5. Click Next, click Save and Schedule, and then click Finish. 6. Ensure that the By Week tab is selected, and then Under every 1 week(s) on, ensure that only the Tuesday box is selected and that all the other boxes are deselected. 7. In the Start section, ensure that the date is current, and then in the time box, type 12:00 PM. 8. In the End section, ensure that No end date is selected, and then click OK. The scheduled job is added to the Ad Hoc Analysis subfolder. 9. Close Internet Explorer.

A-16

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

INDEX

Index A activity monitoring, 1-28 analysis in Analysis Studio, 1-24 Analysis Studio, 1-7

C calculate data, 4-23, 4-24, 4-27, 4-36 charts, 3-9, 4-19, 4-20, 4-22, 4-36, A-14 Cognos 8 Business Intelligence, 1-4 Cognos Connection accessing external files, 2-38 accessing shared pages, 2-54, 2-55 folders, 2-39 introduction, 2-4 preferences. See Cognos Connection:specifying properties roles and responsibilities, 2-5 searching entries, 2-42 shortcuts, 2-40 specifying properties, 2-31, 2-32, 2-33 Cognos Planning, 1-5 conditional styles, 5-4, 5-5 Corporate Performance Management, 1-4–1-5, 1-7 creating a report view, 2-30 crosstab, A-14 pivoting, 4-15, 4-16 cube, 1-24 custom groups, 4-26

D dashboard, 1-25 dashboarding, 1-5 data expand and collapse, 4-14 reordering columns, 4-13 sorting in reports, 4-7, 4-27 summarizing, 4-25, 5-29, 5-30 data foundation, 1-29 Data Manager, 1-29 dimensional, 1-15, 4-30 drilling up and drilling down, 4-31, 4-33 go to related data, 4-32 dimensionally modeled relational, 1-11, 1-12, 3-5

E Enterprise Planning, 1-14 event detection, 1-27 Event Studio, 1-7

M MDX, 1-13 member, 1-12 Metric Studio, 1-7 modeling, 1-9

O OLAP, 1-9, 1-11, 3-5 open data access, 1-9

P packages, 3-5 portal, 1-30 portlets, 1-30, 2-48, 2-49, 2-50, 2-58, 2-59 PowerCube, 1-10 PowerPlay, 1-10

Q Query Studio, 1-7 introduction, 3-4

R relational, 1-15, 3-5 Report Studio, 1-7 reports, 3-6 adding data, 3-8 creating, 4-4 defining, 2-6 distributing, 2-29 formatting, 5-34, 5-35, A-13 job. See reports:scheduling opening, 3-7 printing, 4-40 ranking, 4-43 running, 2-7, 2-15 saving, 3-10 scheduling, 2-8, 2-9, 2-10, 2-11, 2-19, 2-23 viewing, 2-12, 2-13, 2-14, 4-39, A-15 ROLAP, 1-11

S scorecarding, 1-5 single architecture, 1-6 single platform, 1-4, 1-5

T F

filtering, 5-9, 5-10, 5-11, 5-12, 5-14, 5-15, 5-24, A-12 with prompts, 5-13, 5-20

L

templates, 1-26, 5-39, 5-40 transformation, 1-29

W watch list, 1-28

list reports, 4-5, 4-8 grouped, 4-6, 4-8, A-11

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language..

1

COGNOS

2

8

BI

AD

HOC

QUERYING

© 2005, Cognos Incorporated Cognos Confidential. For internal use only. This guide contains proprietary information which is protected by copyright. No part of this document may be photocopied, reproduced, or translated into another language.

kjashks dvf.ksu y.sv

W W W. C O G N O S . C O M