How to Publish Salesperson-Specific Data to the Field with Microsoft Dynamics GP and SQL Reporting Services

I was recently engaged to resolve an issue a new client was having with Business Portal for Dynamics GP.  It didn't take long to figure out that the problem wasn't with Business Portal, but rather the way they were trying to use it and NOT using SQL Server Reporting Services (SSRS).  Both tools have a place in their environment, but one is not a substitute for another.  They complement each other.  This client, like most Dynamics GP customers, really needed to be leveraging the strengths of both together.

Its goal was simple; deliver relevant data to the sales organization in the field through the web browser.  Its requirements were not much different from those of any other sales organization.  It Its priorities were that 1) the reports were simple to use and 2) the sales people didn't have to filter out other sales territory or sales person data.

The simplicity requirement was simple, no pun intended, to meet.  The company was already using Business Portal lists to mine some data, so the deployment tool for the new reports had already been accepted and implemented.  Linking to SSRS reports directly to create a one-click experience is native Business Portal Functionality.  The technical challenge was to organize the data, develop the queries, and integrate both with the SSRS Reports so that they returned results specific to the user.

Adding the Sales Person or Territory as a parameter to a SSRS report is an obvious place to start.  That method doesn't meet either of its requirements.  The company would have had to both click unnecessarily to populate the Sales Person parameter at runtime and by doing so filter out other sales territory or sales person data.  In addition, Sales Managers needed to see the data for all of ...