Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013 by Mark Polino – A (short) Review

Monday 27th May 2013Tuesday 24th Jun 2014 Ian Grieve10 minute read2 Comments

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.

Chapter 4 covers one of the main reasons I dislike reporting so much; making things look pretty. I don’t do pretty; fortunately

So, a lot of progress can be made very quickly towards building the complete dashboard. Chapter 6 covers adding slicers and timelines to the dashboard in order to add interactivity to the pivot tables; Timelines are new in Excel 2013 and is a special kind of slicer which is made to work with a range of dates. Slicers are an area of Excel that I have never used before and it’s both nicely introduced and covered in sufficient detail and depth that I’d be willing to have a go.

Chapter 7, Drilling Back to Source Data in Dynamics GP 2013, gets me back onto comfortable territory. Drill down for Dynamics GP is an area that has hardly been covered; as Mark points out, Microsoft seems reticent to supply information on the drill down functionality and the other books and guides which have been produced seem to skip over the subject entirely so it was great to see a chapter in this book focussed on the drill down functionality.

As well as showing how to use the drill back, Mark covers fixing a problem with the drill down to a Journal Entry in the Dynamics GP 2013 RTM which I believe may have been in the recently released Service Pack 1.

A detailed breakdown of the hyperlink used for drill downs is given along with an explanation of making them dynamic.

Drill Down Builder is briefly covered before Mark goes onto some complex drill downs such as drilling down with Dynamics GP 2013 on a Citrix or Terminal Server and Excel 2013 locally installed. This is one that a reader of Mark’s blog told him about and is offered up as something to try.

Chapter 8, Bringing it All Together, is the final chapter covered the build of the dashboard introduced during the book’s preface. The chapter covers headers, cleanups, logos, backgrounds as well as touching on what makes good design and the deployment options. Again a chapter makes me feel like a wallflower at a party peering into the room wondering what the **** is going on. Mark does cover the subject well, with detailed guides of how to accomplish the tasks under discussion.

The finalising of the dashboard isn’t the final chapter of the bok as mark then goes on to cover the new PowerPivot feature in Excel which is only available with Microsoft Office 2013 Professional Plus. PowerPivot uses a new in-memory analytics engine which allows users to work with pivot tables based on millions of rows which is more than could fit into an Excel worksheet and also allows multiple sources of dara to be connected together.

As with the rest of the book, this chapter contains step by step practical examples which are easy to follow. The chapter covers the subject from a low level, such as describing relationships for those not used to working with databases, but is done in such a way that more advanced readers don’t need to wade through all this information if they want to jump ahead.

The final chapter of the book is Chapter 10, Slightly Crazy Stuff, where Mark covers the elements which didn’t fit into the dashboard being build. It’s a nice look at some of the features which could be used if required.

If you’re looking to get into building dashboards of Dynamics GP data in Excel 2013, then this book is defintely one to buy. The coverage is of sufficient depth and easy to follow that building dashboards should be quite easy with the information learned.

I do sometimes worry I sound like a fanboy when I review books, because I am usually very impressed with what I have read, that I then feel that I have veered the other way and been too critical. Much like I felt while reviewing Leslie Vail’s recent book.

Like Leslie’s book, I’d feel no hesitation to recommend this one to anyone looking to build dashboards for Dynamics GP in Excel. In fact, as much as I have described hating design work and partially skipping over those chapters, the explanations of how to do the formatting and design work seemed really good so I’d suggest a read to anyone looking to improve the look of dashboards for data from any system.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

PrintFriendly Email Linkedin Twitter Facebook

Related Posts
Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013 by Mark Polino
Review of Microsoft Dynamics GP 2013 Reporting by David Duncan and Christopher J Liley
Review of Microsoft Dynamics GP 2013 Implementation by Victoria Yudin
Sales Analytics Dashboard Available Now From DynamicAccounting.net
Coming Soon: Microsoft Dynamics GP 2013 Cookbook
Microsoft Dynamics GP 2013 Reporting – Review Incoming
MDGP 2013 R2 Feature of the Day: Dashboards
Microsoft Dynamics GP 2013 Cookbook Has Been Published