| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "XL3SetProperty"
(→Slicer) |
|||
| Line 256: | Line 256: | ||
| Descendants of the member from the level "X" are returned. X must be a number, or level name. | | Descendants of the member from the level "X" are returned. X must be a number, or level name. | ||
|} | |} | ||
| + | |||
| + | ==Example (based on the [Sales Analysis Cube|Sales cube]])== | ||
==See Also== | ==See Also== | ||
Revision as of 12:53, 4 October 2011
This function updates XLCubed objects in the workbook such as grids, slicers and small multiples.
You can use this to update some setting from the properties screen or move hierarchies.
Contents
[hide]Syntax
XL3SetProperty( ObjectType, ObjectName, Property, Arg1, [Arg2],…, [Arg27] )
Parameters
| Parameter | Description |
|---|---|
| ObjectType | Object Type to update. Valid values are "Workbook", "Grid", "Slicer" and "SmallMultiple". |
| ObjectName | Name of the object to update. You can not update an object unless you have given it a name in its properties screen. |
| Property | Name of the property to update. Valid names depend on the object type. Details for each are below. |
| Arg1, [Arg2],…, [Arg27] | Value or values to set the property to. Valid values depend on the object type. Details for each are below. |
Workbook
For workbook level properties the object name refers to the area of the product being updated.
| Object Name | Property | Description | Value |
|---|---|---|---|
| "Writeback" | "SpreadMethod" | Sets the formula spread method. | Valid values are "USE_EQUAL_ALLOCATION", "USE_EQUAL_INCREMENT", "USE_WEIGHTED_ALLOCATION", "USE_WEIGHTED_INCREMENT" |
Grid
Grid Display
| Property | Description | Value |
|---|---|---|
| "RemoveEmptyColumns" | Sets the columns to hide or show members with no data. | TRUE or FALSE |
| "RemoveEmptyRows" | Sets the rows to hide or show members with no data. | TRUE or FALSE |
| "HierarchiesOnHeaders" | Sets hierarchies on headers. Will move hierarchies as required. | Hierarchy name or names. |
| "HierarchiesOnColumns" | Sets hierarchies on columns. Will move hierarchies as required. | Hierarchy name or names. |
| "HierarchiesOnRows" | Sets hierarchies on rows. Works as HierarchiesOnColumns. | Hierarchy name or names. |
| "MergeRepeatingCells" | Merge cells containing the same member in cross-joins. | TRUE or FALSE |
| "MemberSelectionType" | Sets the selection type of the members, e.g. children, parent, descendants etc. | See the Member Selection Type section for more details. |
Grid Permissions
See Grid Permissions for details of what the following options do.
| Property | Value |
|---|---|
| "ColumnMembers" | TRUE or FALSE |
| "ColumnNavigation" | TRUE or FALSE |
| "RowMembers" | TRUE or FALSE |
| "RowNavigation" | TRUE or FALSE |
| "DimensionNavigation" | TRUE or FALSE |
| "Menus" | TRUE or FALSE |
| "SlicerMembers" | TRUE or FALSE |
Slicer
| Property | Description | Value |
|---|---|---|
| "RemoveEmpty" | Sets the slicer to hide or show members with no data. | TRUE or FALSE |
| "Visible" | Show or hide the slicer (will only be applied for published reports). | TRUE or FALSE |
| "MultiSelect" | Allow multi selection on the slicer, dependant on slicer type. | TRUE or FALSE |
| "MemberSelectionType" | Sets the selection type of the members, e.g. children, parent, descendants etc. | See the Member Selection Type section for more details. |
SmallMultiple
| Property | Description | Value |
|---|---|---|
| "RemoveEmptyCategories" | Sets the categories to hide or show members with no data. | TRUE or FALSE |
| "RemoveEmptySeries" | Sets the series to hide or show members with no data. | TRUE or FALSE |
| "RemoveEmptyColumns" | Sets the columns to hide or show members with no data. | TRUE or FALSE |
| "RemoveEmptyRows" | Sets the rows to hide or show members with no data. | TRUE or FALSE |
| "HierarchiesOnCategories" | Sets hierarchies on categories. Will move hierarchies as required. | Hierarchy name or names. |
| "HierarchiesOnSeries" | Sets hierarchies on series. Works as HierarchiesOnCategories. | Hierarchy name or names. |
| "HierarchiesOnColumns" | Sets hierarchies on columns. Works as HierarchiesOnCategories. | Hierarchy name or names. |
| "HierarchiesOnRows" | Sets hierarchies on rows. Works as HierarchiesOnCategories. | Hierarchy name or names. |
| "MemberSelectionType" | Sets the selection type of the members, e.g. children, parent, descendants etc. | See the Member Selection Type section for more details. |
| "ChartType" | Sets chart type of the series. | Valid values are "Column", "StackedColumn", "StackedColumn100", "Bar", "StackedBar", "StackedBar100", "Area", "StackedArea", "StackedArea100", "Line". |
| "ChartType2" | Sets chart type of the series once moved to the secondary axis. | Same as ChartType. |
Member Selection Type
Arguments
When setting the member selection type you must pass two or three additional arguments.
| Argument | Description |
|---|---|
| Hierarchy name | The hierarchy that will be updated. |
| Selection type | The type of selection to be applied. |
| Index (Optional) | Which member to apply the selection to. If not passed then the selection will be applied to all members. |
Selection Types
| Value | Description |
|---|---|
| "Member" | Only the member itself is returned. |
| "Children" | Children of the member are returned. |
| "Ancestors" | All ancestors of the member are returned. |
| "Descendants" | All descendants of the member are returned. |
| "FirstChild" | First child of the member is returned. |
| "FirstSibling" | First sibling of the member is returned. |
| "LastChild" | Last child of the member is returned. |
| "LastSibling" | Last sibling of the member is returned. |
| "LowestDescendants" | Descendants of the member from the lowest level are returned. |
| "Parent" | Parent of the member is returned. |
| "SameLevel" | All members at the level of the member are returned. |
| "Siblings" | All siblings of the member are returned. |
| "Next:X" | The member and the next "X" members at the level are returned. X must be a number. |
| "Prev:X" | The member and the previous "X" members at the level are returned. X must be a number. |
| "DescendantsAt:X" | Descendants of the member from the level "X" are returned. X must be a number, or level name. |
