(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
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.
- 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
webcontentsamplesimagesfolder. If not, then create them using any image editor software or use the images supplied with this book.
- Once you have the three images which you need to conditionally show on the report, place them on the Cognos server under
webcontentsamplesimagesfolder. (If the folder is not there, create one).
- Change the IIS security to allow ‘Anonymous Read and Browse’ accesses.
- Now open the report that shows the month-on-month running differences.
- Insert a new ‘image’ from the insertable objects pane on the list report, as a new column.
- Now go to Condition Explorer and create a new string variable. Define the expression as:
if ([Query1].[Running Difference] > 0)
else if ([Query1].[Running Difference] then ('red')
- Call the variable Traffic and define three possible values for the same (red, yellow, and green).
- Now go back to the report page. Select the image. Open its URL Source Variable dialog. Choose the variable Traffic and click OK.
- From Condition Explorer, choose ‘red’ condition. Now click on the image again. It will allow you to define the image URL for this condition.
- Set the URL to: ../samples/images/Red.jpg
- Similarly, define the URL for ‘yellow’ and ‘green’ conditions as ../samples/images/yellow.jpg and ../samples/images/green.jpg respectively.
- 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.
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.
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.
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…
- 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.
- 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).
- Now define the HTML for different conditions. Start with ‘red’. Choose ‘red’ from conditional explorer and define the HTML as:
- For ‘yellow’, define the HTML as:
- For ‘green’, define HTML as:
- 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.
- Define this HTML as:
- 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…
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.
Create a simple list report that shows Retailers information by Product lines.
How to do it…
- From the ‘Insertable Objects’ toolbox, drag a hyperlink object onto the report as a new column.
- 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]
- 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.
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’.
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…
- Create a drill through to first drill the report from the crosstab intersection.
- 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.
- This should create a copy of the text item within that cell and will look like the following:
- 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.
- 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.
- 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.
- Similarly, define Render Variable for the second text item, but choose to Render for: E-mail.
- 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.
So, now we have two text items in the cell, each going to different drill reports.