Top Reasons To Love Microsoft Power BI

  1. Direct connection into SQL databases to connect into Microsoft Dynamics GP and also Microsoft Dynamics 365.

This product is designed to work with a variety of data sources.  Including Excel, CSV, XML, Access databases, Microsoft SQL Server, MySQL, etc.  If you have data stored, there is a high chance that Microsoft Power BI will connect to it with little difficulty.  Additionally you can connect multiple data sources into one report.

  1. Access from anywhere

Sharing is caring.  This cloud based solution offers access to anyone you permit at any time of the day and from anywhere.  It has mobile apps on Apple, Android and Windows devices.

  1. Easy to comprehend and create data.

Visual charts and graphs make Power BI useful for all users.  The charts setup is also drag and drop.  Simply select your data and it is there.

  1. Up to 1GB is free

Typically, free users are extremely limited, and in this case they are limited.  However, even with the limitations, users can create dashboards and customized reports.  They can still connect to their data and schedule refreshes.  The cost of $9.99 the paid version offers additional features and flexibility with the software that is not available for the free version, please see their pricing page for more detailed information of the differences.

  1. Designed for the non-technical person.

What you normally see with many reporting systems, is that the person creating the reports has to have hours upon hours of training and trial and error to become competent at creating the reports.  With Microsoft Power BI, they have created a solution that allows the non-technical person to hit the ground running.  Simply install the Power BI Desktop, connect to your data, and start creating.

Disclaimer:
The pricing in this post is based on the current cost on their website as of 2/6/2017.  Pricing is subject to Microsoft and can change.  For current information, please review their pricing page.

Business Intelligence: When Standard Reporting Is Not Enough

It should be no surprise to anyone that we are living in a digital world and data is being kept on the most mundane items.  Especially in every aspect of a business.  Once a business creates data, it needs to be able put it together in a meaningful, productive way.  Reports once filled this void.  There are many reporting solutions that work with Microsoft Dynamics GP.  Such as Management Reporter, Crystal Reports, Excel Reports, and Jet Reports.  The question becomes, what do you do with the information that you have at your fingertips when that monthly report is not enough?

The next step is Business Intelligence.  Once thought to be a flash in the pan, is now on its way to becoming the standard for driving business decisions for many companies.  As a consultant, I see a constant need to have relevant data and Power Bi create real-time dashboards with your data to make data-driven decisions.  It can connect to wherever you store your information and be made available to you anywhere.  This is the next step to the standard reports and can be used in conjunctions with reporting to drive your business to the next level.

Microsoft has created an extremely powerful tool called Power BI.  Power BI creates dashboards with relevant, constantly updated information that you can have at your fingertips to make business decisions.  You can create a dashboard item for any variety of reasons, such as the current sales for the year, see where your sales are at, or even compare current year sales with past years.

 

 

Understanding SELECT Statements

The first time anyone looks at SQL can be overwhelming.  Mostly because it is like learning a foreign language.  You need to learn the vocabulary and the syntax to get what you want.  The first step to learning should be how to navigate information.  In my examples, I will be using examples from Microsoft Dynamics GP 2015’s database.  I will also be using the table PM00200 (Vendor Master) for all Select queries for my examples.

The simplest concept within SQL querying is the select statements.  Once you understand the concept you would be able to apply the select statements to all types of databases.  It is important to note that a select statement does not change data in any shape or form.  Select statements are simply a method to pulls data in a way that is simpler to analyze.

The vocabulary that we will be using in this post are:

SELECT – Pulls rows and columns from the database
FROM – Specifies the specific table or view.  Always used in conjunction with a SELECT, DELETE, and UPDATE statement.
WHERE – Specifies additional conditions for a SELECT, DELETE, or UPDATE statement.
GROUP BY – Specifies which columns you want to have information grouped by.
ASC – Is used with GROUP BY to place items in an ascending order.
DESC – Is used with GROUP BY to place items in a descending order.

Let’s start with the basics.  A simple select statement.  All select statements have similar components.  If you have questions about navigating the SQL query windows please see my post Understanding SQL Query Window.

  • They all start with the word SELECT.
  • Then they specify the columns. If you want all columns in a table to appear then you would select an asterisk (*).  If you want specific columns, then you would select the column name and separate then with a comma.
  • Then you would use the word FROM to specify where the columns are located.
  • Then you will type the column name.

Using my example for this you would type the following to return all columns

SELECT * FROM PM00200

sql2-1

This returns all the columns.  Let’s say we only want the vendor ID, Vendor Name and their state.  We would then take the column names and replace the asterisk.

SELECT VENDORID, VENDNAME, STATE FROM PM00200

sql2-2

Now let’s say we only want to see the vendors for the state of Illinois.  To accomplish this we would add in the WHERE clause.

SELECT VENDORID, VENDNAME, STATE FROM PM00200 WHERE STATE=’IL’

sql2-3

Please note that the single ‘ sometimes does not copy and paste correctly into the query screen and you will need to delete and retype this into the query window.  Adding ASC or DESC to the end will change how the order goes.

sql2-4

SELECT VENDORID, VENDNAME, STATE FROM PM00200 ORDER BY STATE ASC
SELECT VENDORID, VENDNAME, STATE FROM PM00200 ORDER BY STATE DESC

ASC is the abbreviation for ascending.  DESC is for descending.  Meaning ASC will place the items in alphabetical (ascending) order and DESC will place the items in reverse alphabetical (descending) order.

Integration Manager Error: “RPC Server is unavailable”

This error has been the most troublesome for me in the past.  Typically of you try searching the error you will get pointed to KB article 943948.   What I have found is that this is caused when you run an integration and Microsoft Dynamic GP seems to disappear and Integration Manager freezes or gets closed down integration.  I have also seen this occur after an error and the user tries to re-run the integration.

What is occurring is that Integration manager and Microsoft Dynamic GP still has processes running in the background.  If you go into task manager and look at the running processes you will see multiple for Microsoft Dynamics GP for the user trying to run Integration Manager.  You have two solutions, either go into task manager to stop the processes and test again or have the user reboot.

Sometimes just stopping the services does not resolve the error and a reboot is still required.  If a user is logged into a terminal server it is important to verify that they fully log off the server and simply does not disconnect.

 

Top 5 SQL Tips for Microsoft Dynamic GP

  1. Think necessity – Only look for the information you really need. The fewer rows that you pull in the quicker your query will be.
  2. Keep it simple stupid (KISS) – When you attempt to get fancy with the queries, it can have consequences. Meaning that the queries can take longer and pull unnecessary data.  Overcomplicating a query can cause unintended headaches that are avoidable.
  3. Select before a change – Select statement before you change it to a delete or update statement
  4. Measure twice, cut once when restoring a backup – Setup the restore and then go through the screens to verify that you have the correct databases, MDF and LDF selected.
  5. Always make a backup – Before you start troubleshooting an issue ALWAYS make a backup. Even if you do not anticipate making changes, make the backup.  For example, when I accidently dropped a table from the database while troubleshooting, anticipated hours of hardship were avoided by backing up the database before I started making changes.

Top 3 Reasons Your Database Maintenance Utility Won’t Run

1.  The utility is run during a SQL server move to verify the database.  This is always necessary when moving clients into a Multitenant move.  I have run into an issue where the version of GP.

2.  The database has an open query in SQL. If you ran a query in SQL and the Query window is still open then this can.

3.  There is a process for the database running in SQL Activity Monitor.

Change a Vendor to a 1099 Vendor in Microsoft Dynamics GP

We have all been there, a vendor changes their status from being a non-1099 vendor to a 1099 vendor.  You can simply go into the vendor card and edit the setting to make them a 1099 vendor.  The only problem with this situation is that historical transactions posted in Microsoft Dynamic GP would still show the vendor as a non-1099 vendor.

This would be a total nightmare, however, you’re in luck that Microsoft Dynamics GP has built functionality into GP 2013 and higher that enables us to easily change the status for a vendor and their entire history.

If you are on an older version than GP 2013 then you would need to use Professional Services Tools Library (PSTL).  The Professional Services Tools Library (PSTL) has a modifier tool for the 1099 vendors.  For additional information, you can also reference KB Article 918536.

For Microsoft Dynamics GP 2013 and higher, please follow these steps.

1.  Navigate: Microsoft Dynamics GP>>Tools>>Utilities>>Purchasing>> Update 1099 Information.

2. In the Update 1099 Information window, select the last radio button for ‘Vendor and 1099 Transactions‘.

6-1

3. In the FROM and TO sections, select the appropriate

Method
Method 1: currently not being a 1099 vendor to BE a 1099 vendor
FROM
Tax Type = Not a 1099 Vendor*
1099 Box  Number is grayed out
TO
Tax Type = Miscellaneous (or appropriate 1099 type as needed)
1099 Box Number=7 (or appropriate 1099 box number as needed)

6-2

Method 2: vendor from currently being a 1099 vendor to NOT BE a 1099 vendor
FROM
Tax Type = Miscellaneous (or appropriate 1099 type the vendor had)
1099 Box  Number = 7 (or appropriate 1099 box number the vendor had)
TO
Tax Type = Not a 1099 Vendor*
1099 Box Number is grayed out

6-3

4.  Look for the ranges section. From here select a vendor for FROM and TO. Restrict the update to your criteria by selecting insert.

6-4

5. Click process at the top to update all the information within your selected criteria.

6-5

6. Print the Update 1099 Information Audit Report to the screen and/or printer. It is highly recommended to verify that all changes were correct. To print this, you will automatically prompt after you select the process button and its processes.

6-6

7.  Verify vendor setup:  Navigate: Cards>> Purchasing>>Vendor. Select the Vendor ID and click on options button. Under this window verify that the 1099 selections are correct. The process should have automatically changed this for you.

8. Verify transactions: If you did Method 1 above, now navigate: Transactions>> Purchasing >>Edit 1099 Transaction Information. Enter the Vendor ID and select redisplay, the vendor’s transaction history should populate the bottom section of the window. Review all the transactions.

Note: You can edit the Tax Type, Box number and 1099 Amount for each individual transaction as needed and remember to select the process to apply the changes.

Note 2: You can also make a smartlist to review all the transactions.