[Updated on September 9, 2023: blog post title extended, Office Script code added and VBA code improved for better performance]
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.
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 🙂
The new report will be available with SPS17 (July 2023).
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.
All SolDoc reports can be found in the Global functions dropdown in the upper right corner.
Select Solution Documentation Structure Tree.
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.
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.
The report is started directly with a click on Execute or with "Schedule..." in the background.
If the report was executed directly, the result is automatically displayed in the Reporting view of the SolDoc.
Business users often request an export of the result to display it in Excel instead of the SolDoc.
In the Global functions dropdown, the file can be downloaded in .txt format via File Download.
First of all, open the Excel application and then import the text file. Note that the file type filter is set to All files (*.*).
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.
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.
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.
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.
Filtering is now possible, especially in the Folder, Scenario and Process columns.
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.
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.
Switch to the Automate tab and create a new script. The code editor appears on the right edge.
Remove the three lines of example code and paste the following code into the editor.
Save the script.
Click on the grey area above the code and change the name of the 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.
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.
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.
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.
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.
- SAP Managed Tags:
3 Comments
