Blog

On a prior blog I outlined the steps for retrieving data into a Dashboard using a Table View and a Spreadsheet component. What if you need to write data back to a relational table (you could even write back to the cube, however that will be the subject of another blog) using a Table View and a Spreadsheet component? The Table View in this example is called “TableView_Demo” and it’s based on a custom relational table called “XFC_TableView_Demo” and it has 2 data columns, Account and Amount. The finished Table View data in the Spreadsheet component on the Dashboard will look like the following screenshot. Note that in this example I am not passing any parameters.

I have already built out a Spreadsheet Component and added the Table View to it. Notice that in this example I am hiding the Spreadsheet Ribbon and Formula bar.

I have added a Button to the Dashboard which saves the Spreadsheet and refreshes it. The save action calls the SpreadsheetFunctionType.SaveTableView section of the Spreadsheet business rule, looking for the name of the Table View that is saved.

Here are the steps to enable write-back using a Table View.

• 1. The first step is in your prior code, where you setup the Table View that is being displayed on your spreadsheet component, you need to set the “CanModifyData” property of the Table View object to “True”. If you don’t take this step you will not be able to enable write back.
• 2. In addition I added 2 lines of code to the Table View creation that add blank lines to the table view and format them Light Blue. These are the lines where a user can add additional data to the table view and subsequently save the data to the relational table.
• 3. I then add the following section of code to the SpreadsheetFunctionType.SaveTableView section of the Spread Sheet Business Rule. This section gets called when the Spreadsheet Component is saved. Notice that I have used the name of my Table View which is on the Spreadsheet component on the Dashboard.
• 4. I set up the variables that will hold the Tableview Columns and the values in those columns and rows.
• 5. Create an Application Database Connenction.
• 6. Now loop through each row in the Table View. Evaluate the Column name, loading the value of the column and row into the string Variable and the column into the column variable. The column variable is used to evaluate if the cell is dirty or not, a feature of the Table View. If the cell is dirty (has updated data) then using a sql statement, the values are written back to the custom Application Relational Table.

Note that there is no error handling logic in this example. However, during this step, you could evaluate the values in the table view using code and perform different actions based on the values. You could even break up the return values from the table view and write them back to different relational tables, even different ones than what you originally retrieved from. For one client I set it up to not only write back to a relational table but to also write data back to the cube. The only limitation here is your imagination.

Here is the final code in Full.