XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Difference between revisions of "Table Properties"

(→‎Performance Tips for Large Grids)
(→‎Behaviour)
 
(19 intermediate revisions by 4 users not shown)
Line 1: Line 1:
−
==Behaviour Options==
+
==Behaviour==
−
===Excel===
+
===General===
 +
; Title
 +
: The title of the Table, displayed in dialogs and menus whenever the Table is referred to.
 +
===Formatting===
 
; Resize columns/rows after refresh
 
; Resize columns/rows after refresh
 
: Automatically adjust the columns and rows to fit the data.
 
: Automatically adjust the columns and rows to fit the data.
−
; Insert/delete columns/rows when Grid size changes
+
; Apply formatting
−
: Determines the behaviour when members are added or removed.
+
: Override formats on the Table, or let you manage them manually.
 +
; Fill formulae next to Table
 +
: Detect and fill down any formulae directly adjacent to the Table as rows are added or removed.
 +
; Merge repeating cells
 +
: When data is repeated, merge the repeated cells for the outer values
 +
{| align="center"
 +
| [[File:TableMergeRepeatingCellsOn.png|thumb|250px|alt=With merged repeating cells|With merged repeating cells]]
 +
| [[File:TableMergeRepeatingCellsOff.png|thumb|250px|alt=With separate repeating cells|With separate repeating cells]]
 +
|}
 +
; Transpose
 +
: Changes the position of table rows and columns by swapping them over (new in v8.1)
 +
 
 +
; Insert/delete columns/rows when Table size changes
 +
: Determines the behaviour when rows are added or removed.
 
{{TableHeader}}
 
{{TableHeader}}
 
| No Shift
 
| No Shift
Line 15: Line 31:
 
| XLCubed inserts and deletes entire columns or rows to accommodate the new data.
 
| XLCubed inserts and deletes entire columns or rows to accommodate the new data.
 
{{TableFooter}}
 
{{TableFooter}}
−
; Fill formulae next to Grid
 
−
: Detect and fill down/across any formulae directly adjacent to the Grid as members are added or removed.
 
  
−
===Data===
+
; Fill formatting when expanding
−
; Remove empty columns/rows
+
: Controls whether cell formatting is copied into new rows when the table expands. Happens when a column is formatted and new rows are created when the table is refreshed.
−
: Exclude columns/rows with no values from the result set.
 
−
; Feed hierarchy member results
 
−
: Use the MDX function Generate to allow dynamic ranking and filtering (note: this option can be slower).
 
−
; Use NonEmpty on crossjoins
 
−
: Use the NonEmptyCrossJoin MDX function (see [http://msdn.microsoft.com/en-us/library/ms144797.aspx this MSDN article] for more information).
 
  
 
===Refresh===
 
===Refresh===
−
; Display error on invalid members
+
; Refresh Table on open
−
: Displays a warning if invalid selections are found on any hierarchy.
+
: Causes the Table to update itself when the workbook is opened.
−
; Refresh Grid on open
+
; Refresh when driving cells change
−
: Causes the Grid to update itself when the workbook is opened.
+
: Causes the Table to update itself when cells driving the query are updated.
 +
 
 +
 
 +
; Allow formulae
 +
: Controls whether text returned from the query is inserted as plain text, or whether it allows Excel formulae to also be inserted
  
−
==Appearance Options==
+
==Appearance==
−
===General===
+
===Pivoting===
−
; Title
+
These options apply when pivoting data
−
: The title of the Grid, displayed in dialogs and menus whenever the Grid is referred to.
+
; Group by pivoted value
−
; Display Grid title
+
: Pivoted values are kept together, e.g. all columns for January will be together
−
: Display the Grid title in the worksheet.
+
; Nest by pivoted value
−
; Replace nulls with
+
: Numeric columns are kept together, e.g. all columns for Sales Value will be together
−
: Instead of an empty cell, XLCubed inserts the desired value.
+
; Show in-cell charts
 +
: Useful when pivoting on a date, automatically displays a chart of the data.
  
−
===Formatting===
+
[[Image:PivotGroupingExamples.png|thumb|center|300px]]
−
; Apply formatting
 
−
: Override formats on the Grid, or let you manage them manually.
 
−
; Format Grid without borders
 
−
: Exclude borders from the formatting applied to the Grid.
 
−
; Apply Cube formatting
 
−
: Retrieve and apply formats that are defined in the cube.
 
−
; Merge repeating cells
 
−
: When crossjoining, merge the repeated cells for the outer hierarchies:
 
−
{| align="center"
 
−
| [[File:MergeRepeatingCellsOn.png|thumb|250px|alt=With merged repeating cells|With merged repeating cells]]
 
−
| [[File:MergeRepeatingCellsOff.png|thumb|250px|alt=With separate repeating cells|With separate repeating cells]]
 
−
|}
 
−
; Show member properties in separate columns
 
−
: If any member properties are selected, display each in its own column, or in the same cell as the member caption:
 
−
{{TableHeader}}
 
−
| align="right" | [[File:MemberPropertyDisplay(PropColumn).png]]
 
−
| Show member properties in separate columns
 
−
|-
 
−
| align="right" | [[File:MemberPropertyDisplay(PropMem).png]]
 
−
| Property - Member
 
−
|-
 
−
| align="right" | [[File:MemberPropertyDisplay(MemProp).png]]
 
−
| Member - Property
 
−
|-
 
−
| align="right" | [[File:MemberPropertyDisplay(PropOnly).png]]
 
−
| Property
 
−
{{TableFooter}}
 
−
; Hide header hierarchies
 
−
: Show only the row and column hierarchies and members. The header hierarchies can still be seen in the [[Report Designer|Edit Grid dialog]].
 
−
; Hide draggable hierarchy labels
 
−
: Prevent the hierarchy labels being rendered. This option speeds up Grid updates.
 
−
; Hide hierarchy label warning icon
 
−
: Prevent display of a warning icon when advanced selections are active on a hierarchy.
 
  
−
===Drilling===
+
; Pivot fixed on column name
−
; Show drill indicator
+
: By default XLCubed stores the pivoted column index. You can instead switch so the column name is used. This is useful if the query can vary the columns it returns, e.g. from a stored procedure.
−
: If a member is drillable, a symbol is displayed before the member's caption:
 
−
{{TableHeader}}
 
−
| + || Member can be drilled down.
 
−
|-
 
−
| align="center" | - || Member can be drilled up.
 
−
|-
 
−
|  || Member cannot be drilled
 
−
{{TableFooter}}
 
−
; Indent members when drilling
 
−
: The indentation of the members in a Grid reflects what level they are on. The size of indentation can also be adjusted.
 
  
−
==Lockdown Options==
+
;When using named query for pivot
−
; Password protect Grid properties
+
:For Sql queries you can specify the order for pivoted columns to be displayed (by default alphabetical order is used). If you specify an order query, you can also decide whether to emit all columns in the order query, or just those which have data.
−
: Before showing the Grid Properties dialog, a password is required.
 
−
; Apply restrictions in Excel
 
−
: Enforce the following permissions in Excel. If off, the restrictions are only applied when [[Web Publishing|published]] to [[Web Edition|XLCubed Web Edition]].
 
  
−
===Permissions===
+
===Sections===
−
; Show Grid menus
+
This is used to configure the columns used as section headers, as described in the [[Tables#Sections|section]] help.
−
: Show the right-click Grid menu. Note: The Grid properties dialog is always available unless specifically excluded below.
 
−
; Allow hierarchy navigation
 
−
: Show the [[Hierarchy Editor]] for any hierarchy.
 
−
; Can change filter/column/row members
 
−
: Show the [[Hierarchy Editor]] for the hierarchies on the given axis.
 
−
; Can drill columns/rows
 
−
: Allow drilling on members in the given axis.
 
−
; Set All to No
 
−
: Disallows all the above permissions.
 
  
−
===Hiding Menu Items===
+
==Column Display==
−
These options allow you to hide specific operations from the Grid's right-click menu.
+
When enabled, this feature allows you to reorder the columns of the query before they are displayed, and to suppress any columns that aren't required.
  
−
==Writeback Options==
+
This reordering is done after any pivoting has been done, so only affects the final displayed results.
−
See [[Writeback]] for more detail about writeback in XLCubed.
 
  
−
==MDX Options==
+
==Query==
−
; Connection
+
; Auto-generate Query (default)
−
: Set the connection for the Grid
+
: XLCubed controls the query for the Table given the selected options.
−
; Auto-generate MDX (default)
+
; Manual Query
−
: XLCubed controls the MDX for the Grid, given the selected options, hierarchies and members.
+
: You may type your own query in the edit field.
−
; Manual MDX
+
; Get Query from Excel range
−
: You may type your own MDX in the edit field. Using {{Code|XL3Parm( Address )}} allows you to substitute parts of the MDX with cells on the worksheet.
+
: The entire query statement for the Table is taken from a cell on the worksheet.
−
; Get MDX from Excel range
 
−
: The entire MDX statement for the Grid is taken from a cell on the worksheet.
 
  
−
==Default Grid Options==
+
==Interaction==
−
The defaults for new Grids can be set by using the '''Save as Defaults''' button. To set a Grid back to the saved defaults, use the '''Load from Defaults''' button.
+
XLCubed can create named ranges based on Table elements that can then be used by other Excel objects, such as formulae and charts. See [[Named Ranges]] for more information.
  
 
== Performance tips for large tables ==
 
== Performance tips for large tables ==
Line 131: Line 87:
  
 
Turn off
 
Turn off
−
* Resize Columns
 
 
* Apply Formatting
 
* Apply Formatting
 
* Merge repeating cells
 
* Merge repeating cells
 +
* Resize Columns
 +
 +
==See Also==
 +
* [[Tables]]
 +
 +
[[Category:Tabular Reporting]]
 +
[[Category:Relational Reporting]]

Latest revision as of 11:13, 29 July 2020

Behaviour

General

Title
The title of the Table, displayed in dialogs and menus whenever the Table is referred to.

Formatting

Resize columns/rows after refresh
Automatically adjust the columns and rows to fit the data.
Apply formatting
Override formats on the Table, or let you manage them manually.
Fill formulae next to Table
Detect and fill down any formulae directly adjacent to the Table as rows are added or removed.
Merge repeating cells
When data is repeated, merge the repeated cells for the outer values
With merged repeating cells
With merged repeating cells
With separate repeating cells
With separate repeating cells
Transpose
Changes the position of table rows and columns by swapping them over (new in v8.1)
Insert/delete columns/rows when Table size changes
Determines the behaviour when rows are added or removed.
No Shift XLCubed overwrites neighbouring cells.
Shift Range XLCubed inserts the required number of cells only.
Shift Entire XLCubed inserts and deletes entire columns or rows to accommodate the new data.
Fill formatting when expanding
Controls whether cell formatting is copied into new rows when the table expands. Happens when a column is formatted and new rows are created when the table is refreshed.

Refresh

Refresh Table on open
Causes the Table to update itself when the workbook is opened.
Refresh when driving cells change
Causes the Table to update itself when cells driving the query are updated.


Allow formulae
Controls whether text returned from the query is inserted as plain text, or whether it allows Excel formulae to also be inserted

Appearance

Pivoting

These options apply when pivoting data

Group by pivoted value
Pivoted values are kept together, e.g. all columns for January will be together
Nest by pivoted value
Numeric columns are kept together, e.g. all columns for Sales Value will be together
Show in-cell charts
Useful when pivoting on a date, automatically displays a chart of the data.
PivotGroupingExamples.png
Pivot fixed on column name
By default XLCubed stores the pivoted column index. You can instead switch so the column name is used. This is useful if the query can vary the columns it returns, e.g. from a stored procedure.
When using named query for pivot
For Sql queries you can specify the order for pivoted columns to be displayed (by default alphabetical order is used). If you specify an order query, you can also decide whether to emit all columns in the order query, or just those which have data.

Sections

This is used to configure the columns used as section headers, as described in the section help.

Column Display

When enabled, this feature allows you to reorder the columns of the query before they are displayed, and to suppress any columns that aren't required.

This reordering is done after any pivoting has been done, so only affects the final displayed results.

Query

Auto-generate Query (default)
XLCubed controls the query for the Table given the selected options.
Manual Query
You may type your own query in the edit field.
Get Query from Excel range
The entire query statement for the Table is taken from a cell on the worksheet.

Interaction

XLCubed can create named ranges based on Table elements that can then be used by other Excel objects, such as formulae and charts. See Named Ranges for more information.

Performance tips for large tables

If you have very large tables, the following options will speed up data refreshes:

Turn off

  • Apply Formatting
  • Merge repeating cells
  • Resize Columns

See Also

Retrieved from ‘https://help.xlcubed.com/index.php?title=Table_Properties&oldid=10213’