Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
marco_matjes
Explorer
3,603
[Updated on September 9, 2023: blog post title extended, Office Script code added and VBA code improved for better performance]

 

Introduction


With Service Pack Stack 17 (SPS17) for Solution Manager 7.2, SAP provides a fantastic new report for the Solution Documentation ("SolDoc"). In contrast to the traditional Solution Documentation Structure report, the new one reflects the tree structure of the Business Processes as it is displayed in the Browser view.


Structure report (left) vs. Structure Tree report (right)


 

Since the display of the result in SolDoc cannot be filtered and this is not working in a reasonable way even after downloading and importing it into Microsoft Excel, I have developed a mini-macro in Excel VBA that can be quite useful.

In this blog post I will guide you step by step through the general usage of the report and the preparation in Excel. No coding required, just Copy & Paste 🙂

 

Step by step guide


 

1. How to use the "Solution Documentation Structure Tree" Report


The new report will be available with SPS17 (July 2023).

 

1.1      Structure- vs. Structure Tree-Report


The existing Structure Report does not split the columns according to the process hierarchies. This can be done manually, if needed, as explained in the SAP Support Wiki article Create a Business Process hierarchy tree.


 

The output of the new Structure Tree Report is based on the tree structure of the process hierarchies. This option was particularly requested by business users who would like to get their SolDoc processes as an export.


 

However, with both reports the table-like display does not offer any filter options.


 

1.2 Execution of the report in the Solution Documentation


All SolDoc reports can be found in the Global functions dropdown in the upper right corner.


 

Select Solution Documentation Structure Tree.


 

1.3 Restriction to a Scope


The scope which is selected to display the SolDoc has no influence when running the report. Therefore, at this point it does not matter which scope is displayed on the screen. The selected branch, on the other hand, is the one for which the report will be generated.


This scope does not matter for the report


 

The scope for the report is defined in the report definition which can be accessed with the Open button where you can also make further settings and save the adjustments as a variant.



Scope selection for the report output


 

The report is started directly with a click on Execute or with "Schedule..." in the background.


 

1.4 Output of the result in the SolDoc


If the report was executed directly, the result is automatically displayed in the Reporting view of the SolDoc.


 

2. Importing the result into Microsoft Excel


Business users often request an export of the result to display it in Excel instead of the SolDoc.

 

2.1 Download as a text file


In the Global functions dropdown, the file can be downloaded in .txt format via File Download.



 

2.2 Opening the file


First of all, open the Excel application and then import the text file. Note that the file type filter is set to All files (*.*).


 

2.3 Converting the text file to columns


If the file was opened in exactly this way, the text conversion assistant lis launched automatically. Confirm the first step with Next to split the columns using delimiters.


 

The delimiter in this file is a tab stop. Confirm the second step.


 

In the third step, change the data format for the first column from Standard to Text so that the level sequence is preserved. Finish the text conversion assistant.


 

3 Modifying the Excel file using a VBA macro


The imported file with the hierarchies is rather difficult to filter in Excel. However, this can be significantly improved with a few lines of VBA code.


 

3.1 Pasting the VBA code


Open the code editor with Alt+F11 and create a new module with a right click on the free area in the top left area.


 

Copy the following code into the large area on the right side of the VBA editor.
Sub SolDoc_StructureTree_AddFilters()
For i = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
Select Case Cells(1, i)
Case "Folder", "Scenario", "Process", "Ordner", "Szenario", "Prozess"
For j = 3 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
If IsEmpty(Cells(j, i)) And _
(i = 2 Or (Cells(j, i - 1).Font.ColorIndex = 15 And IsEmpty(Cells(j, 1)))) Then
Cells(j, i) = Cells(j - 1, i)
Cells(j, i).Font.ColorIndex = 15
End If
Next
End Select
Next
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
End With
With Rows(1)
.Interior.Color = vbYellow
.AutoFilter
End With
End Sub


 

3.2 Running the VBA code


Place the cursor anywhere within the code and press F5 or click the small green arrow to run the code.


Bring the Excel spreadsheet to the foreground, for example by pressing Alt+F11 again or closing the window with the VBA editor.

The code has filled some cells in the Folder, Scenario, and Process columns. Additionally, the top row is frozen and a filter has been added to the columns.


 

3.3 Filtering the table


Filtering is now possible, especially in the Folder, Scenario and Process columns.



 

3.4 (Optional:) Exporting and importing the VBA macro


You can export the VBA code as a bas file and later import it into any other Excel file from a SolDoc export with just a few clicks.

 

Exporting the module

It is best practice to give the module a descriptive name before exporting it as a file with a right-click.



 

Importing the module

Instead of copying and pasting the code into a new module each time, you can import the .bas file with a right click in the upper left area.



 

4 Running an Office Script instead of VBA code


As an alternative to VBA code, a more modern Office script for Excel can be created. The advantage is that this script can be made available to other users and used by them directly in Excel.

Office Scripts are not available in all versions of Excel. Your Excel version must provide an Automate tab. Details can be found in this article from Microsoft.

 

4.1 Creating an Office Script with the code


Switch to the Automate tab and create a new script. The code editor appears on the right edge.


New Office Script with example code


 

Remove the three lines of example code and paste the following code into the editor.
function main(workbook: ExcelScript.Workbook) {
 const sheet = workbook.getActiveWorksheet()
 for (let i = 2; i <= sheet.getUsedRange().getLastColumn().getColumnIndex() + 1; i = i + 1) {
   switch(sheet.getCell(0, i - 1).getValue()) {
     case "Folder":
     case "Scenario":
     case "Process":
     case "Ordner":
     case "Szenario":
     case "Prozess":
       for (let j = 3; j <= sheet.getUsedRange().getLastRow().getRowIndex() + 1; j = j + 1) {
         if ((sheet.getCell(j - 1, i - 1).getValue() == "") && (i == 2 || ((sheet.getCell(j - 1, i - 2).getFormat().getFont().getColor() == "#C0C0C0") && (sheet.getCell(j - 1, 0).getValue() == "")))) { sheet.getCell(j - 1, i - 1).getFormat().getFont().setColor("C0C0C0"); sheet.getCell(j - 1, i - 1).setValue(sheet.getCell(j - 2, i - 1).getValue()) }
         }
       break
   }
 }
 sheet.getFreezePanes().freezeRows(1)
 sheet.getAutoFilter().apply(sheet.getRange().getUsedRange())
 sheet.getRange("1:1").getFormat().getFill().setColor("yellow")
}

Save the script.


Click on the grey area above the code and change the name of the script.


 

4.2 Running the Office Script


Click Run to execute the code.


 

The result is exactly the same as when executing the VBA code explained above. However, the runtime of the current version is significantly longer than with VBA.


 

4.3 Re-using the Office Script


An Office Script must only be saved once and can be re-used in all Excel spreadsheets. You can find all existing Office Scripts in the Automate tab. Just click the one you want to execute.


 

In this example, the Office Script is protected and only available for me. Depending on the location where it is saved, it can be shared with other users in the organization.


 

In a nutshell



  • Call the Structure Tree Report

  • Select a scope in the report definition

  • Run the report

  • Download the result as a file

  • Import the file into Excel

  • Copy the macro code into a VBA module or import the module into Excel (alteratively: create/open an Office Script)

  • Run the code


 

Limitations


The Solution Documentation Structure Tree report displays all levels of the Folder type in the same column, one below the other...



...and thus also in the Excel spreadsheet after the import, which makes filtering via the folder levels difficult.


 

Conclusion


The Solution Documentation Structure Tree report for the Solution Documentation closes a gap in the representation of the process structure, especially for business users without access to the solution documentation or with little or no knowledge of its handling.

IT users can export the entire structure or only a scope of it with just a few clicks and make it available to the recipient as an Excel file.

My way for modifying the Excel file with a small VBA macro (or an Office Script) makes filtering the table easier, even if nested folders cannot yet be filtered in the current version.

Suggestions for improvements or optimization are welcome to be written in the comments, as well as comments on the new report and/or likes if you like this blog post 😉

Finally, it should be noted that the "old" Structure Report also has its right to exist. Depending on the use case, you always have to decide which report is the most suitable.

 

Have fun with the new tool!

Marco Matjes

 

All screenshots were taken by me in SAP Solution Manager 7.2 SPS17 and Microsoft Excel 365.
3 Comments
Labels in this area