Over the years it has become increasingly more important to present data in a way that is quick and easy to understand whilst ensuring data integrity is maintained.
We believe that the real power of Excel is in the background coding capability and how reports are built. Data can and should be offered in a number of different formats dependent on the audience and purpose the report has to serve.
For the auditors who need fine attention to detail, Excel can be used to highlight even the slightest deviations from compliant measures; data can be manipulated and edited ad hoc without the need to re-engineer the report each time. The colourful Power BI dashboards however, with graphs and basic statistics can help busy managers who need quick overviews of their operations and staff.
In summary: data is most valuable to provide insights into a business when analysed and presented in Business Intelligence reports.
We can deliver business data, either:
Directly within the administration web portal,
Within Microsoft Excel Workbooks,
Or using Microsoft Power BI apps
In this article, we outline the advantages of these different reporting methods and where they are most applicable:
Web Portal
Business data is stored within a centralised SQL server where we can ensure the data is clean and valid. Our data engineers ensure any violations to integrity rules are alerted, so any issues can be corrected. Having one central store for data ensures there is a single consistent and secure source of all data, which can be processed rapidly, and the results communicated to external systems and reporting tools.
The web portal provides a simple and direct mechanism to present data as simple HTML tabular reports extracted using simple TSQL stored procedures. Some simple filters are usually provided to allow the user to select subsets of the data, and then hyperlinks are provided to allow the data to be viewed, edited, or transformed in some way.
Excel Workbooks
Although the web portal provides a quick and simple way to see data, it is not practical when the user wishes to manipulate hundreds or tens of thousands of records. A typical example might be a list of all sales transactions within the past year. An Excel Workbook is an ideal method to deliver this data, as the workbook can be encrypted, write-protected, and formatted as required by the user. Typically we will supply active workbooks which can download data from the server at the click of a button. We use HTML as the method of delivery – so it is possible to access the reports outside of corporate firewalls.
Clients often require transaction reports to be sent to them daily or weekly via email or FTP. We operate an automated windows engine that generates and sends these reports as required.
Once data has been downloaded into Excel, it is easy to view that data within linked charts and pivot tables. These can be embedded into different worksheets within the Excel workbooks. For preformatted static and interactive reports this is best done using VBA and standard features of Microsoft Excel. Although Microsoft does provide “Power Pivot” and “Power View” add-ons to allow end-users to create reports, we recommend that Power BI is used for DIY reporting.
Advantages of Excel
When users want direct access to their data to manipulate and analyse from a PC Desktop.
When edits are necessary. The user can make changes directly in the worksheet and then upload the changes back to the server (hence maintaining consistency across the whole business).
Using VBA (Visual Basic for Applications) it is possible to control the formatting and computations with the workbook to any level of sophistication that is required. This is especially useful when data is not structured uniformly (e.g. regional or language variations).
Snapshots of reports can be automatically generated, embedded into other Office documents, and distributed to users via email or file share.
VBA allows ultimate control of functionality and presentation:
Graphs and charts linked to data downloaded from server:
Power BI
In recent years Microsoft has moved the focus of its business architecture from internal client-server networks, to cloud-based services. Rather than buying software once, users rent web-based services that store data off site, and where functionality evolves continually. For DIY reporting, everything that can be performed with Excel Power Query, Power Pivot, and Power View (and more) is now more easily delivered using “Power BI” from the cloud.
When to use Power BI
When a high-degree of interactive slicing and drill-down is desirable.
When natural language and voice-driven queries are needed.
To avoid the need to licence Microsoft Office from every PC.
When delivery from mobile phones and tablets is desirable.
When data is regularly structured and high-degree of customisation is not needed.
When there is a need for interactive dashboards
Finally: Here is a graphic of how we generate reports for Mobile Scorecard: Data gathered from the Mobile Scorecard App is uploaded to our server in the cloud. The data is saved in SQL server where it can be processed and exported to external systems. When users require direct access to the raw data on desktop we prefer to use Microsoft Excel, however, when the users prefer highly interactive visualisations of their business data, then Microsoft Power BI provides the best solution.
