Is it possible to create a dashboard using Analysis for Office?

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.

Analysis for Office dashboard example

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:

1. Update the data source
2. Select the fields corresponding the selected view
3. select the key figures corresponding the selected view
4. Determine range of the data source
5. Create visualizations
1. Create multiple small charts (small multiples) or single chart containing the correct series of data
2. Set proper scaling
3. Set proper styling
6. Update the title and description above the chart

Create your own views

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.

Code Example

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

Functionality overview

The dashboard contains the following default and ready to use functionalities:

• Data integrity guaranteed by SAP BW
• Data access regulation by SAP BW authorizations
• Navigate between different views
• Automatic update of data
• Visualization of data with proper scaling and styling
• Automatic generation of (multiple) charts
• Auto update of titles and description
• Filtering to drill down on data
• Showing percentages instead of absolute values
• Showing headcount instead of FTE
• Show trend data to do analyzes over time
• Create your own view

Conclusion

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