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.

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!

Resources

Dallas Marks

Dallas Marks

I am an analytics and cloud architect, author, and trainer. An AWS certified blogger, SAP Mentor Alumni and co-author of the SAP Press book SAP BusinessObjects Web Intelligence: The Comprehensive Guide, I prefer piano keyboards over computer keyboards when not blogging or tweeting.

7 thoughts on “Sorting Hyperlinks in Web Intelligence

  1. Nice post! And interesting to know that Web Intelligence sorts by the HTML syntax rather than what is actually displayed – seems like an oversight to me and maybe ought to be fixed in a future release.

    The “name” attribute of the tag is being deprecated in HTML5 and we should use “id” instead however I would expect that in your case it would have the same desired result. Another option is to use the “title” attribute however I think some browsers would display this text when hovering over the link and that may also be useful.

    1. Alastair,

      Thanks for the clarification on “name” vs. “id”. Either will solve the sorting problem but staying current with HTML5 is a better idea. I’ll give “title” a try and see how it is rendered in Web Intelligence.

      Regards,
      Dallas

  2. I have two variable PPP has date and AAA has date as well and I’m trying to create Alert on webi.Did alot of research but couldnt find right solution so far Here what i m trying to do “If PPP date exceeds AAA by between 14-27 calendar days, then highlight the cell Yellow. If PPP date exceeds AAA by 28 calendar days or more, then highlight the cell Red

    Any help?

  3. Any chance to have web consume a google spreadsheet via web services, and using it as a data source which can be updated.

    1. I do not believe such a feature is on the roadmap at this time. Since a Google spreadsheet is cloud-based, I would assume SAP will eventually support such a scenario for SAP Analytics Cloud. To make a product suggestion, visit https://influence.sap.com/.

Comments are closed.