Reporting Puzzle (1 of 2)- Dynamics GP SQL Financial Trial Balances

Along with a coworker, I have been chewing on a SQL Reporting Services (or so I thought) issue for the past few weeks.  Users had reported that both of the financial trial balances (Detail and Summary) in SQL Reporting Services were not returning results.  The report worked fine for them in TWO (Fabrikam), but not in either their live or test database.  Strange.  And even stranger, the report worked fine if we changed the data source to use the 'sa' user and password.

I felt like we had checked everything, including:

• Confirmed access to reports in Report Manager, and access to Data Source- other reports work fine (including other financial reports)
• Confirmed the database roles- all affected users had rpt_poweruser roles to both the company and DYNAMICS databases
• Placed users in to SYSADMIN server role for testing- still didn't return results
• Recreated stored procedures used by report- no luck
• Recreated report itself- running out of ideas

What we found through all of this testing is that a) no errors were being returned (yes, we did a trace to confirm this) and b) when running the stored procedure in SQL Server directly, we still did not get results.  We found any number of blog posts related to similar issues, but these all had to do with GP2010 R2 and the temp table not populating correctly.  We were on GP2010 SP1, so it didn't seem likely that it was the same issue.  We started a case with Microsoft, and after a couple more weeks of working on it back and forth we uncovered the rather embarrassingly simply cause. Three words.  Account. Level. Security.

So, the SSRS financial trial balances use the same stored procedure that is used for the trial balance reports in Dynamics GP.  This stored procedure incorporates account level security.  In our company (and test company), account level security (organizational structures) is activated (Tools-Setup-Company-Company).  So when the report is generated, it cannot locate any accounts based on the user's security (since the SSRS reports use windows credentials, not the GP user accounts). This causes the report to not return any results, and does not produce any errors.

Simple cause.  The workaround is to change the data source for the report to use the 'sa' user and password.  If you choose to do this, keep in mind that all users who have access to the report will in turn have access to all data.  In our situation, this works since the only users who can access the report are allowed access to all accounts.  In a more restrictive environment, I think you would have to look at modifying the report to map the windows accounts to GP accounts.  If you did this, you would need to take care not to affect the stored procedure used by the standard Dynamics GP reports.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.