OneStream

OneStream – Table Views Part 1 of 2

African Computer Programmer Writing Code

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 ManagerUTM” 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“.

Blog 2021 10 26 20 05 00 Clipboard

Expand “Tools” and then select “Business Rules“.

Blog 2021 11 17 18 48 57 Onestream Xf

Select the “Create Business Rule” icon Blog 2021 11 17 19 09 32 Onestream Xf.

Blog 2021 11 17 19 08 55 Onestream Xf

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

Blog 2021 11 17 19 11 28 System.windows.controls.grid

Blog 2021 11 17 19 14 25 System.windows.controls.grid

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.

Blog 2021 11 17 19 18 00 Onestream Xf

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.

Blog 2021 11 17 19 28 34 Onestream Xf

Next code is added that will return the result of Private Function “GetUTMTaskListExport” which is created next when the TableViewName equals “TaskListExport“.

Blog 2021 11 17 20 34 24 Onestream Xf

Create the Private Function “GetUTMTaskListExport” as type, TableView.

Blog 2021 11 17 19 41 54 Onestream Xf

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.

Blog 2021 11 21 16 10 45 Onestream Xf

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.

Blog 2021 11 21 16 28 40 Onestream Xf

The next step in the code creation will be to declare a variable “TV_TaskListExport” as a TableView object and set “CanModifyData” to “False“.

Blog 2021 11 21 16 31 25 Onestream Xf

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.

Blog 2021 11 21 16 36 07 Onestream Xf

Blog 2021 11 21 16 41 02 Onestream Xf

To test the BR, expand “Tools” and then select “Spreadsheet” accessible from Application.

Onestream - Modern Accounting: How to Overcome Financial Close Challenges
Modern Accounting: How to Overcome Financial Close Challenges

Improvements in each of the following period-close core tasks can provide transformative change and are reviewed in this guide include closing the books and external reporting, periodic reconciliations, and managing the period-close process.

Get the Guide

Blog 2021 11 17 18 48 57 Onestream Xf Copy

After a Spreadsheet is rendered, select “OneStream XF“.

Blog 2021 11 21 16 57 12 Clipboard

Select “Table Views” from the OneStream XF ribbon.

Blog 2021 11 21 16 58 38 Clipboard

After the Table View Definitions dialog box renders, select “Add“.

Blog 2021 11 21 16 59 05 Onestream Xf

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

Blog 2021 11 21 17 03 35 System.windows.controls.grid

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.

Blog 2021 11 21 17 11 39 Onestream Xf

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

Blog 2021 11 22 18 40 17 Clipboard

Select “Template” from Task Administration which will download and open the template with Excel.

Blog 2021 11 24 11 26 53 Clipboard

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.

Blog 2021 11 24 11 38 49 Clipboard

After the “File Explorer” dialog box opens, select the “File Upload” icon Blog 2021 11 24 11 41 27 Clipboard.

Blog 2021 11 24 11 40 44 Clipboard

Select the template “#1” and then select “Open – #2” to upload the file.

Blog 2021 11 24 11 43 42 Clipboard

After the file has been uploaded, select “Close“.

Blog 2021 11 24 11 48 24 Clipboard

Return to or open the Spreadsheet Page.  Once this is done, select “File – #1” and then “Open – #2″.

Blog 2021 11 24 11 53 53 Blog 2021 11 21 16 55 18 Onestream Xf

Select “Open File From XF File System“.

Blog 2021 11 24 11 57 34 Clipboard

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

Blog 2021 11 24 12 01 44 Clipboard

Blog 2021 11 24 12 04 47 Clipboard

After the template has opened, select the “Task List” tab.

Blog 2021 11 24 12 10 25 Clipboard

As stated in cell “B3 – #1“, remove “delete” the examples in rows 12 and 13 “#2“.

Blog 2021 11 24 12 12 26 Clipboard

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.

Blog 2021 11 24 12 20 52 Clipboard

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.

Blog 2021 11 24 12 33 30 Clipboard

Blog 2021 11 24 12 43 37 Clipboard

To save the file, select “File – #1“, “Save As – #2“, and then “Save As File In XF File System – #3“.

Blog 2021 11 24 12 51 30 Clipboard

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.

Blog 2021 11 24 12 58 59 Clipboard

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Terry Ledet, Senior Solutions Architect

Terry is a Senior Solutions Architect at Perficient and a OneStream Certified Professional (OCP) - Lead Architect R1. He is a frequent speaker at Splash and passionate about making clients successful. When not blogging about OneStream, Terry is a husband, parent, and baseball enthusiast.

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram