Spreadsheet

The OneStream Windows App Spreadsheet feature enables users to incorporate spreadsheet functionality experienced by users of the OneStream Excel Add-in without needing to have Excel loaded on their computer.  It can be used for ad hoc querying/reporting, analysis, data entry, and formatted reports. The Spreadsheet feature enables users to stay within the OneStream Windows App while being able to use functionality similar to what they know and love in Excel.  Similar to the Excel Add-in, the Spreadsheet feature leverages OneStream’s re-usable Cube Views for fast and easy analysis. See Navigating the Excel Add-In for more details on this feature.

• Here are some other functional highlights and benefits:

• Change point-of-view, interact with Forms, assign Cube Views, drill through to source data and update Workflow status

• Utilize familiar retrieve functions for ad-hoc reporting and analysis

• Eliminate risk of errors and duplication of efforts with standardized and centralized spreadsheet controls

• Safely edit, update and analyze data as spreadsheet forms respect Application and Workflow and security

• Eliminate spreadsheet maintenance when metadata changes because Cube Views are read dynamically

• Sheet-based calculations remain intact even when rows or columns are added

• Include multiple Cube View results in the same sheet

The OneStream Windows App Spreadsheet feature can perform most of the tasks accomplished by the OneStream Excel Add-in, with some limitations. These limitations include, but are not limited to:

• Macros

• Solver

• Document properties

• No option to insert copied/cut cells in spreadsheet

• Preview format does not update for format types Effects (Superscript, Subscript, Strikethrough)

• Ability to manage conditional formatting – color scales are reversed

• Shift+End

• Spreadsheet border button does not store last selection

• Text Direction option from Alignment formatting

• Underline formatting does not appear same as in Excel

• Not all Chart options are available in Spreadsheet

• Ability to change chart colors in spreadsheet

• References to other sheets in Spreadsheet is a different process

• Ctrl+PageUp / Ctrl+PageDn correctly changes the tab but does not reset tab focus in spreadsheet

• CTRL+N creates a new spreadsheet, however if an existing spreadsheet was opened with unsaved changes, it closes the existing spreadsheet without saving edits made since last save

Some functionality is currently not supported in the Spreadsheet Tool.  Functionality that is known not to be supported in the Spreadsheet feature at this time is listed below:

• Fill number formatting

• The ability to format part of a cell with different formatting

• Justify alignment option

• Managing Rules under spreadsheet conditional formatting

• Spreadsheet alignment cell format "Center Across Selection"

• Undo/Redo

The following Excel Column Charts are incompatible and unavailable with the Spreadsheet feature:

• Stacked 3-D

• 100% Stacked 3-D

• Stacked Cylinder

• 100% Stacked Cylinder

• Stacked Cone

• 100% Stacked Cone

• Stacked Pyramid

• 100% Stacked Pyramid

The following Excel Line chart is incompatible and unavailable with the Spreadsheet feature:

• 3-D Line

The following Excel Bar charts are incompatible and unavailable with the Spreadsheet feature:

• Stacked 3-D Bar

• 100% Stacked 3-D Bar

• Stacked Horizontal Cylinder

• 100% Stacked Horizontal Cylinder

• Stacked Horizontal Cone

• 100% Stacked Horizontal Cone

• Stacked Horizontal Pyramid

• 100% Stacked Horizontal Pyramid

Microsoft Excel 2016 Chart Types

There are seven new chart types added to the Spreadsheet component in the Windows application. These charts were first introduced to Microsoft Excel 2016, and are now available in our spreadsheet component as an alternative to using Excel.

The OneStream Windows application spreadsheet feature does not require that Excel be loaded on your computer. It can be used for querying and reporting, analysis, data entry, and formatted reports. The Spreadsheet feature allows you to remain in the Windows application using Excel-like functionality.

This is an enhancement to the existing spreadsheet component in the Windows application. It is available to administrators or general users with security rights to access it.

Chart Types

Microsoft Excel 2016 introduced several chart types that help you visualize financial, statistical, and hierarchical data. They include:

• Box and Whisker

• Waterfall

• Histogram

• Pareto

• Funnel

• Sunburst

• Treemap

You can add charts to a OneStream spreadsheet worksheet in the same manner as any other chart type. Chart styles allow you to quickly change chart appearance. Styles change the background fill, specify the color of the data series, and apply different shape effects and outlines to the chart.

To apply Excel 2016 chart types to a predefined chart style, right-click on the chart and select one of the available styles.

Box and Whisker

A box and whisker chart shows distribution of data in quartiles, highlighting the mean and outliers. Boxes may have lines extending vertically called “whiskers.” These lines indicate variability outside the upper and lower quartiles, and any point outside those lines or whiskers is considered an outlier.

Box and whisker charts are commonly used in statistical analysis. For example, you could use a box and whisker chart to compare medical trial results or teachers' test scores.

Waterfall

A waterfall chart shows a running total as values are added or subtracted. It is useful for understanding how an initial value (for example, net income) is affected by a series of positive and negative values. The columns are color coded so you can quickly tell positive from negative numbers. The initial and the final value columns often start on the horizontal axis, while the intermediate values are floating columns.

If your data includes values that are considered Subtotals or Totals, such as Net Income, you can set those values so they start on the horizontal axis at zero and don't float. To do this:

1. Right-click on the chart and select Set as Total.

2. In the dialog box, select the checkbox for value(s) in the dataset to be set as Total.

3. To make the column float again, clear the Set as total checkbox.

Histogram

Data plotted in a histogram chart shows the frequencies within a distribution. Each column of the chart is called a frequency bin.

Pareto

A pareto chart is a sorted histogram that contains columns sorted in descending order along with a line representing the cumulative total percentage.

Funnel

Funnel charts show values across multiple stages in a process. Typically, the values decrease gradually, causing the bars to resemble a funnel. For example, you could use a funnel chart to show the number of sales prospects at each stage in a sales pipeline.

Sunburst

The sunburst chart displays hierarchical data and can be plotted when empty (blank) cells exist within the hierarchical structure. Each level of the hierarchy is represented by one ring or circle with the innermost circle as the top of the hierarchy. A sunburst chart without hierarchical data (one level of categories), looks similar to a doughnut chart. However, a sunburst chart with multiple levels of categories shows how the outer rings relate to the inner rings. The sunburst chart is most effective at showing how one ring is broken into its contributing pieces.

Treemap

The treemap chart provides a hierarchical view of your data and an easy way to compare different levels of categorization. The treemap chart displays categories by color and proximity and can show data which might be difficult to display with other chart types. The treemap chart is plotted when empty (blank) cells exist within the hierarchical structure. Treemap charts are good for comparing proportions within the hierarchy.

Conclusion

These chart types help you visualize financial, statistical, and hierarchical data inside the Windows application spreadsheet component. They are ideal for those who prefer working inside the Windows application rather than Microsoft Excel.