Using Excel as a Powerful Reporting Tool

I experienced, over the years, a movement towards making ERP systems all that they can be, in an effort to reduce dependencies on “Excel”. ERP systems, speaking from the perspective of Microsoft Dynamics-GP, matured to accomplish some, arguably a lot of this. However, after that accomplishment, the end- user community was appreciative of those efforts, and then asked…how to get those results into Excel?

Newer tools get us closer to this like eOne's smart view, and of course smart list builder, which has been around for years now. Regardless, in many cases – the end result is “Export to Excel”. The typical reason behind that is to get the data to an individual who can’t access it, or it needs further manipulation capabilities. In either case – it needs to get to Excel.

I think that there is argument to be made for taking it to Excel direct. Why build all that you need into SmartView, or Smartlist builder, handle the security and deployment of that if all you need is data in Excel? If the data source is a view, or a direct table read, a view, a direct table read can be attached easily (With SQL appropriate permissions) into a spreadsheet. From there it is a “database” of sorts in Excel and a pivot table can be produced easily based on the direct connection of the view, or the “Data” presented from the data connection within the workbook.

If what is needed can be presented via the existing interface (using whatever tool), then exported in the format needed, then by all means – that is the answer. If data needs to be sliced/diced delivered to other users, and the “tool” cannot handle it, but excel can – then take it to Excel first, why not?

Data is fairly easy to get, what the data tells you, once organized to your needs, becomes operational information and a basis for decisions. A customer needed a way to reconcile their open customer deposits. They needed to know, by customer, what their liability is. In Dynamics GP – this does not exist, it's a gap. We wrote the view. Since the customer at the time, did not have smartlist Builder, and at the time this was the only need to look at data that combines several tables, we used excel as the tool to present that data to them. Even with that, they wanted more, they wanted it aged. So we connected the view to Excel, which allows them to pivot, and “Age” or slice and dice the data as they see fit. They can use that as collection information, fulfillment information, and satisfy the auditors with the supporting detail.

I was a beta site for FRx when it was first introduced. Even then it had the export to Excel option. Prior to this, I spent upwards of a week writing my “Monthly Report” to our executives.

I then wrote my entire monthly report using excel (it became a “Template”). I copied my Profit and Loss Statement and Balance Sheet into the workbook in separate “data” sheets. I did cell references in my “Text” presentation pages using concatenate statements to the appropriate data. I had charts, graphs based on that data that all updated “automagically”. The result was that the report was done in half a day after that. I still had to write specifics “Text” about the current month's activity, trends etc. but no more manual input of raw data.

Don't Discount the Power of Excel

Here is a starting point from a Blank Excel Work Book

Select “From Other Sources”, Then “From SQL Server”


Enter The SQL Server Name – My instance is 5S92CS1, and I am using Windows Authentication


Select the Data base, and the Table/View to connect – This “TB_Summary_Aging” is a Custom View


Follow the steps to choosing to save the password in the file, Update on open (Under Properties) – all depending on how you authenticate to the database, and desired actions, select Finish.


Where and how to put the Data into the sheet:


The Excel Sheet will look like the following, will update based on your set up options (On open for instance): Filters are automatically added as well.


Do you have additional tips to share on using Excel as a reporting tool? Feel free to comment below or for additional questions, contact me

Next Post