Skip to main content

I am trying to have an end user look at the results, apply their business knowledge and then adjust a value in a SQL table.  To adjust the value, I am having the end user select how much they want to adjust the value by (Adjustment parameter) and what category they would like to adjust for (Category parameter).  I am trying to get these selections to kick off a R script in a calculated field and write their values back to a table in SQL. 

 

The datasource is a SQL table called ForecastTable.  For this simple example there are two fields, Quantity and Class, and could look something like below.

Quantity      Class

500               Category 1

750               Category 2

 

Here is my R script I am using to try to write this back to SQL.  Access is not an issue and the connection is also not the issue.  I am able to run this code in R directly and it works fine if I plug in values for the variables.

 

SCRIPT_REAL("library(RODBC);

library(Rserve);

dbhandle <- odbcDriverConnect('driver={SQL Server};server=TEST;database=Test1;trusted_connection=true');

Quantity2 <- .arg1[1]

Category2 <- .arg2[2]

sqlQuery(dbhandle, "UPDATE [Test].[dbo].[ForecastTable]

            set [Quantity] = Quantity2

            where [Class] = Category2)",

[Adjustment], [Category])

 

In the end, once the data is written back to the SQL table, the end user would refresh the datasource and see the changes they have made.

5 answers
  1. Dec 28, 2016, 11:15 PM

    Hi,

    My code works in R when I run it, so I know this is correct R code.  Below is the error code I am getting in my calculated field.  I believe it is erroring out because of the quotation marks in the SQL update statement, when I change those to single quotes the error msg goes away, however, it is not valid R code so it will not execute correctly.

    Hi,My code works in R when I run it, so I know this is correct R code. Below is the error code I am getting in my calculated field.

     

    If you have any other suggestions to updating SQL, even going outside of straight up Tableau, I am open to trying that as well.  I have looked into the Shiny package in R as well, but am running into security issues with that so I am not sure that will work for me.  I have also just begun looking into the JavaScript API so will see where that takes me.

     

    Thanks

    Brandon

0/9000