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

Difference between revisions of "XL3SetProperty"

(→‎Slicer)
(→‎SmallMultiple)
 
(80 intermediate revisions by 7 users not shown)
Line 1: Line 1:
−
This function updates XLCubed objects in the workbook such as grids, slicers and small multiples.
+
The XL3SetProperty formula updates XLCubed objects in the workbook such as [[Grids]], [[Slicers]] and [[Small Multiple Charts]].
  
 
You can use this to update some setting from the properties screen or move hierarchies.
 
You can use this to update some setting from the properties screen or move hierarchies.
Line 13: Line 13:
 
|-
 
|-
 
| {{Code|ObjectType}}
 
| {{Code|ObjectType}}
−
| Object Type to update. Valid values are "Workbook", "Grid", "Slicer" and "SmallMultiple".
+
| Object Type to update. Valid values are "Workbook", "Grid", "Slicer", "Table", "SmallMultiple", "Chart", "Map", "Pager" and "Range".
 
|-
 
|-
 
| {{Code|ObjectName}}
 
| {{Code|ObjectName}}
Line 34: Line 34:
 
! Description
 
! Description
 
! Value
 
! Value
 +
|-
 +
| "WebPublication"
 +
| One of the following option names:
 +
{| class="mw-collapsible mw-collapsed"
 +
! 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
 +
|}
 +
| Sets which options are permitted when published. Available from [[Version 9.1]].
 +
| TRUE or FALSE
 
|-
 
|-
 
| "Writeback"
 
| "Writeback"
Line 39: Line 117:
 
| Sets the formula spread method.
 
| Sets the formula spread method.
 
| Valid values are "USE_EQUAL_ALLOCATION", "USE_EQUAL_INCREMENT", "USE_WEIGHTED_ALLOCATION", "USE_WEIGHTED_INCREMENT"
 
| Valid values are "USE_EQUAL_ALLOCATION", "USE_EQUAL_INCREMENT", "USE_WEIGHTED_ALLOCATION", "USE_WEIGHTED_INCREMENT"
 +
|-
 +
| "Writeback"
 +
| "Enabled"
 +
| Sets whether formula writeback is enabled. Available from [[Version 8.1]].
 +
| TRUE or FALSE
 
|}
 
|}
  
Line 50: Line 133:
 
! Value
 
! Value
 
|-
 
|-
−
| "RemoveEmptyColumns"
+
| "ApplyFormatting"
−
| Sets the columns to hide or show members with no data.
+
| Toggle whether formatting is applied. Available from [[Version 9]].
−
| TRUE or FALSE
 
−
|-
 
−
| "RemoveEmptyRows"
 
−
| Sets the rows to hide or show members with no data.
 
 
| TRUE or FALSE
 
| TRUE or FALSE
 
|-
 
|-
 
| "HierarchiesOnColumns"
 
| "HierarchiesOnColumns"
 
| Sets hierarchies on columns. Will move hierarchies as required.
 
| Sets hierarchies on columns. Will move hierarchies as required.
 +
| Hierarchy name or names.
 +
|-
 +
| "HierarchiesOnHeaders"
 +
| Sets hierarchies on headers. Will move hierarchies as required.
 
| Hierarchy name or names.
 
| Hierarchy name or names.
 
|-
 
|-
 
| "HierarchiesOnRows"
 
| "HierarchiesOnRows"
−
| Sets hierarchies on rows. Works as HierarchiesOnColumns.
+
| Sets hierarchies on rows. Will move hierarchies as required.
 
| Hierarchy name or names.
 
| Hierarchy name or names.
 
|-
 
|-
−
| "MergeRepeatingCells"
+
| "MemberProperties"
−
| Merge cells containing the same member in cross-joins.
+
| Sets the selected member properties for a hierarchy.
−
| TRUE or FALSE
+
| See the [[#Member Properties|Member Properties]] section for more details.
 
|-
 
|-
 
| "MemberSelectionType"
 
| "MemberSelectionType"
 
| Sets the selection type of the members, e.g. children, parent, descendants etc.
 
| Sets the selection type of the members, e.g. children, parent, descendants etc.
 
| See the [[#Member Selection Type|Member Selection Type]] section for more details.
 
| See the [[#Member Selection Type|Member Selection Type]] section for more details.
 +
|-
 +
| "MergeRepeatingCells"
 +
| Merge cells containing the same member in cross-joins. Applies both {{Code|MergeRepeatingRowCells}} and {{Code|MergeRepeatingColumnCells}} at once.
 +
| TRUE or FALSE
 +
|-
 +
| "MergeRepeatingColumnCells"
 +
| Merge cells containing the same member in column cross-joins. Available from [[Version 9]].
 +
| TRUE or FALSE
 +
|-
 +
| "MergeRepeatingRowCells"
 +
| Merge cells containing the same member in row cross-joins. Available from [[Version 9]].
 +
| 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
 +
|-
 +
| "ShowDescendantComments"
 +
| Toggle whether descendant comments should be shown, if [[Cube Comments]] are enabled for the Grid. Available from [[Version 9.2]].
 +
| TRUE or FALSE
 
|}
 
|}
  
Line 103: Line 210:
 
| "SlicerMembers"
 
| "SlicerMembers"
 
| TRUE or FALSE
 
| TRUE or FALSE
 +
|}
 +
 +
===Member Properties===
 +
 +
When setting the member properties, you must pass additional arguments to define the hierarchy and level you would like the member properties to be applied to.
 +
 +
{{Code|XL3SetProperty( "Grid", GridName, "MemberProperties", HierarchyUniqueName, LevelUniqueName, [MemProp1],…, [MemPropN] )}}
 +
 +
Some things that may be useful:
 +
* Any blank member property arguments will be ignored.
 +
* Parent-child hierarchies ignore the LevelUniqueName argument.
 +
 +
===Other Settings===
 +
 +
{| class="wikitable"
 +
! 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". Available from [[Version 8.1]].
 
|}
 
|}
  
Line 117: Line 247:
 
|-
 
|-
 
| "Visible"
 
| "Visible"
−
| Show or hide the slicer.
+
| Show or hide the slicer (pre-V9 will only be applied for published reports, V9 works in both).
 
| TRUE or FALSE
 
| TRUE or FALSE
 
|-
 
|-
Line 123: Line 253:
 
| Allow multi selection on the slicer, dependant on slicer type.
 
| Allow multi selection on the slicer, dependant on slicer type.
 
| TRUE or FALSE
 
| TRUE or FALSE
 +
|-
 +
| "Hierarchy"
 +
| Sets the slicer hierarchy. Will move hierarchies as required.
 +
| Hierarchy name.
 +
|-
 +
| "HierarchiesOnHeaders"
 +
| Sets hierarchies on headers. Will move hierarchies as required.
 +
| Hierarchy name or names.
 
|-
 
|-
 
| "MemberSelectionType"
 
| "MemberSelectionType"
 
| Sets the selection type of the members, e.g. children, parent, descendants etc.
 
| Sets the selection type of the members, e.g. children, parent, descendants etc.
 
| See the [[#Member Selection Type|Member Selection Type]] section for more details.
 
| See the [[#Member Selection Type|Member Selection Type]] section for more details.
 +
|-
 +
| "Enabled"
 +
| Allows toggling of the whether or not the slicer is enabled. Available from [[Version 8.1]].
 +
| TRUE or FALSE
 +
|-
 +
| "OutputRange"
 +
| Sets the output range of the slicer.
 +
| Text. This must be the text of the range, not a reference to it.
 +
|-
 +
| "WaitForSubmitOnWeb"
 +
| Allows toggling of the whether or not the slicer reqires submitting. Available from [[Version 9]].
 +
| TRUE or FALSE
 +
|}
 +
 +
==Table==
 +
 +
{| class="wikitable"
 +
! 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. <pre>=XL3SetProperty("Table", "myTable", "Sorting", "Product", "asc", "Profit", "desc")</pre>
 +
|-
 +
| "ApplyFormatting"
 +
| Toggle whether formatting is applied. Available from [[Version 9.1]].
 +
| TRUE or FALSE
 +
|-
 
|}
 
|}
  
Line 136: Line 308:
 
! Value
 
! Value
 
|-
 
|-
−
| "RemoveEmpty
+
| "RemoveEmptyCategories"
−
| Sets the slicer to hide or show members with no data.
+
| 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
 
| TRUE or FALSE
 
|-
 
|-
−
| "Visible"
+
| "RemoveEmptyColumns"
−
| Show or hide the slicer.
+
| Sets the columns to hide or show members with no data.
 
| TRUE or FALSE
 
| TRUE or FALSE
 
|-
 
|-
−
| "MultiSelect"
+
| "RemoveEmptyRows"
−
| Allow multi selection on the slicer, dependant on slicer type.
+
| Sets the rows to hide or show members with no data.
 
| TRUE or FALSE
 
| TRUE or FALSE
 +
|-
 +
| "HierarchiesOnCategories"
 +
| Sets hierarchies on categories. Will move hierarchies as required.
 +
| Hierarchy name or names.
 +
|-
 +
| "HierarchiesOnSeries"
 +
| Sets hierarchies on series. 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. Will move hierarchies as required.
 +
| Hierarchy name or names.
 +
|-
 +
| "HierarchiesOnHeaders"
 +
| Sets hierarchies on headers. Will move hierarchies as required.
 +
| Hierarchy name or names.
 
|-
 
|-
 
| "MemberSelectionType"
 
| "MemberSelectionType"
 
| Sets the selection type of the members, e.g. children, parent, descendants etc.
 
| Sets the selection type of the members, e.g. children, parent, descendants etc.
 
| See the [[#Member Selection Type|Member Selection Type]] section for more details.
 
| See the [[#Member Selection Type|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.
 +
|-
 +
| "DataLabelsVisible"
 +
| Shows or hides the data labels. Available from [[Version 9.1]].
 +
| TRUE or FALSE
 +
|-
 +
| "Visible"
 +
| Sets the chart visibility. Available from [[Version 9.2]].
 +
| TRUE or FALSE
 +
|-
 +
| "ClearHighlights"
 +
| Clear the current chart highlights, requires a toggle cell as the value argument which is reset after the highlights are cleared. =XL3SetProperty("SmallMultiple", "test", "ClearHighlights", A1) See [[XL3RunSQL#Examples]] for an example of setting up a trigger cell.
 +
| A cell containing FALSE, which is set to TRUE by an [[XL3Link]].
 +
|}
 +
 +
==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).
 +
 +
{| class="wikitable"
 +
! 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. Available from [[Version 9]].
 +
| Numeric or "auto". Also works for other axes.
 +
|-
 +
| "Visible"
 +
| Sets the chart visibility. Available from [[Version 8]].
 +
| TRUE or FALSE
 +
|-
 +
| "SeriesFillColor"
 +
| Sets the series color. Available from [[Version 9]].
 +
| Numeric values for series index an HTML colour value (#RRGGBB) or a named [[Colours|colour]].
 +
|-
 +
| "SeriesBorderColor"
 +
| Sets the series color. Available from [[Version 9]].
 +
| Numeric values for series index and HTML colour value (#RRGGBB) or a named [[Colours|colour]].
 +
|-
 +
| "XCrosses", "YCrosses", "X2Crosses", "Y2Crosses"
 +
| Sets the axis crossing point.
 +
| Numeric value for custom settings, or "auto", "min", "max" to cross at those positions. Available from [[Version 9.1]].
 +
|-
 +
| "SeriesDataLabels"
 +
| Updates the series datalabels from a range. Available from [[Version 10]].
 +
| Pass 2 parameters: the zero-based index of the data series to update and an [[XL3Address]] formula for the range to use (must be on the same worksheet)
 +
|}
 +
 +
==Map==
 +
 +
{| class="wikitable"
 +
! Property
 +
! Description
 +
! Value
 +
|-
 +
| "PlaceCodeSet"
 +
| Name of the set of shapes to plot. Can be one of the [[Map_place_sets|predefined sets]] or a custom set if you have provided those to XLCubed. For custom shapes the value should be <code>custom/MyShapeSet</code>. Available from [[Version 9]].
 +
| Text
 +
|-
 +
| "Visible"
 +
| Toggle whether or not the map is visible. Available from [[Version 9.2]].
 +
| TRUE or FALSE
 +
|}
 +
 +
==Pager==
 +
 +
Available from [[Version 9]].
 +
 +
{| class="wikitable"
 +
! 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. Available from [[Version 9.1]].
 +
| TRUE or FALSE
 +
|}
 +
 +
==Dashboard==
 +
 +
Available from [[Version 9]].
 +
 +
{| class="wikitable"
 +
! 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
 +
 +
From [[Version 9.2]] you can create targets for different display sizes:
 +
 +
SimpleDesktop
 +
SimpleMobile
 +
SimpleSmallMobile
 +
 +
Then switch to the sized-matched target by using "Simple*" as the target name.
 +
 +
| String
 +
|}
 +
 +
==Range==
 +
 +
Available from [[Version 9]]. The ObjectName should be text in the form {{Code|"Sheet1!A1:D10"}}.
 +
 +
{| class="wikitable"
 +
! Property
 +
! Description
 +
! Value
 +
|-
 +
| "Locked"
 +
| Sets the cell locked status, useful for controlling data enty on the web.
 +
| TRUE or FALSE
 +
|}
 +
 +
==Connection==
 +
 +
Available from [[Version 9.1]]. Allows changing a connection dynamically.
 +
The {{code|ObjectName}} must be the connection ID as text, e.g. {{code|=TEXT(1,"#")}}
 +
 +
{| class="wikitable"
 +
! Property
 +
! Description
 +
! Value
 +
|-
 +
| "Copy"
 +
| Copies the connection properties to the connection passed in.
 +
| The ID of the source connection.
 +
|}
 +
 +
==Viewport==
 +
 +
Available from [[Version 9.1]]. Allows changing a Viewport dynamically.
 +
 +
{| class="wikitable"
 +
! 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: <pre>=XL3SetProperty("viewport", "myVP", "range", "Sheet1!A1:D10")</pre>
 +
 +
Incorrect: <pre>=XL3SetProperty("viewport", "myVP", "range", Sheet1!A1:D10)</pre>
 +
 +
Note the quote marks around the address.
 +
|-
 +
| "Visible"
 +
| Toggle whether or not the Viewport is visible. Available from [[Version 9.2]].
 +
| TRUE or FALSE
 +
|}
 +
 +
==PagedViewport==
 +
 +
Available from [[Version 9.2]]. Allows changing a Paged Viewport dynamically.
 +
 +
{| class="wikitable"
 +
! Property
 +
! Description
 +
! Value
 +
|-
 +
| "ActiveViewport"
 +
| Sets the currently active Viewport.
 +
| Either the index of the Viewport (1-based), or the Viewport name (case-sensitive).
 +
|-
 +
| "Visible"
 +
| Toggle whether or not the Viewport is visible. Available from [[Version 9.2]].
 +
| TRUE or FALSE
 +
|}
 +
 +
==Shape==
 +
 +
Available from [[Version 9.1]]. Allows changing pictures, grouped shapes and charts.
 +
 +
{| class="wikitable"
 +
! Property
 +
! Description
 +
! Value
 +
|-
 +
| "Visible"
 +
| Sets the shape's visiblility.
 +
| TRUE or FALSE
 +
|}
 +
 +
==PictureLink==
 +
 +
Available from [[Version 9.1]]. The name of the [[Picture Links|picture link]] can be 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 {{code|@PL}}.
 +
 +
{| class="wikitable"
 +
! Property
 +
! Description
 +
! Value
 +
|-
 +
| "Enabled"
 +
| Sets the link's enabled property.
 +
| TRUE or FALSE
 +
|}
 +
 +
==PowerBI==
 +
 +
Available from [[Version 9.2]]. Allows changing PowerBI objects.
 +
 +
{| class="wikitable"
 +
! Property
 +
! Description
 +
! Value
 +
|-
 +
| "ActivePage"
 +
| Sets the active page.
 +
| TRUE or FALSE
 +
|-
 +
| "Visible"
 +
| Sets the object's visibility.
 +
| TRUE or FALSE
 
|}
 
|}
  
Line 224: Line 670:
 
| 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==
 +
 +
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 – i.e. 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: [[Image:Set1.png|350px|centre]]
 +
*We will need to create a number of slicers so that we can change hierarchies and level of detail displayed:
 +
**Create a slicer that will allow the user to select which hierarchy appears on rows: [[Image:Set2B.png|centre|frame|Choose the range K18:L19 as the Slicer's source range]] [[Image:Set2A.png|centre|frame|Update L31 with the value in the First Column]]
 +
**Create a slicer that will allow the user to select a particular Region.  This will put the user selection in L29: [[Image:Set4.png|350px|centre]]
 +
**Create a slicer that will allow the user to select a Product.
 +
**Create a slicer that will allow the user to select the member level – e.g. member, children, or descendants: [[Image:Set5.png|350px|centre|frame|Choose L13:L15 as the source and update L30 with the selection]]
 +
*You must name the object you want to use XL3SetProperty with – in our example our Grid has the Title ''GridToSet'' in the Grid Properties screen.
 +
*Add selections in the Grid for the Region and Product slicers. These will then be updated by the XL3SetProperty formulae.
 +
*There are several XL3SetProperty statements at the bottom right of our example, updating properties MemberSelectionType; HierarchiesOnRows and HierarchiesOnHeaders.
 +
**Set MembSelType – the two cells to the right hold the following XL3SetProperty formulae:
 +
***In cell L21, passing Region as an argument when the user has selected this option from the first slicer:<br />{{Code|1==XL3SetProperty("Grid","GridToSet","MemberSelectionType","[Region]",IF(L31="[Region]", $L$30, "Member"))}}
 +
***In cell M21, passing Product as an argument when the user has selected this option from the first slicer:<br />{{Code|1==XL3SetProperty("Grid","GridToSet","MemberSelectionType","[Product]",IF(L31="[Product]", $L$30, "Member"))}}
 +
**Set Rows – the cell to the right holds the following XL3SetProperty formula, passing the value of L31 as an argument for the hierarchy that should appear on rows of the grid:<br />{{Code|1==XL3SetProperty("Grid","GridToSet","HierarchiesonRows",$L$31)}}
 +
**Set Header – the cell to the right holds the following XL3SetProperty formula, passing the values of cells L32 and L33 as the hierarchies on the headers of the grid:<br />{{Code|1==XL3SetProperty("Grid","GridToSet","HierarchiesOnHeaders",$L$32,$L$33)}}
 +
*L32 contains an IF function to swap the values of Rows and Headers:<br />{{Code|1==IF(L31="[Product]","[Region]","[Product]")}}
 +
*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==
 
==See Also==

Latest revision as of 09:25, 21 June 2023

The XL3SetProperty formula updates XLCubed objects in the workbook such as Grids, Slicers and Small Multiple Charts.

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
"WebPublication" One of the following option names:
Value ExpandDescription
"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
Sets which options are permitted when published. Available from Version 9.1. TRUE or FALSE
"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. Available from Version 8.1. TRUE or FALSE

Grid

Grid Display

Property Description Value
"ApplyFormatting" Toggle whether formatting is applied. Available from Version 9. TRUE or FALSE
"HierarchiesOnColumns" Sets hierarchies on columns. Will move hierarchies as required. Hierarchy name or names.
"HierarchiesOnHeaders" Sets hierarchies on headers. Will move hierarchies as required. Hierarchy name or names.
"HierarchiesOnRows" Sets hierarchies on rows. Will move hierarchies as required. Hierarchy name or names.
"MemberProperties" Sets the selected member properties for a hierarchy. See the Member Properties section for more details.
"MemberSelectionType" Sets the selection type of the members, e.g. children, parent, descendants etc. See the Member Selection Type section for more details.
"MergeRepeatingCells" Merge cells containing the same member in cross-joins. Applies both MergeRepeatingRowCells and MergeRepeatingColumnCells at once. TRUE or FALSE
"MergeRepeatingColumnCells" Merge cells containing the same member in column cross-joins. Available from Version 9. TRUE or FALSE
"MergeRepeatingRowCells" Merge cells containing the same member in row cross-joins. Available from Version 9. 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
"ShowDescendantComments" Toggle whether descendant comments should be shown, if Cube Comments are enabled for the Grid. Available from Version 9.2. 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

Member Properties

When setting the member properties, you must pass additional arguments to define the hierarchy and level you would like the member properties to be applied to.

XL3SetProperty( "Grid", GridName, "MemberProperties", HierarchyUniqueName, LevelUniqueName, [MemProp1],…, [MemPropN] )

Some things that may be useful:

  • Any blank member property arguments will be ignored.
  • Parent-child hierarchies ignore the LevelUniqueName argument.

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". Available from 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
"Hierarchy" Sets the slicer hierarchy. Will move hierarchies as required. Hierarchy name.
"HierarchiesOnHeaders" Sets hierarchies on headers. Will move hierarchies as required. 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.
"Enabled" Allows toggling of the whether or not the slicer is enabled. Available from Version 8.1. TRUE or FALSE
"OutputRange" Sets the output range of the slicer. Text. This must be the text of the range, not a reference to it.
"WaitForSubmitOnWeb" Allows toggling of the whether or not the slicer reqires submitting. Available from 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")
"ApplyFormatting" Toggle whether formatting is applied. Available from Version 9.1. TRUE or FALSE

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. 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. Will move hierarchies as required. Hierarchy name or names.
"HierarchiesOnHeaders" Sets hierarchies on headers. Will move hierarchies as required. 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.
"DataLabelsVisible" Shows or hides the data labels. Available from Version 9.1. TRUE or FALSE
"Visible" Sets the chart visibility. Available from Version 9.2. TRUE or FALSE
"ClearHighlights" Clear the current chart highlights, requires a toggle cell as the value argument which is reset after the highlights are cleared. =XL3SetProperty("SmallMultiple", "test", "ClearHighlights", A1) See XL3RunSQL#Examples for an example of setting up a trigger cell. A cell containing FALSE, which is set to TRUE by an XL3Link.

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. Available from Version 9. Numeric or "auto". Also works for other axes.
"Visible" Sets the chart visibility. Available from Version 8. TRUE or FALSE
"SeriesFillColor" Sets the series color. Available from Version 9. Numeric values for series index an HTML colour value (#RRGGBB) or a named colour.
"SeriesBorderColor" Sets the series color. Available from Version 9. 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. Available from Version 9.1.
"SeriesDataLabels" Updates the series datalabels from a range. Available from Version 10. Pass 2 parameters: the zero-based index of the data series to update and an XL3Address formula for the range to use (must be on the same worksheet)

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. Available from Version 9. Text
"Visible" Toggle whether or not the map is visible. Available from Version 9.2. TRUE or FALSE

Pager

Available from 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. Available from Version 9.1. TRUE or FALSE

Dashboard

Available from 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

From Version 9.2 you can create targets for different display sizes: 

SimpleDesktop
SimpleMobile
SimpleSmallMobile

Then switch to the sized-matched target by using "Simple*" as the target name.

String

Range

Available from 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

Available from 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

Available from 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.

"Visible" Toggle whether or not the Viewport is visible. Available from Version 9.2. TRUE or FALSE

PagedViewport

Available from Version 9.2. Allows changing a Paged Viewport dynamically.

Property Description Value
"ActiveViewport" Sets the currently active Viewport. Either the index of the Viewport (1-based), or the Viewport name (case-sensitive).
"Visible" Toggle whether or not the Viewport is visible. Available from Version 9.2. TRUE or FALSE

Shape

Available from Version 9.1. Allows changing pictures, grouped shapes and charts.

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

PictureLink

Available from Version 9.1. The name of the picture link can be 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

PowerBI

Available from Version 9.2. Allows changing PowerBI objects.

Property Description Value
"ActivePage" Sets the active page. TRUE or FALSE
"Visible" Sets the object's visibility. 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 – i.e. 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:
    Set1.png
  • We will need to create a number of slicers so that we can change hierarchies and level of detail displayed:
    • Create a slicer that will allow the user to select which hierarchy appears on rows:
      Choose the range K18:L19 as the Slicer's source range
      Update L31 with the value in the First Column
    • Create a slicer that will allow the user to select a particular Region. This will put the user selection in L29:
      Set4.png
    • Create a slicer that will allow the user to select a Product.
    • Create a slicer that will allow the user to select the member level – e.g. member, children, or descendants:
      Choose L13:L15 as the source and update L30 with the selection
  • You must name the object you want to use XL3SetProperty with – in our example our Grid has the Title GridToSet in the Grid Properties screen.
  • Add selections in the Grid for the Region and Product slicers. These will then be updated by the XL3SetProperty formulae.
  • There are several XL3SetProperty statements at the bottom right of our example, updating properties MemberSelectionType; HierarchiesOnRows and HierarchiesOnHeaders.
    • Set MembSelType – the two cells to the right hold the following XL3SetProperty formulae:
      • In cell L21, passing Region as an argument when the user has selected this option from the first slicer:
        =XL3SetProperty("Grid","GridToSet","MemberSelectionType","[Region]",IF(L31="[Region]", $L$30, "Member"))
      • In cell M21, passing Product as an argument when the user has selected this option from the first slicer:
        =XL3SetProperty("Grid","GridToSet","MemberSelectionType","[Product]",IF(L31="[Product]", $L$30, "Member"))
    • Set Rows – the cell to the right holds the following XL3SetProperty formula, passing the value of L31 as an argument for the hierarchy that should appear on rows of the grid:
      =XL3SetProperty("Grid","GridToSet","HierarchiesonRows",$L$31)
    • Set Header – the cell to the right holds the following XL3SetProperty formula, passing the values of cells L32 and L33 as the hierarchies on the headers of the grid:
      =XL3SetProperty("Grid","GridToSet","HierarchiesOnHeaders",$L$32,$L$33)
  • L32 contains an IF function to swap the values of Rows and Headers:
    =IF(L31="[Product]","[Region]","[Product]")
  • 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

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