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.
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.
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