On March 9, 2018, Microsoft in partnership with Orion Systems Integrators will host another Dashboard in a Day event at its Pittsburgh office. I encourage anyone interested in learning more about Power BI’s business analytics service to monitor your company’s health to attend this event.
This hands-on event is a built around a demo environment, however, attendees are encouraged to bring their own CSV or excel documents to play and explore with a familiarity.
Here is a link to the registration.
If you are not near Pittsburgh, then I would recommend reaching out to your local Power Bi group to see if they host or can create a similar event.
Over the past few months, I have spoken with many customers and discussed Power Bi and how it can benefit their company. There is always a push back because people do not believe they need the service or they already had a report for that and it seems too complicated. In retrospect, it seemed complicated because it was a new, unfamiliar way to analyze and filter their company data. From my perspective, Power BI can be useful for your accountants to your CEOs and everywhere in between.
Instead of long complicated reports, Power Bi creates dashboard snippets of your data that allow you to grab a snapshot of where your company is at that point in time. For example, I created power BI template for a that mimics the Payables Aging Summary, as you can see in the screenshot below. What makes this unique from other reporting solutions, such as Management Reporter, Jet Reports, SSRS, Etc, is that it is interactive. You can narrow the slicer for the aging bucket to show you the items in only the 1-30 days. Or you can select a vendor on the left and it will filter all the invoices that create those totals. It’s a snapshot that is intuitive and does not need an analysis of 10 pages to get the final total. It connects to the database and updates the data with a simple refresh.
Or you can place the report in the Power Bi Portal and share it within your company or outside your company, based on your payment plan. There are different ways to access the data.
- Desktop: This version allows you to create reports on your desktop and uploads them to the Power Bi Service. Clients have the option to create reports and storing them in a share. Then everyone with permissions to the share and the desktop version installed could open the reports and refresh them. The desktop version of Power BI is free as of February 2018.
- Service: Power Bi offers the Power Bi Services which is their online portal. This is where the pricing can become tricky and I would recommend consulting with a Microsoft partner if you have questions.
- Free/without subscription: You can upload a report to your dashboard within the Power BI Service and access it on your mobile device for free. This feature limits the ability to share with other users or auto update the report.
- Office 365 business license: This is tricky for Office 365 administrators to locate, however once installed it will allow users to share Power BI reports only with users in your company. When you log in to your Office 365 admin portal you would go to Billing, then Select Subscriptions. Under the subscriptions select Add subscriptions. You will look for one that says “Power BI (free).” You will select your license quantity and this choice will allow you to share Power BI reports within your company.
- Pro: There is a paid version of $9.99 a month/ per user, that allows you to share reports, internally and externally.
- Office 365 Enterprise E5 license: With the Office 365 Enterprise E5 license for office 365 it includes Power BI. This license is purchased on a per-user basis.
- Mobile: When your reports upload to the Power BI service you can download the iPhone or Android app from their respective app stores. Once you sign on via the app, you can open your reports and view from anywhere you have an Internet connection.
With so many options, it can overwhelm for some companies that are still trying to grasp what Power BI is and if it can be useful for making their lives easier. Several companies may find the Payables Aging Summary useful, while others have no need for this specific data. It comes down to what our clients need and asking the right questions to determine where their current reporting structures are failing them. Power BI just provides the tools to see your business in a snapshot without long tedious reports.
The pricing in this post is based on the current cost on their website as of 2/10/2018. Pricing is subject to Microsoft and can change. For current information, please review their pricing page.
Here is a link to the Office 365 licensing comparison.
I’m sure that this has happened to everyone, you follow the steps and you launch your web client web page. Then, after you log in you receive the dreaded error, “A problem occurred creating a session error.” Often the first response is to repair the web client and proceed to check the local certificate. This is followed by checking the services. And alas, nada. None of these processes fix the error.
The problem is not the web client install, but the added feature on the actual Microsoft Dynamics GP installation. One misconception about installing the web client is that multiple Runtime installs exist. You have the Web Client Runtime and the Web Services Runtime. The Web Client Runtime is often missed because it hides in the Microsoft Dynamics GP install.
1. Web Client Runtime: On the Web Client server go to Programs and Features, select Microsoft Dynamics GP 2015 (Desktop Client Install), click Add/Remove Features and check if Web Client Runtime is installed. If this runtime is not installed edit and then try logging into the Web Client.
2. Web Services Runtime: The second Runtime is located in the GP installation. When you open the installer you will see the Web Services Runtime.
I’m certain that this has taken place in many businesses. Someone logs in for the day and when they print a report or a check, they receive this error. Or they receive a new computer with a fresh copy of Microsoft Dynamics GP and when they try to print a report, the error stops them. This error is interesting because it can happen in different circumstances. It is one of three problems, a missing reports dictionary, a missing 3rd party, or a communication issue with the reports share. Every time I see this error I take specific troubleshooting steps.
1. If the install is a new install on the PC, I check the reports dictionary by going to Tools>> Customize >> Customization Maintenance. If there are no reports stored here, my next step would be to identify the storage location of the reports. On new installs, the reports need imported or pointed to the shared location. If they are on a share, I would point the GP installation to the share.
2. If there are reports visible and they will not print, I navigate to the shared reports folder while logged in as the user. This verifies that the user has access to the share. If you cannot navigate to the shared file location, it may not be a problem with Microsoft Dynamics GP. The permissions to the folder the reports dictionary writes to could have communication issues. Anytime there is a communication issue, I recommend having the server checked over by your IT department. If you are a technical consultant, I would recommend that you take a more in-depth look at why the communication issue appeared. In past situations, I identified communication issues because disks malfunctioned or the operating system corrupted. The result was the server being decommissioned and replaced. Another example, I discovered that they had Cryptolocker on the file share and the virus encrypted the dictionary within the shared folder.
3. Check the form to verify that the correct security is set by going to Tools >> Setup >> System >> Alternatives/Modified Forms and Reports. If you had a 3rd party and removed it, this could have no selection. In one example, I had no selections in this window. By selecting the default Microsoft Dynamics GP report in this window, the report could print again.
Microsoft Dynamics GP 2018 was released in December 2017 and new features are abound. One of the most impressive features for me is the changes to the workflow process, which defines how information flows through the system.
Workflow 1.0, released with Microsoft Dynamics GP 2013, worked the same as it did in Business Portal without the dependency on Sharepoint to function. Microsoft Dynamics GP has expanded workflow’s capabilities with each version. It had started with a workflow for Purchase order approval and has increased to six modules. In Microsoft Dynamics GP, the following workflows available in their most recent release:
- General Ledger Account Approval
- General Ledger Batch Approval
- Receivables Batch Approval
- Payables Batch Approval
- Payables Transaction Approval
- Purchase Order Approval
- Purchase Requisition Approval
- Purchase Invoice Approval
- Receivings Transaction Approval
- Vendor Approval
- Employee Profile Approval
- Employee Skills Approval
- Payroll Direct Deposit Approval
- Payroll Timecard Approval
- Payroll W4 Approval
- Expense Report Approval
- Timesheet Approval
- Smartlist Designer View Approval
Originally, Workflow 1.0 restricted its functionality into four purchasing workflows. In Workflow 2.0, it became compatible with the Web Client and integrated with Active Directory. Workflow 3.0 improved set conditions and created notifications within GP to alert users. The release of Microsoft Dynamics GP 2018 added new interesting features to expand Workflow. The two most expected are the reminders and copying of workflow steps.
Microsoft Dynamics GP 2018 added the ability to send reminder e-mails without having end users tracking down their supervisors, or the workflow administrator reach out to the approver. Reminders allow users to select intervals of when the reminders go out. The interval’s present daily or even hourly options.
2. Copy workflow steps
In previous versions of Microsoft Dynamics GP, copying an entire workflow was the only way to make changes. The new release brings the capability to copy a step within a workflow. If you have complicated workflows that require you to add multiple steps that are approximately the same, thus this will save you hours of work.
Payroll in January is the most havoc time of year. One common problem I see on the help desk in January is the 1095C printing a form for an employee that has not worked this year or has been inactive since before the current year.
To correct this there are two scripts to run. Both of these scripts are from Terry Heley’s ACA post which can be referenced here. In both scripts, please replace XXX for field YEAR1 with the current year.
This script will remove any employee without coverage for the year:
delete from upr10111
where (MonthofCost_1=’0′ AND MonthofCost_2=’0′ AND MonthofCost_3=’0′ AND MonthofCost_4=’0’and
MonthofCost_5=’0′ AND MonthofCost_6=’0′ AND MonthofCost_7=’0′ AND MonthofCost_8=’0′ AND
MonthofCost_9=’0′ AND MonthofCost_10=’0′ and MonthofCost_11=’0′ AND MonthofCost_12=’0′ AND
MonthofCoverage_1=’0′ and MonthofCoverage_2=’0′ and MonthofCoverage_3=’0′ and MonthofCoverage_4=’0’and
MonthofCoverage_5=’0′ AND MonthofCoverage_6=’0′ AND MonthofCoverage_7=’0′ AND MonthofCoverage_8=’0′ AND
MonthofCoverage_9=’0′ AND MonthofCoverage_10=’0′ AND MonthofCoverage_11=’0′ AND MonthofCoverage_12=’0′)
This script will need to have the year replaced in two places. This will delete users who are older than the current payroll year. For example , n 2016 you would replace the XX with 2016.
DELETE UPR10111 WHERE EMPLOYID IN (SELECT EMPLOYID FROM UPR00100 WHERE INACTIVE = ‘1’
AND DEMPINAC <= ‘XXX-01-01 00:00:00.000′) and YEAR1=’XXX’
What these scripts do is update the UPR10111 table where the wage file creates data for the 1095C and 1094C transmittal form.
This information can be found on Terry Heley’s Blog, the payroll ACA information can be found here. Her blog is an amazing source of information and I highly encourage everyone to check it out.
Disclaimer: Always run a new script in your test environment and run a good backup before you run any scripts that will delete or change data.
In Microsoft Dynamics GP, users have access to nothing until security access to windows, reports, and files are granted. There are standard Roles and Tasks are the pre-defined set of windows, reports, and files based on the tasks they are granted. Microsoft built the system in a way that allows Administrators to edit the Roles and Tasks to fit our requirements or even create new ones.
There are specific building blocks that build on one another to create the security in Microsoft Dynamic GP.
- Operation: Is the base level access to windows or reports. These are assigned to a task.
- Ie. The Account Maintenance window
- Task: A Task component is the group of operations that are needed to complete a business task.
- Role: The Role component is the group of tasks that define a particular job in a company.
A great tool to lookup security Roles and Tasks for specific windows or reports is GP Window.
The roles that are assigned to each user are company-specific and you would be able to assign different roles to different companies if needed.
Setting up a Task
Click Microsoft Dynamics GP, point to Tools, point to Setup, point to System, and then click Security Tasks.
Then lookup or create your task ID. If this is a new task then you would need to fill in the Task Name and Category.
Then Select a Product, Type, and Series and this will bring up a selection to provide access. If it is checked then the task will have access to the window or report.
Setting up a Role
On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to System, and then click Security Roles.
Then lookup or create your Role ID. If this is a new task then you would need to fill in the Role Name.
You will then be able to select various security tasks to apply to the Role. If it is checked then the Role will have access to the Task.
Applying user security
On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to System, and then click User Security.
Then look up your user and select the company that you would like to apply the Role. You will need to apply this to each company. If the same permissions are for the same for each company then you can use the copy button on the ribbon to copy the setup between companies.
You will then be able to select various security roles to apply to the user’s security.
Microsoft Dynamics GP Deleting User Error “Deleting the login failed for an unknown reason. Contact your SQL Server administrator for assistance.”
￼Every time I have encountered this is when a user is set up to a specific company database. To resolve this follow these steps:
- Go to your SQL and verify that the user has a SQL login setup. If they do then follow
- Expand your company database
- Expand Security
- Expand Users
- Right-click on the user you are trying to delete and select Delete. This will remove the user’s SQL profile from the database and any schema’s that are causing the error.
- Repeat for all company databases
- Repeat in the Dynamics database
NOTE: DO NOT REMOVE THE OVERALL SQL LOGIN only under each specific database
Log back into Microsoft Dynamics GP and remove the user through the normal user setup. This will remove the user from Microsoft Dynamics GP and also SQL.
After you are comfortable with selecting information and isolating it, I would then recommend moving on to DELETE statements. You must be cautious when running a delete statement as this will remove data from your system. Because you will be making a change to your data, I always recommend getting into the practice of creating a backup before running any statement that will alter your data. You may think, I am extremely careful and this is unnecessary. I used to think this and then accidently delete an entire table with a poor query. The only way I was able to fix my mistake was by having a backup of the system.
Typically, I would create a select statement for the information that I want to delete and then replace SELECT * FROM table WHERE Condition with DELETE table WHERE Condition. In Microsoft Dynamics GP I will typically use the Column Dex_Row_ID to narrow my results when I only want to remove one line. This is because Dex_Row_ID is a unique key and does not duplicate in any of the tables.
SELECT BACHNUMB, MKDTOPST, Dex_Row_ID FROM SY00500 WHERE DEX_ROW_ID=’5′
Would then be changed into a delete statement with the following
DELETE SY00500 WHERE DEX_ROW_ID=’5′
When using a select statement and a delete statement in the same query window, it is best to also use two – in front of a statement that you do not want to run right away. This can assist with accidently deleting the wrong row or table.
Top Tips from This Post
- Always make a backup before running queries that alter data.
- Isolate the information you want to delete with a select statement.
- With Microsoft Dynamics GP, the Dex_Row_ID Column is great to isolate information.
- Use — before delete statements to keep the statement from accidently running
- In Microsoft Dynamics GP it is recommended to delete records through the system if possible and only manually remove records via SQL as a last result.
Knowing where to look to run a query is also extremely helpful to getting started. There are three locations within SQL Server that you need to be aware of to get started.
- You can select New Query and this will bring up a new window to type your SQL query.
- You will see a box appear under the new query with the name of one of the databases. Whatever is in this box is the database that the query will run against (except if specifies otherwise in your query).
- The third box is the name of the databases. If you right click on your database that you want to pull information from and select New Query.
- You can highlight part of a query and it will not run the entire script, only what is highlighted.
- You can select F5 or the execute button in box 2 to run the query.
- Adding – before a query will make it not able to run. This is useful if you have a select statement and a delete statement in same query window and don’t want the delete statement to run yet.