Transcript Slide 1
Cognos ReportNet Training
October 11, 2006
Introduction
Presenter:
E-mail:
Ray Nowell [email protected]
Phone:
(205) 298-8796
Objectives
High level technical review of Cognos ReportNet Basic Security (Setup Users and Groups) Present the Cognos ReportNet Components Learn to use Report Studio to create reports Create Sample Reports
Cognos ReportNet Overview
Made up of Four Components:
Framework Manager Used to create and publish metadata on which the reports are based Cognos Connection The Web portal that provides the access point to all data available in ReportNet
**Report Studio**
Used to create and manage reports (accessible from
Cognos Connection
) Query Studio Used to create ad hoc reports (accessible from
Cognos Connection
)
Cognos ReportNet
Framework Manager
Framework Manager
Windows Application that is run from the Cognos Machine Consists of three main components: Data Source Points to the data store (e.g. Access Database) Data Model Multi-tiered structure containing fields used to display data on the report. Fields from the Data Model are derived from the Data Source Package Makes the Data Model accessible to ReportNet (“Publish the Package”)
NOTE: For contractual reasons, changes in the Framework Manager should be handled by Hunter Systems employees only
Framework Manager
Data Model contains three nodes Presentation Entities Repository Repository contains
fields
from the Data Source Entities contain
fields and relationships
from the Repository Presentation contains the
fields
to be displayed from the Entities
Framework Manager
Field Data Types Unique Identifier Identifies the Key to Query Subject Attribute A value in the database that is
not
used for calculations Fact A value that is used for calculation. Facts answer the questions “
how many?
” or “
how much?
”
Framework Manager
Show Relationships
Framework Manger
Questions?
Cognos ReportNet
Cognos Connection
Cognos Connection
In the Cognos Connection Web portal, you can: Create and run reports Perform administrative tasks: Organize data by creating entries and storing the entries in folders Scheduling reports
Cognos Connection –
Layout
Person Currently Logged In Links to Query Studio and Report Studio General Cognos Links Tools Link Tool Bar Current Folder Location Current Folder Contents Actions that can be performed on each entry in the folder
Cognos Connection –
Layout
Preferences Menu
Sets the page look and default preferences for reports (HTML, PDF, etc.)
Tools Menu
Opens a new menu of options:
Directory
Can alter group permissions and Data Connections
Capabilities
Controls access to functionality (Query Studio, Report Studio, etc.) Schedule Management
View and manage the schedule of reports and jobs Server Administration
Sets properties of the Cognos Server (Logging Level, Running Services, etc.) Deployment
Allows a framework Package to be imported or exported.
Cognos Connection –
Layout
Tool Bar – Standard Icons
Refresh Refreshes the contents of the folder List View / Details View Shows the Contents in either “List View” or “Details View” New Folder/New Page Creates a New Folder or New Page Select All / Deselect All Selects/Deselects all the checkboxes to left of each entry within the folder
Cognos Connection –
Layout
Tool Bar – Standard Icons
Cut, Copy, Paste, Delete
Standard windows operations for the folder entries
Order
Reorders Entries in the Folder
Search
Searches for entries within the folder
Cognos Connection –
Layout
Tool Bar – Special Icons
Set Properties
Only appears when the Public Folder Root is selected Required to Set Properties of the Public Folder
Cognos Connection –
Layout
Tool Bar – Special Icons
Set Properties
Only appears when the Public Folder Root is selected Required to Set Properties of the Public Folder
New Job
Appears when not in the root folder
Allows the user to set up a macro of report events to run New URL
Appears when not in the root folder Creates a link to any Web Page
Cognos Connection –
Layout Folder Entry Actions Set properties Sets the “Run Options” and “Permissions” of the report Run with options Allows user to select how they wish to run and receive the report View output versions Allows the user to select which version of a Report View they want to see Open with Report Studio Opens the report for modification in Report Studio Create a report view Creates a report view Schedule Schedules the report or view to run at a certain time More… Displays an option screen that gives more options to provide on the report
Cognos Connection
Organize Content with Folders You can arrange reports and other content in Cognos Connection by creating folders Ensure that your folders are logically labeled and organized to make locating reports quick and easy Folders come in two types: Public can be viewed by many users My Folders personal folders that you can organize according to your preferences
Cognos Connection
How to create a folder in “My Folders” Click the “New Folder” Icon Enter the name of the Folder Click “Finish”
Cognos Connection Demo Cognos Connection
Cognos Connection Questions?
Cognos ReportNet
Query Studio
Query Studio
Query Studio used to create ad hoc reports To run the Query Studio, click on the Query Studio Icon in the top right corner of the screen: Then select the package for which to load Query Studio (should be only one)
Query Studio
Drag and drop the fields from your presentation layer to the report.
Save the report when satisfied.
Query Studio
Questions?
Cognos ReportNet
Report Studio
Report Studio
Objectives:
Examine Report Studio and its interface Explore the different report types Create reports
Report Studio
Report Studio used to create formatted reports To run the Report Studio, click on the Report Studio Icon in the top right corner of the screen: Then select the package for which to load Reports Studio (should be only one)
Report Studio
The Studio IDE is divided into five sections: Toolbar Provides quick access to commonly used report functions Insertable Objects Pane Contains the fields and objects to be inserted in the Work Area Properties Pane Allows user to adjust various attributes of an object Explorer Bar Determines what to display in the Work Area Work Area Place to format the report or report query
Report Studio
Full Layout: Toolbar Insertable Objects Pane Properties Pane Explorer Bar Work Area
Report Studio –
Toolbar highlights Toolbar Highlights New, Open, Save, Cut, Copy, Paste and Delete These buttons all work the same as they do in any other windows app
BE SURE TO SAVE OFTEN!
Undo/Redo Very useful options as they not only pertain to changes to the report, but to the report query as well (will demonstrate) Validate Report Ensures that every
field
,
formula
and
filter
is valid before a report is run. Show Specification (XML) Displays the meta-data of the report in XML Run Report Immediately runs the report The drop down will allow user to specify Output Format (HTML, PDF, Excel, XML) Paper Size Paper Orientation Language Rows per page Display prompt menu
Report Studio –
Toolbar highlights Toolbar Highlights View Tabular Data Pulls up a quick report of the report Query
Page Design/Page Structure
Puts the report in “Page Design” or “Page Structure” view Boundary Lines Shows the boundary lines of the report Lock Locks/Unlocks the report
Filters…
Sets the filters for the reports
Sort Aggregate
Sets a calculation for the report
Group/Ungroup
Groups selected fields together. If more than one field is selected then puts subsequent fields in subgroups Pivot List to Cross Tab Turns a list report into a cross-tab report
Report Studio –
Toolbar highlights Toolbar Highlights Create Section Sections the report based on fields selected Swap Rows and Columns Swaps Rows and Columns (only works with Charts and CrossTab). No Row labels exist with List Reports Display Header Footer Allows report designer to display the Header/Footer on the report List Headers and Footers… Allows user to display the following: List Header Overall Header Overall Footer List Footer Create Header/Footer Creates Header or Footer Row
Insert Table
Useful for aligning multiple objects on a prompt page Merge Cells/Split Cells Merge or Splits Cells in a Table.
Report Studio –
Toolbar highlights Toolbar Highlights Font and Color Adjust the Font and Color of Cells
Container Alignment
Aligns an object within a container object (e.g. Aligns amounts to the right of a cell)
Build Prompt Page
Automatically builds a prompt page for the fields selected Data Format Allows report designer to override default OS Options for given data types
Drill Throughs
Displays the Drill-Through pop-up menu Help Displays the help menu
Report Studio –
Toolbar highlights
Toolbar Questions?
Report Studio –
Insertable Objects The Insertable Objects Pane contains three Tabs: Model
Contains the fields from the defined presentation layer
Query Items
Contains the fields from all the report queries
Toolbox
Lists all objects available for the selected item in the work area
Report Studio –
Insertable Objects The Insertable Objects Pane contains three Tabs: Model
Contains the fields from the defined presentation layer
Query Items
Contains the fields from all the report queries
Toolbox
Lists all objects available for the selected item in the work area
Report Studio –
Insertable Objects Toolbox Drag and Drop items from the Toolbox onto the work area The objects in the Toolbox will change depending which item you select in the
Explorer Bar
Commonly used objects for List Reports are: Text Item A text item in a report. The content can be static text, or it can come from a query item or report expression.
Calculation Displays a result from either a mathematical or logical expression. Block A container in which you can insert other objects.
Mainly used in the report headers Table Helps to format the data (useful when organizing prompt information) Image Puts an image on the report.
Report Studio –
Insertable Objects Toolbox There are also “prompt” objects Useful on prompt pages to create parameters and filter data Commonly used “prompts” are: Value Prompt A prompt control that allows you to select one or more values from a list (the control is either a list box or combo box) Select & Search Prompt An advanced prompt control that allows you to search for values
Report Studio –
Insertable Objects
Insertable Objects Questions?
Report Studio –
Properties The Properties Pane allows the user to manipulate various attributes of any object in the Work Area The properties can be related to content or the look of the control It has three areas: Title Bar Useful because the up arrow allows user to highlight the parent control Properties Section These are the individual properties that can be changed. They are categorized Description Block Gives a brief description of what the property does
Report Studio –
Properties
Properties Questions?
Report Studio –
Explorer Bar The Explorer Bar has three sections: Page Explorer Allows user to navigate, add or remove report pages or prompt pages Query Explorer Creates or modify queries and performs complex tasks such as defining union joins and writing SQL statements.
Condition Explorer Creates variables
Report Studio –
Work Area The Work Area allows user to edit the following: Report Pages Prompt Pages Queries
Report Studio –
Work Area The Report Page and Prompt Page are divided into three areas: Header Report title, parameters, etc. go here Body For Prompt Pages: All Prompts will be placed here For Reports Pages: All reportable objects can be placed in this area Footer Contains any information user would like displayed at the bottom of the report
Report Studio –
Work Area The display for the “Queries” Section of the Work area depends on the type of Query you select There are Four Types of Queries: Tabular SQL A SQL statement that is declared as a derived table whose items may be reused within the tabular structure. Tabular Set A relational set expression that combines two or more tabular structures. Tabular Reference The tabular data of another query that will be reused as this query's tabular data.
**Tabular Model**
A simple list query run against the Framework Manager model. Use it to identify the data items (columns) to include in the query.
Report Studio –
Work Area Query Layout: Dimensions Provides a hierarchy for reporting your information Facts The fields and calculations to be printed on your report Filters Any filters to restrict the data Tabular Data Specifies the type of Query it is
Report Studio –
Misc. Info
Demo Filter Creation and Usage
Show Cognos SQL and Native SQL
Report Studio –
Work Area
Explorer Bar or Work Area Questions?
Cognos ReportNet
Report Demos
Report Demos
In the following demonstrations, we will cover: The reporting process An Example of each type of report (four types): List Shows detailed information from your database CrossTab Shows information in a more compact form than in a grouped list.
Chart Presents a graphical representation of data Repeater Presents tables in which you can insert items that are repeated when you run the report (useful for mailing labels)
Report Demos
We will also cover: Filters Conditions: Conditional Layouts Conditional Formatting Calculations Prompt Pages
Report Studio
Reporting Process
Determine Data to be Displayed Choose Appropriate Report Type Build Report Apply Filters and/or Create Prompts Format Report
Report Demos
First Report Demo
Simple List Report
Report Demos
Report Objective: List Associates who have Transactions over $100,000 for a fiscal year Step 1 (Determine the data to be displayed) Broker ID Associate Name Fiscal Year Amount Step 2 (Choose Appropriate Report Type) List Report Step 3 (Build Report) Go to Cognos Report Studio and create Report Step 4 (Apply filters or create prompts) Apply the $100,000 dollar filter Add a Period Year Selection Prompt Step 5 (Format the Report) Add Title to Report Fix column titles Set format of Amount column to currency Sort by Amount descending
Report Demos
Second Report Demo
CrossTab Report
Report Demos
Report Objective: Compare transactions by Sales/Returns and Class Group Step 1 (Determine the data to be displayed) All will come from the Transaction View: Match1 Match6 Amount Step 2 (Choose Appropriate Report Type) CrossTab Report Step 3 (Build Report) Go to Cognos Report Studio and create Report Step 4 (Apply filters or create prompts) Add a Period Year Selection Prompt Add Field “PremMonth” to create a Master/Detail Report Step 5 (Format the Report) Set format of Amount column to currency Set Variables for Negative Amounts Show Negative Amounts in Red
Report Demos
Third Report Demo
Chart Report
Report Demos
Report Objective: View Sales vs. Returns by Store for a Fiscal Year Step 1 (Determine the data to be displayed) All will come from the Transaction View: CustomerID Match6 Amount Step 2 (Choose Appropriate Report Type) Chart Report (Stacked Column with 3-D effect) Step 3 (Build Report) Go to Cognos Report Studio and create Report Step 4 (Apply filters or create prompts) Add a Fiscal Year Selection Prompt Add a “Select and Search Prompt” for Stores Step 5 (Format the Report) Add Title to Report
Report Demos
Fourth Report Demo
Repeater Report
Report Demos
Report Objective: Create mailing labels for Associates.
Step 1 (Determine the data to be displayed) Associate Name Associate Address Fields Step 2 (Choose Appropriate Report Type) Repeater Report Step 3 (Build Report) Go to Cognos Report Studio and create Report Step 4 (Apply filters or create prompts) N/A Step 5 (Format the Report) Add Title to Report
Cognos ReportNet Report Demo Questions?
Cognos ReportNet
Practical Exercises
Practical Exercises
Objective Give User Hand-on Experience in Creating Reports PE 1 Create “Store Info” Report PE 2 Create “Store Plan Info” PE 3 Enhance PE 1 by creating a drill down to PE 2
Practical Exercises
Practical Exercise 1
Practical Exercises
Report Objective: Using the “Customer Information” and “RBA Store Plan” Presentation layer, create a report that will display Stores and the plan associated with them Step 1 (Determine the data to be displayed) Customer ID CustomerRef1Name to CustomerRef5Name PlanID Amount Step 2 (Choose Appropriate Report Type) List Report Step 3 (Build Report) Go to Cognos Report Studio and create Report Step 4 (Apply filters or create prompts) None required, but an optional “Search and Select Prompt” could be useful Step 5 (Format the Report) Add Title to Report Fix column titles
Practical Exercises
Practical Exercise 2
Practical Exercises
Report Objective: Using the “RBA Store Plan” Presentation layer, create a report that will display Store Plans and the fields associated with their Tiers (TrueUp, Quota Adjustment, and Kicker Tier) Step 1 (Determine the data to be displayed) Grouped Fields from RBAStorePlan PlanId, Name, TrueUpType, StoreMinNsph, AssocMinNsphAttain AssocMinNsph, MinCommStatusDays, FullTimeThreshold KickerEnabled, Chargeback, NsphAttainType True Up Tier RecType, NetSalesQuotaAttainment, TrueUpPct Quota Adj Tier ScheduledHours, QuotaAdjPct, HireDate2 Kicker Tier RecType1, NetSales, KickerPct Step 2 (Choose Appropriate Report Type) List Report Step 3 (Build Report) Step 4 (Apply filters or create prompts) Step 5 (Format the Report)
Practical Exercises
Practical Exercise 3
Practical Exercises
Report Objective: Enhance PE reports 1 and 2 by creating a drill down on Plan ID in PE Report 1 Step 1 (Determine the data to be displayed) Reports already created so we’re just creating a drill down on Plan ID in PE Report 1 Step 2 (Choose Appropriate Report Type) List Report Step 3 (Build Report) Open PE Report 2 and create an optional filter on PlanID Open PE Report 1, select “PlanID” field and click on the Drill-through Icon Step 4 (Apply filters or create prompts) Step 5 (Format the Report)