Difference between revisions of "Grid Properties"

(Added categories)
Line 1: Line 1:
−
Performance tips for large grids.
+
==Behaviour Options==
 +
===Excel===
 +
; Resize columns/rows after refresh
 +
: Automatically adjust the columns and rows to fit the data.
 +
; Insert/delete columns/rows when Grid size changes
 +
: Determines the behaviour when members are added or removed.
 +
{{TableHeader}}
 +
| 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.
 +
{{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===
 +
; Remove empty columns/rows
 +
: 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===
 +
; Display error on invalid members
 +
: Displays a warning if invalid selections are found on any hierarchy.
 +
; Refresh Grid on open
 +
: Causes the Grid to update itself when the workbook is opened.
 +
 
 +
==Appearance Options==
 +
===General===
 +
; Title
 +
: The title of the Grid, displayed in dialogs and menus whenever the Grid is referred to.
 +
; Display Grid title
 +
: Display the Grid title in the worksheet.
 +
; Replace nulls with
 +
: Instead of an empty cell, XLCubed inserts the desired value.
 +
 
 +
===Formatting===
 +
; 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.
 +
; 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 filter hierarchies
 +
: Show only the row and column hierarchies and members. The filter 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===
 +
; Show drill indicator
 +
: 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==
 +
; Password protect Grid properties
 +
: 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 [[XLCubed Web Edition]].
 +
 
 +
===Permissions===
 +
; Show Grid menus
 +
: 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===
 +
These options allow you to hide specific operations from the Grid's right-click menu.
 +
 
 +
==Writeback Options==
 +
See [[Writeback]] for more detail about writeback in XLCubed.
 +
 
 +
==MDX Options==
 +
; Connection
 +
: Set the connection for the Grid
 +
; Auto-generate MDX (default)
 +
: XLCubed controls the MDX for the Grid, given the selected options, hierarchies and members.
 +
; Manual MDX
 +
: 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.
 +
; Get MDX from Excel range
 +
: The entire MDX statement for the Grid is taken from a cell on the worksheet.
 +
 
 +
==Default Grid Options==
 +
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.
 +
 
 +
==Performance Tips for Large Grids==
  
 
Turn Off:
 
Turn Off:
−
Apply Formatting
+
* Apply formatting
−
Merge Repeating Cells
+
* Merge repeating cells
  
 
Turn On:
 
Turn On:
−
Hide draggable hierarchy labels
+
* Hide draggable hierarchy labels
  
 
[[Category:Grid Reporting]]
 
[[Category:Grid Reporting]]

Revision as of 14:25, 13 October 2010

Behaviour Options

Excel

Resize columns/rows after refresh
Automatically adjust the columns and rows to fit the data.
Insert/delete columns/rows when Grid size changes
Determines the behaviour when members 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 formulae next to Grid
Detect and fill down/across any formulae directly adjacent to the Grid as members are added or removed.

Data

Remove empty columns/rows
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 this MSDN article for more information).

Refresh

Display error on invalid members
Displays a warning if invalid selections are found on any hierarchy.
Refresh Grid on open
Causes the Grid to update itself when the workbook is opened.

Appearance Options

General

Title
The title of the Grid, displayed in dialogs and menus whenever the Grid is referred to.
Display Grid title
Display the Grid title in the worksheet.
Replace nulls with
Instead of an empty cell, XLCubed inserts the desired value.

Formatting

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.
Merge repeating cells
When crossjoining, merge the repeated cells for the outer hierarchies:
With merged repeating cells
With merged repeating cells
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:
MemberPropertyDisplay(PropColumn).png Show member properties in separate columns
MemberPropertyDisplay(PropMem).png Property - Member
MemberPropertyDisplay(MemProp).png Member - Property
MemberPropertyDisplay(PropOnly).png Property
Hide filter hierarchies
Show only the row and column hierarchies and members. The filter hierarchies can still be seen in the |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

Show drill indicator
If a member is drillable, a symbol is displayed before the member's caption:
+ Member can be drilled down.
- Member can be drilled up.
Member cannot be drilled
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

Password protect Grid properties
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 published to XLCubed Web Edition.

Permissions

Show Grid menus
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

These options allow you to hide specific operations from the Grid's right-click menu.

Writeback Options

See Writeback for more detail about writeback in XLCubed.

MDX Options

Connection
Set the connection for the Grid
Auto-generate MDX (default)
XLCubed controls the MDX for the Grid, given the selected options, hierarchies and members.
Manual MDX
You may type your own MDX in the edit field. Using XL3Parm( Address ) allows you to substitute parts of the MDX with cells 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

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.

Performance Tips for Large Grids

Turn Off:

  • Apply formatting
  • Merge repeating cells

Turn On:

  • Hide draggable hierarchy labels
Retrieved from ‘https://help.fluencexl.com/index.php?title=Grid_Properties&oldid=3447’