Skip to Content
Technical Articles
Author's profile photo Marco Matjes

SolDoc Report “Solution Documentation Structure Tree” with extended filter options via Excel VBA macro or Office Script code

[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.

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Fabian Bischoff
      Fabian Bischoff

      Wow Marco,

      this is a great addition to this brand new function!
      Now it¬īs possible to give this informations to the relevant departments.

      But stop, VBA Macro? Is this secure for the next years and the rest of the lifetime of our beloved SolMan? I think a good idea would be this function in "Office Script". This a newer function that is less dangerous. These can also be saved as files and thus easily traded. Also it would be possible to manage or modify this in GIT. But  I am not quite sure if all this is possible with it.

      I would try this and give feedback.

      Thanks a lot till now!

      Kind regards

      Fabian

      Author's profile photo Marco Matjes
      Marco Matjes
      Blog Post Author

      Hi Fabian,

      thanks for you reply. That¬īs indeed an excellent idea!

      I have added a first version of the corresponding Office Script code which works quite well. However, as this is my first attempt coding Office Script and I am not familiar with this language at all, feel free to improve it. The performance is very poor as the getValue() method is executed several times within a loop, and I have no idea so far how to improve this.

      I¬īm curious if someone can solve this issue and provide a more sophisticated code:-)

      Marco