Table Views if you are not familiar is the OneStream functionality which enables the querying and updating of relational data utilizing the Spreadsheet Page. In this blog which is Part 1 of 2, I will review the data querying functionality of Table Views, and then in Part 2 will extend Part 1 by discussing how to update the data queried in Part 1.
To begin the process, I will log into OneStream and select an application that has Task Manager “UTM” configured as this MarketPlace solution will be used to demonstrate how to query and update data. As with most of my blogs, I will utilize the reference application, GolfStream, to demonstrate which includes Task Manager. Once you have logged into the application, select “OnePlace“-> “Application“.
Expand “Tools” and then select “Business Rules“.
Select the “Create Business Rule” icon 
.
From the “Type” drop-down, select “Spreadsheet” which is the last item in the list. Key “UTM_TaskList” for the Business Rule Name and then, select “OK“.
A Spreadsheet Business Rule “BR” has been created which will I will add code to query the Task List table, XFW_UTM_TaskList, of UTM which will be returned/inserted into the Spreadsheet Page. Note, OneStream Software provides an excellent reference, Table Views User Guide.pdf, which provides more details and examples than I will provide on this blog; therefore, I will proceed through the addition of code with brief explanations and rely on the comments included with the code to supplement the brief explanations.
The first step in updating the BR will be to comment out Function Types: Unknown, GetCustomSubstVarsInUse, and SaveTableView which are Line 25, 27, and 31 as these Function Types will not be used in Part 1 of this two-part blog.
Next code is added that will return the result of Private Function “GetUTMTaskListExport” which is created next when the TableViewName equals “TaskListExport“.
Create the Private Function “GetUTMTaskListExport” as type, TableView.
Next, add a sequel statement to the variable, SQL_TaskListExport_L. Note, the data type “uniqueidentifier” which are the ID fields has been CAST to VARCHAR(36) which will simplify the data column and data row section of the code as all data will be a string data type.
With the query added, variables for a DataTable “DT_TaskListExport_L” and DbConnInfo “dbConnApp_L” will be added. The DataTable will store the result of the query in memory after being executed by “BRApi.Database.ExecuteSql” against the database specified using DbConnInfo which is the OneStream application database.
The next step in the code creation will be to declare a variable “TV_TaskListExport” as a TableView object and set “CanModifyData” to “False“.
The remaining steps in the code creation will be to create the TableViewRow “Line 97“, the DataColumn “Line 102″, and the DataRow “Line 114“. Once completed, the code added to Private Function “GetUTMTaskListExport” will query the XFW_UTM_TaskList table stored in the application database and return the result set stored in TableView, “TV_TaskListExport” to a tab using the Spreadsheet Page. If you have not already done so, save and then compile the BR to check the syntax. At this point, the BR is ready to test and can be closed or remain open.
To test the BR, expand “Tools” and then select “Spreadsheet” accessible from Application.
After a Spreadsheet is rendered, select “OneStream XF“.
Select “Table Views” from the OneStream XF ribbon.
After the Table View Definitions dialog box renders, select “Add“.
Update the Name dialog “#1” as “TaskListExport” which is the TableViewName created toward the being of this blog post, then either key or select using the ellipsis option the Table View Business Rule “#2” with the name of the Business Rule created which in the example is “UTM_TaskList“. Once this is done, select “OK“.
The records of the table, XFW_UTM_TaskList, will be displayed on the Spreadsheet Page. Select “Close” to close the Table View Definitions dialog box.
In addition, to returning the Task List to any tab of a Spreadsheet Page, the Task list can be returned to the Task List Import template. To begin, access UTM and then select “Show Task Administration Page“.
Select “Template” from Task Administration which will download and open the template with Excel.
Save the template “TaskImportTemplate_UTMT.xlsx” to your preferred location which will enable uploading the file to OneStream File Explorer. To accomplish the uploading, log into OneStream and then select “File Explorer“. Note, uploading the template to File Explorer is not required; however, it is included to demonstrate how an Excel file can be open from the Spreadsheet Page.
After the “File Explorer” dialog box opens, select the “File Upload” icon 
.
Select the template “#1” and then select “Open – #2” to upload the file.
After the file has been uploaded, select “Close“.
Return to or open the Spreadsheet Page. Once this is done, select “File – #1” and then “Open – #2″.
Select “Open File From XF File System“.
Navigate to the location of the template file. Select and then right-click the file choosing “Open in Spreadsheet Page” from the context menu. After the file is opened in the Spreadsheet Page, select “Close“.
After the template has opened, select the “Task List” tab.
As stated in cell “B3 – #1“, remove “delete” the examples in rows 12 and 13 “#2“.
With the examples removed, select cell “B12“. Repeat the process, to return the result that started with -> Select “Table Views” from the OneStream XF ribbon.
After completing the aforementioned steps, the results are returned to the template file which is displayed in the next two images. Note, this process updates the named range “TaskListExport” and can be imported into Task Manager if needed.
To save the file, select “File – #1“, “Save As – #2“, and then “Save As File In XF File System – #3“.
Select “OK” to save the file which concludes Part 1 of the blog. In Part 2 of the blog, I will demonstrate how to update queried from UTM.
Hope this blog was helpful, and thank you for your time in reading. Should you have any questions, feel free to either post a comment or email me at terry.ledet@perficient.com.
