OneStream

OneStream Specialty Planning Quick Tip

FR 2052a

In my previous blog, the focus was a Quick Tip for Thing Planning and this post will provide a Quick Tip for any Specialty Planning Solutions as the focus is the exporting and importing of register data.

Prior to providing instructions on how to customize the export and import process, I will demonstrate the default process which will occur with the GolfStream application and People Planning.

After logging into the application, set the Workflow POV to “Houston People Plan” -> “BudgetV1” -> “2011” which is accessible for “Cube Root Workflow Profile” GolfStream.

2021 02 01

Select “People Planning”.

2021 02 02

To export register data, select the “Export Register to a CSV File2021 02 03 00 icon.

2021 02 03

The previous selection exports all register fields to a comma-delimited file with header information.  Starting on line 9, the data from the register is displayed which can be modified and imported back into the register using the “Import Register Items from an Excel File” 2021 02 04 00 icon.  This process typically occurs if mass updates need to be made to register data.

2021 02 04

Prior to importing data, the register template should be downloaded.  To download the register, select the “Download Register (Single WF) XLSX Import Template2021 02 05 icon.  This selection will open the template which is displayed with the second image.

2021 02 06

2021 02 07

To import data using the template, delete the data starting with row fourteen to the last row of data.  Note, the default template includes sample data.

2021 02 08

Return to the CSV file and import the file into Excel.

2021 02 09

Delete columns C, D, and E which are not needed for the import.  Then cut column J and insert as column C which updates the current column C to column D and will align the columns in the Excel template with the exported data.

2021 02 10

Select the exported data and then paste the data into the register.  Once this is done, update cell B4 to “|WFProfile|” or key “Houston People Plan.People Planning” and then save the file.

2021 02 84

2021 02 83

Return to the register and select the “Open Delete Register Plan Dialog2021 02 12 icon.

2021 02 13

Select the “Entire Register” option and then select “Execute Delete”.

2021 02 14

Select “OK” when the dialog box renders stating the data in the register has been deleted.

2021 02 15

Select “Close”.

2021 02 16

After the execution of the delete process has completed, the register will display without data.

2021 02 17

Select the “Import Register Items from an Excel File2021 02 18  icon to begin the process of importing the Excel template.  Once this is done, select “Open”.  Note, the Excel file should be closed prior to selecting “Open”.

2021 02 19

Navigate to the location of the Excel template and select the file.  With the file, selected, click “Open” to import the contents of the Excel template.

2021 02 20

2021 02 21

Select “OK” when the Extensible Finance dialog box renders, and the register will display the data which was imported.

2021 02 22

2021 02 23

With the demonstration of the default process complete, I will transition to the Quick Tip which will consist of the following:  modifying the Excel template to only display the columns which are displayed in the register as well as deleting the sample data, create an export which will also have the columns displayed, and for both the Excel template and export have the columns in the same order as the register display which simplifies the copying of data from the export to the Excel template.  The customization process will begin with the Excel template; therefore, select “Application”.  Prior to beginning the customization in this Quick Tip, a brief reminder that the customization of a MarketPlace Solution WILL impact a future upgrade of the solution and should occur when there is a business benefit which in this circumstance is a simplified export and import process.

2021 02 24

With Application specified, select / expand “Presentations” -> “Dashboards”.

2021 02 25

Navigate to Dashboard Maintenance Unit “XFW People Planning (PLP)” and select / expand “Files”.  Once this is done, select “ExcelEmployeeRegisterSingleWF_PLP.xlsx”.

2021 02 26

Select the “Download File2021 02 27 icon.

2021 02 28

When the Save As dialog box displays, “Save” the file to the preferred location.  Once this is done, open the file with Excel.

2021 02 29

With the downloaded file opened in Excel, delete the sample data and then update cell B4 to “|WFProfile|.

2021 02 30

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

Next order the columns to have the same order as the register which can be done by selecting the entire column to move, cutting the column “Cut” and then inserting cut cells “Insert Cut Cells” in the appropriate order.  Once this is done, hide the columns which are not displayed in the register.

2021 02 31

With the columns reordered, row eight can be updated to reflect the Alias instead of the Register Field Name.  For PLP make the following changes:  OutCode to Salary Basis, InPeriod to In Per, Entity to Corp Entity, Code3 to Region, and Code1 to Dept.  Once this is done, save and close the file.

2021 02 85

With the template updated, return to the File component.  Select the “Upload File2021 02 33 icon.

2021 02 34

Navigate to and select the updated Excel template.  Once this is done, select “Open”.

2021 02 35

2021 02 36

After the file uploads, select the “Save” icon.

2021 02 37

With the Excel template updated, the next step will be to create a custom export.  To begin the process, return to the PLP Dashboard Maintenance Unit.  Select “Data Adapter” and then select the “Create Data Adapter2021 02 38 icon.

2021 02 39

Key a name for the Data Adapter which in this example is “da_PLP_Register_Export”.

2021 02 40

Update the Command Type to “SQL” and the Database Location to “Application”.

2021 02 41

Select the “Edit2021 02 42 icon to develop the query for the export.

2021 02 43

Create the SQL statement based on the Excel template adding variables for the Workflow Profile which are utilized in the WHERE statement.  Once this is done, select “OK”.

2021 02 44

With the Data Adapter created, navigate to and select “Report” in the same Dashboard Maintenance Unit.  Once this is done, select the “Create Dashboard Component2021 02 45 icon.

2021 02 46

When the Create Dashboard Component dialog box renders, Select “OK” after confirming “Report” is selected.

2021 02 47

For the Name, key “rpt_PLP_Register_Export” and for Description key “Export Register”.  Once this is done, select “Data Adapters”.  Note, the description is displayed when the report is added to a dashboard.

2021 02 49

Add the Data Adapter “da_PLP_Register_Export” previously created by selecting the “Add Dashboard Component” 2021 02 50 icon.  Once this is done, select the adapter from the list and then select “OK”.

2021 02 51

2021 02 52

With the Component Property and Data Adapter updated, select “Save”.

2021 02 53

After the save has completed, select “Report Designer”.

2021 02 54

After the Report Designer renders, select the “Page Settings2021 02 55 icon.

2021 02 56

Update the Paper Kind from “Letter” to “Tabloid” and select the “Landscape” check box.  Note, the report is intended to export not print; therefore, the maximum Paper Kind from a Width and Height perspective is selected.

2021 02 57

2021 02 58

Next, the fields in order of the Display and Excel Template will be added to the “Detailed Band”  This can occur by selecting a field from the Field List and then dragging and dropping it on the grid.  Once the fields are added, select the “Save2021 02 59 icon.

2021 02 60

With the fields added, select all the fields.  Once this is done, select the “Behavior” icon from Properties.  Unselect “Can Grow”.  This is done to prevent the data from wrapping to multiple rows in Excel.

2021 02 61

 

Next Labels for each Field will be added.  Select the “Label2021 02 62 icon, drag it to the PageHeaderBand, and place it on top of the bottom band.  Repeat this process for each field.

2021 02 63

With the labels added, update the text to equal the Alias of the Field Name.  The text can be changed by double-clicking the label and then typing the appropriate text.  Repeat the process for every label added.  Also, unselect the “Can Grow” property using the same method as was done for the detail band.

2021 02 64

2021 02 65

Remove the white space displayed in the DetailBand and PageHeaderBand which is done by selecting and dragging the DetailBand which is the line at the bottom of the band to the bottom of the Detail Band fields.   Repeat this process for the PageHeaderBand first by moving the labels to the top of the band and repeat the process used for the DetailBand.  Once this is done, select the “Save” icon.

2021 02 66

2021 02 67

Delete bands,  “ReportheaderBand” and “PageFooterBand”.  This is done by selecting the band, right-clicking, and selecting “Delete”.

2021 02 68

Minimize the size of “TopMargin1″ and “BottomMargin1″ which is done in the same manner that the “DetailBand” and “PageHeaderBand” were resized.  With the report complete, select the “Save” icon.

2021 02 69

2021 02 70

With the component to export the register created, the final step will be to add the Report, rpt_PLP_Register_Export, to the People Planning tab.  To begin this process, navigate and select dashboard, 3b2b_PeoplePlan_PLP.

2021 02 71

2021 02 72

With the Dashboard selected, click the “Dashboard Components” tab and then select the “Add Dashboard Component2021 02 73  icon.

2021 02 74

Navigate to add select the report, rpt_PLP_Register_Export, created to export register data.  Once this is done, select “OK”.

2021 02 75

With the report added, select the “Save” icon.

2021 02 76

Return to OnePlace and refresh People Planning if needed.  Once this is done, “Export Register” will be the fourth tab displayed which should be selected.

2021 02 77

The data in the register based on the current Workflow Profile will be displayed and by selecting the “Export2021 02 79 icon data can be exported to Excel.  After the “Export” icon is selected, click the “XLSX file” option from the “Export” dropdown.

2021 02 88

2021 02 87

Select “OK” when the dialog box renders.  Depending on your computer configuration, the file may open in Excel.  If the file does not open automatically, open it with Excel.

2021 02 81

The register data will be displayed and is in the same order as the register display and Excel template.

2021 02 89

At this point, the data without adjusting column order can be copied and pasted to the Excel template and then imported which concludes the Quick Tip.

2021 02 90

Thank you for reading the blog.  Should you have any questions, feel free to either post a comment or email me at terry.ledet@perficient.com.

About the Author

Terry is a Senior Solutions Architect at Perficient specializing in OneStream CPM solutions. 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 enthusiasts.

More from this Author

Leave a Reply

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

Subscribe to the Weekly Blog Digest:

Sign Up
Categories