A few weeks ago Mark Polino, in conjunction with Packt Publishing published another book, the Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013;
I got a copy of Mark Polino’s Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013 the other week with the intention of working through it following all of the examples. However, the reality is I don’t have the time to do this due to other commitments (both work and outside of work ones).
When I sat down with the book I also had to admit two things to myself; I like neither Excel nor reporting (however, to maintain by usual delicious inconsistency, I do like Management Reporter). Which is a bit of a problem when it comes to reviewing a book reporting out of Dynamics GP using Excel. So, rather than the step by step I originally intended, I’m going to do a shorter review.
The intention of the book is to introduce the required concepts to create an Excel 2013 dashboard reporting off Dynamics GP 2013 and then give step by step guides on how to make the dashboard;
In case you don’t want to read on through what was meant to be a short review, but then became a lot longer because I find it easier to remember things when I write them down and I want to remmeber what is in the book for when I discuss it with people, I have included a short “too long, didn’t read”;
TLDR: it’s well worth buying this book if you want to build dashboards in Excel 2013 with data from Dynamics GP 2013
In Chapter 1, Mark starts with the basics of getting data from Microsoft Dynamics GP into Excel covering nine methods which are a mix of Dynamics GP interfaces and accessing the Microsoft SQL Server database directly. Mark covers each of the nine methods giving a practical step by step example to follow and also discusses the benefits and shortcomings of each one.
It’s a fairly basic example but I have to admit I liked the SmartList Export Solutions example as this is something I have struggled with in the past because, basically, I am not very good with Excel. I’m trapped in a bit of a vicous circle with Excel; I don’t really like it so I’ve not spent the time to learn how to get the most out of it and because I can’t get the best out of it I continue to dislike it.
All of the other examples are detailed enough that there should be no problems following them through and getting the data into Excel. Mark wraps up Chapter 1, Getting Data from Dynamics GP 2013 into Excel 2013, with a quick look at the popular third party products which can get used to get data into Excel.
Chapter 2, The Ultimate GP to Excel Tool: Refreshable Excel Reports, covers the Excel Reports introduced in later versions of Dynamics GP (memory lets me down here as to whether they were introduced in v10 or 2010). Mark opens with a look at the configuring security so users are able to use the Excel reports; network, database and Excel security al get covered. This is the first guide I’ve seen on configuring this security and it is a very welcome addition to the book (and one I hadn’t expected to see when I started reading).
There is one element missing from the chapter; Deploy refreshable Excel reports gets mentioned as a topic in the chapter introduction, but then doesn’t get mentioned again. Fortunately, deploying the Excel Reports is a very simple process.
Mark covers simple topics such as saving the file without automatic refresh set, Flash Fill of which I had never heard, how to modify the data source to add additional columns of information and also how to modify the Office Data Connection (ODC) so the changes are available to all Excel Reports using that ODC.
The final area covered in Chapter 2 is the Excel Report Builder which is very like SmartList Builder and allows custom Excel Reports to be built and published. As with the other areas, Mark’s explanation is detailed enough that the steps are easily reproducable.
Chapter 3, Pivot Tables: The Basic Building Blocks, moves onto what I would describe as an advanced area although I suspect those who know Excel, including all of the Accountants I work with, would also regard as a fairly simple area. Having reads Mark’s explanations of pivot tables, I think I might have to agree; they’re not complicated.
Fortunately, there is a lot more to the chapter than the introductory example. Mark covers how to create a pivot table from one of the GP 2013 data connections and then how to create the first part of the dashboard; a revenue pivot table which will be used as the source for revenue data.
Mark covers the building of a number of other pivot tables to be used in the resulting dashboard, with all getting a detailed explanation and follows up with using Excel Report Builder to produce a pivot table.
The final part of the chapter covers the new Power View data visualisation tool in Excel 2013. These aren’t used on the dashboard but they still get covered in enough depth that you can use what you learn for other reporting purposes.