SQL Editor enhancements in OneStream 5.2 2

For all of you who are familiar with SQL Table Editor, I don’t need to explain it to you.

For those of you who are not familiar with SQL table editor and those who have not yet used this component, SQL Table editor is a component type available in OneStream’s dashboarding system. It can be used to edit (add/delete/update) an SQL table. Most of them (all of them?) specialty solutions make use of this component.

OneStream 5.2 adds a couple of new features to SQL editor.

As you can see, you can now perform multiple selections in the editor. Another option that got added is to save the state of the editor. I’m going to cover both features in detail.

Allow Multi-Select

When you allow multi-select on a SQL Table editor component, you’ll now notice that an extra column with checkboxes gets added.

With this option, you can add some interesting dashboarding options to the solution you are delivering.

In this example, I’m going to choose some employees from the People Planning solution and going to perform some analysis on them.

Now, keep in mind that the bound column (the column which you can choose to pass values from) assignment is still limited to one. The use cases where you got more than one primary key requires you to come up with ways to show all the rows related to the selection.

As you can see from the picture, I did add a custom button to the end of the register toolbar (the last icon which looks like a candle chart) for analyzing the selected employees.

When I click the” Analyze Plan” button, a new dashboard with calculated data gets presented to the user. This approach assumes that you’ve already performed a calculation.

As you can see, it adds more analysis capabilities to the user.

You might be wondering about the new component that I’ve used on the dashboard. The dashboard above is built using the new BI Viewer component, and the grid-like component is a” Pivot grid.”

In 5.2, you’ve got two types of pivot grids.

• Pivot grid
• Large pivot grid

The pivot grid that is present in the BI Viewer is the regular pivot grid. I’ll try to cover pivot grids in a different post.

The quick tip is you can export the pivot grid with the grouping. As you can see, the export to EXCEL did keep my total columns and the expansions.

Save state

Using the “Save State” option, you can let the user reorder/hide the columns according to their preference.

In my opinion, it’s a nice feature to have for rearranging the columns. However, I’m not a fan of the hide columns option. When you hide columns, you, as an admin, must make sure that the column has a default value, or else the save operation may result in an error.

In the screenshot above, I did hide the Grade level and moved Status from column 3 to the end, and below is the result.

You can still reorder columns without saving the state. The only difference is you need to repeat the same operation for the next session.

Not for the faint-hearted 🙂

If you’ve been following my blogs (this one and the one before), you’ll notice that I have got a habit of trying to find out how things work.

This is an explanation of how saving the state is achieved in OneStream. I first thought it might be getting added to the XFUserState table. However, that was not the case.

If you’ve not looked at the following folders, you should look at them.

• %APPDATA%\OneStream\Desktop\Settings – Click once client
• %APPDATA%\OneStream Software\Desktop\Settings – Thick client

The folder above (depending on the one you are using) holds many settings. To name a few

• Values of Custom Sub Vars
• POV Pane Pinned or not value
• OnePlace selected Pane (whether you were on Workflow/Cube Views/Dashboard)

In that folder, now you’ll see a new setting. This setting has StateInfo, User name, ApplicationName, and Dashboard ID in the filename.

If you look at the Embedded Dashboards ID path, you’ll find that the order of IDs follows the path.

Little more visuals. I ran a SQL to get the names and description of all embedded dashboards and arranged them in EXCEL

All IDs arranged in the order.

Now let’s look at whether they match the tree.

Yup, it does. Now, you’ll see that all the visible columns are having a ControlState node and a number to mark their position in the SQL Editor component. What happens if I delete a node? 😉

I went ahead and deleted the Status column, which was the last one and Inperiod column (something in the middle) at position 9. I did this to test whether the Visibility<n> needs to be rearranged.

I closed Click Once Desktop client and relaunched it and logged back in.

Tadaaa, I don’t see the Status column and In Per column anymore.

When the client gets launched, it reads the StateInfo settings, matches the user, application, and the dashboard ID. Once it finds the appropriate settings file, it then reads the contents and figures out which columns are visible and what is their order.