Every accountant can verify that reports are a necessary evil. Microsoft Dynamic GP first supplemented its internal reports with FRx. Which was transitioned out to Management Reporter to the relief of the GP world? Even experts, such as Mark Polino have written since 2016 about how it is time to rethink Management Reporter, as seen in his article here.
Issues with the datamart have failed consultants consistently. I cannot count the number of times I have received a call that their datamart database is over 100GB. We patch it and provide guidance on report retention. Legacy mode, which works the best for most of my clients, has been discouraged. Microsoft has also removed its road-map for the product, essentially putting it in a graveyard for development. What you see is what you will get. There are no new features coming our way. Updates are only to fix the known bugs and issues with the product.
If Management Reporter is now a functionally dead product, we must ask ourselves what product will replace it? My theory is Jet Global. Here are my top 4 reasons. (https://www.jetglobal.com/)
4. Gain some of your time back. Many companies spend hours upon hours every week manually recreating the reports from information pulled from Microsoft Dynamics GP. With Jet Global you set up the report once to pull and calculate from certain fields. The next time your, team opens the report and selects a refresh button. Voila. You got your report and all of your calculations.
3. It is Microsoft Excel based. Every customer I have worked with lives within Microsoft Excel. Whenever I set up an SSRS, Smartlist, or even Management Reporter reports the first question I get is “How do I get this into Excel?” Why go through all that hassle and not just skip directly to Microsoft Excel. Jet Global acts as an Excel Add-on that allows you to securely pull your data into Microsoft Excel. Power Pivot does the same, without the encryption.
2. Which brings me to my third reason…security. As a data professional, I would be remiss to provide any option without consideration to the security of the data. I have had other clients utilize Microsoft Excel data connections. These are not secure and can often have the password for the ODBC within the connection when it refreshes. With Jet Global, everything is encrypted. Even after you are connected, you can have row-level security on the report. Microsoft Excel that uses the data connection doesn’t offer the same level of control over the security.
1. And finally, Jet Global offers additional data connections that will allow your team to connect to multiple data sources at once. Meaning you can have SalesForce and Microsoft Dynamics GP data within the same refreshable Excel report. Additionally, the formulas that you place in from the Excel worksheet will auto-refresh from all connected data sources.
I have had several clients come to me with the following error: GplPostBatch returned the following results: DBMS:0, Microsoft Dynamics GP 20052. This typically occurs when they are posting a Journal Entry in the GL or printing an edit list. The one thing that they all have in common is that they used the Excel Copy Paste Feature.
Solution; To prevent this error:
1. Always have the cursor in the first entry position when you paste from Excel.
2. Only the data that is being copied needs to be highlighted. This would leave the header information blank.
3. If your cursor ends up anywhere other than the blank account after pasting the data, then you will need to delete the blank line that appears at the bottom. Data is still in the line and is only appearing blank.
Solution; After the error already accrued:
1. Go to the end of the journal entry. If you expand the blank entry the entry should be blank. If you expand and its information in the Distribution reference then the entry is reading data in the field. Delete this line.
2. If the error still occurs after the line is deleted, then recreate the Journal entry by repasting it in. Delete the old journal entry.
3. If this is a persistent issue, then consider upgrading to GP2018 R2. In their most recent release, they included a fix list and this error was marked as repaired.
I noticed I have been wanting to write more about other topics than just Microsoft Dynamics GP. I will be expanding to Power BI, Power Apps, and SQL. Because of this, I changed the name of my blog from Straightforward Dynamics GP to Straightforward IT Talk. Anyone have any topics they want to learn about, please feel free to reach out.
Its that time again where Microsoft releases a new update for our beloved product. Here is the countdown of my favorite top 5.
5. Retain the Ship-To-Address with Customer Modifier. In prior versions of Microsoft Dynamics GP, the Ship-To-Address name field was removed whenever a customer was modified with the modifier utility or the customer combiner.
4. A new smartlist that shows “Deposits on Unposted Sales Transactions.” This can be found in the smartlists by going to Sales -> Sales Transactions-> Deposits on Unposted Sales Transactions. This will allow your users to save time by not searching through sales transactions to see if any deposits exist. It is programmed to look at the Sales Order WORK table (SOP10100) with a ‘Deposit Received’ (DEPRECVD) greater than zero.
3. Excluding Inactive checkbooks in your lookup. I had a customer ask for this feature two months ago and Microsoft has delivered. It can make your users spot the correct checkbook even faster!
2. Add Vendor Document Number to Purchasing All-In-One View. When I did not think the All-In-One view could get better. This tool allows users to track related documents through the system. Where I ran into trouble when troubleshooting documents is that the vendor document number is the one known and not the document number. With this update, it has been added to the payments and credit documents columns.
1. The Vendor Hold Status will produce a warning when a user enters a transaction. Many clients complain that after they entered transactions they found that the vendor was on hold. Microsoft added a warning, “Vendor XXXX is currently on hold. Do you want to continue?,” to the Payables Vendor Inquiry, Payables Transaction Inquiry – Vendor, the Purchasing All-In-One View, and Payables Transaction Entry Zoom. These windows show a red stop sign after the vendor ID and show a clear indication that the Vendor has issues.
These are my Top 5 favorites. Has anyone else had a chance to review the changes?
Recently I have logged into a client’s new server. They recently upgraded and have a new report server. They requested that I edit an SSRS report for them. This was delayed because of the error: “This page might not function correctly because either your browser does not support scripts or active scripting is disabled.”
After some trial and error, looking at permissions and tech guides. I found that this was resolved in an IT 101 troubleshooting task. I added it to the Trusted Sites in Internet Explorer.
One of the most frustrating thing that happended to me recently was looking for an unenclosed quotation mark after a character. The error message “Unclosed quotation mark after the character string” appeared when a client ran a Historical Aged Trial Balance for Payables Management in Microsoft Dynamics GP. Microsoft offered a solution here. My initial thought that I would be able to find the problem voucher with little effort. It should have been a simple select statement filtered to look for a wildcard and single quotation. I was wrong.
I found that T-SQL likes the single quotes as much as my cat likes water. I still have scratches from the last bath (not pretty). Here is a screenshot of my first attempt.
The syntax wasn’t compatible with my theory. After about an hour of attempting different solutions, I found that you had to search for wildcards before and after the quotations. Here is the SQL solution.
Select * from PM30200 where Doctype=’6′ and VCHRNMBR like ‘%”%’
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.
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.
Let’s face it, people like free items. Free is amazing. You get something for nothing. In the case of most businesses, they get more patrons coming into their venue and staying longer when they offer this server. People stick around longer and potentially spend more money. Seems like a win, win.
In all reality, free Wi-Fi should be treated as a creepy stranger offering a child a swirly lollypop. It’s enticing. Just within your reach. It also has the unforeseen consequence of stealing your life away.
There are two common ways that hackers accomplish this. The first is that they hack the free Wi-Fi and view all the connections currently connected to the Wi-Fi connection. Why go through all that trouble. People are trusting. If they see a free Wi-Fi connection at a business they will assume that it is offered through the business. In highly congested areas with many cafés or shops, there could be many options that show up on the Wi-Fi connection search. Because of this aspect of social engineering, many hackers do not bother to hack into a network, they will simply create their own.
Once they create their own free Wi-Fi connection, all they have to do is sit with a cup of coffee until someone connects to it. Once their victim is connected, they have access to everything they are doing on their device.
What you can do to avoid being a victim
- Don’t connect to a public Wi-Fi (not always possible)
- If you have Xfinity, then you can connect to the Xfinity anywhere. Xfinity offers secure connections to all their customers to connect through other routers by logging in with their Xfinity accounts. Other companies may offer similar user experiences.
- Don’t access banking or financial information while connected to a public Wi-Fi. Even if you turn off your network discovery they can still use packet sniffers to see what you are accessing. Don’t access anything that you would want someone to see.
- Secure Access to your computer. Go to Control Panel >> Network and Sharing Center>>Change Advanced Sharing Settings. Verify that network discovery is turned off for Guest or Public