Two tips to create professional reports, and keep them up-to-date.
Imagine this scenario: You have an Excel workbook containing important data that you have gathered from various sources, and using Excel you create some charts and pivot tables, slicing and dicing the data to create a number of views of the data to provide important insights which you now want to share with your colleagues.
Problem: Now assuming the workbook is not too large to email, you distribute the workbook around your business. (You're a hero!). But what happens when you want to update the source data or the report design? You end up with multiple copies cluttering up your PC and email folders. But which file has the correct report, and the most up-to-date data? (Not so good).
Now if you have used Microsoft Power BI, you will know it is exceptionally easy to import data from many different data sources. Create a report using the desktop designer, and publish it to the cloud. Click a button, and you get a URL which you can email to your colleagues. Now, whenever the report design, or the underlying data change, your colleagues will always see the latest version. And as you know, Power BI reports are “interactive” so users can slice and filter the data themselves without you having to prepare all the different views in advance. 😊
Learn more about Power BI here: Microsoft Docs
There is one more tool Microsoft have given us to make it easy to update the report data. It’s called a “Gateway”. It remembers where your data source-files are held, and automatically refreshes the report when necessary. This saves you effort of having to marshal data files and redistribute large workbooks.
Tip 1: How to view the raw data?
This question always comes up: “Martin, the report looks great, but show me the data that was used to create that chart/pivot table. I want to work with it in Excel.”
Answer: When logged into the Power BI service, there is a well-hidden button called “Analyse in Excel”. If you click on it, it will download an “ODC” file to your computer.
If you simply drop that file onto an Excel worksheet, you can create a pivot table similar to anything you can create in Power BI. However, before you do that, open the ODC file in Notepad and look for two lines similar to this:
<odc:CommandType>Cube</odc:CommandType> <odc:CommandText>Model</odc:CommandText> Let's assume your Power BI data is contained within a table called “Sales”, change the above two lines to look like this:
<odc:CommandType>Query</odc:CommandType> <odc:CommandText>Evaluate Sales</odc:CommandText> Now, when you drop this ODC file onto Excel, it will create an Excel data table with the same data that its Power BI – all ready for you to create your Management Information (MI) report.
Tip 2: How to format that data in Excel to make it presentable?
Having the data in Excel is all very good, but now you need to manipulate it into (say) Excel Summaries?
One lovely feature of Excel, is its ability to record all of your instructions and create a “Macro” which can be run later-on with the click of a button.
First of all, make the “Developer” tab visible in Excel. Then click “Record Macro”.
Do any the filtering, formatting, and sorting of the workbook that you want, and then click “Stop Recording”. All of your instructions will be saved in the “Code” section of the workbook – and with a little tidying-up the code can be used again to make your workbook as complex and sophisticated as you want.
So, there you have it:
Your data in one place,
Business Intelligence created by drag, drop, configure, and share, and
Management Reports with all the code created by Excel itself. 😉
Feel free to visit us at: Business Net Solutions