Monday, July 11, 2011

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!

Mariano Gomez, MVP
IntellPartners, LLC


Q FACTOR said...

I'm getting the same results for the detailed tb. Any workarounds for either of these?

jpkd said...

Do you know if this has been resolved?

Q FACTOR said...

There is a fix on Partnersource:

Andy Nifong said...

Awesome - thanks for finding this Constance - you saved me a ton of work!

Vishal Pandya said...

Thanks for posting, it saves my tons work to research and create a support case.

I ran the stored procedure in my database not 'Fabrikam' and
exec seeglPrintSRSTrialBalance 0,0,0,'00-00-0000-00','99-00-9999-00','01/01/2010','12/31/2012',2011,0,1,1

Stored procedure does give value

It seems to be some issue in SSRS
with GP 2010/R2(SP2)

Vishal Pandya

Mike Lupro said...

I followed Q Factor's link, made the changes per the Microsoft article and the report now delivers the expected results.

Thank you for making this an easy fix to find.