Sorting Hyperlinks in Web Intelligence

A handy trick for sorting hyperlinks.

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.

Top 10 Candy Companies 01 Excel 600

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.

Top 10 Candy Companies 02 Table 600

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

Top 10 Candy Companies 03 Hyperlink Formula 400

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.

Top 10 Candy Companies 04 Hyperlink as Text 600

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

Top 10 Candy Companies 05 Hyperlink Properties 400

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.

Top 10 Candy Companies 06 Bad Sort 600

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.

Top 10 Candy Companies 07 Fix Formula 400

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

Top 10 Candy Companies 08 Good Sort 200

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.

Top 10 Candy Companies 09 Table 600

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.

Top 10 Candy Companies 10 Dash 600

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.

Top 10 Candy Companies 11 Zoom 600

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!