XLCubed Options/Version 7

XLCubed Options

These are the XLCubed installation level defaults which are applied to any new workbooks opened during the Excel session.

Main

Options1.png
  • Format File – the default FormatSheet location.
  • Default at level view in member tree – Sets the default view of hierarchies to be level based, rather than member based
  • Sort hierarchies alphabetically – rather than the cube defined order
  • Show dimensions in folders – Display folders shown at a level above the dimension rather than beneath the dimension
  • Limit number of members returned in tree to – limits to specified value in user interface
  • Show right-click menu option at top (restart Excel to apply) – The XLCubed right click menu option is by default at the bottom.
  • Use single members on filters – Implements ‘select and replace’ on hierarchies in the filter area of grids (selecting a member replaces any existing selected member)
  • Automatically select Children of first member
  • Display Dimension Name with Hierarchy Name – prefixes the hierarchy name with the dimension name
  • Auto apply changes in Task Pane – Grid task pane defaults to ‘Auto Apply’, i.e. changes are made instantaneously
  • Unicode Compatibility (Excel 2003) – ** Note should only be set in Excel 2003 / 2002 where Unicode member names exist. This is required for Unicode support in Excel versions pre 2007.

Publication

Options2.png
  • Default Web Address – the default web address for publishing
  • Maximum number of columns allowed – The maximum number of columns allowed per page
  • Maximum number of rows allowed – The maximum number of rows allowed per page
  • Log Publication Process – in the event of a problem, logs additional detail
  • Check Web Compatibility – checks the workbook pre-publication for any known compatibility issues.

Workshop Defaults

Options3.png
  • Replace null values – Replaces null values with the contents of the field Replace null values with:
  • Remove nulls when drilling – If there are any null rows/columns when drilling down on a member, they will be removed so that there are no fully empty rows/columns in the report.
  • Remove 0 values when drilling - If there are any rows/columns when drilling down on a member that only contain zeros, they will be removed so that there are no zero rows/columns in the report.
  • Resize Columns after drilling – This allows a column to resize itself when drilled
  • Indent drilled members – When the user drills down on a member, they will be displayed indented

Workbook Options

Workbook

Options4.png
  • Maximum calculation depth during grid refresh – Please do not change without checking first with XLCubed Support.
  • Refresh Format Sheet – This removes any customised formatting from workbook by replacing the existing FormatSheet sheet with the default sheet as defined in Options -> XLCubed Options -> Main.

Web Publication

Specifies the behaviour and options available in the report once published to XLCubed Web.

Options5.png
  • Display Submit Changes – Controls display of Submit Changes toolbar button on XLCubed Web.
  • Allow Printing – Controls display of the Print toolbar button on XLCubed Web.
  • Allow save workbook to Excel – Controls display of save to Excel on XLCubed Web.
  • Allow save active sheet to Excel – Controls display of save to Excel on XLCubed Web.
  • Allow save to repository – Controls display of save to Repository on XLCubed Web.
  • Display Refresh button – Controls display of Refresh button on XLCubed Web.
  • Enable Right Click Menus – Controls availability of right click menus on XLCubed Web
  • Save Custom View – Controls availability of save custom view on XLCubed Web
  • Drill Formulae – Whether formulae can be drilled on XLCubed Web
  • Edit Member Formulae – Whether XL3Member formulae can be edited on XLCubed Web
  • Auto Reload – Whether the report auto-reloads if IIS has timed out due to inactivity
  • Edit XL3LookupRW without unlocking – Enable LookupRW formulae for editing
  • Open Links in same Window – Whether any XL3Link formulae to other websites or reports are opened in the same or different windows

Formula Options

Options6.png
  • Replace null values – Replaces null values with the contents of the field Replace null values with:
  • Remove nulls when drilling – If there are any blank rows/columns when drilling down on a member, they will be removed so that there are no empty rows/columns in the report.
  • Remove 0 values when drilling - If there are any rows/columns when drilling down on a member that only contain zeros, they will be removed so that there are no zero rows/columns in the report.
  • Resize Columns after drilling – This allows a cell to resize itself when a formula is inserted to display the result correctly.
  • Indent drilled members – When the user drills down on a member, they will be displayed indented.

Writeback

Formula Writeback

Options7.png
  • Enable Writeback – Enables Writeback, whether or not this happens on or offline is controlled by the individual Grid Properties
  • Allow at Lowest Level Only – data is entered at leaf level only.
  • Allow at Any Level – data is entry at any level is allowed , select one of the following Analysis Services Writeback spreading methods. Note this can be overridden at Grid Properties level
    • Equal allocation – Each constituent cell is assigned an equal value.
    • Equal increment – Every constituent cell will be changed according to an incremental value
    • Weighted allocation - Each constituent cell will be assigned an equal value that is weighted against a formula
    • Weighted increment - Every constituent cell is changed incrementally according to a weighting formula
  • Lowest Level Indicators
    • Highlight members – This highlights the members that are available for Writeback.
    • Highlight data – This highlights the data that is available for Writeback.

Relational Writeback

Analysis Services is the default writeback method. However, when working with XLCubed or XLCubed Authorised partners on a consultancy basis it is also possible to use ‘Relational’ Writeback.

Options8.png

When using relational writeback, the server and database being used must be specified in the dialog above.

Connections

Connection details can optionally be specified within defined Excel cells in the workbook. These can also be parameterised for web deployment.

In Excel, any changes made to the connection via the defined cells will only take effect after closing and re-opening the workbook. When published, if they have been set as web parameters, the connection detail can be passed into the report as part of the calling URL. This enables straightforward switching of published reports between development / acceptance and production databases in environments where the reporting is hosted within other portals such as SharePoint.

To access the parameters in the published report, click on the view parameters icon from Recent reports, as shown below.

Options9.png

Connection details can optionally be specified within defined Excel cells in the workbook. These can also be parameterised for web deployment.

Options10.png

This is accessed via Options – Workbook Options – Connections. Specify the values for each parameter, and where required change the server, database, and cube, before pressing Get URL.

Options11.png

On accessing the report through the URL, the connection will switch to the server and database specified.