Hello Colleagues,
With this blog post, I would like to explain some of the features that are available with the SAP ByDesign Excel Add-in.
I have noticed that many of the end users do not use the full potential of the Excel add-in. Rather they run the report in the system and export the data to excel or CSV and then work with the downloaded file. However this creates a challenge when there is change in the data or change in the view or selection. which means user will need to adjust the report again in the system and download the file again. hence any additional work done in the first file, needs a rework (For example, Pivot table, Vlookup, Chats etc.)
So following topics can help to address the some of the challenges and can help user to work faster productively.
In this document, I would not cover the basic functions for example Inserting report, How to login, refresh the data, Change the selection, Change the view etc. But this would cover the some of the not very well known options available in Excel add-in.
Please note, this is an general explanation and experience sharing of the features.
With that, Let's begin :
================================================================
1) Display settings
In SAP Excel Add-in, there is an option which will allow you to see the report with different options
- Grid Display : By Default when you insert a report in the excel, system will use this display settings
- Excel Table : With this display option,
--> Report data will be formatted with alternating colour lines,
--> With Filters for the headers. which can be turned off if not required.
--> You can also insert the total row.
--> You can also insert Slicer
--> Remove Duplicates
i.e Once enabled, some of the excel table functionality can be easily accessed under the table design menu option.
- Pivot Display :
This will Automatically create the pivot table based on the report data.
So you can directly generate Pivot table and use excel Pivot table options. If user needs to create a pivot after exporting the data from the system, this option is very useful as it would save the time in downloading the file, Opening it up, Adding column headers and then inserting a pivot. More over, if there a change in the data, or you need to refresh the data, column sequence or addition removal of columns can directly done in excel and pivot can be refreshed easily without need of download and all other procedures. 🙂
2) Insert Header
This option allows you to insert the header data meaning : system details, Last refreshed time, Currency converted to:, Access Context and also the Selection.
3) Insert Chart
With this option, you can insert the chart in the excel using data that is retrieved from the system. This also means that you have most of the excel chart feature also available with you.
4)Freeze Pane
You can free the panes in the inserted reports as well. This same feature is also available in the system when you run the report.
as a normal excel, this will be helpful when you have large number of columns and want to keep the row headers or first few columns as fixed.
5) Convert currencies
When you run the report in excel but you need to convert the amounts to different currency using the exchange rates maintained in the system. you can use this option. Advantage here is that you don't need to change your selection i.e display currency in the report selection.
6) Report Navigation
just like the system report, you can also use report navigation option to navigate from one report to another report with the selection parameters that is set with the source report. This will eliminate the need of inserting the report in the excel or better downloading the report from the system when you are working in excel.
7) Name Manager
When you insert the report in the excel using excel add-in, add-in also creates the Name for the data range. this name you can easily use in the formula. This means that your formula will be dynamic and you don't have to worry about the data selection in formula as every time you refresh the data, system automatically updates the data range (for example for additional rows or columns). Also helpful in faster processing of the formula with large amount of data.
you can access the names (report title or the data) by going to formula menu option of excel and click on name Manager.
For each report inserted in the excel, you will see two names defined.
For example : SAP_ByDesign_R0001 -> For the data of the first report that is inserted
SAP_ByDesign_T0001RP_TITLE -> For the title of the first report that is inserted in excel.
this names you can use in formula according to your need. Example :
if you use the formula : = VLOOKUP($A6,Sheet1!$A$1:$Z$100,3,0) when you refresh the data, there might be some new rows added in the report which will not be considered in the formula and hence results will not be accurate.
= VLOOKUP($A6,SAP_ByDesign_R0001,3,0)
With this, Excel wil look for the value that is stored in the A6 cell in Report : SAP_ByDesign_R0001 and return the value of 3rd column. So even if there are new rows added in the report when you refresh the data, since the Range of the name : SAP_ByDesign_R0001 is udpated automatically when you refresh the data, you don't have to worry about adjusting your formula again.
😎 Text conversion for VLookup
With the example formula in above section, creates one more issue. Which is when report is inserted, the numeric values of the report (For example G/L Account number : 123456) is stored as text. or every time you refresh the data, numeric values are converted to text. This means Vlookup formula will not work (When Value in Cell A6 is stored as numeric value :123456, and not as text) and you need to convert the values from text to numeric again.
Solution to this problem is updating the formula with text function :
=VLOOKUP(TEXT($A6,"#"),SAP_ByDesign_R0001,3,0)
with these, whenever the numeric value is stored as text in report, Vlookup will still be able to search for this values and return you the correct results instead of #NA.
================================================================
I hope this will help you explore the different settings and help you use SAP Business Bydesign Excel Add-in with its full potential.
Let me know if there are any suggestions/feedback. Also let me know if there is some feature used by you but not included in the blog.
Regards,
Harshal
