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.