IBM Cognos 10 Report Studio: Creating Consumer-Friendly Reports
- Highlight Selected Text
- Avoiding Query Macros
- Complex Crosstab Calculations
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
One of the biggest advantages of creating reports in IBM® Cognos® Business Intelligence (BI) is that you can now create one (or more) reports that can be designed to match how business users process information. These consumers do not need to wade through waves of report pages. A series of small reports that independently focus on specific information can link to other reports. We will take a look at using drill-through in the next chapter, but you should look for ways to simplify the reports at every opportunity.
This means you, as the report author, should look for ways to present the data in a format that makes the exploration process easier for the consumers. This chapter provides examples of simple reports that present data.
In our training classes, we regularly hear from students who want to create very complex reports. We begin by asking what the users need to accomplish with the report and begin to build the report using an iterative building block process.
This chapter presents several reports that are designed to leverage features to create reports that focus on a specific task.
Highlight Selected Text
You have been asked by the product marketing staff to create a product catalog. When you ask how it will be used, the market analyst wants to be able to find descriptions of products that use specific phrases to ensure that the proper messages are reaching the customer.
Search technology has become sophisticated enough that documents can be searched for text and the phrases can be highlighted to allow the searcher to focus on the text. The analyst would like to simulate that functionality in the report.
This report should be able to prompt the user for a search phrase and return a list of product descriptions that contain the phrase and highlight the selected text. The final result should look like Figure 1.1.
)
Figure 1.1 Completed enhanced product catalogw
Design
The trick to solving this problem is to utilize several string functions that are available in IBM Cognos Report Studio in order to find the requested part of the text and then cut the preceding and succeeding text. Once the searched text field has been separated into three parts, we can use the logic within our query items to put the strings back together within a single column and highlight only the searched text.
The list report should filter on only those products that contain the search text.
Step-by-Step
The key steps involve the creation of a query that parses the product description and removes any products that do not have the matching phrase. With the query built and tested, the list report is designed.
Step 1: Start the Report
- Launch Report Studio and select the GO Data Warehouse (query) package.
- Click on the Create new option.
- Select the List report template and click OK.
We will be using the Sales (query) namespace inside the Sales and Marketing (query) folder.
Step 2: Begin the Report Query
The key component of this report is the capability to parse the text of the report description for selected text. This step defines the two fields.
- In the Explorer Bar, mouse over the Query Explorer tab and select the Query1 object.
- From the Source tab of the Insertable Objects pane, drag the Product description query item from the Products query subject into the Data Items pane of the query design window.
- From the Toolbox tab of the Insertable Objects pane, drag a Data Item into the Data Items pane below Product Description.
The Data Item Expression window opens.
- Type the following code in the Expression definition window:
?SearchText?
This creates a parameter called SearchText and assigns the value to the Data Item we just created. We will be searching for the text that will be typed into the server-generated prompt because we will not create a prompt page on our own in this example.
- Validate the expression and click OK to close the dialog box.
- In the Properties window for the Data Item, use the Name property to rename the DataItem1 data item to SearchText.
Step 3: Include the Search Functionality
In this section, we will add the key functionality to the query. First, we will add a function to search for the matching text. If a match is found, we will break up the description into three fields. If a match is not found, we will leave the description in the first field.
- From the Toolbox tab of the Insertable Objects pane, drag another Data Item into the Data Items pane below the SearchText data item.
The Data Item Expression window opens.
- Create the following expression:
position([SearchText], [Product description])
The position function returns an integer value that represents where the first character of the searched text begins within the Product description string. If no match is found, the position function returns a zero.
- Validate the expression and click OK to close the dialog box.
- In the Properties window for the Data Item, use the Name property to rename the DataItem1 data item to Position.
- From the Toolbox tab of the Insertable Objects pane, drag another Data Item into the Data Items pane below the Position data item.
The Data Item Expression window opens.
- Create the following expression:
IF ([Position]=0) THEN ([Product description]) ELSE (substring([Product description], 1, [Position]-1))If the searched text does not exist in the Product description field, then we will set this first field to the full product description.
In case the string is found, we want to cut off the text that precedes the string we are looking for, including the space before the string. This is why we use [Position] - 1 as the third argument in the substring function.
- Validate the expression and click OK.
- In the Properties window for the Data Item, use the Name property to rename the DataItem1 data item to PartOne.
- From the Toolbox tab of the Insertable Objects pane, drag another Data Item into the Data Items pane below the PartOne data item.
The Data Item Expression window opens. This field contains the text to be highlighted only if the text is found.
- Create the following expression:
IF ([Position]=0) THEN ('') ELSE ([SearchText]) - Validate the expression and click OK.
- In the Properties window for the Data Item, use the Name property to rename the DataItem1 data item to PartTwo.
- From the Toolbox 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.
- 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])))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.
- Validate the expression and click OK.
- In the Properties pane for the Data Item, change the Name property to PartThree.
This completes our report query build.
- 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 dismissed by clicking the OK button.
Sample text for a search that you could use is rope.
Your results will be similar to Figure 1.2.
Figure 1.2 Tabular data view
- Close the IBM Cognos Viewer window to return to IBM Cognos Report Studio.
)
Step 4: Create the Report Design
Now we will add the three parts to a list column named Product description.
- Mouse over Page Explorer and click on Page1.
- From the Data Items tab of the Insertable Objects pane, drag the following data items into the List object: PartOne, PartTwo, and PartThree.
- Unlock the List object cells by clicking on the Unlock (currently locked) button on the toolbar.
- Click on the PartTwo text item within the PartTwo list column body to select it. Drag it over into the list column body of the PartOne column to the right of the PartOne text item.
- Click on the PartThree text item within the PartThree list column body to select it. Drag it over into the list column body of the PartOne column to the right of the PartTwo text item.
- Click on the PartTwo text item and then click on the Foreground Color button on the toolbar and select the drop-down arrow. From the Named Colors menu, change the foreground color to Red. Click the Bold button on the toolbar to change the font effect to bold.
Your design should look similar to Figure 1.3.
Figure 1.3 Start of the report design
- Click on the PartOne text item within the PartOne list column title area.
- In the Properties pane, change the Source Type property to Text.
- Double-click the Text property and type Product Description.
- Click OK to close the dialog box.
- Lock the List object cells by clicking on the Lock (currently unlocked) button on the toolbar.
- Ctrl-click the PartTwo and PartThree list column bodies and press Delete on the keyboard to remove them from the report design. Your design should now look similar to Figure 1.4.
Figure 1.4 Key report design
- From the Run menu, select Run Report – HTML to view the report.
When prompted, click in the Provide a value prompt box and type glasses.
Your results should look similar to Figure 1.5.
Figure 1.5 Report view
- Close the IBM Cognos Viewer window to return to IBM Cognos Report Studio.
)
)
)
Step 5: Finalize the Report Design
The core development of this report is finished; what is left are the finishing touches. We will add additional data elements for the product catalog and filter the report to show only the products whose descriptions contain the keyword that was entered at runtime.
- In the Explorer Bar, mouse over the Query Explorer tab and select the Query1 object.
- From the Data Items pane, drag the Position data item into the Detail Filters pane.
The Detail Filter Expression dialog box opens and shows [Position] in the Expression Definition box.
- Add the following code in the Expression Definition window after the [Position] expression:
<> 0
Your expression should now be this:
[Position] <> 0.
- Click OK to close the Detail Filter Expression dialog box.
This ensures that only product records with descriptions containing the keyword get retrieved from the database.
- Mouse over Page Explorer and click on Page1.
- Click on the Report Title text to select it.
- Change the Source Type property to Report Expression.
- Double-click the Report Expression property.
The Report Expression dialog box window opens.
- Create the following expression in the Expression Definition box:
'Product Catalog records for keyword - ' + ParamDisplayValue('SearchText') - Validate the expression and click OK to close the Report Expression dialog box.
- From the Source tab of the Insertable Objects pane, Ctrl-click and drag the Product query item from the Products query subject and the Product Number query item from the Codes folder as columns in front of the Product Description column in the report list.
- From the Source tab of the Insertable Objects pane, Ctrl-click and drag the Product color, Product size, Introduction date, and Discontinued date query items from the Products query subject as columns after the Product Description column in the report list.
Your report design should now look similar to Figure 1.6.
Figure 1.6 Final report design
- From the Run menu, select Run Report – HTML to view the report. When prompted, click in the Provide a value prompt box and type rope.
Your results should look like Figure 1.7.
Figure 1.7 Final enhanced product catalog
- Close the IBM Cognos Viewer window to return to IBM Cognos Report Studio.
)
)
By using a combination of string functions, we were able to split the original text field into three text items that contained all text before the searched string, the actual search string, and all text after the searched string, respectively. Once the query was built, we were able to use a simple IBM Cognos Report Studio built-in feature to unlock the report list cells in order to be able to condense the report and combine all the row data in one defined column.
Our careful string manipulation and simple font-color change allowed us to create an illusion of word highlighting within a larger text field based on the word search entered by the user at runtime.