Cognos Report Development

1
Cognos Report Development
Enterprise Reporting Services

• Best Practices

2
Report Development Steps

• Step 1 Become Familiar with the ODS data for
  the report
• Use the ODS Metadata Business Concept Diagrams to
  become familiar with the joins (relationships),
  keys, hub views, and the various reporting views
  available for your report .
• http//www.temple.edu/ers/metadata
• Step 2 Locate the data items needed in a Cognos
  package
• Use the ERS developed MS Excel spreadsheet to
  locate the required packages, subjects, and query
  items/fields.
• List of ODS packages, query subjects, and query
  items/fields
• Step 3 Login to Cognos Connection and begin
  writing the report. (Make sure your IE settings
  are correct.)

3

• Step 1 Become Familiar with the ODS data for
  the report
• Use the ODS Metadata Business Concept Diagrams to
  become familiar with the joins (relationships),
  keys, hub views, and the various reporting views
  available for your report .
• http//www.temple.edu/ers/metadata
• See the document How to Find a Banner Field in
  the ODS on the ERS/Report Studio Web page.

4
(No Transcript)
5
(No Transcript)
6
Step 2 Locate the data items needed in a Cognos
package. Use the ERS developed MS Excel
spreadsheet to locate the required packages,
subjects, and query items/fields. List of ODS
packages, query subjects, and query items/fields
7
Step 3 Login to Cognos Connection and begin
writing the report
8
Report Development Tips and Tricks

• Grab as many query items as you can from the
  hub view.
• Use as many recommended search columns as
  possible for report columns, filters, and sorts.
  
• Use as few queries and query subjects as possible
  for better performance.
• Use hard coded prompts when possible versus
  query sources, including List of Values (LOV).
• (Fields are not included in the data request if
  they are not used in the report display, sort, or
  filter. That is, a field included in a report
  query that is not used in the display, sort, or
  filter will not be included in the SQL that is
  sent to Oracle.)

9

• Grab as many query items as you can from the
  hub query subject/view.

10

• Use as many recommended search columns as
  possible for report columns, filters, and sorts.

11

• Use as many recommended search columns as
  possible for report columns, filters, and
  sorts.
• Example
• Course Catalog.Course Catalog.COURSE_IDENTIFI
  CATION
• Course Catalog.Course Catalog.SUBJECT
• Course Catalog.Course Catalog.COURSE_NUMBER
• Course Catalog.Course Catalog.ACADEMIC_YEAR
• Course Catalog.Course Catalog.ACADEMIC_PERIOD
  
• Course Catalog.Course Attribute.COURSE_ATTRIB
  UTE
• Course Catalog.Course Attribute.COURSE_ATTRIB
  UTE_DESC
• Course Catalog.Course Coreq.COURSE_TYPE
• Course Catalog.Course Coreq.COURSE_TYPE_DESC
  
• Takes 8 seconds to run without the following
  filter
• Course Catalog.Course Catalog.ACADEMIC_PERIOD
  is not null
• Include the filter and it takes 1 second.
  Academic Period is a recommended search column
  for the Course Catalog query subject.

12

• Use as few queries and query subjects as
  possible for better performance.

13

• Use hard coded prompts when possible versus
  query sources, including List of Values (LOV).

LOV is useful when there are a lot of possible
values. However, the user can then select a
valid value that results in no data being
returned. For example, Academic Period LOV
includes values like 202036.
14
Report Development Tips and Tricks

• When building report filters, use fully qualified
  query items.
• Example
• Admission Application.Admissions
  Application.COMPLETE_IND ?complete?
• Instead of
• COMPLETE_IND ?complete?
• Use as many filters as possible to limit the
  amount of data requested from the ODS.

15
Report Development Tips and Tricks

• Select as many query items as you can from the
  same query subject. Minimize the number of query
  subjects the report uses.
• Build the report one query subject at a time to
  monitor when performance issues occur. Use the
  tabular view to test your query performance.

16
Report Development Tips and Tricks

• Use the view tabular data feature in Report
  Studio to help troubleshoot and see what data is
  being retrieved by your query(s).
• Use the row number insertable object in Report
  Studio to provide row counts for troubleshooting
  purposes.
• Use comments (in an HTML object).

17

• Use the view tabular data feature in Report
  Studio to help troubleshoot and see what data is
  being retrieved by your query(s).

18

• Use the view tabular data feature in Report
  Studio to help troubleshoot and see what data is
  being retrieved by your query(s).

19

• Use the row number insertable object in
  Report Studio to provide row counts for
  troubleshooting purposes.

20

• Use the row number insertable object in
  Report Studio to provide row counts for
  troubleshooting purposes.

21

• Use an HTML object to record notes and comments
  about the report. Make sure that the text is
  commented.

22

• Aggregation (Auto Summarize and Group)

23
When using the GUI to get the possible values
for a field, Cognos issues a select distinct
which scans the entire table and this scan may
take a very long time to get all of the unique
values.
24
(No Transcript)