| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "XL3DoWriteback"
(→Parameters) |
|||
| (11 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | + | Allows a writeback to be performed on demand. It can be used in conjunction with [[XL3Link]] to trigger the execution. | |
| + | |||
| + | This setup is designed to allow the calculation of any number of cells to be separated from the sometimes slow writeback process. The final calculations for the cells can all be submitted in one batch, without having to wait for the writeback to occur between each calculation. | ||
==Syntax== | ==Syntax== | ||
| − | {{Code|XL3DoWriteback( PerformWriteback, WriteValue, Connection, [Hierarchy1], [Member1],…, [ | + | {{Code|XL3DoWriteback( PerformWriteback, WriteValue, Connection, [Hierarchy1], [Member1],…, [Hierarchy100], [Member100] )}} |
| + | |||
| + | {{Excel Parameter Limitation|This allowed up to 13 hierarchy-member pairs.}} | ||
==Parameters== | ==Parameters== | ||
| Line 11: | Line 15: | ||
|- | |- | ||
| {{Code|PerformWriteback}} | | {{Code|PerformWriteback}} | ||
| − | | A boolean value ({{TRUE}} or {{FALSE}}) specifying whether to actually perform the writeback. | + | | A reference to a range containing a boolean value ({{TRUE}} or {{FALSE}}) specifying whether to actually perform the writeback. That cell's value will be reset to {{FALSE}} following the writeback operation |
|- | |- | ||
| {{Code|WriteValue}} | | {{Code|WriteValue}} | ||
| Line 20: | Line 24: | ||
{{Standard_HierarchyMember_List}} | {{Standard_HierarchyMember_List}} | ||
|} | |} | ||
| + | |||
| + | ===Example=== | ||
| + | |||
| + | This example shows an input template that could be achieved using XL3DoWriteback. While this template has a single column, and writes back for just 5 members on 3 dimensions, the same concept could be applied to many members. To use it: | ||
| + | # enter the new values in column C. These could be entered by: | ||
| + | #* Directly typing the values | ||
| + | #* Copy and paste from another document | ||
| + | #* Formula calculations based on other values | ||
| + | #* XL3Lookup formulae | ||
| + | # Click the XL3Link in cell A1, which updates B1 to be {{TRUE}} | ||
| + | # The XL3DoWriteback formulae in column E are triggered. The writeback speed can vary depending on your cube | ||
| + | # When the writeback is complete, cell B1 is automatically reset to {{FALSE}} | ||
| + | |||
| + | {| class="wikitable" | ||
| + | |- | ||
| + | ! !! A !! B !! C !! D !! E | ||
| + | |- | ||
| + | ! 1 | ||
| + | | style="color:#900" | {{Code|1==XL3Link(,"Submit Writeback",,XL3Address($B$1),TRUE)}} || style="color:#900" | {{FALSE}} || || || | ||
| + | |- | ||
| + | ! 2 | ||
| + | | || || || || | ||
| + | |- | ||
| + | ! 3 | ||
| + | | {{Code|1==XL3Member(1,"[Department]","[Department].&[0001]")}} || || style="color:#009" | 1500 || || style="color:#090" | {{Code|1==XL3DoWriteback($B$1,$C3,1,"[Measures]","[Measures].[Budget Amount]","[Date].[Calendar]","[Date].[Calendar].[Date].&[1128]","[Department]",$A3)}} | ||
| + | |- | ||
| + | ! 4 | ||
| + | | {{Code|1==XL3Member(1,"[Department]","[Department].&[0002]")}} || || style="color:#009" | 1400 || || style="color:#090" | {{Code|1==XL3DoWriteback($B$1,$C4,1,"[Measures]","[Measures].[Budget Amount]","[Date].[Calendar]","[Date].[Calendar].[Date].&[1128]","[Department]",$A4)}} | ||
| + | |- | ||
| + | ! 5 | ||
| + | | {{Code|1==XL3Member(1,"[Department]","[Department].&[0003]")}} || || style="color:#009" | 1250 || || style="color:#090" | {{Code|1==XL3DoWriteback($B$1,$C5,1,"[Measures]","[Measures].[Budget Amount]","[Date].[Calendar]","[Date].[Calendar].[Date].&[1128]","[Department]",$A5)}} | ||
| + | |- | ||
| + | ! 6 | ||
| + | | {{Code|1==XL3Member(1,"[Department]","[Department].&[0004]")}} || || style="color:#009" | 2700 || || style="color:#090" | {{Code|1==XL3DoWriteback($B$1,$C6,1,"[Measures]","[Measures].[Budget Amount]","[Date].[Calendar]","[Date].[Calendar].[Date].&[1128]","[Department]",$A6)}} | ||
| + | |- | ||
| + | ! 7 | ||
| + | | {{Code|1==XL3Member(1,"[Department]","[Department].&[0005]")}} || || style="color:#009" | 2000 || || style="color:#090" | {{Code|1==XL3DoWriteback($B$1,$C7,1,"[Measures]","[Measures].[Budget Amount]","[Date].[Calendar]","[Date].[Calendar].[Date].&[1128]","[Department]",$A7)}} | ||
| + | |- | ||
| + | |} | ||
| + | |||
| + | The finished input template: | ||
| + | |||
| + | [[File:XL3DoWriteback.png]] | ||
| + | |||
| + | ====Legend==== | ||
| + | * <span style="color:#900">Control block</span>: used to trigger the XL3DoWritebacks' writeback action. | ||
| + | * <span style="color:#009">New values</span>: these values are written to the cube. | ||
| + | * <span style="color:#090">XL3DoWriteback block</span>: these XL3DoWriteback formulae control the cross-section of the cube the values are written to. | ||
| + | |||
| + | ===Notes=== | ||
| + | Writeback for the workbook must be enabled in the Workbook Options screen for this to work. | ||
| + | |||
| + | |||
| + | ===Web Restrictions=== | ||
| + | Web edition does not support using an XL3Lookup or other XLCubed formulae for the source values of the XL3DoWriteback - the XL3Lookups are run after the XL3DoWriteback. You would need to use a grid in the instance where the data to be written is being sourced from the cube. | ||
==See Also== | ==See Also== | ||
* [[Formula Reference]] | * [[Formula Reference]] | ||
| + | * [[XL3LookupRW|XL3LookupRW formula reference]] | ||
[[Category:Formulae]] | [[Category:Formulae]] | ||
[[Category:Writeback]] | [[Category:Writeback]] | ||
Latest revision as of 11:07, 2 May 2023
Allows a writeback to be performed on demand. It can be used in conjunction with XL3Link to trigger the execution.
This setup is designed to allow the calculation of any number of cells to be separated from the sometimes slow writeback process. The final calculations for the cells can all be submitted in one batch, without having to wait for the writeback to occur between each calculation.
Contents
[hide]Syntax
XL3DoWriteback( PerformWriteback, WriteValue, Connection, [Hierarchy1], [Member1],…, [Hierarchy100], [Member100] )
Before XLCubed Version 9, this formula was limited to 30 parameters. This allowed up to 13 hierarchy-member pairs.
Parameters
| Parameter | Description |
|---|---|
| PerformWriteback | A reference to a range containing a boolean value (TRUE or FALSE) specifying whether to actually perform the writeback. That cell's value will be reset to FALSE following the writeback operation |
| WriteValue | The value to be written. Typically an Excel cell reference. |
| Connection | Connection number to use |
| Hierarchy1,…, HierarchyN | Name of the hierarchy that the following member applies to e.g. "Measures" or "[Customer].[Customer Geography]" |
| Member1,…, MemberN | Either a single member unique name or an XL3Member formula |
Example
This example shows an input template that could be achieved using XL3DoWriteback. While this template has a single column, and writes back for just 5 members on 3 dimensions, the same concept could be applied to many members. To use it:
- enter the new values in column C. These could be entered by:
- Directly typing the values
- Copy and paste from another document
- Formula calculations based on other values
- XL3Lookup formulae
- Click the XL3Link in cell A1, which updates B1 to be TRUE
- The XL3DoWriteback formulae in column E are triggered. The writeback speed can vary depending on your cube
- When the writeback is complete, cell B1 is automatically reset to FALSE
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | =XL3Link(,"Submit Writeback",,XL3Address($B$1),TRUE) | FALSE | |||
| 2 | |||||
| 3 | =XL3Member(1,"[Department]","[Department].&[0001]") | 1500 | =XL3DoWriteback($B$1,$C3,1,"[Measures]","[Measures].[Budget Amount]","[Date].[Calendar]","[Date].[Calendar].[Date].&[1128]","[Department]",$A3) | ||
| 4 | =XL3Member(1,"[Department]","[Department].&[0002]") | 1400 | =XL3DoWriteback($B$1,$C4,1,"[Measures]","[Measures].[Budget Amount]","[Date].[Calendar]","[Date].[Calendar].[Date].&[1128]","[Department]",$A4) | ||
| 5 | =XL3Member(1,"[Department]","[Department].&[0003]") | 1250 | =XL3DoWriteback($B$1,$C5,1,"[Measures]","[Measures].[Budget Amount]","[Date].[Calendar]","[Date].[Calendar].[Date].&[1128]","[Department]",$A5) | ||
| 6 | =XL3Member(1,"[Department]","[Department].&[0004]") | 2700 | =XL3DoWriteback($B$1,$C6,1,"[Measures]","[Measures].[Budget Amount]","[Date].[Calendar]","[Date].[Calendar].[Date].&[1128]","[Department]",$A6) | ||
| 7 | =XL3Member(1,"[Department]","[Department].&[0005]") | 2000 | =XL3DoWriteback($B$1,$C7,1,"[Measures]","[Measures].[Budget Amount]","[Date].[Calendar]","[Date].[Calendar].[Date].&[1128]","[Department]",$A7) |
The finished input template:
Legend
- Control block: used to trigger the XL3DoWritebacks' writeback action.
- New values: these values are written to the cube.
- XL3DoWriteback block: these XL3DoWriteback formulae control the cross-section of the cube the values are written to.
Notes
Writeback for the workbook must be enabled in the Workbook Options screen for this to work.
Web Restrictions
Web edition does not support using an XL3Lookup or other XLCubed formulae for the source values of the XL3DoWriteback - the XL3Lookups are run after the XL3DoWriteback. You would need to use a grid in the instance where the data to be written is being sourced from the cube.
