Database Reference
In-Depth Information
11.
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
.
12.
In the
Properties
window for the
Data Item
, use the
Name
property to rename the
DataItem1
data item to
PartTwo
.
13.
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
PartTwo
data item.
The Data Item Expression window opens.
14.
Create the following expression:
IF ([Position]= 0) THEN
('')
ELSE
(substring ([Product description], [Position] +
char_length([SearchText]), char_length([Product
description]) - char_length([SearchText]) -
char_length([PartOne])))
NOTE
The expression has two single quotes without spaces.
If the searched text does not exist in the Product description field, we will just default to
an empty string.
If we do find the text, PartThree needs to contain text that is after the searched string,
including the space after the searched string. This is why we need to use character length
functions to figure out the positioning of the starting point for the substring function and
the length of the remaining string.
15.
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
.
16.
In the
Properties
pane for the
Data Item
, change the
Name
property to
PartThree
.
This completes our report query build.
17.
Click on the
Run
menu item and choose the
View Tabular Data
option to test the
Report query before starting the report design. The warning message pop-up can be dis-
missed by clicking the
OK
button.
Sample text for a search that you could use is
rope
.
Yo u r r e s u l t s w i l l b e s i m i l a r t o F i g u r e 1 . 2 .
18.
Close the
IBM Cognos Viewer
window to return to IBM Cognos Report Studio.
