xtt->MERGE( IV_BLOCK_NAME = ‘R’ IS_BLOCK =
{"TITLE"=>"Title", "T"=>[{"GROUP"=>"GRP A", "CAPTION"=>"<Caption 1 />", "DATE"=>"2020-10-21", "SUM1"=>5971.44, "SUM2"=>5021.46}, {"GROUP"=>"GRP C", "CAPTION"=>"<Caption 2 />", "DATE"=>"2020-10-19", "SUM1"=>2276.75, "SUM2"=>7717.2}, {"GROUP"=>"GRP B", "CAPTION"=>"<Caption 3 />", "DATE"=>"2020-10-20", "SUM1"=>3031.38, "SUM2"=>445.22}, {"GROUP"=>"GRP A", "CAPTION"=>"<Caption 4 />", "DATE"=>"2020-10-21", "SUM1"=>9476.79, "SUM2"=>3683.27}, {"GROUP"=>"GRP A", "CAPTION"=>"<Caption 5 />", "DATE"=>"2020-10-21", "SUM1"=>4604.0, "SUM2"=>5525.85}, {"GROUP"=>"GRP C", "CAPTION"=>"<Caption 6 />", "DATE"=>"2020-10-19", "SUM1"=>2159.71, "SUM2"=>2260.94}, {"GROUP"=>"GRP B", "CAPTION"=>"<Caption 7 />", "DATE"=>"2020-10-20", "SUM1"=>2490.3, "SUM2"=>6388.81}, {"GROUP"=>"GRP A", "CAPTION"=>"<Caption 8 />", "DATE"=>"2020-10-21", "SUM1"=>5476.81, "SUM2"=>406.18}, {"GROUP"=>"GRP D", "CAPTION"=>"<Caption 9 />", "DATE"=>"2020-10-18", "SUM1"=>689.66, "SUM2"=>518.6}, {"GROUP"=>"GRP C", "CAPTION"=>"<Caption 10 />", "DATE"=>"2020-10-19", "SUM1"=>5451.87, "SUM2"=>4061.85}, {"GROUP"=>"GRP D", "CAPTION"=>"<Caption 11 />", "DATE"=>"2020-10-18", "SUM1"=>4573.55, "SUM2"=>5436.0}, {"GROUP"=>"GRP C", "CAPTION"=>"<Caption 12 />", "DATE"=>"2020-10-19", "SUM1"=>1196.35, "SUM2"=>522.46}, {"GROUP"=>"GRP C", "CAPTION"=>"<Caption 13 />", "DATE"=>"2020-10-19", "SUM1"=>7686.95, "SUM2"=>2727.98}, {"GROUP"=>"GRP D", "CAPTION"=>"<Caption 14 />", "DATE"=>"2020-10-18", "SUM1"=>978.18, "SUM2"=>295.55}, {"GROUP"=>"GRP D", "CAPTION"=>"<Caption 15 />", "DATE"=>"2020-10-18", "SUM1"=>71.12, "SUM2"=>8878.56}], "DATE"=>"2020-10-21", "TIME"=>"12:12:12", "DATETIME"=>"20201021121212"}).
xtt->MERGE( IV_BLOCK_NAME = ‘A’ IS_BLOCK =
{"INFO"=>"String 1 "}{"INFO"=>"String 2 "}{"INFO"=>"String 3 "}).
Formulas in XTT
Direct editing of formulas from the code is prohibited, as this approach is prone to errors and is not very informative.
In addition, the internal representation of formulas in Excel xlsx (absolute) and Excel xml (relative) does not allow using a uniform approach if formulas were filled from the ABAP side. Instead, there are a few (hopefully simple) rules for editing formulas in MS Excel itself.
Copying data
New lines (and cells) are added by copying the original line in the template. Formulas are copied along with cell data, borders, and other formatting.
To prevent the formulas from “shifting” during copying cells, you can use several methods:
Relative links in XLSX
The most native and reliable way for Excel is to calculate distance from the current cell where we write the formula, you could use =OFFSET() in conjunction with =INDIRECT() for creating relative formulas.
those, to get the sum of the previous three cells (1,2,3), you need to shift by -3 cells by columns and expand the range by 3 cells
The offset can also be specified directly in INDIRECT(“RC[-3]”, 0), in such formulas it can also be useful:
- =ROW() &
- =COLUMN() functions
which, without passing arguments, return the current row and column
$ sign for rows
It is not very convenient to specify offsets from the current cell, therefore, for simplicity, a special rule was introduced:
If you specify the current row number with $ sign, it will be replaced at runtime with actual row number
The final report will be like this
PS: For ;direction=column (table output by columns) this rule will accordingly work for formulas in this form $E
Shared formulas
Previously, AOK & XTT had “mysterious” formulas disappearing
This bug occurred when a relative link was encountered several times in a row
In the current version (I hope), the conversion from absolute reference to relative and vice versa happens without problems
Named cell ranges
By referring to a named range that will be modified at runtime
- The entire range will be stretched once
- If the name of the range ends with ‘_’ this range will be multiplied and the final list (with all ranges) will be replaced in the formula itself
Sum of children
For the sum of children it is not at all necessary to use the previous method with named cells or to create hierarchies
If you need to summarize a table field, you can use standard Excel tools
Array formulas
Array formulas are also supported
Word & PDF
But what if you need to sum the field values not only in Excel, but also in Word or Pdf? In this case, you can use the aggregation functions ;func= SUM | AVG | COUNT | FIRST
Conditional formatting formulas
For formulas in conditional formatting, it is advisable to specify the scope as whole columns
