Clickable URL Links In Pivot Rows

[intapiuser]

Render existing HTML code stored in your data model

Follow the steps below in order to URL clickable within a pivot table. Some steps are different depending on whether your Sisense instance is running on Windows or Linux. 

You can check whether your Sisense instance is running on Linux or Windows by following the steps in this article: Check whether running Sisense on Linux or Windows

Common Steps

1. Create a custom column within the table with the URLs you would like to make clickable. In this example I called it HTMLURL

Craft the HTML version of the link using <a href> tags. I used column "Link" as the URL and column "Name" as the display text

'<a href="' + [Link] + '" target="_blank">' + [Name] + '</a>'

This will create a column with the URL wrapped with HTML elements.

3. Rebuild the cube to include the custom column

4. Create a pivot table with the new column

Additional Steps for Sisense on Linux

These steps will only need to be performed once and can only be taken by an Admin or Sys Admin in the Sisense instance. 

5. Go to Admin tab > System Management > Configuration

6. Within the resulting window, expand the "Pivot Table" section, and toggle on "Allow rendering Pivot Table content as HTML"

7. Then scroll down to the bottom of the page and click Save

8. Check back on your pivot widget, it should be rendering the HTML as a hyperlink.

Additional Steps for Sisense on Windows

5. To render the HTML, we need to edit the the script of the widget. Within the widget editor, click the 3 dots at the top right hand corner, then click "Edit Script"

6. Paste the following script into the script editor window. Then click Save.

widget.on('ready', function(){
     var tags = $('tbody span:contains("<")');
     for(var i = 0; i<tags.length; i++){
         $(tags[i]).replaceWith(String(tags[i].textContent))
     }
});

7. Close the script window and refresh the widget page. The result should be a formatted link

8. Save the newly edited widget by hitting "Apply"

Limitations

File exported to Excel/CSV will contain not rendered HTML code.

Using capabilities of Pivot API (for Linux only) to add additional HTML code on the client side to links stored in cube.

1. Add a custom field in your data model and compute there your link. Your link should have the following view:
   https://example.com?displayedValue=[SomeValue]&{optionalParameters}
   In terms of this link, displayedValue is a value that a user will see instead of the link on the pivot
   
   Build your cube.
2. Add created field/fields to your Pivot widget and apply the following widget's script:

var linkRowNumber = [
 1, 3
]; //Columns' numbers where we need to render links. Numeration in JS starts from 0.
var displayedValue = 'displayedValue'; //Name of the query parameter that stores displayed value for link
var defaultDisplayedValue = 'Link'; //This value will be displayed on top of the link, if another value was not found

widget.transformPivot(
 {
  type: ['member']
 },
 function setCellBackground(metadata, cell) {
  if (metadata.rowIndex && linkRowNumber.includes(metadata.colIndex)) {
   cell.content = "<a href=" + cell.content + ' target="_blank">' + getParameterByName(displayedValue , cell.content) + "</a>"; //Prepare link
   cell.contentType = "html";
  }
 }
);


function getParameterByName(name, url) {
 name = name.replace(/[\[\]]/g, "\\$&");
 var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)");
 var results = regex.exec(url);
 if (!results) return defaultDisplayedValue;
 if (!results[2]) return defaultDisplayedValue;
 return decodeURIComponent(results[2].replace(/\+/g, " "));
}

Now, links will be rendered by the widget's script. Also, when you export this Pivot into Excel/CSV, these links will be shown as valid links without any additional HTML code:

Enjoy!

[rafaelderas]

Does this solution only works with custom columns? I have added the url generation to the SQ part of a data node on a live model, but when displaying the dashboard, I don't get the clickable link I just get all the html code from the column I generated in the data model (URLs blurred intentionally)

[Laflet]

https://community.sisense.com/t5/knowledge/clickable-url-links-in-pivot-rows/ta-p/9479

How does one remove an unintended comment?

[liamcameron]

hey @intapiuser what if we just want to display the text in our pivot... so maybe we want to show the min(date) or first(string)... pivots do not seem to support this and we've been unable to find a way to do it... seems like this is close! but we're not using URLs