Access Keys:
Skip to content (Access Key - 0)

Cognos Query Studio - Organizing a Report

On this page:

Return to Cognos Query Studio topics menu.

Sorting & Grouping

This illustration shows the difference between sorting and grouping.

Sort organizes records in order. Group does the same, only it eliminates repetitions of field values.

Sort a Report

Typically, you will sort on the leftmost column on your report, though you can choose any column. Cognos will sort the selected column and all further columns to the right.

  1. Select the column to sort by.
  2. From the Edit Data menu, click Sort. The Sort pane displays at the bottom of the window.
  3. Choose the desired Sort Order and click OK.

Using Indicators

As you develop a report, it will be useful to see filter, sort, and data suppression indicators – these appear in the report header and serve as visual tip-offs for your design choices. This is what the sort indicator looks like - it hovers just about the report headings:

Once you are done with the report, you may remove them or retain them, as you see fit.

  1. Click the report Title displayed in the report header. The Edit title area pane opens at the bottom of the window.
  2. Note the Show indicators. When turned on, Cognos places informational note(s) in the header of the report to indicate the presence of filters, sorts, and suppressions in place.
  3. Click OK.

Remove a Sort

  1. Click on the column selected for sorting.
  2. From the Edit Data menu, select Sort. The Sort pane displays.
  3. Select the Don’t Sort option and click OK.

Group a Report

Grouping not only sorts the report by the selected column, but it also eliminates multiple instances of a sort value.

Typically, you will group your report on its leftmost column. If you choose to group on a field that is not the leftmost column, Cognos will move the column to the leftmost position and then group the report by it. For example, if your report has columns for FieldA, FieldB, and FieldC, and you choose to group on FieldB, Cognos will move FieldB to the leftmost position.

You can group on many columns in your report. The grouping will occur in order of the sequence of the columns on the report. Cognos automatically relocated grouped columns to the left side of the report.

By default, grouping create subtotals and a grand total for all numeric fields. If you have several groups, subtotals will be generated for each grouping and subgrouping. In Query Studio, if you are set to Preview with limited Data, the subtotals do not display – they will show once you change to Run with All Data

  1. Select the column to group.
  2. From the Change Layout menu, click Group.
    Grouping sorts in ascending order. If you want the grouping in descending order, click in the grouped column, choose Sort from the Edit Data menu, and then select the Descending option.
    Note that when you download a report into Excel, the cells containing the counts will contain values, not summing formulas.

Remove a Grouping

  1. Select the column that you wish to ungroup.
  2. From the Change Layout menu, click Ungroup.
    Note that when you ungroup, Cognos will remove all subtotals associated with the group.

Totals, Subtotals, and Counts

Totals and subtotals add up numeric values to arrive at sums. Counts enumerate the number of values. For example:

Create a Total

  1. Select the numeric column.
  2. From the Edit Data menu, select Summarize to display the Summarize pane.
  3. Select Totals and click OK. The grand total displays at the bottom of the last page of the report.

Subtotals and Totals for Numeric Fields

By default, grouping creates subtotals and a grand total for all numeric fields. If you have several groups, Cognos generates subtotals for each grouping and subgrouping.

In Query Studio, if you are set to Preview with Limited Data, the subtotals do not display – they display once you change to Run with All Data.

You cannot delete a subtotals or totals.

Note that when you download a report into Excel, the cells containing the counts will contain values, not summing formulas.

Create Count Totals and Subtotals

  1. Select the alphanumeric column to be counted.
  2. From the Edit Data menu, click Summarize to display the Summarize pane at the bottom of the window.
  3. From the Summary for footers drop down, select Count.
  4. Click OK.
    Cognos will calculate a count for the report and subtotal counts for each grouping.
    In Query Studio, if you are set to Preview with limited Data, the subtotals do not display – they will show once you change to Run with All Data

Page Breaks & Sections

Create a Page Break on a Group

You can only page break on a grouped column. When you page-break, the change in group value prompts a new page. For example:

  1. Group the column on which you wish to page-break.
  2. From the Change Layout menu, select Set Page Breaks.

Create Sections for a Group

You can turn group values into section headers in your report. For example:


Here's how:

  1. Click on the column heading of the grouped field
  2. From the Change Layout menu, select Create Sections.
    The field is no longer a column but an entry in the Header.
    To page break on a section, click inside a section header, then from the Change Layout menu, select Set Page Breaks.

Unsection a Field

  1. Click the Section within the report header.
  2. From the Change Layout menu, click Ungroup. This removes the sections and undoes the grouping.

IS&T Contributions

Documentation and information provided by IS&T staff members


Last Modified:

September 02, 2015

Get Help

Request help
from the Help Desk
Report a security incident
to the Security Team
Labels:
c-cognos c-cognos Delete
c-cognos-qs c-cognos-qs Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
Feedback
This product/service is:
Easy to use
Average
Difficult to use

This article is:
Helpful
Inaccurate
Obsolete
Adaptavist Theme Builder (4.2.3) Powered by Atlassian Confluence 3.5.13, the Enterprise Wiki