Reporting with the Excel-Add-In – Advantages of the Excel Table Display
Today, I want to show you another great feature in Excel reporting. Perhaps you have already discovered this strong functionality: The Excel Table Display.
When adding a report to an Excel sheet, it will be displayed as an Excel range with a format that is being generated by the Excel Add-In, applying the ByDesign visual design.
Switching the thing on
Put the cursor into your uploaded report, go to the SAP Business ByDesign ribbon and choose Grid Display > Excel Table Display. Your report will change to an Excel table, with all of the comfort of this Excel feature.
You will be able to apply formats, filters and other features. I just added a “Slicer” to my report, as an example. You can see the result below: a new box comes up, populated by values visible on the screen and allowing you to select a distinct value as a filter for the report. But please keep in mind, as well as for filters: ByDesign prepares the data based on your selections and sends them to Excel. Excel filters and slicers will only work on these data – available in Excel, which might be a subset of the data you might expect to see.
Using Excel formulas
Along with the Excel Table, another feature comes with that helps you to format a pretty report. You can add a column that is being calculated in Excel solely, like an average. In the example, I added a value doing a division of the net value by quantity, to get a net price.
To achieve this, just add a new column to the Excel table, by typing the column header (here: in G3). Excel will automatically add this column to the Excel Table and format it. Now, add your formula, using symbolic names (Excel talk: “structured reference”) instead of cell references. The screenshot below gives an example. Typing the formula into one data cell will populate the whole column. In case your report shrinks or growths (perhaps due to a modified selection), your column will always look accurate.
Adding a new header cell:
Adding a formula:
References to certain values
In Excel, aggregation formulas (e.g. SUMIF, COUNTIF) are very popular. Using structured references, you can use them as well, e.g. to populate a dash board or key figures. I added an example to my report example:
Using the formula =SUMIF(Table1[Product];G16;Table1[Invoiced Net Value])will produce this key figure display (in this example, there might be a certain focus on product SR_INSP_01):
Summary
Switching to the Excel Table Display will let you treat SAP Business ByDesign’s analytical reports with Microsoft Excel tools and formulas. You are able to add local calculations (formulas), to increase data filtering capabilities and add a usability the user already knows.
—