Database Reference
In-Depth Information
We have to change the syntax of the filter expression depending on what the user selects in
the prompt at runtime.
The reason we cannot just pass the filter expression as a parameter value is that it will be
treated by the report engine as a text value and not as “code” that has to be used as a filter expression.
For this reason, the expression is captured in a prompt macro function. However, writing the
macro function or finding out what the correct syntax is may be difficult for novice report developers
who may not have access to the IBM Cognos Framework Manager developers who can help them.
The solution we are proposing in this example will completely avoid the use of macros, and
it will be easy for business users to understand and replicate.
Our sample report will be a crosstab report that will be filtered in one of the three ways that
users select at runtime. The options will be by date range, by order method, or by sales rep.
We will start with a simple crosstab template.
Step 1: Start the Report
1.
Launch
Report Studio
and select the
GO Data Warehouse (query)
package.
2.
Click on
Create new
option.
COGNOS 8 NOTE
The option is
Create a new report or template
.
3.
Select the
Crosstab
report template and click
OK
.
We will be using the
Sales (query)
namespace inside the
Sales and Marketing (query)
folder (same as for the previous example).
Step 2: Set Up the Crosstab and the Query
When it comes to creating reports, there are two approaches. As in this example, you can build
the query and the report layout at the same time. Our first example provides an approach where
you can build the query first and then create the report layout.
1.
From the
Source
tab of the
Insertable Objects
pane, drag the following query items
into the
Crosstab
:
•
Product Line
from the
Products
query subject to the
Rows
drop zone
•
Quantity
,
Revenue
, and
Gross profit
from the
Sales fact
query subject to the
Columns
drop zone
COGNOS 8 NOTE
The
Products
query subject is called
Product
, and
Time
is called
Time Dimension
.