Friday, April 8, 2011

SQL Reports for GP 10 / 2010 Fun

There are three things to know when setting up the Dynamics GP SQL Reports:

1) You need to have your registration keys entered

The first indication that something is going wrong shows up on the screen where you "select the module(s) to deploy reports for" - and I don't mean because there is a preposition at the end of the sentence. There should be a list of modules here other than "Charts and KPIs." That indication aside, when you get through the wizard and look at your SQL Report Manager site, you will find that nothing more than a couple Data Sources and Report Models were actually created.

Register your Dynamics GP inside GP at Tools >> Setup >> System >> Registration

2) You need to use a not-so-obvious URL in the Reporting Tools Setup window

You will get a friendly "The Report Server URL entered is not valid." warning when you enter the standard Report Server URL that you've used everywhere else you've been asked. No, GP requires something a little different.

For Reporting Services 2005, use
http://ServerName:PortNumber/reportserver/reportservice.asmx

For Reporting Services 2008 (Native Mode), use:
http://ServerName:PortNumber/reportserver/reportservice2005.asmx

For Reporting Services 2008 (SharePoint Integrated Mode), use:
http://ServerName:PortNumber/reportserver/reportservice2006.asmx

UPDATE: It appears that GP 2010 R2 does not need this special link. http://ServerName:PortNumber/reportserver is all that is required.

You may instead get an error regarding "set maxRequestLength='20690' in the web.config file". Edit the web.config found in the ReportServer folder in the SQL Reporting Services program folder; it would be similar to C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer.

Add the following line to the web.config file just above the <securityPolicy> section. If there already is an httpRuntime line, change it to match this statement:

<httpRuntime executionTimeout="9000" maxRequestLength="20960"/>

3) You need to point to a default SQL instance

With the third issue, and you'll have to correct me if this has changed, but I remember running into issues when I had a named SQL instance for Dynamics GP. There really wasn't much that could be done to get the reports deployed. Sorry, no solution here for that one.