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.
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: http://www.developmentnow.com/g/115_2006_4_0_0_735861/Request-Length-Exceeded.htm
I hope this would be a useful tip for people out there deploying SSRS for GP.
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.
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.