In previous post I described list of ways of data extraction from SAP ByD. Here I continue story about case “ByD to Excel” using SAP ByD Excel add-in.
Currently, I use version 135.0.2109.722 of SAP ByD addin for Excel
In name mentioned that add-in for Excel 2010, but I’m using it for a long time with Excel 2013. From time to time have same problems that I had when used Excel 2010.
This is a basic way that SAP offers to SAP ByD consumers. However, when I opened for myself Power Query and Odata protocol in ByD, I use ByD Excel add-in only for temporary solutions or quick data exploration.
Assume, that you inserted report on worksheet. Say, Invoice Volume.
First thing that I usually do – I change view to Excel Table
then disable formatting
Disable “Format Report”
Then Add-in will not reset applied Table Style.
Disable “Adjust to Report Result Set”
If you don’t want to allow add-in to adjust columns width.
Disable “Merge Repeated Cells”
Actual for Grid view.
I cannot say anything interesting about View Pane, therefore let’s play with Selection Pane.
It can be activated by two ways
Bottom right corner in Selection group of buttons
Or
Fields with different types can be added for Selection (dates, numbers, strings, hierarchies etc.). In the same time fields in Selecion can be set to one of selection type: any, interval, multiple values and single value.
E.g. it impacts on ability to use Exclude options in fields.
Some types of fields support rich Relative selection. Mostly “date”-around fields, such as Date (Invoice Date, Posting Date etc.), Created On, Changed On, Year, Year/Month, Month etc.
example of several options for “Changed On” field.
For me it was a surprise when I discovered that relative selection for field Month contains different list of relative options. I was looking for Rolling 12 Month, for Date-fields we have no such option (typical SAP). Almost any report doesn’t contain field Month in selection by default. However, we can add it
Then (after re-connect to system in Excel and refresh of report) it will be available on Selection Pane
Where I found “Last 12 Month Excluding Current Month”.
I can say that relative selection is very useful option. Such variants as “Last day of previous month”, “Yesterday”, “Today”, “12 Months Ago” and so on allow to avoid manual change of criteria.
However, existing relative selection options do not cover all possible situations. And here we should consider opportunity to use references on sheet cells and named ranges in Selection Fields.
E.g. we need to compare end of last month with end of previous to last month period. Such relative selection doesn’t exist. But in Excel we can simply find this date by formula
=EOMONTH(TODAY(),-2)
it gives “end of previous to last month period”.
Assume that we entered this formula in cell A1 of sheet with report. Then we can use in “date”-field reference “=A1”
Unfortunately, using this approach I couldn’t find a way to reference date-fields to Interval, only “one date”. If interval is a Fiscal Period – then we can reference on values like “003.2015”.
Here I should notice that date-fields are sensetive to date format used in Excel and in SAP ByD.
In SAP ByD in right-top corner in Personalize – My Settings manu
we may find Date format set for particular user (different users can have different date format).
Fo me convenient to use DD.MM.YYYY, because it is standard for Russian Locale.
And if in Windows user has different format, e.g. M/D/YYYY, then current version of ByD add-in will tell user about error or simply ignore value in cell and run report without criteria.
Even if we manually set cell format to “M/D/YYYY” in hope that ByD addin will parse value in cell using applied format
it doesn’t work.
This behavior makes difficulties if such Excel-solution used by users from different countries, having different local date formats.
I will write how to avoid this problem in next posts.