Aaron Berquist Rotating Header Image

Dynamics GP

Tool for Improving “Mark All” Speed in Contract Administration

Dec 1st, 2010
by Aaron Berquist.
5 comments

One of my users complained recently that, when using the “Mark All” button on the Revenue Recognition screen in Contract Administration (Dynamics GP: Transactions: Contract Administration: Revenue Recognition), it would sometimes take 6-8 hours for the process to complete. This was not to post the revenue, merely to mark all of the records in the window for processing.

Now, in some of our larger company databases, over 30,000 records were being marked to process, due to a combination of:

  • # of contract records
  • the business process of recognizing multiple months worth of revenue

Clearly, this is not an acceptable amount of time to wait for a process to complete. I arranged to do a SQL trace while the user clicked the “Mark All” button. I found that the Revenue Recognition window would run a SQL stored procedure (SVC_Check_Contract_Revenue) for every contract record in the window to determine if the contract was “Marked to Post” by another user. If so, a message would display, alerting the user to the fact that another user had a record marked to post. Technically, the code was:

  • examining the SVC00625 table (SVC_Contract_Revenue_WORK)
  • looking at each contract/fiscal period combination to see if another user also had that contract/fiscal period record marked to post (MKDTOPST = 1).

The key thing I found was that this routine was being run row-by-row, one record at a time. Even though the check was very quick to complete for any one given record, the number of records it had to check added up to the 6-8 hours I mentioned earlier. Fortunately, I was able to devise a solution to this issue. In reality, my users did not care if someone else posted the record, and did not need to know which contract in question was going to be posted by someone else. It was enough for them to know “Hey – some of the records you want to mark are currently marked by someone else. I’m going to skip marking those particular records, and just mark the ones that are safe to post.” By taking this approach, I could craft a routine which would work on ALL records at once, which was orders of magnitude faster.

The solution I devised to this problem involved 4 components:

  1. I modified the Revenue Recognition window to add my own “Mark All” and “Unmark All” buttons. I hid and disabled the actual “Mark All” and “Unmark All” buttons in Modifier.
  2. I created a custom stored procedure (X_OT_SVC_REVREC_MARK_UNMARK_ALL) to mark all/unmark all records to post. It will skip any records marked by other users, and raise an alert if any are found.
  3. I added VBA to the window which will call the procedure when either of the buttons are clicked.
  4. I noticed that the window did not refresh to show the buttons as checked/unchecked after the stored procedure was run. I also noticed that if I clicked the “down” arrow on the keyboard, the line would refresh. So, I recorded a macro of pressing “down” 16 times (as that’s the number of rows that show in the window) which is then called from the VBA code.

If you’re experiencing slow performance when marking/unmarking all, try using the code pack below to implement this solution in your environment (test first!). Instructions on how to install are included, as there are a few tweaks you will have to make based on how your environment has been crafted. If you have questions, or if this solution helps you out, please leave a comment below!

Download the package here: Enhanced Mark All Revenue Recognition Routine.zip

Post to Twitter

Quickly Add Users to POWERUSER Role in Dynamics GP 2010

Nov 25th, 2010
by Aaron Berquist.
5 comments

One of the more tedious processes in configuring a “demo” or “test” system is setting up user accounts. Recently, I needed to configure a GP 2010 “Sandbox” for some internal users who wanted to look at the new features Dynamics GP 2010 has to offer.

After manually creating their accounts (Dynamics GP: Tools: Setup: System: User Setup), and granting access to the Fabrikam company (Dynamics GP: Tools: Setup: System: User Access), I wrote a quick script to add all users to the POWERUSER role, with the DEFAULTUSER Alternate/Modified Forms and Reports ID.

The script adds records to the SY10500 and SY10550 tables for all users in the SY01400 table, excluding “sa”:

 
/*
AUTHOR: AARON BERQUIST - WWW.AARONBERQUIST.COM
DATE: 11/25/2010
PURPOSE: THIS SCRIPT WILL ADD EVERY DYNAMICS GP USER TO THE POWERUSER ROLE, IN FABRIKAM, INC.
IT WILL ALSO SET THEIR ALTERNATE/MODIFIED FORMS AND REPORTS ID TO DEFAULTUSER
USAGE: USE THIS SCRIPT TO QUICKLY SET UP USERS FOR ACCESS TO A TEST OR DEMO SYSTEM
 
TESTED ON DYNAMICS GP2010, BUILD 11.00.1524 (SP1)
*/
 
USE DYNAMICS
GO
--PROCESS THE ROLES
INSERT INTO SY10500
	(USERID,
	CMPANYID,
	SECURITYROLEID)
SELECT
	sy4.USERID,
	-1, --THIS IS THE CMPANYID FOR FABRIKAM (AKA TWO),
	'POWERUSER' --FOR THE PURPOSES OF THE DEMO SYSTEM, ALL USERS HAVE FULL RIGHTS TO THE APPLICATION
FROM
	--USER TABLE
	SY01400 sy4
	LEFT JOIN SY10500 sy5 on sy4.userid = sy5.userid
WHERE
	sy5.USERID IS NULL 
	--WE DO NOT WANT TO TOUCH THE "SA" ACCOUNT IN ANY WAY
	AND sy4.USERID <> 'sa' 
GO
--PROCESS THE AFA'S
INSERT INTO SY10550
	(USERID,
	CMPANYID,
	SECMODALTID)
SELECT
	sy4.USERID,
	-1, --THIS IS THE CMPANYID FOR FABRIKAM (AKA TWO),
	'DEFAULTUSER' --FOR THE PURPOSES OF THE DEMO SYSTEM, ALL USERS WILL HAVE THE DEFAULTUSER AFA
	FROM
	--USER TABLE
	SY01400 sy4
	LEFT JOIN SY10550 sy5 on sy4.userid = sy5.userid
WHERE
	sy5.USERID IS NULL 
	--WE DO NOT WANT TO TOUCH THE "SA" ACCOUNT IN ANY WAY
	AND sy4.USERID <> 'sa'

Download the script here: GP 2010 – Configure Users for Demo System.sql

Post to Twitter

Easily Add New Databases to Global Dynamics GP Reports

Nov 24th, 2010
by Aaron Berquist.
No comments yet

Last post I explained how using the OPENQUERY SQL syntax can help skirt the 260 tables in a view limit of SQL Server. Today I’ll show how to combine that tip with additional logic to automatically add new Dynamics GP company databases to your custom global reports.

If you have a large number of global reports, or even if you only have a few, maintenance can be a hassle whenever a new Dynamics GP company is added to your system. I have devised a solution to this maintenance headache by combining a custom table which holds the name and code for my global reports, with a nightly procedure which will rebuild all of the global objects automatically. The items that make up the solution are:

1. A custom table, stored in DYNAMICS, with the following structure:

  • Report ID – a unique identifier for the report
  • Report Name – the name of the SQL object
  • Report Code – the report code I want to run against every Dynamics GP database
  • Report Type – whether the SQL object to build is a stored procedure or a view

An example of what a record in the table would look like is:

Report ID Report Name Report Code Report Type
1 usp_NonVoidedSOPDocs Select * from REPLACEME..SOP30200 where VOIDSTTS = 0 Stored Procedure

Note the “REPLACEME” line in the “Report Code” field. This value will be replaced with the database name when the item below is run.

2. A stored procedure, also stored in DYNAMICS, which is called every night from a SQL Job. This procedure does the following for each entry in the table above:

  • Gets a list of database from the SY01500 table in DYNAMICS. The field it returns is INTERID.
  • Dynamically builds a SQL statement which will DROP the existing SQL object, based on the Report Name and Report Type values from the table.
  • Replaces the value REPLACEME in the Report Code string with the INTERID value.
  • Dynamically builds a SQL statement to CREATE the SQL object, based on the Report name and Report Type values from the table.
  • Grants EXECUTE permissions to DYNGRP to the SQL Object.

As an example, thejob above would dynamically generate, then execute, the SQL String below:

USE DYNAMICS

GO

IF EXISTS (SELECT name from sysobjects where name  = ‘usp_NonVoidedSOPDocs’)

BEGIN

DROP PROCEDURE usp_NonVoidedSOPDocs

END

GO

CREATE PROCEDURE usp_NonVoidedSOPDocs as

SELECT * FROM COMPANY1..SOP30200 WHERE VOIDSTTS = 0

UNION ALL

SELECT * FROM COMPANY2..SOP30200 WHERE VOIDSTTS = 0

UNION ALL

SELECT * FROM COMPANY3..SOP30200 WHERE VOIDSTTS = 0

GO

GRANT EXEC ON usp_NonVoidedSOPDocs to DYNGRP

By using this technique, new Dynamics GP company databases can be automatically added to your custom reports without any intervention on your part – freeing you up to work on other tasks.

Next week, I plan to talk about why I am such a fan of the Field Service module in Dynamics GP and how the way it has been designed is such a boon to analysts and developers who need to customize it’s behaviour.

Post to Twitter

Creating Global Dynamics GP SmartLists with OPENQUERY

Nov 18th, 2010
by Aaron Berquist.
1 comment

From time to time, I have needed to create a SmartList which pulls in data from multiple Dynamics GP company databases, with multiple tables. Because of the number of databases involved, and the number of tables, I hit the “260 tables in a view” limit of SQL Server. Because of the specific needs of this particular business unit, the report needed to be a SmartList, and I could not deliver it via Reporting Services. 

After trying a number of different approaches, I stumbled upon using the SQL command OPENQUERY to call a stored procedure which obviously is not encumbered with the 260 table limit. 

The steps I took to utilize this feature are as follows: 

  1. Create a linked server in SQL which pointed back to itself. In my case, I named the linked server “SMARTLIST”
  2. Write the view in a single database.
  3. Deploy the view against all of the databases in Dynamics GP.
  4. Create a stored procedure in one database which unioned all of the views together.
  5. Create a view in one database which called the stored procedure using OPENQUERY, like so: SELECT * FROM OPENQUERY(SMARTLIST, ‘exec MyProcedure’) AS derivedtbl_1 
  6. Build a SmartList off of the view in question.

Voila! Now I have a view I can use in my SmartList which gets around the 260 table limit in SQL Server.

In an ideal world, this report would be delivered via another mechanism, such as Reporting Services, or a consolidated reporting server. However, sometimes a less-than-ideal solution is needed and this is one trick to keep in mind should your design constraints dictate going with a SQL View.

Next week I’ll expand on this item and show how you can dynamically generate a view which will automatically add new Dynamics GP company databases to your global reports.

Post to Twitter

Newer Posts →