Menu

Blog

An interactive commenting solution for Tableau Server

Something I often hear after finishing a dashboard, as a Tableau Consultant, is the following: “Now can we add comments to this chart/dashboard/report?” Comments help create a context for users who require it, and thereby helps people understand the data they are looking at. For example, a plant manager can indicate a declining production in a certain month was due to maintenance taking place in a certain machine.
If you want the TL;DR of what we’re going to create, just check out this GIF:
While the answer to the aforementioned question is usually yes, there are different ways to implement this, some easier and some more complex.
  • The easiest method of commenting on dashboards, is to use Tableau Server’s built-in commenting functionality. While no additional setup or configuration is required to use this method, it is also the least flexible method. Though it must be noted, the commenting functionality has been significantly improved in Tableau 10.4: http://onlinehelp.tableau.com/v10.4/pro/desktop/en-us/comment.html
  • An alternative is to make use of a combination of Google Forms and Google Sheets. The form being filled out feeds the Google Sheet, which is then connected to the dashboard as a data source. The way that the form is integrated and filled out, can vary, but the form retains the look and feel defined by Google. Hence, this method requires slightly more preparation, but is somewhat more flexible.
  • A third possibility, which will be explained in this post, is more complex in setup, but offers the highest level of flexibility. It consists of a custom web page, integrated into the dashboard, which feeds a database that will contain the comments (and feed these back).
Let’s go with the following scenario to illustrate the concepts around this third possibility: we have a dashboard with just one sheet, which we want to comment on. The dashboard represents our profit ratio, for each Category of products we have, per Segment. The dashboard is usually released monthly, release/version which can be selected using the filters on the right. You may recognize a hint of Superstore here (where Sub-Category has taken the place and name of Category).
Also added to the sheet are two fields containing ATTR(USERNAME()) and ATTR(FULLNAME()), as to be able to identify the user who posted the comment later on.
To display the input form for a comment, we’ll use a URL action from a tooltip, i.e. by selecting what part of the sheet we want to comment on. This “part” will be a combination of Category and Segment, to start with. Additionally, these comments will have to be input each month of the year (i.e. for each release of the report). These are the 4 main dimensions, coming from the data, which we’ll store in our comment table in the database:
  • Year
  • Month
  • Category
  • Segment
In addition, we’ll be storing the following information about the comment in the table:
  • The comment itself.
  • The Tableau username of the person who has posted the comment.
  • Their full name.
  • The timestamp of when the comment has been posted.
For the purpose of illustrating this, the table containing these comments will be set up in MySQL. This solution, however, is agnostic to which database it is implemented with. Here’s the DDL for the creation of the comment table:
CREATE TABLE `comments_profitratio_segments` (
`category` varchar(50) NOT NULL,
`segment` varchar(50) NOT NULL,
`year` int(11) NOT NULL,
`month` int(11) NOT NULL,
`comment` varchar(1024) NOT NULL,
`username` varchar(255) NOT NULL,
`fullname` varchar(255) NOT NULL,
`insert_timestamp` timestamp NOT NULL DEFAULT URRENT_TIMESTAMP,
PRIMARY KEY (`category`,`segment`,`year`,`month`)
)
Notes:
  • The timestamp can be automatically filled in by how it is defined, although we will pass it from the web page sending the data.
  • The primary key is a combination of the 4 dimensions we chose from our dashboard. In MySQL, this will allow us to “insert or update” comments in one query. That is, we’ll be able to have MySQL decide if a certain comment already exists, and if so, update it.
Having defined the table in which we’ll store the comments, it is now time to create the two web pages that will “transport” our comment into the database. I won’t go into the details of how this (PHP) code works exactly, but it is important to understand the principles. This will allow you to build a similar application using the language of your choice. Also to be honest my PHP was a bit rusty, so this code is a bit best-effort (but does what it should do for the purpose of demonstrating this method). Here’s what the two pages (of which you’ll find the source code below) do:
  • input_comment.php: actually more HTML than PHP, this is the page that displays the form for the entry of the comment by the user. This is the page that will be displayed when someone selects part of the chart, and clicks through on the link in the tooltip. We’ll make sure it is displayed in the dashboard by using a Web Page object there.
  • submit_insert.php: this is the PHP code which does the actual work: it will receive the comment (and necessary dimensions and metadata such as the username) from the first page, and insert the data into the table. It will then display either a message confirming the successful addition of the comment, or the error that may have been triggered.

input_comment.php:

<?php
    include "head.html";    
?>

    <p>Category: <?php echo $_GET['category']; ?></p>
    <p>Segment: <?php echo $_GET['segment']; ?></p>
    <p>Period: <?php echo $_GET['year']; ?>, <?php echo $_GET['month']; ?></p>
    <p>User: <?php echo $_GET['comment_username']; ?> (<?php echo $_GET['comment_fullname']; ?>)</p>
        <form action="submit_insert.php" method="post">
            Comment:<br>
            <textarea name="comment" rows="15" cols="15">Enter your comment here.</textarea><br>
            <input type="hidden" name="category" value="<?php echo $_GET['category']; ?>">
            <input type="hidden" name="segment" value="<?php echo $_GET['segment']; ?>">
            <input type="hidden" name="year" value="<?php echo $_GET['year']; ?>">
            <input type="hidden" name="month" value="<?php echo $_GET['month']; ?>">
            <input type="hidden" name="comment_username" value="<?php echo $_GET['comment_username']; ?>">
            <input type="hidden" name="comment_fullname" value="<?php echo $_GET['comment_fullname']; ?>">
            <input type="submit" value="Submit Comment">
        </form>
        
<?php
    include "foot.html";    
?>

submit_insert.php:

<?php
    include "head.html";   

    ini_set('display_errors', 1);    

    $servername = "localhost";
    $username = "comments";
    $password = ""; // removed in this example
    $dbname = "comments";
    
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
    $category = $_POST['category'];
    $segment = $_POST['segment'];
    $year = $_POST['year'];
    $month = $_POST['month'];
    $comment = $_POST['comment'];
    $comment_username = $_POST['comment_username'];
    $comment_fullname = $_POST['comment_fullname'];
    
    $category = $conn->escape_string($category);
    $segment = $conn->escape_string($segment);
    $year = $conn->escape_string($year);
    $month = $conn->escape_string($month);
    $comment = $conn->escape_string($comment);
    $comment_username = $conn->escape_string($comment_username);
    $comment_fullname = $conn->escape_string($comment_fullname);
    
    echo "Category: $category<br>";
    echo "Segment: $segment<br>";
    echo "Year: $year<br>";
    echo "Month: $month<br>";
    echo "Comment: $comment<br>";
    echo "Submitting comment as: $comment_username ($comment_fullname)<br>";
    echo "<br>";
    
    // A better statement that will replace when necessary (assuming the keys are correctly defined)
    $sql = "INSERT INTO `comments_profitratio` (`category`, `segment`, `year`, `month`, `comment`, `username`, `fullname`, `insert_timestamp`) VALUES ('$category', '$segment', '$year', '$month', '$comment', '$comment_username', '$comment_fullname', now()) ON DUPLICATE KEY UPDATE `comment`='$comment', `username`='$comment_username', `fullname`='$comment_fullname', `insert_timestamp`=now()";
    if ($conn->query($sql) === TRUE) {
        echo "<p style='font-weight: bold'>Comment saved successfully.</p>";
    } else {
        echo "<p style='font-weight: bold; font-color: #990000'>Error: " . $sql . "<br>" . $conn->error . "</p>";
    }    $conn->close();
    
    include "foot.html";
    
?>
With these pages having been set up and being hosted where we need them to be, it’s time to modify our Tableau dashboards to make use of all this. As mentioned above, we’ll want to use a link from a tooltip to indicate which mark we want to comment on. This URL will load our form into the Web Page object of our dashboard, while at the same time feeding it with the parameters it needs (Segment, Category, Year, Month).
The steps we can go through to achieve this:
  • Add a Web Page object to the bottom right of the dashboard, leaving the URL blank for now.
  • Go to Dashboard > Actions and create a new URL Action
  • Then what we can do as well, is change the settings for the tooltips to appear “on hover” instead of responsively; which I find slightly easier when using hyperlinks in comments.
  • Then we test this setup by clicking one of the hyperlinks…
  • Bingo! The web form, pre-populated with the correct values for our dimensions, appears on the right-hand side of the dashboard:
  • We can then enter a comment and submit it as a test…
  • And indeed! The comment has been inserted into the database:
This was the hard part of the whole exercise. We’ve created the tiny web app that saves comment to the database. All that’s now left to do is… Retrieve this comment and display it in our dashboard. There’s different ways this can be achieved. You can blend in the table containing the comments, and enforce the relationship on the necessary dimensions. This blended data source can be either live (show comments by hitting refresh on Tableau Server) or extracted (periodically refresh automatically). You can also join in the table, and maybe refresh the whole thing daily, if extracted. In this case, we’ll go with blending, so we start by adding a completely new data source, the connection to our table.
We’re then defining the relationship (Data > Edit Relationships) between my two source manually, as the names don’t fully match and we have a “year of date” to match with a “simple year”.
Once this is done, we can drag in the necessary information (comment, timestamp, user, …) into our sheet (and in this case into the tooltip) to make use of them:
And sure enough…
We might even decide to use a visual cue to indicate there is indeed a comment that has been made for a certain mark:
So there you have it – a basic but powerful mechanism for adding comments to dashboards. There’s a lot of other principles that can be applied here. Currently anyone with access to the dashboard could comment on it, but we could build in the functionality to only allow commenting by certain users. This could probably be achieved by checking whether they are part of a certain Tableau Server users group. Furthermore, the actual form for submitting comments could be made more interactive. The whole thing can be further developed to work for multiple dashboards, in all kinds of ways.
You’re welcome to extend on this functionality and present your own findings in the comments!

Leave a Reply

Your email address will not be published. Required fields are marked *