GP2010 – Sales Charts & KPIs – Top Items By Sale Amount – Stored Procedure Bug

I stumbled across a bug (of sorts) in GP2010 SSRS Charts & KPIs for Sales Module.

GP2010 has got default Charts & KPIs designed in SSRS and we have quite hands full of Charts & KPIs which covers most of our basic requirements. One module which is very good in terms of coverage is Sales. Under Sales, we have a Chart & KPI called Top Items By Sale Amount.

When I ran this report to view the results, I got the following error message:

The error message mentions that there is an extra bit of string data that’s getting forcefully truncated due to size restrictions. This clearly indicates a SQL Error Message. I ran the SQL Profiler furthering the probe. And the issue was rounded in to a Stored Procedure which is run to extract the needed data for this Chart & KPI, which is seeSOPTopTenSalesItemsByPriceMetric.

In that stored procedure, a temporary table is created with a column named ItemNumber which is of size char(18). We all (Consultants/Developers) know that, in GP, size of a Item Number is 31 in SQL.

So, this SSRS Chart & KPI will fail if any Item Number exceeds the size 18 in variably, which is a bug in this Stored Procedure.

FIX: I ALTERed this stored procedure to resize this particular temporary ItemNumber column to char(31) and report retrieved the details excellently.



Deploying GP SSRS Reports – maxRequestLength Error & Resolution

I was deploying GP SSRS Reports on SQL Server 2008 64 Bit and was facing the following error message:

There was an exception running the extensions specified in the config file. —> Maximum request length exceeded.

Googled it and here the resolution:

I hope this would be a useful tip for people out there deploying SSRS for GP.


Setting up SSRS in Dynamics GP – Mariano’s Input saved my day

I installed Dynamics GP 10, a fresh install to make sure that I properly install SSRS Add-On and configure it to GP 10.0.

Everything went on well and I started configuring SSRS Paths in Dynamics GP Reporting Tools Setup window.

I entered the path and I received a message “The Report server URL entered is not valid.”. No clue, whatsoever.

I surfed and here I found Mariano’s input to one of the users.

If you are busy enough to not going to that link and read, here is what Mariano had to say:

If you are on SQL Server 2005, the URL should be http://ServerName:PortNumber/reportserver/reportservice.asmx.
However, if your server happens to be a SQL Server 2008 server, the URL should be http://ServerName:PortNumber/reportserver/reportservice2005.asmx.

Thanks Mariano for this input and I am delighted to have succeeded in my task. Though this is for my own R&D, this has kick started a really interesting and sincere learning process.


Generate Charts Using SSRS

MichaelJ2 has pointed out to a very good article in SQL Server Central portal, which explains step-by-step on how to generate charts using SSRS.

As he mentioned in the end, it’s worth to have a look since GP is moving more towards SSRS and it would be great to see more Decision Making charts generated right from GP.