Difference between revisions of "Workbook Options/Version 9.1"

(Workbook)
 
(15 intermediate revisions by 2 users not shown)
Line 4: Line 4:
 
===Workbook===
 
===Workbook===
  
[[File:WorkbookOptions1.png|centre|400px]]
+
[[File:WBOpWB91.png|centre|600px]]
  
 
*'''Design Mode:''' allows you to change the grid without running the query behind it
 
*'''Design Mode:''' allows you to change the grid without running the query behind it
*'''Overtyping a grid creates an Alias:''' overtyping a member name creates an alias for it
+
*'''Overtyping a grid creates an Alias:''' overtyping a member name creates an [[Aliases|alias]] for it
 
*'''Available Actions - Ignore multiple filter members:''' with this setting enabled, then the available actions uses only the first member for each filter hierarchy (if more than one member is select). This can speed up the display of the action form if lots of members are selected
 
*'''Available Actions - Ignore multiple filter members:''' with this setting enabled, then the available actions uses only the first member for each filter hierarchy (if more than one member is select). This can speed up the display of the action form if lots of members are selected
 
*'''Get unique names from formula ranges:''' Grids, Slicers and other XLCubed objects will attempt to get the unique name from any formulae in ranges they are driven from (from [[Version 8.1]])
 
*'''Get unique names from formula ranges:''' Grids, Slicers and other XLCubed objects will attempt to get the unique name from any formulae in ranges they are driven from (from [[Version 8.1]])
Line 14: Line 14:
 
*'''Worksheet protection password:''' setting the password value used to "protect" the worksheets will allow XLCubed to unprotect the sheets during grid refreshes, the sheets are protected again after the refresh is completed. (This feature is new in [[Version 7.1]])
 
*'''Worksheet protection password:''' setting the password value used to "protect" the worksheets will allow XLCubed to unprotect the sheets during grid refreshes, the sheets are protected again after the refresh is completed. (This feature is new in [[Version 7.1]])
 
*'''Limit comments by key:''' limits the [[Cube Comments]] made and retrieved in the workbook to the specified key (from [[Version 8.1]])
 
*'''Limit comments by key:''' limits the [[Cube Comments]] made and retrieved in the workbook to the specified key (from [[Version 8.1]])
 +
*'''Comments Web Server:''' location of web server for commentary
 
*'''Reset Format Sheet:''' this removes any customised formatting from workbook by replacing the existing FormatSheet sheet with the default sheet as defined in {{Menu|Options|XLCubed Options|Main}}
 
*'''Reset Format Sheet:''' this removes any customised formatting from workbook by replacing the existing FormatSheet sheet with the default sheet as defined in {{Menu|Options|XLCubed Options|Main}}
  
 
===Formula Options===
 
===Formula Options===
[[image:WorkbookOptions2.png|centre|400px]]
+
[[image:WBOpForms91.png|centre|600px]]
 
*'''Replace null values:''' replaces null values with the contents of the field Replace null values with:
 
*'''Replace null values:''' replaces null values with the contents of the field Replace null values with:
*'''Remove null values 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.
+
*''New in [[Version 9]]'' - '''Validate members''' when parsing a member, the formula will check that a valid member has been selected
 +
*'''Remove drill indicators:''' do not display indicator that row/column can be drilled
 +
*'''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.
 
*'''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.
 
*'''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.
 
*'''Indent drilled members:''' when the user drills down on a member, they will be displayed indented.
 
*''New in [[Version 7.6]]'' - '''Lighten background colour on drill''' when the user drills on a member a slightly lighter version of its background colour will be used for the new members
 
*''New in [[Version 7.6]]'' - '''Lighten background colour on drill''' when the user drills on a member a slightly lighter version of its background colour will be used for the new members
*''New in [[Version 9]]'' - '''Validate members''' when parsing a member, the formula will check that a valid member has been selected
 
  
 
===Web Publication===
 
===Web Publication===
 
Specifies the behaviour and options available in the report once published to XLCubed Web.
 
Specifies the behaviour and options available in the report once published to XLCubed Web.
[[image:WorkbookOptions6.png|centre|400px]]
+
[[image:WBOpWP91.png|centre|600px]]
*'''Auto Refresh (minutes):''' sets an interval after which the published report will automatically refresh in the user's browser
 
*'''XLCubedWeb download filename:''' define the filename for the download to Excel/PDF, can also be defined in an Excel range
 
 
*'''Display Submit Changes:''' controls display of Submit Changes toolbar button on XLCubed Web - '''default is Yes'''
 
*'''Display Submit Changes:''' controls display of Submit Changes toolbar button on XLCubed Web - '''default is Yes'''
 
*'''Allow Printing:''' controls display of the Print toolbar button on XLCubed Web  - '''default is Yes'''
 
*'''Allow Printing:''' controls display of the Print toolbar button on XLCubed Web  - '''default is Yes'''
Line 41: Line 41:
 
*'''Drill Formulae:'''  whether formulae can be drilled on XLCubed Web  - '''default is No'''
 
*'''Drill Formulae:'''  whether formulae can be drilled on XLCubed Web  - '''default is No'''
 
*'''Edit Member Formulae:''' whether XL3Member formulae can be edited on XLCubed Web  - '''default is Yes'''
 
*'''Edit Member Formulae:''' whether XL3Member formulae can be edited on XLCubed Web  - '''default is Yes'''
*'''Auto Reload:'''  whether the report auto-reloads if IIS has timed out due to inactivity  - '''default is No'''
+
*'''Auto Refresh (minutes):''' sets an interval after which the published report will automatically refresh in the user's browser
 +
*'''XLCubedWeb download filename:''' define the filename for the download to Excel/PDF, can also be defined in an Excel range*'''Auto Reload:'''  whether the report auto-reloads if IIS has timed out due to inactivity  - '''default is No'''
 +
*'''Prefer aliased images:'''  avoid anti-aliasing images when publishing
 +
*'''Auto-hide workbook slicer pane if no slicers available:''' auto hide the workbook slicer pane if no slicers are available on current worksheet
 +
*'''Use faster PDF generation:''' PDF generation will be faster but some Excel features may not be supported
 +
*'''Auto Reload:''' whether the report auto-reloads if IIS has timed out due to inactivity - default is No
 
*'''Edit XL3LookupRW without unlocking:''' enable LookupRW formulae for editing  - '''default is No'''
 
*'''Edit XL3LookupRW without unlocking:''' enable LookupRW formulae for editing  - '''default is No'''
 
*'''Open Links in same Window:''' whether any XL3Link formulae to other websites or reports are opened in the same or different windows  - '''default is Yes'''
 
*'''Open Links in same Window:''' whether any XL3Link formulae to other websites or reports are opened in the same or different windows  - '''default is Yes'''
Line 52: Line 57:
 
*'''Open WebReportLinks in same Div:''' New in [[Version 8.1]], enables this for reports embedded in custom applications - '''default is No'''
 
*'''Open WebReportLinks in same Div:''' New in [[Version 8.1]], enables this for reports embedded in custom applications - '''default is No'''
 
*'''Convert shapes to images:''' New in [[Version 9]],  Excel Shapes (e.g. Callouts, Lines, etc.) are converted to images so that they can appear in the workbook when published. - '''default is No'''
 
*'''Convert shapes to images:''' New in [[Version 9]],  Excel Shapes (e.g. Callouts, Lines, etc.) are converted to images so that they can appear in the workbook when published. - '''default is No'''
 +
*'''Use Refresh on Driving Cell Change:''' controls whether a published report can ignore that grid property setting
 +
*'''Write a space in blank cells:''' writes a space in blank cells when report is published
  
 
===Writeback===
 
===Writeback===
Line 57: Line 64:
 
====Formula Writeback====
 
====Formula Writeback====
  
[[image:WorkbookOptions4.png|centre|400px]]
+
[[image:WBOpWrite91.png|centre|600px]]
 
*'''Enable Writeback:''' enables Writeback, whether or not this happens on or offline is controlled by the individual Grid Properties   
 
*'''Enable Writeback:''' enables Writeback, whether or not this happens on or offline is controlled by the individual Grid Properties   
 +
*'''Write to DataMembers in parent-child hierarchies:'''
 
*'''Allow at Lowest Level Only:''' data is entered at leaf level only.
 
*'''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  
 
*'''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  
Line 104: Line 112:
 
New in [[Version 7.5]] - you can define on this tab the report name and description to be displayed in [[:Category:Web_Publishing|XLCubedWeb]] for different language users.
 
New in [[Version 7.5]] - you can define on this tab the report name and description to be displayed in [[:Category:Web_Publishing|XLCubedWeb]] for different language users.
  
[[image:optionslang.png|centre|400px]]
+
[[image:WBOpLang91.png|centre|600px]]
  
 
===Multi-sheet Web Printing===
 
===Multi-sheet Web Printing===
  
With v8, web reports can be configured to enable multi-selection printing or save to Excel. For example in the report below, slicers let the user choose the Month and the Point of Sale. A web based user can now generate a single PDF or static Excel workbook containing one page per slicer combination they specify.
+
*'''Allow printing Multi-Sheet:''' enabling this option will cause the multi-sheet option to appear after clicking the print icon in the web.  
 
+
*'''Allow Save to Excel Multi-Sheet:''' enabling this option will cause the multi-sheet option to appear after clicking the save to excel icon in the web.  
[[image:msp2.png|500px|centre]]
+
*'''Custom Multi-Sheet caption:''' define the caption that appears in the print dialog for multi-sheet printing. If no caption is set, the dialog will display "Multi-Sheet".
 
+
*'''Worksheets to propogate:''' select the worksheets that will be generated in the pdf/excel workbook.
This is enabled within the workbook options:
+
*'''Slicers to use for Multi-Sheet selections:''' select which slicers will be available to make selections from upon printing.
 
 
 
 
[[image:msp3.png|350px|centre]]
 
 
 
 
 
Firstly you can enable multi sheet printing (allows creation of a multi-sheet pdf) and/or save to Excel multi sheet which saves a static version of the workbook.
 
 
 
You also need to specify the worksheet(s) to be propagated and the slicers which can be used.
 
 
 
The custom multi sheet caption enables a custom title for the print dialog which will be shown in the published Web report. If this is not set, the additional print option is called 'multi-sheet print' (see below).
 
 
 
  
[[image:msp4.png|350px|centre]]
 
  
 +
See [[Multi-sheet Web Printing]] for more information.
  
With the appropriate settings applied the workbook is then published as normal. When running in Web if the user chooses the option to Print they will be presented with the additional multi-select option:
+
===PowerPoint settings===
The user can then select slicer choices they wish to use:
 
  
 +
New  in v9.1 (Enterprise) you can now setup a workbook to support exporting to PowerPoint.  This needs to be setup here:
  
 +
[[image:WBOpPP91.png|600px|centre]]
  
[[image:msp5.png|350px|centre]]
+
*'''Allow save to PowerPoint slideshow:''' enable save/export to PowerPoint
 +
*'''Slides:''' add appropriate workbook range for each slide that will be created
 +
*'''Use slide title:'''  specify static text or cell range to use as slide's title
 +
*'''Resize content to fit space:''' enable to fit slide space available and optionally retain aspect ratio
  
  
A PDF file will be created with one page for each slicer selection. Save to Excel creates a workbook in the same way.
+
See [[PowerPoint export]] for more information.

Latest revision as of 16:37, 5 July 2018

Workbook Options

These are the workbook level options that can be set for each workbook.

Workbook

WBOpWB91.png
  • Design Mode: allows you to change the grid without running the query behind it
  • Overtyping a grid creates an Alias: overtyping a member name creates an alias for it
  • Available Actions - Ignore multiple filter members: with this setting enabled, then the available actions uses only the first member for each filter hierarchy (if more than one member is select). This can speed up the display of the action form if lots of members are selected
  • Get unique names from formula ranges: Grids, Slicers and other XLCubed objects will attempt to get the unique name from any formulae in ranges they are driven from (from Version 8.1)
  • Slicers output numbers: when output to a cell, the output will be values rather than text
  • Maximum calculation depth during grid refresh: if a workbook has "Circular" dependencies in grids, then this setting controls the maximum number of times to recalculate the same grid
  • Worksheet protection password: setting the password value used to "protect" the worksheets will allow XLCubed to unprotect the sheets during grid refreshes, the sheets are protected again after the refresh is completed. (This feature is new in Version 7.1)
  • Limit comments by key: limits the Cube Comments made and retrieved in the workbook to the specified key (from Version 8.1)
  • Comments Web Server: location of web server for commentary
  • Reset 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

Formula Options

WBOpForms91.png
  • Replace null values: replaces null values with the contents of the field Replace null values with:
  • New in Version 9 - Validate members when parsing a member, the formula will check that a valid member has been selected
  • Remove drill indicators: do not display indicator that row/column can be drilled
  • 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.
  • New in Version 7.6 - Lighten background colour on drill when the user drills on a member a slightly lighter version of its background colour will be used for the new members

Web Publication

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

WBOpWP91.png
  • Display Submit Changes: controls display of Submit Changes toolbar button on XLCubed Web - default is Yes
  • Allow Printing: controls display of the Print toolbar button on XLCubed Web - default is Yes
  • Allow save to Excel: controls display of save to Excel on XLCubed Web - default is Yes
  • Only 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 - default is Yes
  • Display Refresh button: controls display of Refresh button on XLCubed Web - default is Yes
  • Enable Right-Click Menus: controls availability of right click menus on XLCubed Web - default is Yes
  • Save Custom View: controls availability of save custom view on XLCubed Web
  • Drill Formulae: whether formulae can be drilled on XLCubed Web - default is No
  • Edit Member Formulae: whether XL3Member formulae can be edited on XLCubed Web - default is Yes
  • Auto Refresh (minutes): sets an interval after which the published report will automatically refresh in the user's browser
  • XLCubedWeb download filename: define the filename for the download to Excel/PDF, can also be defined in an Excel range*Auto Reload: whether the report auto-reloads if IIS has timed out due to inactivity - default is No
  • Prefer aliased images: avoid anti-aliasing images when publishing
  • Auto-hide workbook slicer pane if no slicers available: auto hide the workbook slicer pane if no slicers are available on current worksheet
  • Use faster PDF generation: PDF generation will be faster but some Excel features may not be supported
  • Auto Reload: whether the report auto-reloads if IIS has timed out due to inactivity - default is No
  • Edit XL3LookupRW without unlocking: enable LookupRW formulae for editing - default is No
  • Open Links in same Window: whether any XL3Link formulae to other websites or reports are opened in the same or different windows - default is Yes
  • Use V5 Chart Rendering: For historical (pre v6 reports) this option allows charts to be rendered as per v5 - default is No
  • Allow save to Excel live: whether the workbook can be saved as "Live" (i.e. remain interactive) when downloading from the web - default is Yes. This is new in Version 7.1
  • Handle Excel chart styles: whether Excel chart formatting is handled in a backwards-compatible way - default is Yes. This is new in Version 7
  • Hide Sheet Tabs: New in Version 8, Option to hide sheet tabs when workbook is published - default is No
  • Enforce Excel data validation rules: whether XLCubed ensures data input follows data validation rules once published - default is No. This is new in Version 8
  • Submit when value changes: New in Version 8.1, Automatically submits changes in unlocked cells - default is No
  • Open WebReportLinks in same Div: New in Version 8.1, enables this for reports embedded in custom applications - default is No
  • Convert shapes to images: New in Version 9, Excel Shapes (e.g. Callouts, Lines, etc.) are converted to images so that they can appear in the workbook when published. - default is No
  • Use Refresh on Driving Cell Change: controls whether a published report can ignore that grid property setting
  • Write a space in blank cells: writes a space in blank cells when report is published

Writeback

Formula Writeback

WBOpWrite91.png
  • Enable Writeback: enables Writeback, whether or not this happens on or offline is controlled by the individual Grid Properties
  • Write to DataMembers in parent-child hierarchies:
  • 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 parametrised 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. The connection is set up when the report is opened, it can not be changed dynamically while the report is open.

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

WikiUrl.png

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

WorkbookOptions5.png

This is accessed via the Options > Workbook Options > Connections tab. Specify the values for each parameter, and where required change the server, database, cube, and whether the report requires a password before pressing Get URL.

Options11.png

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

Language Options

New in Version 7.5 - you can define on this tab the report name and description to be displayed in XLCubedWeb for different language users.

WBOpLang91.png

Multi-sheet Web Printing

  • Allow printing Multi-Sheet: enabling this option will cause the multi-sheet option to appear after clicking the print icon in the web.
  • Allow Save to Excel Multi-Sheet: enabling this option will cause the multi-sheet option to appear after clicking the save to excel icon in the web.
  • Custom Multi-Sheet caption: define the caption that appears in the print dialog for multi-sheet printing. If no caption is set, the dialog will display "Multi-Sheet".
  • Worksheets to propogate: select the worksheets that will be generated in the pdf/excel workbook.
  • Slicers to use for Multi-Sheet selections: select which slicers will be available to make selections from upon printing.


See Multi-sheet Web Printing for more information.

PowerPoint settings

New in v9.1 (Enterprise) you can now setup a workbook to support exporting to PowerPoint. This needs to be setup here:

WBOpPP91.png
  • Allow save to PowerPoint slideshow: enable save/export to PowerPoint
  • Slides: add appropriate workbook range for each slide that will be created
  • Use slide title: specify static text or cell range to use as slide's title
  • Resize content to fit space: enable to fit slide space available and optionally retain aspect ratio


See PowerPoint export for more information.