4 Replies Latest reply on Jan 2, 2014 10:34 AM by Aaron Clancy

    Possible to write comment by user and save data in excel file then read back ?

    paul yeo

      Hi All

       

      I want the user look for debt report. And they will call the customer for payment collection.

      If customers reply will issue cheque on next month. She will enter the info on this software.

      And the info will save at excel file. When the next open . It will write the data to this app. So other is aware the follow up status.

      Can this be achieve ?

        • 1. Re: Possible to write comment by user and save data in excel file then read back ?
          Jonathan Drummey

          Tamas Foldi had come up with a hack awhile back (search for it on the forums), in Tableau 8.1 you might be able to put something together using stored procedures or R. I've been trying out something with the R integration and found an unanticipated behavior where the R code was being called whether it needed to be or not, so that might not be quite ready to be used in production yet.

           

          Jonathan

          • 2. Re: Possible to write comment by user and save data in excel file then read back ?
            Aaron Clancy

            I currently do this with a PHP page that that receives http requests from a Tableau click action.

             

            Interaction:

            When the user mouses over the mark they would like to make a comment on, there is a menu item that says "Leave Comment"

             

            When this is clicked, a popup comes over the visualization that allows the user to enter a comment and click submit.

            Upon submission, a record is inserted into a "Comments" table of my DB.

             

            This table is blended to my original data source and joined off of a composite key that determines which marks have comments associated to them.  Marks with comments will show up as a different color to show the end user which marks have been "Processed."

             

            It's not a lot of code, just takes a little setup.

            If you would like a code example I can post it.

            • 4. Re: Possible to write comment by user and save data in excel file then read back ?
              Aaron Clancy

              FOR CODE REFERENCE ONLY

               

              I do this with a WAMP server.  (Great for people who don't know much about web servers but want to do server side scripting)  Quick easy installation.  (You might have to modify the httpd.conf file to allow outside requests if you have WAMP installed on another machine and not the one that Tableau Server is on)

              I'm not comfortable posting 3rd party links on here but you can Google it.

              (After successfully installed and running with a "Green W")

              You'll create three files in a subdirectory of the www directory called comments.

              c://wamp/www/comments/popup.php

              c://wamp/www/comments/insertcomment.php

              c://wamp/www/comments/splash.html

               

              None of this was tested, I just mocked it up to show you how it works (I can't post my actual code)

               

              This example is very light.  I only used ItemID as the primary value (most scenarios will need to have multiple dimensions passed to create a unique condition for your marks but I'm just trying to keep the code concise.

               

              First file is the Popup page stored as popup.php

              <!-- code starts here  -->

              <html>

              <head>

              <script>

              <?PHP

              function issetor(&$variable, $or = NULL) {

                  return $variable === NULL ? $or : $variable;

              }

              //  Grabs the itemID from the HTTP GET request from the Tableau Click action

              if (issetor($_GET['itemID']) == true){$itemID = $_GET['itemID'];}

              else{$itemID="";}

               

              //   Grabs the username from the HTTP GET request from the Tableau Click action

              if (issetor($_GET['username']) == true){$username = $_GET['username'];}

              else{$username="";}

              ?>

               

              //Popup box

              var comment=prompt("<?PHP echo $username;?>, Enter Comment:","");

              if (comment.length>1){

              window.location.href='insertcomment.php?itemID=<?PHP echo $itemID;?>&username=<?PHP echo $username;?>&comment='+comment;

              };

              </script>

              </head>

              <body>

              </body>

              </html>

              <!-- end of file   -->

              ***************************************************************

              A splash page will need to be created that could literally be a blank file saved as splash.html (it's just a placeholder)

              After you've stored the above code in the location specified, you'll need to embed your splash page as a dashboard Web Page component.

              Screen Shot 2014-01-02 at 11.38.02 AM.png

              The URL will be  http://yourwebserverhost/comments/splash.html

              You'll then create an HTML Click Action on the Tableau Dashboard.  Dashboard>Actions>Add Actions> URL

              Name the action something like  "Add Comment"

              Set the URL to: http://yourwebserverhost/comments/popup.php?username=<username>&itemID=<itemID>

              You'll need two fields in your data, itemID is just an example and username is just a calc with the value: username() stored in it.  (I do this to track which users leave which comments)

               

              ***************************************************************

              <!--second page insertcomment.php  starts here   -->

               

              <?PHP

              //function that accounts for missing GET values

              function issetor(&$variable, $or = NULL) {

                  return $variable === NULL ? $or : $variable;

              }

              //  Grabs the itemID from the HTTP GET request from commentpopup page

              if (issetor($_GET['itemID']) == true){$itemID = $_GET['itemID'];}

              else{$itemID="";}

               

              //   Grabs the username from the HTTP GET request from the commentpopup page

              if (issetor($_GET['username']) == true){$username = $_GET['username'];}

              else{$username="";}

              ?>

              $con=mysqli_connect("mysqlserverhostname","mysqlusername","mysqlpassword","mysqldatabase");

              // Check connection

              if (mysqli_connect_errno())

                {

                echo "Failed to connect to MySQL: " . mysqli_connect_error();

                }

               

              //Didn't test this so you might have to play around with the quotes to get right

              mysqli_query($con,"INSERT INTO commentstable (username,itemID)

              VALUES ('".$username."','".$itemID."')");

               

              mysqli_close($con);

              ?>

               

              <html>

              <body>

              <!--  There is no Error Handling here   -->

              Your comment has been added.</br>

              Changes will not reflect until the data</br>

              has been refreshed.

              </body>

              </html>

               

              <!-- end of file     -->

               

              Create a live connection to the commentstable and blend off of itemID.

              You can now check for comments associated to a particular itemID through this relationship and color your marks accordingly.

               

              Considerations:

              • There is pretty much no security with this as you are using GETS in the URL string.
              • I wouldn't attempt this unless you're fairly familiar with SQL as you will have to be creating tables etc...
              • You can add a "Delete Comment" action as well with pretty much the same code as the insert page (with a delete statement of course).

               

              If you leverage the Javascript API you can do real fun stuff with this (and you can use POSTS instead of GETS if security is an issue).

               

               

               

              Hope the code helps  (I'm not a developer and it's a mockup so there is a lot left out  i.e. error handling etc.....)