Analysis for Office is a powerful add-in to Microsoft Excel, it enables a direct connection to your SAP BW or SAP HANA system. As a reporting tool Analysis for Office can be very useful, but is it possible to create a dashboard in Excel using Analysis for Office?
Users need to be experts in how to use Analysis for Office to create the reports they need. But you want to provide your users with default views which they can open as if it is a dashboard. In SAP Design Studio all components are available to create a dashboard, Analysis for Office lacks this functionality by default. But since Analysis for Office is an add-in for Microsoft Excel you can use Excel macros and VBA-code to create all functionalities you need.
By using Analysis for Office and VBA-code a dashboard can be created, an example is being shown below. The layout of this dashboard is created using default Excel components. The navigational and filter elements of the dashboard are being displayed on the left side. In this area specific filters can be set, and you can navigate to other views. The visualization of each view is being showed in the center of the screen.
Filtering and the data update is controlled using Analysis for Office components. Navigating, update data, showing percentages and creating the charts is being handled by VBA-code. The data is automatically updated via the Analysis for Office plugin. Which is automatically loaded when the Excel dashboard is opened. Users login with their default SAP BW username and password so that the data can only be accessed by users who have enough permission in SAP BW. This way the data access is secured, and access is limited to authorized users only. After a user logged in successfully the data is refreshed automatically without any other manual user action.
Let’s take a deeper dive into the dashboard displayed in the example above to explain the functionalities in more detail. This dashboard contains more than 50 default views which can be accessed by clicking on a hyperlink. After clicking this hyperlink, a VBA event is triggered. This event handles other events which result in showing a default view to a user in Microsoft Excel. The events which are being triggered are as follows:
Next to the 50 default views which are available users can also create their own views and use all SAP BW dimensions. The functionality to create your own view is mostly used by power users at this moment because extended knowledge of Analysis for Office is required. After saving the file to their local desktop these users can also reuse the view they created themselves. This is a powerful addition because the data is automatically updated each time to dashboard is opened.
The code below gives an example of how to use VBA to create charts automatically based on a given source of data.
Sub createCharts(FirstRow As Long, LastRow As Long, FirstColumn As Long, LastColumn As Long, Max As Long) Dim CurrRow, ChartWidth, ChartHeight As Long Dim chartArea, serieRange As Range ChartWidth = 150 'width of the chart ChartHeight = 100 'height of the chart chartArea = Range("A30:E100") 'size where the charts will be placed For CurrRow = FirstRow + 1 To LastRow 'loop through all data rows of source table serieRange = "=" & Ws.Name & "!R" & CurrRow & "C" & FirstColumn & ":R" & CurrRow & "C" & LastColumn & "" Set ch = Ws.Shapes.AddChart.Chart 'create empty chart With ch 'use the chart to make adjustments .Axes(xlValue).MaximumScale = Max 'set max value of chart .Axes(xlValue).MinimumScale = 0 'set minimal value to 0 for proper scaling .Parent.Left = chartArea 'place the chart within the chartArea defined above .Parent.Top = chartArea 'place the chart within the chartArea defined above .SeriesCollection.NewSeries 'add new serie .SeriesCollection(1).Values = serieRange 'add value to series .ChartArea.Width = ChartWidth 'set chart width .ChartArea.Height = ChartHeight 'set chart height Next CurrRow 'go to next data row of source table End Sub Sub showChart() Call createCharts(1,10,1,4, 500) 'call function to create charts with defined parameters End Sub
The dashboard contains the following default and ready to use functionalities:
So, to come back to the question stated at the start of this blog if it is possible to create a dashboard using Analysis for Office the answer is; Yes! It is possible to create a fully operational dashboard in Analysis for Office which make it easier for users to extract and analyze data. But this is not possible by default and you need to write some VBA-code yourself to make it work.
If you have any question about this blog or its content, please do not hesitate to contact me: k.engelen@JUGO.nl