Graph using Excel from ALV
This article explains how we can make use of MS Excel Inplace functionality in the SAP ALV toolbar with macro recording to display a Graph for the records displayed in an ALV.
It is quite often that we have downloaded the standard/custom SAP ALV report as Excel document to the front-end computer and created Graph, converting to pivot table etc. These repetitive tasks in Excel can be recorded as an Excel macro and we can upload this excel layout containing macro to SAP as a new ALV layout, so that, when you run the report/selecting the layout, immediately the output will be displayed as Excel along with the Graph. To achieve this functionality, below mentioned steps needs to be executed.
1. Execute the report and display the output in ALV.
2. Click on Change Layout (Ctrl+F8).
3. Select the View tab and Choose Microsoft Excel in the Preferred View Dropdown list. A Window appears specifying the list of all the available templates. Select ‘CUS_SAP_SM.xls’ and click on Specify Selection.
4. The Output now appears in the Excel format. Click on the file option and download the template to the local machine. It is required that the name of template must be prefixed with the CUS_* while uploading. Therefore it is suggested the name of downloaded template should be prefixed with CUS_ followed by a suitable description.
5. The excel file contains multiple sheets.
a. The view includes the detailed information.
b. In addition to the Format sheet, the following sheets are included:
1. Header (only in full screen mode): Includes the header information.
2. Pivot: Includes the details of list of data.
3. Sub1-Sub10: Displays Sub-total Corresponding level.
4. RawData: Displays data.
5. Raw Header: Displays the header information.
6. Delete all the sheets from the template except for Header, Pivot and RawData and add a new sheet where graph needs to be displayed.
7. Click on Insert in application toolbar menu and Select Pivot Chart. A window would appear asking for the table for which pivot chart needs to be created. Specify the name of RawData and Click OK.
8. A pivot chart and a pivot table automatically get added to the sheet. In the Right hand side of the sheet, Pivot table field list can also be viewed. By making use of this pivot table field list, Graph is designed.
The field that needs to be displayed in Y-Axis is dragged to the ∑ Values in the Pivot table field list.
The field that needs to be displayed in X-Axis is dragged to the Axis field or Category Axis in the Pivot table field list.
The field for which the value needs to be displayed in graph is dragged to the Legend Fields in the Pivot table field list.
9. The Design, chart type and the layout of the graph can be changed. User can go to Design option in the application tool bar and select options accordingly.
10. It should be of the utmost concern that if the user changes values in the RawData manually, the same changes should be reflected in the Graph. This task can be automated using macro.
In order to write a macro, user needs to press ALT + F11. Select the new sheet added for Graph under Microsoft Excel Objects and write the code to refresh and update the graph if any changes are done in RawData. The exact name of Pivot table should be written in the code. Save and close the Window.
Private Sub worksheet_activate()
11. The template now needs to be uploaded to SAP. SAP has provided a standard functionality to upload it. Go to SE38 and execute the “BCALV_BDS_MAINTENANCE” program. Click on the radio button Import templates and execute.
12. Select the Language as ‘EN’ and click on Upload template.
Before uploading the template, two things need to be checked.
A. Make sure the name of template is prefixed with CUS_.
b. The template should not be opened while uploading.
13. The same template can be checked in OAOR transaction. Specify the Class name as ‘ALVLAYOUTTEMPLATES’, Class Type as ‘OT’ and execute it.
14. Under CUS_STANDARD_TEMPALTE, user can confirm whether the template has been uploaded. Also, if a user wants to delete the template, he needs to select the template and click on delete option. The template will be deleted from the CUS_STANDARD_TEMPLATE.
15. As the template is uploaded, user can execute the report and check the graph functionality. Click on the Microsoft Excel (Control + Shift + F7).
16. Select the template and presses continue.