Database Reference
In-Depth Information
every single record as it is being pulled from the database. Every database record will be
evaluated.
10.
Va l i d a t e t h e e x p r e s s i o n a n d c l i c k
OK
.
11.
Rename the
DataItem1
data item to
Count
.
12.
From the
To o l b o x
tab of the
Insertable Objects
pane, drag another
Data Item
into the
Data Items pane below the
Count
data item.
13.
Create the following expression in the dialog box:
'0-10'
This expression will serve as a label for the unioned set that we will use as a column in a
crosstab or a chart.
14.
Va l i d a t e t h e e x p r e s s i o n a n d c l i c k
OK
.
15.
Rename the
DataItem1
data item to
Range
.
16.
From the
To o l b o x
tab of the
Insertable Objects
pane, drag
Filter
object into the
Detail
Filters
pane.
The Detail Filter Expression dialog box is displayed.
17.
Create the following expression:
[Count]>0
We would like to optimize this query to deal with only the records that match our count
criteria.
18.
Va l i d a t e t h e e x p r e s s i o n s a n d c l i c k
OK
.
Our first query has been created.
19.
From the
Run
menu, select
View Tabular Data
for results similar to those shown in
Figure 5.13.
20.
Close the
IBM Cognos Viewer
window to return to IBM Cognos Report Studio.
NOTE
We are using a building-block approach to solve a common report query prob-
lem. This approach is easy to follow and understand. However, in case your record
count is high, the database query that this report will generate may not be the most opti-
mized. By bringing in the date fields into the query, we are making the queries too
detailed, and some of the aggregation is happening after the records have been pulled
from the database.
The more efficient query would be to perform all the days between calculations and the
if
then else
logic of the record counter in one query calculation. The aggregation of the
query would have to be turned on and the
Count
calculation would have to use the
Total
aggregate function. This will push all the query execution to the database level and the
query would return only one record, which would show the exact number of records that fit
the desired range of days it took to close the order.
