Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Building Dashboards with Microsoft Dynamics GP 2016
  • Toc
  • feedback
Building Dashboards with Microsoft Dynamics GP 2016

Building Dashboards with Microsoft Dynamics GP 2016

By : Belinda Allen, Polino
5 (1)
close
Building Dashboards with Microsoft Dynamics GP 2016

Building Dashboards with Microsoft Dynamics GP 2016

5 (1)
By: Belinda Allen, Polino

Overview of this book

Microsoft Dynamics GP is a complete ERP solution that is extremely beneficial for small to midsize organizations in helping them grow exponentially. The book shows you in detail how to build great-looking dashboards with Microsoft Dynamics GP that enhance a company’s decision-making processes. This guide will take you from the basics of setting up and deploying to creating secure, refreshable Excel reports. Using a whole host of tools available within Microsoft Dynamics GP and Excel, this tutorial will show you how to visualize your data using simple conditional formatting techniques and easy-to-read charts, and allow you to make your data interactive with slicers. We will also cover core topics such as Business Analyzer, Microsoft SQL Reporting services reports, BI360, and more. You will find out to use Power BI, share and refresh data and dashboards in Power BI, and use Power BI Query Editor. By the end of this book, you will have all the information required to build interactive dashboards using Dynamics GP.
Table of Contents (16 chapters)
close
close
Preface
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Free Chapter
1
1. Getting Data from Dynamics GP 2016 to Excel 2016
chevron up
1. Getting Data from Dynamics GP 2016 to Excel 2016
SmartList exports
SmartList Export Solutions
Office Data Connection files
SQL Server Reporting Services
Jet Reports Express for Excel
SQL Server Management Studio
Analysis Cubes
Third-party solutions
Licensing
Summary
2
2. The Ultimate GP to Excel Tool – Refreshable Excel Reports
2. The Ultimate GP to Excel Tool – Refreshable Excel Reports
Security
Running Excel reports
Excel refreshable reports via SmartList Designer
Summary
3
3. Pivot Tables – The Basic Building Blocks
3. Pivot Tables – The Basic Building Blocks
Creating pivot tables from GP 2016 Excel report data
Creating pivot tables from GP 2016 data connections
Copying pivot tables
Creating connected pivot tables from inside Excel
Summary
4
4. Making Your Data Visually Appealing and Meaningful with Formatting, Conditional Formatting, and Charts
4. Making Your Data Visually Appealing and Meaningful with Formatting, Conditional Formatting, and Charts
Recap
Preparation
Get pivot data
Icon sets
Sparklines
Data bars
Bar chart with trend line
Pie chart
Speedometer chart
Slicers and timelines
Some more formatting
Summary
5
5. Drilling Back to the Source Data and Other Cool Stuff
5. Drilling Back to the Source Data and Other Cool Stuff
Recap
Slicers and timelines options
Learning about hyperlinks
Using drill downs in GP 2016
Other cool Excel stuff
Good design
Refreshing the data
Sharing
Summary
6
6. Introducing Jet Reports Express
6. Introducing Jet Reports Express
Recap
What is Jet and why should I use it?
What is Table Builder?
Other Jet offerings
Summary
7
7. Building Financial Reports in Jet Express for GP
7. Building Financial Reports in Jet Express for GP
Recap
Building a balance sheet
Building a profit and loss statement
Summary
8
8. Introducing Microsoft Power BI
8. Introducing Microsoft Power BI
Recap
Power BI Desktop versus Service versus Mobile
Power BI Professional (paid) versus Power BI (free)
Typical workflow of Power BI
Update speed
Summary
9
9. Getting Data in Power BI
9. Getting Data in Power BI
Recap
Getting data from files
Connecting to data in Dynamics GP
Content packs
Getting data from folders
Summary
10
10. Creating Power BI Visuals
10. Creating Power BI Visuals
Recap
Using Filters
Formatting as a tool
Understanding standard visuals
Development options - if you have a techie on staff
R - what is it and do I need it?
Summary
11
11. Using the Power BI Service
11. Using the Power BI Service
Recap
Publishing to the service
Creating a dashboard
Getting to know the Q&A feature
Importing an Excel report
Summary
12
12. Sharing and Refreshing Data and Dashboards in Power BI
12. Sharing and Refreshing Data and Dashboards in Power BI
Recap
Power BI Template
Content Packs
Online Services
Refreshing data
Summary
13
13. Using the Power Query Editor
13. Using the Power Query Editor
Recap
What is a query?
Using Applied Steps
Removing unnecessary column(s)
Formatting column Data Types
What is the M language?
Using Replace Values
Transforming data
Working with dates and times
Merging columns
Splitting columns
Merging queries
Appending queries
Summarizing with Group By
Summary
14
14. Bonus Chapter
14. Bonus Chapter
Recap
Excel 2016
Jet Express for GP
Microsoft Power BI
Summary
15
Index
Index

Office Data Connection files

Excel has its own method of importing external data from a variety of sources, including data in the SQL Server. This method can be embedded directly in the workbook or stored in a separate file, the Office Data Connection (ODC) file. When this .odc file is created, it can be reused over and over for a quick connection between your GP and Excel that is refreshable.

Creating an .odc file

Let's create an .odc file to bring in our vendors using these steps:

  1. In Excel 2016, go to Data | From Other Sources | From SQL Server:
    Creating an .odc file
  2. The Data Connection window will open. Enter the name of your server and your GP log in credentials. Click on Next.
    Creating an .odc file

    Tip

    If you do not know the server or database names, consult your IT department or your GP Partner. Usually, the server name is the name of the machine on which the SQL Server is installed.

  3. Select the database you want to report on and then select the view called Vendors. Click on Finish:
    Creating an .odc file

    Tip

    The database name can be found in the upper-right corner on the Company Setup window in GP.

    In the Excel Data Connection Wizard window, first, SQL Views are displayed in alphabetical order; then, SQL Tables are displayed in alphabetical order.

  4. The Import Data window will open, providing you with the option of either importing the data into an Excel Table, a PivotTable Report/PivotChart, or just creating the connection between SQL and Excel. Let's leave the option marked as Table and click on OK. The data will then import into the spreadsheet:
    Creating an .odc file

The location of the .odc file

We'll find the location of the .odc file we created earlier, with the spreadsheet still open. Follow these steps:

  1. Open the Connection Properties window by going to Data | Connections | Properties…
  2. Click on the Definition tab, and the Connection File path will be displayed. For me, our file and path is C:\Users\Belinda Allen\Documents\My Data Sources\cherry TWO Vendors.odc. As you can see, the actual .odc name is a combination of your server, your database, and the table/view you used for the connection:
    The location of the .odc file

Reusing an .odc fie

One of the great features of using an .odc file is that once it is created, it can be used over and over again. Let's reuse the connection file we just created and follow these steps:

  1. Open a blank Excel workbook. Go to Click Data | Existing Connections and scroll to find the connection you just created. For us, it is cherry TWO Vendors. Click on Open:
    Reusing an .odc fie
  2. The Import Data window will open, asking how you want to view your data: Table, PivotTable Report/PivotChart, or if you just want to create a connection.

    Tip

    Note that you can also simply double-click on the file in Windows Explorer, which will open Excel and take you directly to the Import Data window.

  3. You'll then be prompted for your SQL login, which is the same as your GP login.
End of Section 4
Next Section
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete