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.

Password Expiration Prompt - Using VST to Notify Users before their Password Expires in Dynamics GP

I was recently asked by another consultant to quote a modification that would give users advanced notice before their GP Password was going to expire.  This customer was growing frustrated that their user's passwords were expiring without notice and IT was having to reset them frequently.  The typical work around is to change your GP Password when you change your network password but that wasn't good enough in this instance.  The customer ended up choosing to go with one of the ISV solutions instead of a custom solution for additional features but I went ahead and finished this in my "spare time" anyway.

To make this work with SQL2005 I had to store a Maximum Password Age and the Number of Days before expiration to begin notifying users that their passwords would expire.  I'm sure you could integrate with windows security policy or active directory to get these values directly but I didn't do that here. With SQL2008 you would only need to store the Number of Days before expiration to start notifying because you can query the DaysUntilExpiration LoginProperty in SQL2008.  I wrote this work both ways depending on which version of SQL was running.

To capture the fields I needed I created a new Dynamics GP form in Visual Studio Tools that could be accessed from the Additionals Menu in Company Setup.  I chose to store this data in the DUOS so that I didn't have to deal with creating any new tables.  Therefore, you can setup each company to behave differently which could be a blessing or a curse.  Of course, you could change this to store the data in a custom table but it would also be nice if GP had a installation specific DUOS in the DYNAMICS database for this purpose.  Hint, hint.

Next, I added a NewHandler in VST on the SwitchCompanyCloseAfterOriginal event to compare the age of the users password to the maximum password age and days before notification captured in the PWExpirationNotice VST Form.  If the user is due to be notified that their password would expire soon I present them with a message box...

... and then the User Password Setup window in which they could change their password on their way into the GP Company.

The prompt and User Password Setup window are only presented on the first login for a session.  So, the users won't be nagged every time they change companies without shutting down GP first.  They also aren't forced to change their password but rather given the option to do so easily at a convenient time so that they don't get locked out later.

I chose VST over Modifier/VBA to ease deployment as much as anything.  You can download the .dll for this mod if you want to try it out here.  Just drop it in your Addins directory and it will work in Fabrikam.

The VBA Source for making the Purchases Account Required in PO Entry

I did a blog post a while back where I made available the functionality to force users to enter a Purchases Account on PO Lines when one didn't automatically default in.  That was compiled with the VST for GP.  Since, I've received several requests for the VBA source code to do the same.  Finally, I've gotten around to posting that for you.  You can download the .package file here.

I know there's a better way to do this other than using ADO to check for the presence of the account number but someone much smarter than I once said "if it works, it's the right way".  I'm sticking with that here but would like to hear about the techniques other are using to do the same or similar customizations.  Please comment and share.

This will only work on GP 10 and importing the package file will overwrite any existing VBA code behind POP Entry and POP Item Detail Entry.  Thus, the reason why I prefer using the VST version in this situation.