Page 1 of 212

Book Review: Microsoft Dynamics GP 2013 Reporting (Second Edition) – David Duncan & Chris Liley

My second book as reviewer is Microsoft Dynamics GP 2013 Reporting – Second Edition written by David Duncan and Chris Liley.


This book is second edition to their famous Microsoft Dynamics GP 2010 Reporting. So those who have read this book before would see the new book as an upgrade. And that’s quite obvious as this book covers more on reporting features that are now part of Dynamics GP 2013.

The book is structured as follows:

  • Chapter 1: Meeting the Reporting Challenge
  • Chapter 2: Where Is My Data and How Do I Get It?
  • Chapter 3: Working with the Builders – SmartList and Excel Reports
  • Chapter 4: Report Writer and Word Templates
  • Chapter 5: Utilising the SSRS Report Library
  • Chapter 6: Designing Your Analysis Cubes for the Excel Environment
  • Chapter 7: Utilising Analysis Cubes for Excel for Dynamic Reporting
  • Chapter 8: Designing Financial Reports in Management Reporter
  • Chapter 9: Viewing Financial Reports in Management Reporter
  • Chapter 10: Bringing it all Together
  • Appendix: Comparing the Dynamics GP Reporting Tools Against Different Reporting Challenges

Chapter 1 discusses about challenges faced by a report developer and how to get prepared to meet them. If there is one thing that’s changing constantly at Dynamics GP front is how we visualise the data that is entered in GP day-to-day. Financial statements, revenue analyses, stock analyses, multi-dimensional analysis of your business and then there is big data which you might want to link with your own business. This chapter provides food for your thoughts on Reporting overall.

Chapter 2 discusses about how GP stores your data and how easily and efficiently you can access them. This one is, in my opinion, very crucial chapter for any GP report developer. Unless you know how GP is structured and how data is stored, you can never become a successful report developer. To quote the authors themselves: Knowing where to begin is a critical first step. This chapter shows us exactly that.

Chapter 3 discusses about SmartList Builder and Excel Reports Builder. Two most amazingly simple yet efficient tools to create any report. Excellent chapter again.

Chapter 4 discusses about Report Writer and Word Templates. Report Writer, in my opinion, is a very underestimated tool. I am sure David Musgrave would pat me to have said this. Knowing this tool would most definitely help any GP report developer to harness their knowledge on GP data and structure. Word Templates were introduced some time back and meant to be alternative to Report Writer reports. So knowing them also would be better.

Chapter 5 discusses about SSRS reports that come with GP. If your are a SQL developer and you are asked to develop some reports on GP, SSRS is THE best reporting tool. Know the data and you are on. Brilliantly written chapter.

Chapter 6 and 7 discusses about Analysis Cubes for GP. If you require multi-dimensional reports on GP data and want to know how to develop them, these chapters are for you.

Chapter 8 and 9 discusses about Management Reporter, the replacement of FRx, but much more an advanced technology. These two chapters take you step by step in understanding MR and how to design reports.

Chapter 10 summarises whatever been discussed on all previous chapters.

Appendix gives you a view of all reporting tools that are discussed in a simple yet informative table. And this book is certainly not complete without this table. Amazing piece of information one could ask for.

David and Chris have put their sincere and precious efforts on writing this book. Their expertise on reporting is going to be a guiding force through this book to all of us.

This book is published by Packt Publications. Those who would like to buy this book can do so by clicking on the link provided at the beginning of this post OR by clicking on the book logo.

To the authors, David & Chris: I thank you both for this great book and accepting me as one of the reviewers. It’s very difficult to talk about the greatness of this book in one single post, but I guess I have done my best. It’s going to be my reference on anything about GP reporting. Thanks again.


Filed under: #MSDYNGP, BI, Book Review, Books, Business Intelligence, GP, GP 2013, GP Functionality, GP Reporting, GP2013, Microsoft Dynamics GP, Microsoft Dynamics GP 2013, Reporting, Reviews

Originally posted here:
Book Review: Microsoft Dynamics GP 2013 Reporting (Second Edition) – David Duncan & Chris Liley

December 6, 2013 · Vaidyanathan Mohan · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 417

GP Airlift 2013 – Day 2

Day 2 at the 2013 Dynamics GP Technical Airlift seemed a lot less busy. Not in the sense of fewer people attending, but in the sense of less rush, less overload. Chalk it up to a better night’s sleep the second night (and chalk that up to less socializing last night, haha, no correlation there I’m sure!).

Today was a mixed bag of sessions for me, once again, partaking in some developer sessions and some consultant sessions.  I must admit yesterday I wished I could clone myself as I would have loved to see 2 or 3 more of the things yesterday, where today I had trouble selecting sessions because not as many stuck out as obvious “must attend” ones to me.

One funny story I forgot to share in yesterday’s post. Somehow at last evening’s welcome reception a small group of us got talking about names, and spelling and language.  I said I always forget when I’m in the US, to pronounce “Z” correctly.  Out of habit, whenever anyone asks my name to fill out something, like checking into a hotel, I always say my name, then spell my name. Not sure why, I probably don’t even realize I do it, it is so automatic. Anyway, out of habit, I spell it out, K U N T “Zed”.  Then I nearly immediately remember where I am and correct myself to say “Zee”. So I was relating this story, and one of the guys asked me “What’s Zed?”.  I’m thinking he’s just never heard that Canadians pronounce Z as “zed”.  So I just explain, “zed” in Canada is “zee” here. Then he asks, “What’s Zee?”. At this point we’re all laughing, and start reverting to A, B, C to explain the alphabet to him, jokingly of course. (and if you fail to see why that’s funny, maybe you just had to be there!).

Opening Session

There were six more sessions today, starting with a general opening session. The keynote speaker was John O’Donnell, a Windows Phone evangelist.  It was a good presentation, but in my opinion, a little too much Windows Phone content for me. It sounded like it was intended to be more about the apps and app stores and future possibilities for GP apps in the future, but it came across to me as really just about Windows Phone history. No offense at all to John, he was clearly invested in the technology, and the history and that was clearly evident; for me it just missed the mark a little.

A couple of nuggets from there that I did take away though, he talked about the concept of live tiles as well as the concept of panoramic displays (multiple screens that you swipe left or right for more info, if I understood it correctly).  He mused about how that might be used on a GP home page in some manner in the future (one or the other or both, they are not necessarily connected concepts).  Live tiles in particular I could see on a home page easily, with live content instead of graphs and KPIs.

Breakout Sessions

Here are the sessions I attended and some thoughts and take-aways on those:

  • Dashboards for Dynamics GP:This was presented by Jared Hall and it was well done overall. He showed off some very cool features in Excel 2013 that I definitely want to check out, things you might never notice if you didn’t know about it (Flash Fill for instance).  It beats the heck out of LEFT, MID and RIGHT formulas!  There were a couple of disappointing things in that session for me. One was there was little demonstration of building an actual dashboard except at the end (he did build one in 6 minutes though, pretty cool!). I would have liked more hands-on of this as that was the title of the presentation.  The second thing was more of a pet peeve: a couple of times he referenced the crowd with phrases that indicated he thought we were customers (“you’ll impress your boss with this”).  No offense to Jared, but I would think he’d know it’s a partner conference, not a customer conference.
  • Dexterity & Services: This was the first session I didn’t attend the entire session… nothing to do with the quality of the presentation but the content was WAY over my head and it was beyond my even attempting to distill it into what I could understand.  It was going through using SanScript vs using .Net to use web services. I hope the content was useful, I definitely knew I wasn’t the target audience when I sat in on it, so I’m sure lots of developers were happy to get some hard core code content!
  • GP BI for Developers: This one was one of the sessions where the alternative session offerings didn’t seem as interesting to me content wise, so I sat in it to see what it might cover. Unfortunately, it seemed like a lot of the content was repeat of the Dashboards session Jared had, except for a deeper dive into Word Templates. I was left hoping for some more “new” stuff for this one…
  • Advanced Excel: This was an excellent session, presented by Belinda Allen.  I really enjoyed this even though a good chunk of the content was not new to me.  Some of the cool things I learned were: double clicking on excel reports or data connections in the navigation list view of Excel Reports deployed, opens it in Excel; creating a Microsoft query (*.qry file), saving it and creating a shortcut in Shortcuts in GP, opens in Excel (neat!); a couple of neat formula tricks for Period and Week Number that I didn’t know existed; and of course the PowerPivot stuff which she did a great job of demo’ing was all new to me.
  • SQL Skills for Consultants: Another excellent session, presented by Steve Endow and Christina Phillips, was a great way to end the day.  I really love the “immediately useful” sessions where you can go home and use this stuff right away. This was one of those. Again, using SQL a lot, a good chunk of the content was not new for me but there were again lots of new things I didn’t know that I know now!  The two of them really presented well together and did a great job of keeping it light. Some of the new things I learned: look at including “with (nolock)” on select queries to avoid locking up tables unnecessarily; some better ways of using Left Outer Join with “where xyzfield is null” (I used to use “where not in” but I think this method is better than mine); Common Table Expressions (CTEs) – way cool; using “with ties” in Top N selects (who knew?); something about a Covering Index I want to research more; ROWNUM and PARTITION functions (commands?); and finally a PIVOT command that I knew existed but have never gotten the syntax correct to make it work.

That’s it for now… now time for some socializing and some dinner!

Originally posted here:
GP Airlift 2013 – Day 2

September 19, 2013 · · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 378

Free Stuff for GP!

You read that correctly!  Microsoft has decided to change the way they work with some of their utilities and tools.  Previously there were charges to get access to tools or utilities like Customer Modifier and Customer Combiner (and Vendor, Account, Item etc.).  On the partner side, we had to purchase those outside of the normal ordering processes (they weren’t “modules” like ordering something like Integration Manager was).  They want to streamline that to eliminate some of their Direct Billing policies and that means Dynamics GP customers win!

What’s Free?

Well here is a list of what I know of, and it’s effective March 1, 2012.  Keep in mind this has only recently been announced so don’t be surprised if your partner doesn’t know all of the details about it yet – the information is just trickling out now and not everything on Partnersource is yet updated for us.

Professional Services Tools Library

All of it, some of the tools were already free, now all of the tools will be free.  There are so many tools in this library, it’s impossible to explain them all here!  Here is a quick list of the ones that in my opinion are AWESOME to have available for free.  More information on all of the tools and what they do is here (this is the general GP2010 installation guide download page – look in the list for ProfServicesToolsLibrary.pdf).

  • System tools: Database disabler for maintenance, Shortcut copy, Update user date
  • Financial tools: Account modifier/combiner, Chequebook modifier, Fiscal Period modifier, GL master record triggers, Fixed Asset modifier
  • Sales tools: Customer & Territory modifier/combiner, Salesperson modifier, RM transaction unapply from history, RM master record triggers
  • Purchasing tools: Vendor modifier/combiner, Select Cheques combiner, PM master record triggers
  • Inventory tools: Item Number and Site modifier/combiner
  • Payroll tools: Employee modifier (TBD if this is for US payroll only or Cdn Payroll too)
  • Misc tools: Doc date verify (not more bad dates outside of fiscal years!), Default “add item” in SOP/POP, Company copy (copy setup to another company)


Utility allows you to launch Great Plains and run one or more integrations or integration groups and have Great Plains exit upon completion of the integrations. This product also supports integrating into several different companies.


A COM dll that can be called from IM or an outside application to post some types of Dynamics Batches.  Currently the application supports posting of SOP, GL Trx Entry, IV Trx Entry and RM Cash Receipts batches. NOTE: Because this application uses the Continuum API, the Great Plains client must be running and a user logged into a company for this application to work.  The posting process would then use that user & company to post the desired batch.


More information to follow.

Payroll Detailed Activity Tracking

The Payroll Detailed Activity Tracker will track changes made to employee records (US Payroll only).  This tool is available for SQL users.  This was essentially a tool that was available prior to introducing the Audit Trails product.  It creates triggers to track changes to payroll records in various US Payroll tables.

PO Returns

When invoicing a receipt in POP, this customization will check the previous quantity invoiced and the quantity returned for the line item. The maximum quantity allowed to be invoiced is based the following formula: Quantity Shipped minus (Previous Quantity Invoiced plus Quantity Returned). This customization will not allow invoicing of more than this amount.

POP Over Receipt Tolerance

Utility that allows a receipt tolerance percentage for quantities.  A setup window allows the user to enter a tolerance percentage, that percentage is then used when a Purchase Order is received.  The highest quantity that can be received is the original quantity ordered plus the percentage amount.  For example, a Purchase Order is entered for a quantity of 10.  A tolerance percentage of 20% is entered.  The highest quantity allowed to be received would be 12.

RM Auto Apply Utility

This application will allow the user to Mass Apply posted RM Credit Documents to posted RM Debit Documents.  The user can select a range of Customer, Debit Document Dates, Credit Document Dates, and a Credit Document Type restriction if desired.  The credit documents will apply to the debit documents in the order specified in Receivables Management Setup window which is the same order that Dynamics Apply Sales Documents Auto Apply button uses.

Shipment Notification

Will allow drop ship sales orders to be transferred to invoice prior to invoicing the purchase order. 

SOP Default Site per Line

This customization changes the site that is defaulted on line items.  Instead of pulling the site from the Default Site on the SOP Entry window, if there is a default site assigned to the item in Cards>Inventory>Qtys/Sites, then that site will default for that line item  If there is no default site id for the item, then the customization will default the first site that is assigned to the item.

SOP Sort Line Items

This utility allows the user to define custom sorting options to display line items in Sales Order Processing.  For example, a sort could be defined by Item Number and then the currently selected SOP Document would display the line items in Item Number order instead of the standard by order entered.  This utility does not physically reorder the records in the underlying tables; it only displays them differently in the Sales Order Processing window.  This application also does not affect the way that sales documents print. (SQL database only)

SOP to POP Line Reordering Utility

 This utility will reorder the line items of Purchase Orders generated by the SOP to POP transfer process to be in the same order as they originally were on the Sales Order document(s).  The preview report will still show an alphabetical list, but on the actual Purchase Order document the line items will maintain their correct sequence. This is critical for companies who rely on matching up PO printouts to Packing Lists or Picking Tickets in SOP.

SOP Tax Schedule Roll down

More information to follow.

How do I get these tools?

If you are an existing Dynamics GP partner, ask your partner.  They are via the partner channel only at the moment, that may change but right now distribution is through your partner.  Partners, the PSTL keys will need to be on a request basis until Partnersource is updated with a master key for free use for anyone.  The information I have is to contact to obtain a key in the interim.

In the next release of GP, Dynamics GP2013, the PSTL will be on the CD as well.


View article:
Free Stuff for GP!

March 23, 2012 · · One Comment
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 1,157

The Dynamics GP Blogster: "Object Reference Not Set" error when …

I have seen a number of forum posts around this subject and have even received a few calls for help in troubleshooting the issue. Journal Entry#’ Line 9: – MicrosoftODBC Driver Manager Data source name not found and no default driver specified Integration Failed Integration Results 1 documents were read from the source query. 1 documents were ….. Dynamics Confessor Blogspot · Posting multiple The new is is located at:… 9 months

See the original post:
The Dynamics GP Blogster: "Object Reference Not Set" error when …

August 30, 2011 · Mariano Gomez · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 352

Everything Dynamics GP #8

Thomas Friedman thinks the next generation might be inventing their jobs, rather than finding them. The Startup of You . Now you can subscribe to Everything Dynamics GP via email GP Reconciling unchanged bank statements (Mariano Gomez) – Mariano tackles an interesting Bank Rec problem Microsoft Dynamics GP Error- Unable to open customizations dictionary | Dynamics GP Help – (Rubal) – digs into all the possible options to resolve this issue

Read More:
Everything Dynamics GP #8

July 25, 2011 · Jivtesh Singh · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 365

Windows 8 Demo

In the past days, my good old friend from the other side of the world, David Musgrave released a sneak peak post into Windows 8. This time, there’s a full 20 minute video showcasing some of the features that will be available in the new Microsoft operating system. If you know own a Windows Phone 7 device, you will find tons of similarities between the two

Read more from the original source:
Windows 8 Demo

June 8, 2011 · Mariano Gomez · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 529

Really???? Dynamics GP Report Writer is the Best Report Writer in the World?

Wow!  I never thought I’d have an opportunity to jump between Polino and Musgrave in a squabble but I just can’t resist any longer.

Original post:
Really???? Dynamics GP Report Writer is the Best Report Writer in the World?

December 6, 2010 · Michael D. Johnson II · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 341