The API command SAPInsertLine is available since version 2.2 SP3 of Analysis for Office. With this function you can insert a new line into a crosstab. There are five input-parameters available:
- RuleID
- Data Source Alias
- Position
- PositionBy
- PositionBy parameters
You can define the RuleID, if you do not define an ID, the system generates one automatically. As Data Source Alias, you have to enter the data source alias, e.g. DS_1. As Position you can choose between Before, After, BelowHeader or BesideHeader. For PositionBy you can enter one of the following elements:
- Dimension
- DimensionResult
- DimensionGroup
- DimensionMember
- HierarchyNode
- Tuple
Here is an example code, more detail information can be found in my book. This code insert a new line after the dimension 0Material.
1 2 |
Dim ret As String ret = Application.Run("SAPInsertLine", "NewLine1", "DS_1", "After", "DIMENSION", "0MATERIAL") |
This example code insert a new line before the dimension 0Material.
1 2 |
Dim ret As String ret = Application.Run("SAPInsertLine", "NewLine1", "DS_1", "Before", "DIMENSION", "0MATERIAL") |
More examples can detailed information can be found in my book SAP Analysis for Office - The Comprehensive Guide.
These posts might also be interesting:
author.
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
Write a comment
Pramod (Friday, 23 April 2021 13:33)
Hi,
Just want to know how do we write DimensionMember syntax? we tried but not getting the result.
Thanks,
Pramod
Tobias (Friday, 23 April 2021 13:42)
Hi Pramod,
just "DIMENSIONMEMBER", "0DIVISION", "1" so after dimension member 1 will be inserted a new line.
Hope this will help you.
Pramod (Saturday, 24 April 2021 16:26)
Hi Tobias,
Thanks for your reply. however it's not working to me. I Have written below syntax. please review the code below and give me some clue.
I'm using the below version
Version is : 2.8.500.94236
Dim lResult As String
lResult = Application.Run("SAPInsertLine", "NewLine1", "DS_1", "Before", "DIMENSIONMEMBER", "/CPD/FRTYP", "ZC01")
Here /CPD/FRTYP is Resource Type Dimension in Rows and have multiple members in Rows i.e ZC01, ZC02, ZC03,ZC04 and I want to insert one new line after ZC01 member. when I debug getting blank for lResult.
When I write the below code it's inserting new line before /CPD/FRTYP column.
Dim lResult As String
lResult = Application.Run("SAPInsertLine", "NewLine1", "DS_1", "Before", "DIMENSION", "/CPD/FRTYP")
Tobias (Monday, 26 April 2021 09:24)
Hi Pramod,
I just tested it with BW/4 and Analysis for Office 2.8.401.94659, and it worked fine. Maybe you can update to the latest version.
Rutuja (Monday, 26 April 2021 16:14)
Hi Tobias,
I have same question as Pramod, I am able to insert new column after Resource Type column. Please check below syntax.
lResult = Application.Run("SAPInsertLine", "NewLine2", "DS_1", "After", "Dimension", "/CPD/FRTYP", "DEC-000589-1")
But not able to write code for adding new row. Could you please suggest what is syntax to add new row?
Thanks in advance!
Tobias (Tuesday, 27 April 2021 08:56)
Hi,
I just tested it with this coding and it worked
Sub test()
lResult = Application.Run("SAPInsertLine", "NewLine1", "DS_1", "Before", "DIMENSIONMEMBER", "ZCITY", "Berlin")
End Sub
A new line was inserted before the city Berlin. Maybe you find an s-note which can help you?