XL3SetProperty

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.

Syntax

XL3SetProperty( ObjectType, ObjectName, Property, Arg1, [Arg2],…, [Arg27] )

Parameters

Parameter	Description
ObjectType	Object Type to update. Valid values are "Workbook", "Grid", "Slicer", "Table", "SmallMultiple", "Chart", "Map", "Pager" and "Range".
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"
"Writeback"	"Enabled"	Sets whether formula writeback is enabled. (Version 8.1)	TRUE or FALSE

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.
"ApplyFormatting"	Toggle whether formatting is applied, new in Version 9.	TRUE or FALSE

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

Other Settings

Property	Value
"WritebackEnabled"	TRUE or FALSE (Version 8.1)
"WritebackSpreadMethod"	Valid values are "USE_EQUAL_ALLOCATION", "USE_EQUAL_INCREMENT", "USE_WEIGHTED_ALLOCATION", "USE_WEIGHTED_INCREMENT" (Version 8.1)

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 (pre-V9 will only be applied for published reports, V9 works in both).	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.
"Enabled"	Allows toggling of the whether or not the slicer is enabled. New in Version 8.1	TRUE or FALSE
"WaitForSubmitOnWeb"	Allows toggling of the whether or not the slicer reqires submitting. New in Version 9	TRUE or FALSE

Table

Property	Description	Value
"ColumnDisplay"	Sets the column(s) shown and their order. (New in V7.1)	Equivalent to setting up the columns on the Column Display tab in the table properties. The column display must be enabled in the properties for this to take effect.
"Sorting"	Sets the column(s) to sort by. (New in V7.1)	Pairs of column name and direction. If no direction is provided ascending is assume. e.g. =XL3SetProperty("Table", "myTable", "Sorting", "Product", "asc", "Profit", "desc")

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.

Chart

You reference the chart to be affected using its Chart Name. This can be seen when you select the chart - the Chart Name is then shown in the box above cell A1 (the same place as named ranges).

Property	Description	Value
"YMin" or "YMax"	Sets the limits of the Y Axis.	Numeric (Can also be set to "auto" from Version 9)
"Y2Min" or "Y2Max"	Sets the limits of the Y2 Axis.	Numeric
"XMin" or "XMax"	Sets the limits of the X Axis.	Numeric
"X2Min" or "X2Max"	Sets the limits of the X2 Axis.	Numeric
"YMajorUnit" / "YMinorUnit"	Sets the units of the Y Axis. Version 9	Numeric or "auto". Also works for other axes.
"Visible"	Sets the chart visibility. Version 8 onwards.	TRUE or FALSE
"SeriesFillColor"	Sets the series color. Version 9 onwards.	Numeric values for series index an HTML colour value (#RRGGBB) or a named colour.
"SeriesBorderColor"	Sets the series color. Version 9 onwards.	Numeric values for series index and HTML colour value (#RRGGBB) or a named colour.
"XCrosses", "YCrosses", "X2Crosses", "Y2Crosses"	Sets the axis crossing point.	Numeric value for custom settings, or "auto", "min", "max" to cross at those positions. Version 9.1 onwards.

Map

Property	Description	Value
"PlaceCodeSet"	Name of the set of shapes to plot. Can be one of the predefined sets or a custom set if you have provided those to XLCubed. For custom shapes the value should be custom/MyShapeSet. New in Version 9	Text

Pager

New in Version 9.

Property	Description	Value
"Page"	The page number to jump to. Note that on the web you should not combine this with a pager the user can access, as the formula will conflict with it. Use for a fully formula driven pager only.	Numeric
"Enabled"	Toggle whether or not the pager is active. If not then it will not restrict the output of the report.	TRUE or FALSE
"Visible"	Toggle whether or not the pager is visible. Version 9.1	TRUE or FALSE

Dashboard

New in Version 9.

Property	Description	Value
"Target"	Sets the active target for the dashboard on the given worksheet, this overides any automatic targets based on the display size. Set to "" to return to automatic target selection	String

Range

New in Version 9. The ObjectName should be text in the form "Sheet1!A1:D10".

Property	Description	Value
"Locked"	Sets the cell locked status, useful for controlling data enty on the web.	TRUE or FALSE

Connection

New in Version 9.1. Allows changing a connection dynamically. The ObjectName must be the connection ID as text, e.g.

Property	Description	Value
"Copy"	Copies the connection properties to the connection passed in.	The ID of the source connection.

Viewport

New in Version 9.1. Allows changing a viewport dynamically.

Property	Description	Value
"Range"	Sets the range that will be displayed in the viewport.	Text. This must be the text of the range, not a reference to it. Correct: =XL3SetProperty("viewport", "myVP", "range", "Sheet1!A1:D10") Incorrect: =XL3SetProperty("viewport", "myVP", "range", Sheet1!A1:D10) Note the quote marks around the address.

Shape

New in Version 9.1. Allows changing pictures, grouped shapes and charts.

Property	Description	Value
"Visible"	Sets the shape's visiblility.	TRUE or FALSE

PictureLink

New in Version 9.1. The name of the picture link canbe found by right-clicking the link and copying the contents of the Excel Name Box (found next to the Formula Bar). It will normally begin with @PL.

Property	Description	Value
"Enabled"	Sets the link's enabled property.	TRUE or FALSE

Workbook Web Publication options

New in Version 9.1. Allows setting which options are permitted.

Property	Description	Value
PermissionName	One of the following values Value Description SubmitChanges Display Submit Changes Print Allow printing SaveToExcel Allow save to Excel SaveToExcelActiveSheetOnly Only allow active sheet to be saved to Excel SaveToRepository Allow save to repository Refresh Display Refresh button ContextMenu Enable Right-Click Menus SaveParameters Save Custom View DrillMemberFormulae Drill Formulae EditMemberFormulae Edit Member Formulae AutoReloadOnExpiry Auto reload EditLockedXL3LookupRW Edit XL3LookupRW without unlocking OpenLinksInSameWindow Open Links In Same Window DundasChartRendering Use V5 Chart Rendering SaveToExcelLive Allow save to Excel live DeAutomaticChartSettingsOnPublish Handle Excel chart styles HideSheetTabsOnPublish Hide Sheet Tabs UseExcelValidation Enforce Excel data validation rules SubmitChangesOnCellBlur Submit when value changes OpenWebReportLinksInSameDiv Open WebReportLinks in same div ConvertShapesToImagesOnPublish Convert shapes to images UseRefreshOnDrivingCellChangeOnWeb Use 'Refresh On Driving Cell Change' WriteNbspInBlankCells Write a space in blank cells	TRUE or FALSE

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.

Example

This example uses a local cube, which holds a measure ‘Value’ against Products and Regions across time. We require a report that allows the user to dynamically select which hierarchies appear on rows and then the level of detail that appears – ie at member, children or descendants of that hierarchy.

We want the user to be able to run the report with either Product or Region on rows; slicer choices must change accordingly so that if the report is being run with Regions on rows the slicer choices must only be based on Region; similarly when running with Product on rows, the slicer must only offer choices of Product.

• The report is shown below:

• We will need to create a number of slicers so that we can change hierarchies and level of detail displayed:

• Create a slicer which will allow the user to select which hierarchy appears on rows.

• This slicer will pick its range from values in cells K18 & K19 and will update N17 with the user selection.

• Create another slicer which will allow the user to select a particular Region. This will put the user selection in L29.

• Create the slicer that will allow the user to select All or a particular Product.

• Create the fourth slicer that allows the user to select the member level – eg member, children or descendants. This slicer will pick its range from values in cells L13 to L15 and update L30 with value selected.

• This screenshot shows the values and settings that will allow the user to use XL3setproperty to define the setup of the grid report.

• Populate cells K18 & K19 with the valid options for hierarchies on rows – we are using Region and Product in this example. In cells L18 & L19 enter the valid cube hierarchy name.

You must name the object type – in our example our grid is called GridToSet – this has been updated using the Properties screen.

• There are three XL3SetProperty statements in our example, updating properties MemberSelectionType; HierarchiesOnRows and HierarchiesOnHeaders.

• setMemberSelType – the two cells to the right hold the following XL3SetProperty statement:

In cell L21:

 =XL3SetProperty("Grid","GridToSet","MemberSelectionType","[Region]",IF(N17="Region", $L$30, "Member"))

This is passing Region as an argument when the user has selected this option from the first slicer.

In cell M21:

 =XL3SetProperty("Grid","GridToSet","MemberSelectionType","[Product]",IF(N17="Product", $L$30, "Member"))

 This is passing Product as an argument when the user has selected this option from the first slicer.

• Set Rows – the cell to the right holds the following XL3SetProperty statement:

 =XL3SetProperty("Grid","GridToSet","HierarchiesonRows",$L$31)

• This is passing the value of L31 as an argument for the hierarchy that should appear on rows of the grid.

• Set Header – the cell to the right holds the following xl3SetProperty statement:

 =XL3SetProperty("Grid","GridToSet","HierarchiesOnHeaders",$L$32,$L$33)
 

• This is passing the values of cells L32 and L33 as the hierarchies on the headers of the grid.

• N17 contains the value of the row slicer (Product or Region). L31 also contains a VLOOKUP statement

 =VLOOKUP(N17,K18:L19,2,FALSE)

• L32 contains Excel MID function and allows the swapping of the values of Rows and Headers.

 =IF(MID(L31,1,3)="[Pr",L18,L19)

• If the first two characters of the string in L31 are ‘Pr’ then take the value of L18 else the value of L19.

• In this way we can swap the value between Region and Product on rows and headers.

• This produces the report as shown at the beginning of this section, allowing Region or Product to be be displayed on rows and swapping between these as desired.

See Also

• Formula Reference