Sorting Hyperlinks in Web Intelligence

Today is Valentine’s Day, so lets have some fun with SAP BusinessObjects Business Intelligence 4.1 Web Intelligence and Mobile BI 5.1. Candy and flowers are popular Valentine’s Day gifts, so let’s analyze the top 10 worldwide candy manufacturers using data from the trade journal Candy Industry. I’ll use this data to show you a tip for sorting hyperlinks in Web Intelligence.

I’m going to model the data using Microsoft Excel. Using previous versions of SAP BI, I would need the Web Intelligence Rich Client to work with Excel data. But using BI 4.1 SP2 I can do all my work from the BI Launchpad, uploading the Microsoft Excel spreadsheet to a public folder and using the Java version of Web Intelligence (sadly, the HTML version of Web Intelligence does not support Excel data sources). All of the data shown below was obtained from Candy Industry except the GPS coordinates, which I manually generated using Google Maps. I’ll show the mapping capabilities of Web Intelligence in a future article.

First, I’ll create a simple Web Intelligence table from the spreadsheet data. Although ranking anything “top 10” by a measure like sales is preferable, I’m going to show you a sorting trick so I’ll keep the default sort by the first column, Company.

Next, I’ll create a variable named Company Link that generates HTML to make the company name a hyperlink using the anchor tag, <a>.

I’ve replaced the Company column with my new Company Link variable. By default, the variable is interpreted as text, not a hyperlink. Although it’s useful to see if the formula generates proper HTML.

Next, I’ll right click in the Company Link column, choose to edit its properties, and change the display property from Text to Hyperlink.

I now have hyperlinks but at the expense of a slightly undesirable result. Instead of sorting on the Company Name, the table is now sorted by the HTML. In certain cases, the URL and the Company name begin with different letters (such as Barcel, Lindt, or Hershey’s), which will make anyone reading this table think that the data is unsorted.

So here’s the trick. Modify the Company Link variable and add the name attribute before the href attribute. Use the desired sort field, the Company object, as the value for the name attribute. Make sure each hyperlink has a unique name attribute, potentially concatenating additional data columns to insure uniqueness.

UPDATE (02/17/2014): See comment below by Alastair Gulland regarding the use of id attribute as preferable to name. I did a little more digging and found a reference here.

Now the HTML in the variable will sort in the same order as the company name.

Hyperlinks will also appear in the Mobile BI app. Here’s a screen shot from my iPad 2 running Mobile BI 5.1.8 on iOS 7.

The built-in Web Intelligence “red” palette is perfect for displaying data about Valentine’s Day candy, as it consists of various shades of red, pink and brown. Notice that the table and charts below sort from highest to lowest 2013 Net Sales, which is a more appropriate sort for a ranked list.

For whatever reason, the Mobile BI app does not render all the labels on the bar chart in the middle of the screen. However, mobile users can simply double-tap on any report element to zoom. The zoomed chart shows all the labels. Tapping any of the bars will display its precise value as a tool tip.

I hope this article will inspire you to use the new Microsoft Excel capabilities to create rapid prototypes for both desktops and mobile devices.

Happy Valentine’s Day!

Resources

• Top 100 Candy Companies ranked by Candy Industry
• SAP BusinessObjects Mobile BI 5.1.8 documentation
• SAP BusinessObjects BI 4.1 SP2 general availability and documentation links from the EV Technologies blog