(Read more interesting articles on IBM Cognos here.)

Showing images dynamically (Traffic Light report)

Let us suppose that we have a report which shows month-on-month difference in sales quantity

Getting ready

Please note that you will need administrator rights on the Cognos server to complete this recipe. If the server is installed on your personal machine, you will have these rights by default.

1. For this recipe, we need to first create three icons or images for red, yellow, and green. They should be already available on the Cognos server under <Cognos Installation>webcontentsamplesimages folder. If not, then create them using any image editor software or use the images supplied with this book.
2. Once you have the three images which you need to conditionally show on the report, place them on the Cognos server under <Cognos Installation>webcontentsamplesimages folder. (If the folder is not there, create one).
3. Change the IIS security to allow ‘Anonymous Read and Browse’ accesses.
4. Now open the report that shows the month-on-month running differences.



5. Insert a new ‘image’ from the insertable objects pane on the list report, as a new column.
6. Now go to Condition Explorer and create a new string variable. Define the expression as:

   if ([Query1].[Running Difference] > 0)
   then ('green')
   else if ([Query1].[Running Difference] < 0)
   then ('red')
   else ('yellow')

7. Call the variable Traffic and define three possible values for the same (red, yellow, and green).



8. Now go back to the report page. Select the image. Open its URL Source Variable dialog. Choose the variable Traffic and click OK.



9. From Condition Explorer, choose ‘red’ condition. Now click on the image again. It will allow you to define the image URL for this condition.
10. Set the URL to: ../samples/images/Red.jpg

11. Similarly, define the URL for ‘yellow’ and ‘green’ conditions as ../samples/images/yellow.jpg and ../samples/images/green.jpg respectively.
12. Run the report to test it.

How it works…

Cognos Report Studio allows you to put the images in the report by specifying the URL of the image. The images can be anywhere on the intranet or internet. They will be displayed properly as long as the URL is accessible from Cognos application server and gateway.

In this recipe, we are using a report which already calculates the Running Difference. Hence, we just had to define conditional variable to trap different possible conditions. The Image component allows us to define the URL for different conditions by attaching it to the Traffic variable in step 8.

There’s more…

In this case, though the URL of the image changes dynamically, it is not truly 100% dynamic. There are three static URLs already defined in the report and one is picked up depending on the condition.

We can also use a data item or report expression as source of the URL value. In that case, it will be totally dynamic, and based on the values coming from database; Cognos will work out the URL of the image and display it correctly.

This is useful when the image filenames and locations are stored in the database. For example, Product Catalogue kind of reports.

More info

This recipe works fine in HTML, PDF, and Excel formats.We have used relative URLs for the images, so that report can be easily deployed to other environments where Cognos installation might be in a different location. However, we need to ensure that the images are copied in all environments in the folder mentioned in step 2.

Handling missing image issue

In the previous recipe, we saw how to add images to the report. You will be using that technique in many cases, some involving hundreds of images (For example, Product Catalogue).There will often be a case in which database has a URL or image name, whereas the corresponding image is either missing or inaccessible. In such a case, the web browser shows an error symbol. This looks quite ugly and needs to be handled properly.

In this recipe, we will see how to handle this problem gracefully.

Getting ready

We will use the report prepared in previous recipe. We need to delete the Green.jpg file (or rename it to something else) from the server, in order to create the missing image scenario.

How to do it…

1. In the previous recipe, we added an image object and defined its conditional URLs . We need to replace that image with an HTML Item. For that, unlock the report objects and delete the image component. Add an HTML Item in the same column.



2. Select this HTML item and from the Properties pane, set its HTML Source Variable to ‘Traffic’. (Please note that we already have this conditional variable in the last recipe).
3. Now define the HTML for different conditions. Start with ‘red’. Choose ‘red’ from conditional explorer and define the HTML as:
4. For ‘yellow’, define the HTML as: <img src=”../samples/images/yellow.jpg” alt=”No Change” onError=”img2txt(this)”/>
5. For ‘green’, define HTML as: <img src=”../samples/images/green.jpg” alt=”Upsell” onError=”img2txt(this)”/>
6. Now go back to the No Variable state by double clicking on the green bar, and add another HTML item on the report. Put it just before the list.



7. Define this HTML as:

   <script>
   function img2txt(img) {
   txt = img.alt;
   img.parentNode.innerHTML=txt;}
   </script>

8. Now run the report to test it.

As you can see, if the image is missing, the report will now handle it gracefully and show some text instead of an error image.

How it works…

Here we are using our custom code to display the image, instead of using CRS’s in-built Image component.We have pulled an HTML item onto the report and defined it to display different images depending on the condition using the <img border=”0″ /> tag. This tag allows us to define an alternative text and onError event as well. We are using the onError event to call our custom made JavaScript function called img2txt .This function replaces the HTML item with a text which was originally defined as ‘alternative text’. Hence, if green.jpg is missing, this function will replace it with a text item Upsell.

There’s more…

As we are using HTML code and JavaScript in this technique, it works in HTML format only. This technique will be useful for a lot of graphical reports (dashboards, scorecards, online product catalogues, and so on).

Dynamic links to external website (Google Map example)

In this recipe, we will introduce you to the ‘Hyperlink’ component. A report shows retailers information by products. It shows various fields like Retailer name, Contact information, City, and Postal zone. Business wants to have a link to Google maps that will show retailer’s place on the map using the Postal zone information.

As the addresses might change in the backend, the technique needs to be dynamic to pick up the latest postal zone.

Getting ready

Create a simple list report that shows Retailers information by Product lines.

How to do it…

1. From the ‘Insertable Objects’ toolbox, drag a hyperlink object onto the report as a new column.



2. Change its Text property to Map. Set the URL Source Type to Report Expression and define the report expression as follows: ‘http://maps.google.com/maps?q=’ + [Query1].[City]



3. Run the report to test it.

As you can see, there is a link for each retailer record. If you Shift+click on the link, it will open the Google map for corresponding postal zone in a new window.

How it works…

Here we are using the ‘Hyperlink’ component of CRS. We can define the URL as any static link. However, for our requirements, we have defined a report expression. This allows us to provide a dynamic link which picks up the latest postal zone from the database. We are passing the postal zone to Google Maps as part of a URL.

The hyperlink component works in HTML as well as Excel and PDF formats of report. This object currently does not have the property to define whether the link target should open in a new window or the same window. Just clicking on the link, opens the target in same window; whereas Shift+click opens in a new window.

There’s more…

You can use this technique to call any external website that accepts parameters within a URL. You can pass multiple parameters too.

 

Alternating drill link

In this recipe, we will learn about a limitation of drill link and overcoming it using Render Variable.

There is a crosstab report which shows sales quantity by month and order method. We need to provide drill-through facility from the intersection. However, the drill-through target needs to be different, depending on the order method.

If order method is e-mail, the drill-through from intersection should go to a report called ‘Alternating Drill Link—Drill Report 2’. For all other order methods, it should go to ‘Alternating Drill Link—Drill Report 1’.

Getting ready

Create a crosstab report to serve as the main report. Drag Month (shipment) on rows, Order method on columns and Sales Quantity on the intersection.

Create two list reports to serve as drill reports. In the sample provided with this book, we have used two list reports for this. One accepts the Order method and Month. The other accepts only month and is designed to work for the order method ‘E-mail’.

How to do it…

1. Create a drill through to first drill the report from the crosstab intersection.



2. Now make sure that the report objects are unlocked. Select the intersection text item (which now looks like hyperlink as there is already a drill-through defined). Hold the Ctrl key down and drag the text to its right within a cell.
3. This should create a copy of the text item within that cell and will look like the following:



4. Now select this copy of the text item. Hit the drill-through button to open definitions. Delete the existing drill-through to first report. Create a new drill to a second report.

So, now we have two text items in the cell, each going to different drill reports.

5. Create a string type of Conditional Variable. Define it as: if ([Query1].[Order method] = ‘E-mail’) then (‘E-mail’)else (‘Other’)Call it OrderMethod and define the two values to be E-mail and Other.



6. Now go back to the report page. Select the first text item from intersection. Open its Render Variable property . Choose the OrderMethod variable and select to Render for: Other.



7. Similarly, define Render Variable for the second text item, but choose to Render for: E-mail.
8. Run the report to test it. You will see that clicking on the intersection numbers opens first drill report for any order method other than E-mail. Whereas for the numbers under E-mail, the second drill report opens.