Publishing User Specific Data from Dynamics GP using SSRS

We recently finished a SQL Server Reporting Services engagement for a client requiring that the Sales Order Processing data displayed in reports be filtered by Salesperson.  That alone is simple enough; you could simply add the Salesperson or Territory as a parameter on the SSRS Report, right?  The challenge here was that reps could never be permitted to view another Salesperson's data.  So, it couldn't be quite that simple.  This was compounded only slightly by the requirement that, logically, sales managers needed to see all of the data for their Territories.  So, depending on whether a Salesperson was a Rep or a Territory Manager the results would vary.

This client had attempted several different ways to meet their requirements only to find that the simplest method turned out to be the most effective both in terms of delivering results and controlling costs.

"Make everything as simple as possible, but not simpler." - Albert Einstein

To do this, start by simply assigning a network login to each corresponding Salesperson in Salesperson Maintenance:

Now, we wrote stored procedures that would accept the network login as a parameter, evaluate whether the user was a Sales Rep or a Territory Manager, and then return the required data to which the user was assigned.  In this example, the stored procedure will return the Top 10 Customers based on Invoice Document Amounts.  You can download the sample stored procedure here to see exactly how we did that and use as a template to create your own.

Next, configure your dataset in Visual Studio to simply call the stored procedure using parameters from the report.

Next, after building the SSRS Report in Visual Studio, configure the Report Parameters...

... to make the @WINLOGIN parameter Hidden, so users cannot change this value when running the report, and Default the value to the Global User!UserID.

Now, when running the report Users will be prompted to select or enter a Cut Off Date but will not be given the option to select the Salesperson or User for which they want the report to display data.  That value is automatically passed to the stored procedure to deliver user specific results back to the report.