Reconciling unchanged bank statements

Just recently, I worked on a case with a partner, whose client had a checkbook setup. For the past serveral months, the bank account associated to the checkbook has had no transaction activity. The client still receives a bank statement each month with no transactions.

When the client would try to enter the cutoff date information into the Reconcile Bank Statment window, even though the difference is zero, the system is not letting them reconcile the statement.

Reconcile Bank Statement window

They keep receiving a message that they need to mark the items they want to clear.

Select Bank Transctions window - error when attempting to reconcile

As a result of not being able to complete the reconcile, the Last Reconciled Date field is not being updated on the Checkbook Maintenance window.

Checkbook Maintenance window

To overcome this issue, we had the client enter both an interest income and an other expense adjustment for a penny ($0.01), as shown below:

Reconcile Bank Adjustments

Once we returned to the Reconcile Bank Statements window and clicked on the Reconcile button, the process went through. Since Microsoft Dynamics GP does not post zero balance transactions to the same account in the General Ledger, then we were able to effectively not affect the GL. The added bonus, of course, and the problem needed to be solved - updating the last reconciled date on the checkbook - was taken care of with this workaround.

Reconciled Checkbook

Of course, we could have made the changes directly in SQL by updating the Checkbook Master table (CM00100), but that would have left no audit trail of the reconciliation for the accounting department. As for those pennies... they are just that, pennies. The adjustments were documented with notes that explained clearly that they served just as a workaround so auditors would not throw a fit.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

SSRS: The deployment has exceeded the maximum request length allowed by the target server

Earlier today, I was deploying SQL Server Reporting Services (SSRS) reports for a client who just upgraded from version 9 to version 2010 R2 on Microsoft SQL Server 2008 R2. Upon beginning the deployment, the following error came up:

maxRequestLength error deploying BI reports

The error and it's resolution are pretty clear and called for setting the maxRequestLength attribute to "20690" in SSRS's web.config file.

Web.config

The SSRS web.config file can typically be found under the %programfiles%\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\Report Server, for a Microsoft SQL Server 2008 R2 SSRS installtion done in Native mode. If you have other types of deployments, for example, SharePoint integrated, it will be necessary to consult with your system administrator.

You can then proceed to use Visual Studio or, simply, Notepad to edit the web.config file, locating the httpRuntime element of the XML document.

web.config - httpRuntime section

The final edit of this section should look like this, with the maxRequestLength attribute added.

web.config - Edited httpRuntime section with maxRequestLength parameter

Once the web.config file was saved, I reinitiated the deployment and all was well. The maxRequestLength attribute specifies the maximum upload size for a file into the SSRS reports library in kilobytes. For this specific instance, 20,690 seems to be the magical number.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More

SmartList Builder and creating Calculated Fields with Extender data

Just recently, I ran into a case where the partner was creating a SmartList Builder calculated field using data from the RM Open table (RM20101) via an Left Join table operation with the Extender Window Field Numbers. In fact, this is a very typical scenario for a lot of deployments where Extender is used, especially when you cannot use the standard Extender functionality to integrate with out-of-the-box smartlists.

The original SLB calculated field look something like this:

Extender Calculated Field

When the SLB smartlist was deployed, a number of results came back as zero for the records where there was no entry in the Extender Window Field Numbers table, even when the Sales Amount and Current Trx Amount fields had a value in the RM Open File table.

Paying a bit more attention to the issue made me think of how LEFT OUTER JOINs are processed by the Microsoft SQL Server query engine. This is best illustrated with the following example:

RM Open				Extender
Customer Number	Document Number	Sales Amount	Current Transaction Amount	PT UD Key	PT UD Number	Total
AARONFIT001	INV3223	200.00	40.00	INV3223	5.00	5.00
ADAMPARK001	INV1020	100.00	20.00	NULL	NULL	NULL

Note that if Extender data was not entered for INV1020, a left outer join query would produce a NULL value as a result of the join operation. To overcome this situation, we applied the T-SQL ISNULL() function to the Extender field in SmartList Builder. Since SmartList Builder uses pass-through SQL to build each portion of the SELECT statement used to retrieve the records, then this should work just fine. The final calculated field is as follows:

Remember, Extender is a valuable tool to capture additional data and enhances the value of SmartList Builder when combined together to deliver reports. Pay special attention when creating calculated fields that rely on information from Extender tables in left join scenarios.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft's upcoming products available for download

A bit away from the Microsoft Dynamics GP world, but at the same time so close, here are some very good news on the upcoming release of Microsoft SQL Server "Denali", Office 2010 and SharePoint, and Internet Explorer.

Download SQL Server Code Name "Denali" CTP3

SQL Server Code Name "Denali" Community Technology Preview
(CTP3) provides the foundation for the cloud-ready information
platform, and will help customers unlock insights across the
organization and quickly build solutions that extend data across
on-premises and public cloud.

Office 2010 and SharePoint 2010 Service Pack 1 Now Available

Service Pack 1 is now available for the 2010 family of products, including Office, SharePoint, Project Server, FAST, Search Server, and Groove Server. For more on deployment, see these articles: Apply Office 2010 Service Pack 1 and Deploy Service Pack 1 for Project Server 2010. Also, download the Service Pack 1 for SharePoint Foundation 2010 and SharePoint Server 2010 White Paper.

Internet Explorer 10: Platform Preview 2

The latest preview build of Internet Explorer 10, Platform Preview 2,
is now available for download. With the second Platform Preview,
developers can start working with several site-ready HTML5
technologies.

Remember... should you decide to try any of this code, do so in a test environment.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

SSRS: GL Trial Balance Summary report returns no data

This one comes courtesy of my friend Steve Sieber at McGladrey.

After installing Microsoft Dynamics GP 2010 R2 and deploying the SQL Server Reporting Services reports, you will encounter an issue when printing the GL Trial Balance Summary SRS report located under Financial.

1. Launch Report Manager and click on the company for which you would like to run the report (the issue can also be reproduced in the Fabrikam (TWO) company database). Click on Financial | Trial Balance Summary, enter all the parameters and options for the report the click on View Report, the following is returned:

GL Trial Balance Summary - SSRS

As you can see, even though the correct parameters are selected, the report returns no records.

2. If the GL Trial Balance Summary report is executed from GP with the same parameters, the report correctly delivers the expected records and result:

GL Trial Balance Summary - GP

From a technical perspective, the GL Trial Balance Summary SSRS report executes the dbo.seeGLPrintSRSTrialBalance stored procedure. The issue appears to be that the #GLTBDTemp temp table does not get populated with the records needed to render the report. You can test the stored procedure by executing the following statement from SQL Server Management Studio against any company database.

exec seeglPrintSRSTrialBalance 0,0,0,'000-0000-00','999-9999-99','01/01/2017','12/31/2017',2017,0,1,1

This issue has been reproduced by Microsoft Support and they are currently researching the problem for a solution. 

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

More Microsoft Dynamics GP Word Templates Articles

If you have been keeping up with this blog, you will remember that sometimes around October of 2010 I posted a series of articles related to Microsoft Dynamics GP Word Templates - see Microsoft Dynamics GP 2010 Word Templates summary for more information. These articles focused on "how to's" and troubleshooting aspects of Word Templates.

Now the Dynamics Support and Services Team Blog has decided to launch a new set of video-articles detailing more common aspects of working with Word Templates. You can now visit the Community Team blog for the release article and the schedule of when these videos will be out. The good news is the first two are out!

Take a look at:

Adding the Developer tab in Microsoft Word after installing the Microsoft Dynamics GP Add-in for Microsoft Word - Video

Helpful tips when modifying Word Templates in Microsoft Dynamics GP 2010 - Video

Please visit the Dynamics GP Support and Services Blog for these videos and to find a collection of very new and interesting articles.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Violation of PRIMARY KEY constraint 'PKSY60100'

Just recently, I assisted a partner with an issue they were having creating a new company in Microsoft Dynamics GP 10 - though, I supposed the same could happen with any other version. In the process of creating the company record, almost at the end of all the routines executed by Dynamics Utilities they were getting the error:

Violation of PRIMARY KEY constraint 'PKSY60100'. Cannot insert duplicate key in object 'dbo.SY60100'

KB Article 871699  suggests the problem could be that the DYNAMICS database is associated with a database owner (dbo) other than DYNSA. After running the sp_helpdb system stored procedure, it was determined that the database owner of the DYNAMICS database (and other company databases) was indeed 'sa'. Knowing this obviously helps, and the solution is as simple as changing the database owner back to DYNSA.

The partner then ran the sp_changedbowner system stored procedure to reset the database owner to DYNSA and got the following error:

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database

Having gotten this error, we proceeded to drop the DYNSA from the DYNAMICS database as follows:

USE DYNAMICS;
GO
DROP USER DYNSA;
GO

Having dropped the user from the database, we needed to re-add DYNSA as the database owner of the DYNAMICS database. This time, I decided to try the new ALTER AUTHORIZATION statement as the customer is running Microsoft SQL Server 2008 R2, as sp_changedbowner will be deprecated from SQL Server sometimes soon.

ALTER AUTHORIZATION ON DATABASE::DYNAMICS TO DYNSA;

Once we executed this command, we restarted the company creation process in Utilities and the error was no longer.

If you find yourself in a similar situation then this should definitely help.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More