Understanding Queries in Report Studio

Understanding Queries in Report Studio
As you create a new report in Report Studio, a query is being built in the background.
Many times, you will never need to look at the query that has been generated. However,
when a more complex query is needed or multiple queries are needed to develop a single
report, editing of the query specification may need to take place.
To view a query, you use the Query Explorer. To access Query Explorer, either hover
over or click on the cube icon on the explorer bar. This should expand the Query
Explorer to allow the user to see each of the queries and their related tabular objects.
There are 2 basic parts to each query – the cube object and the tabular object. The cube
object will control how the data is grouped and organized. The tabular object pulls in the
data in its most basic form requested.
Cube Object
Tabular Object
-1-
Tabular Object
The tabular object will return data in a basic list format. In the
Tabular Object, you can drag multiple types of objects in from the
toolbox. Dragging in a Data item is the same as dragging in a
calculation while you are in the report layout. Dragging in a Filter
is the same as creating a Tabular Filter from the report layout.
There is an option to have Cognos automatically

group and summarize the data. You will find this as a
property of the tabular object. By setting this
property to YES, Cognos will automatically
summarize the data and consolidate rows. By setting
it to NO, Cognos will return all rows with no
consolidation or summarization.
You can see the results of turning this property on and off in the screen shots below.
(Note: The Student Count calculation is a count distinct of Banner ID. That explains
why you see redundant rows of the College_Desc and Campus_Desc all showing the
same number of students when the property is set to NO. Cognos will show each row of
data, regardless of whether or not you have the data fields in there that make those rows
distinct. It is pulling in a row of data for each student.)
Summarized Un-summarized
(Auto Group & Summarize = YES) (Auto Group & Summarize = NO)
Note: The two tables above were produced by clicking on the View Tabular Data icon on the
toolbar. This will run just the tabular object within whatever query is currently active.
-2-
Cube Object
The cube object is where you will organize the data into dimensions, levels and facts. A
dimension is a hierarchy of data. An example of a standard dimension is a Time
dimension – year, quarter, month, and day. Each of these items (year, month, etc.) is a
level within the time dimension. A fact item is any item that either you don’t want to
group into a dimension or an item that you want to summarize (dollars, counts, etc.)
Each level represents something that is grouped in a report. The order of the levels will
specify the grouping sequence in the report. The levels will also determine where
aggregations (totals) can be done in a report object.
In this example, the data will be

grouped first by CAMPUS_DESC
and then by COLLEGE_DESC.
This is the only dimension
(hierarchy) in this query. Each
dimension is designated by .
CAMPUS_DESC and
COLLEGE_DESC are the levels
within this dimension. Each level
is designated by . For each
subsequent level within a
dimension, another blue square
will appear on the icon. ( is for
the second level.) Student Count is
our fact item – the item we want to
summarize.
-3-
One Dimension vs. Multiple Dimensions
The report object determines the number of dimensions required or allowed. If you insert
a list, you can only have a one-dimensional query associated with the list. Crosstabs and
charts are both multi-dimensional report objects.
Dimension 1
Dimension 2
If you attempt to add a list and a crosstab to the same report and base it off of the same
query, you should get any error about the dimensionality of your query. Crosstabs and
Charts may have any number of dimensions in their underlying queries. You can have
four dimensions in a query and base your chart on two of them and base your crosstab on
the other two dimensions.
-4-

Understanding Queries in Report Studio

Uploaded by

Copyright:

Available Formats

Understanding Queries in Report Studio

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Understanding Queries in Report Studio

Uploaded by

Copyright:

Available Formats

Understanding Queries in Report Studio

There is an option to have Cognos automatically

In this example, the data will be

You might also like