This Blog explains how to create very useful and easy to read reports out of the standard SAP NetWeaver “Collaboration Room Inventory” reports by using a Microsoft Excel macro. This macro will automatically create tables and charts according to a raw file you need to extract before out of your Portal. The reports (tables and charts) will be grouped by room category, room template and room creation date. The report will also offer a governance overview grouped by category, which shows you how many rooms are too big or no visited anymore, and a summary over all rooms, also with a governance notice.
So the benefit of using the “Reporting Macro for Collaboration Rooms” is to govern your collaboration rooms hosted in your portal in an easy and comprehensible way. By creating these reports you will be able get more transparency in your collaboration rooms and with that, you become able to clean up collaboration repositories from data which is not needed anymore.
The NetWeaver portal provides the so called “Room Inventory” report in the following versions:
(only these versions of reports are compatible with this reporting tool)
Navigate to Content Administration > KM Content > Toolbox > Reports and open the “Room Inventory” Report.
You should not use Custom Properties at this point in time. The current version of the standard Excel Macro will not work properly on raw data containing custom properties. However, it is possible to report also on custom properties, and maybe in a later stage the standard Excel macro will support this feature as well, but up to know you will have to modify the macro by yourself to enable this feature.
Before starting the report, please specify a path where the generated CSV-file should be stored. Once the report creation is finished, you can download the corresponding CSV-file to an appropriate file system location.
Note: The current Excel Macro expects a CSV-file in format of the report delivered with NetWeaver 04 SP19 or NetWeaver 04s SP12! For other versions of the CSV file the macro needs to be adapted manually.
On the bottom of the Excel window you will see several tabs representing the different Sheets in the Excel file.
Click on the Sheet “Settings”. That’s where the execution of the Macro can be started. On this sheet you can find 2 Buttons and 4 different boxes. Please don’t change the layout of this sheet too much, because some cells are assigned to range names!
The boxes are:
The buttons are:
To start reporting just click the “Start”-button. If the path you entered in the corresponding field of the Settings sheet does not exist, an error message will be displayed which has to be confirmed by clicking the “OK” button. Then a file open dialog box will open and prompt for a raw file. Here you have to select one of the extracted and to the .xls format converted files.
After you selected a file, the macro automatically starts running. All sheets in the workbook will be refreshed and recalculated with the current data from the raw file you have selected before.
The steps the macro executes are as follows:
Now I hope that my tool will help you with creating nice and usefull reports.
So the benefit of using the “Reporting Macro for Collaboration Rooms” is to govern your collaboration rooms hosted in your portal in an easy and comprehensible way. By creating these reports you will be able get more transparency in your collaboration rooms and with that, you become able to clean up collaboration repositories from data which is not needed anymore.
Preparation: Extract raw data from the SAP NetWeaver Portal portal
The NetWeaver portal provides the so called “Room Inventory” report in the following versions:
(only these versions of reports are compatible with this reporting tool)
- NetWeaver 04: starting with SP19 or higher
- NetWeaver 04s: starting with SP10 or higher
Open the NetWeaver Portal Report
First step is to extract the raw data out of the SAP NetWeaver Portal. Therefore you need to navigate to Content Administration > KM Content > Toolbox > Reports and start the “Room Inventory” Report.Navigate to Content Administration > KM Content > Toolbox > Reports and open the “Room Inventory” Report.
Start the NetWeaver Portal Report
Before really starting the report, please make sure that the following parameters were set:- Calculate Size of Documents
- Calculate Size of Documents Versions
- Analyse Invitation/Uninvitations
You should not use Custom Properties at this point in time. The current version of the standard Excel Macro will not work properly on raw data containing custom properties. However, it is possible to report also on custom properties, and maybe in a later stage the standard Excel macro will support this feature as well, but up to know you will have to modify the macro by yourself to enable this feature.
Before starting the report, please specify a path where the generated CSV-file should be stored. Once the report creation is finished, you can download the corresponding CSV-file to an appropriate file system location.
Note: The current Excel Macro expects a CSV-file in format of the report delivered with NetWeaver 04 SP19 or NetWeaver 04s SP12! For other versions of the CSV file the macro needs to be adapted manually.
Convert the CSV file to XLS format
You need to change the file format of the CSV-file you exported from the Portal into an Excel file format. To convert it from CSV-format to an Excel file, open the CSV-file in Excel and just save it in an Excel format, i.e. “Microsoft Office Excel Workbook (*.xls)”. The Excel Macro only works properly with this file format.Usage of the Excel Macro
Download the Excel Macro file
- Download the generic Excel Macro attached to this Blog and save it, for example to your local PC or on a file share.
Download the Tool: SAP NetWeaver Portal Collaboration Reporting Tool
Open the Excel Macro file
Now open the Excel Macro file.On the bottom of the Excel window you will see several tabs representing the different Sheets in the Excel file.
Click on the Sheet “Settings”. That’s where the execution of the Macro can be started. On this sheet you can find 2 Buttons and 4 different boxes. Please don’t change the layout of this sheet too much, because some cells are assigned to range names!
The boxes are:
- "Path of raw data sheet"
In this input field you can enter or paste a path where you expect to store your raw data files. Each time you click the “START” button on top of the page, a “file open dialog” will be displayed for the folder you provided in this field where you can select the raw data file (the one with the .xls format).you want to process.
- "Included Sheets"
This table just gives you an overview over the sheets which are included in this Excel workbook and is created automatically
- "Specify Reporting Columns" – Table "Ranges"
The column “Field Name in raw data sheet” contains all the required fields in the raw data file. This means that your raw data file must provide at least these fields (the first line of the raw data file contains the field names). In case the name of a single field differs from the name in this table, you have to change the name here in the table.
The second table column called “Column” contains the column headline where each field is listed in the raw file. Never change any of these values - they are calculated automatically after selecting the raw data file. Therefore the field names must be maintained correctly. These cells are also assigned to range names referred to by the macro code while creating the reports.
In column “Range Name” you can see the range names on which the cells in column “Column” are assigned to.
At the end of this table you can see some fields which will automatically be added to the raw sheet. These fields provide some detailed information about the room creation date and its activity status. Please don’t change anything here!
- "End date for weekly reporting in sheet "Date" "
In this entry box you can specify a date, which will be used as end date for the creation date report in the Sheet “Date”. This report shows the number of rooms created in the four calendar weeks preceding the date you entered in this field. Rooms which where created later than this date will not be reported in sheet “Date” (and only there!). You can set the current date by pressing the button “Set Date to current”.
The buttons are:
- "Set Date to current"
By pressing this button the formula “=now()” will be inserted into the field “End date for weekly reporting in sheet "Date" “. Use this button just in case you manually entered an end date before.
- "Start"
With this button you can start the report generation process.
Start Reporting
Once you extracted the raw data from your Portal and converted it to Excel format, you can start the reporting macro. Please make sure that the column headings/field names on sheet “Settings” are the same as in the raw file, as described in the chapter above.To start reporting just click the “Start”-button. If the path you entered in the corresponding field of the Settings sheet does not exist, an error message will be displayed which has to be confirmed by clicking the “OK” button. Then a file open dialog box will open and prompt for a raw file. Here you have to select one of the extracted and to the .xls format converted files.
After you selected a file, the macro automatically starts running. All sheets in the workbook will be refreshed and recalculated with the current data from the raw file you have selected before.
The steps the macro executes are as follows:
- Import the raw file and insert all the data on the worksheet “Raw”.
- Insert the current range names according to field names in sheet “Settings” from sheet “Raw” and calculate room creation calendar week, quarter, year and activity status. These 4 values will be added to each room in sheet “Raw” as additional columns.
- Calculate data for tables and charts for the sheets “Category”, “Template” and “Date”; insert and adapt these values on the corresponding sheets and customize the sheet themselves
- Prepare and insert data for sheet “Governance” and customize the sheet.
- List every room in sheet “All Rooms” and insert governance tags if it is appropriate and customize the sheet.
Now I hope that my tool will help you with creating nice and usefull reports.
Your feedback and improvement ideas are always welcome!
Regards, Steffen Weber
11 Comments
