What this book covers
Chapter 1, Getting Data from Dynamics GP 2016 to Excel 2016, covers the first step to building a report or a dashboard and getting data. In this chapter, we discuss the many options of getting GP Data (and in some cases, any SQL data) into Excel.
Chapter 2, The Ultimate GP to Excel Tool – Refreshable Excel Reports, walks us through using prebuilt Excel reports in GP. This chapter also covers two other important topics—granting security to the GP (SQL) data and how to refresh the data in the reports once they are built.
Chapter 3, Pivot Tables – The Basic Building Blocks, explains the most important element of the dashboard, the Pivot Table. The Pivot Table is such a powerful tool; every Excel user should make it their go-to tool of choice.
Chapter 4, Making Your Data Visually Appealing and Meaningful with Formatting, Conditional Formatting, and Charts, guides you through formatting options. Formatting is more than just making a chart pretty. What's more valuable when you are driving in your car, the gas gauge or the number of miles you can drive with the amount of gas you have?
Chapter 5, Drilling Back to the Source Data and Other Cool Stuff, helps you add credibility to your report. Being able to look at the data in its source with a single click will make you the office champion. This chapter also covers slicers and timelines, which are essential for creating focus on your data.
Chapter 6, Introducing Jet Reports Express, explains not only why you would want to use this report to create basic financial statements inside Excel, but also why they are giving their product away for free. We'll even build an Excel-based General Ledger Trial Balance.
Chapter 7, Building Financial Reports in Jet Express for GP, walks us through building a simple Balance Sheet and a simple Profit and Loss report. Using the foundations learned in the chapter, you can become a lean, mean financial report writing machine.
Chapter 8, Introducing Microsoft Power BI, acquaints us with Microsoft's new pride and joy, Power BI. This chapter is an overview of the product itself. Understanding where and how the reports are consumed is essential in planning how to build them.
Chapter 9, Getting Data in Power BI, reviews (only) some of the options of getting data into Power BI.
Chapter 10, Creating Power BI Visuals, will probably be your favorite chapter. In this chapter, we will actually create the charts, cards, tables, and other visuals that display our data.
Chapter 11, Using the Power BI Service, is where we learn how to publish and consume our data on the Web and, therefore, our mobile devices. We will even learn how to combine individual visuals on different reports to make a single dashboard.
Chapter 12, Sharing and Refreshing Data and Dashboards in Power BI, followed by the summary of this chapter.
Chapter 13, Using the Power Query Editor, is probably the most important chapter in the Power BI section. Rarely our data is formatted (or modeled) exactly the way we need for reports. It could be that we just want to combine data from our GP with data from our CRM. This chapter covers how to edit or model our data.
Chapter 14, Bonus Chapter, is kind of the proverbial kitchen sink. We'll see two additional features for Excel—Jet Express for GP and Power BI. This was just for fun!
