Many of our customers use FluenceXL reporting for analysis and data visualization, but an increasing number use it for data updates, either within budgeting and forecasting solutions or in niche applications.
XL3RunSQL is a neat method for running SQL updates from Excel and allows users to build input forms (which can ultimately be web-enabled) quickly and easily.
In this blog, we will walk through the steps to set this up.
Here’s a FluenceXL Table based on a SQL query, showing employee details including their salary and commission:
We will use an XL3RunSQL formula to change and update each employee’s salary and commission.
Columns P and Q will hold the new values for commission and salary that will be used to update the table:
The XL3RunSQL Formula has the following syntax:
=XL3RunSQL(ExecuteSQL, Connection, SQLStatement, [TableSheet], [Table])
To set up the parameters for each row to be used in the XL3RunSQL statement, add four calculations to the tables in columns K-N. We will then add some formulas to manage the update. Adding these to the table means that data growth is handled automatically.
The parameters
1. ExecuteSQL
A cell reference that contains TRUE when the SQL Statement should be run. After execution, the cell reference will be set to FALSE or an error message if an error occurred.
We will create an XL3Link in column N so that when clicked, it will update a certain cell reference to TRUE, allowing the XL3RunSQL to run the SQL statement. Use the following formula:
=XL3Link(“”,”Update”,,XL3Address($L4), if(and($P4<>””,$Q4<>””),”TRUE”,”FALSE”)
This will set the cell L4 to TRUE only when there are new values in columns P and Q – this ensures the SQL query is only run when new values have been added.
2. Connection
The connection to use to connect to the database. This is normally the Connection ID of a workbook relational connection or a connection string.
From the FluenceXL Ribbon > Connections:
3. SQLStatement
The SQL Statement to run.
We have set up the SQL statement in column K using a CONCATENATE formula:
=CONCATENATE(“update employees set salary = “, TEXT(IF($P4=””,$I4, $P4),0),”,commission_pct=”, TEXT(IF($Q4=””,$J4,$Q4),”0.00″), “where employee_id = “, TEXT($B4,0),””)
This formula will set the SQL Statement to update the salary and the commission to the new values. If there are no new values, the SQL statement will not run and the values will remain unchanged.
4. TableSheet (Optional)
Worksheet name containing query table or FluenceXL Table to refresh after the SQL has run.
5. Table (Optional)
The name of the query table or FluenceXL Table to refresh after the SQL has run.
We can name the table by right-clicking on the table > Properties > Behaviour
XL3RunSQL
We now have the parameters we need to set up the XL3RunSQL formula.
We will set up our formula in column M:
=XL3RunSQL($L4,1,$K4,,”Employee_Details”)
Now we can see that every time we add values for the New Salary or New Commission, the table will update every time we click ‘Update’. We can also hide the calculation columns K-M to make the table look neater and not show any unnecessary information.
After we have input all of our data and updated the table, we can then clear the data in columns P and Q. This can be done using an XL3link, for example:
=XL3Link(“”,”Clear Inputs”,,XL3Address($P4:$P100),””,XL3Address($Q4:$Q100),””)
To make the range dynamic, you could also use a named range for both columns P and Q.
In the example below I have used a slicer to filter the information based on a specific Job Type. As you can see, since the formulae are included as Table calculations, the whole input table handles varying number of rows without the need for any manual copying of formulae.
To utilize the same approach with stored procedures, the XL3RunSQLProc formula has a similar logic.
