As promised, here is Part 2 of my series on replacing the Report Writer and Word Template reports in Microsoft Dynamics GP with SSRS reports.
If you’d like to read Part 1 again, here’s the link to it: Report Writer and the Word Templates in Microsoft Dynamics GP: Just Say No.
Let’s do this!
First, we’re going to need some SSRS reports. I’ve made a zip file of all the forms and reports I have been using over the years.
These are all explained in the zip file, but here’s the list of forms:
Field Service RMA Authorization Form
Field Service RMA Traveler Form
Field Service RTV Packing List Form
Inventory In-Transit Transfer Picking Ticket Form
Inventory In-Transit Transfer Packing List Form
Inventory In-Transit Transfer Invoice/Commercial Invoice Form
Inventory Stock Count Tags
Payroll Direct Deposit Remittance Form
Payables Management EFT Remittance Form
Purchase Order Processing Purchase Order Form
Sales Order Processing Back Order Form
Sales Order Processing Order Form
Sales Order Processing Invoice/Return Form
Sales Order Processing Packing List Form
Sales Order Processing Picking Ticket Form
Sales Order Processing Commercial Invoice Form
And the list of reports (more coming):
Commission Plan (from EthoTech) Commission Report
Commission Plan Commission Report from Payables Management
Commission Plan Commission Report for Finance
Inventory Stock Count List
Inventory Stock Count Missing Tag List
Inventory Stock Count Variance List
Inventory Stock Count Verification List
Inventory Stock Status
Payables Management Cash Requirements
Payables Management Aged Trial Balance
Purchase Order Processing Received Not Invoiced
Receivables Management Aged Trial Balance
Receivables Management Customer Statement
Sales Order Processing Document Analysis
These are all deployed on the SSRS report server. I’ve created two folders on the server just to make it simple — all the forms sit in a folder called “Forms” and all of the reports sit in a folder called “GPReports”:
They are set as hidden folders because you don’t want the users to see the folders (easily) if they directly access the SSRS website.
Don’t forget to have another folder for the data sources: I just call mine “Data Sources” and have it hidden too:
Now, let’s get to the fun in Dynamics GP!
Once you install Rockton Software’s Dynamics Report Manager (DRM), there is a new menu and set of icons in the menu bar at the top:
Step 1: Set up the SSRS report server URL.
Hit the yellow and white Rockton logo menu (the R menu) and select System Settings.
Click the SRS tab and enter in the URL for the SSRS server:
There is an interesting feature to be able to impersonate a Windows user. Instead of running the report in the context of the current user logged into their PC, you can pick a different user account — I recommmend a minimum-rights user — to access SSRS and run the reports.
You also get the options to pick and choose what buttons appear to the users when the SSRS report is rendered on the screen: export, print, page navigation, etc.
Step 2: Set up the SSRS report in DRM.
Every SSRS report you are using requires the report to be set up in the Report list. It is a very simple setup even though there are a few steps to follow.
For this example I am going to set up the replacement SOP Invoice/Return form.
Open the Report Manager Explorer and click the + icon on the right side.
Select Enter Manually and select SQL Server Reporting Services as the report type. Click OK.
In the Technical Name field, this is the path to the report. Enter “Forms/SOPInvoiceReturn” (no .rdl extension needed). Do you remember the Forms folder above? That’s why I created it, so the path would be easy to remember for this step.
Give it a nice, descriptive name in the Display Name field and a description in the Description field.
Almost there. Now the four tabs.
Parameters tab: this is all of the report parameters that were set up in the report in Visual Studio. DRM needs these parameters listed so it can pass the data from the GP screens to SSRS and show the correct data for the report on the screen.
Hit the + icon and click the Manual button. I like setting the parameters up manually.
In the Parameter Name field, this is the same name that is in Visual Studio when creating the parameter. This name has to match EXACTLY or it’s not going to work!
Parameter Prompt field is just the descriptive prompt the user sees to know what to enter or select. This is the same as whatever was entered in Visual Studio when setting up the report parameter.
Data Type is what type of information is being asked for (string, integer, date/time, etc). Again, the same as whatever was selected in Visual Studio when setting up the report parameter.
Then just check the parameter option checkboxes:
– Pass this parameter: this tells DRM to send the information in the parameter to SSRS.
– Allow Multiple Values: this is the same as in Visual Studio when defining a parameter — if there is the possibility of multiple values in the parameter.
– Hide this parameter from User entry: show/hide the field from the user when the report displays on the screen.
Click OK. If there are other report parameters, repeat these steps. The SOP Invoice/Return just has a single parameter (the SOP number), so we’re only going to have a single parameter in this example.
Destination tab: Where you want the report to launch. You can use:
– The DRM report explorer window
– A web browser tab
– Directly to a printer
– Save to file
I normally use the Window option. You can check/uncheck the options to show on the DRM Report Explorer screen (export, refresh, print, navigation, etc).
The Window option requires you to install Microsoft Report Viewer Redistributable 2008. It is included in the DRM install files, but can also be downloaded here:
Microsoft Report Viewer Distributable 2008 from Microsoft
Access tab: The security you can place on the report. This report can be restricted to either all of the users in a GP user class or specific users set up in GP security. The report can also be restricted to specific GP companies or available to all GP companies.
Connection tab: If you entered a Windows user to impersonate in the DRM setup screen, these fields are automatically filled in with that user. If nothing is here, DRM will inherit the security of the currently logged in user when running the report. You can also enter in a different Windows user account too, if desired.
Hit Save! The report is set up and ready to be used throughout GP!
Step 3: Set up the launch point.
This is what allows DRM to generate the report from the data showing on the screen in GP. In this example, I am picking the Sales Transaction Inquiry Zoom screen. When the user clicks the Print button on the screen, we’ll launch the SSRS report instead — AND AS AN ADDED BONUS — skip opening the Sales Document Print Options screen!
In the Launch Points tab, click the + button. For this step, it is easier to use the wizard.
Hit Next and let’s get this launch point train moving!
First, DRM will ask you what type of launch point you’re creating. There are lots of options to pick from (scheduled, Additional menu, replace Report Writer report, etc — and I will talk about all of them in future posts) but right now we want to pick Window Field or Button.
Then pick the report we just added: SOP Invoice/Return.
You’ll note on this screen there is a checkbox next to each report. You can link multiple reports to the launch point, which is SUPER COOL for the user. Less clicks!
There are all kinds of options for launching the report: when the window opens, going to the File menu and selecting Print — we want to launch the report the field is entered and BEFORE the Dexterity scripts run for the Print button.
This is how we prevent the Sales Document Print Options screen from popping up on the screen and just launch the report! Yes!
Now we’re going to pick the screen where the report will be launched from. In GP, navigate to the window that’s going to be used. In this example, I am picking the Sales Transaction Inquiry Zoom screen.
Once it opens, the Launch Point Wizard box will reappear. On the Sales Transaction Inquiry Zoom screen, click the Print button.
You’ll see the Window Name and Field fields fill in with the a screen name and a button name that are NOT what you clicked. For Sales Transaction Entry and Sales Transaction Inquiry Zoom, it freaks out a bit because of the Sales Document Print Options screen. That’s OK, we’ll fix it at the end!
Click the Assign button. Make sure the text turns green and click Next.
Now we have to link the field on the Sales Transaction Inquiry Zoom screen to the report parameter in SSRS. Because this is the inquiry screen, we will have to fix it at the end.
Click on the report name, click on the parameter name, click somewhere on the Sales Transaction Inquiry Zoom screen and click the Assign button. Then click Next.
Give it a descriptive name. Click Next.
Click Next again and click Finish.
Our launch point is displayed on the screen. Now we’ll make our fixes to run on clicking the Print button and pass the document number.
Here’s what the Settings tab should look like:
The report parameter properties to pass to DRM and SSRS are wrong too.
Click on the Actions tab and click on the first Action — Set Parameter to Field — and click the edit button.
Here’s how to pass the correct information from the screen:
Click OK. Click Save.
Now, when the user clicks the Print button, the SSRS replacement form displays on the screen and there’s less clicks for the user!
These steps look really long and complicated, but once you get one done, you can duplicate your work and just change what you need. DRM has a nifty duplicate feature for both the reports AND launch points. Yeah!
In closing, BAM. One less Report Writer and/or Word Template report to maintain and all the garbage that comes with them. Just think of all the time you’re going to save with eliminating upgrade headaches, botched Windows updates, incompatible Office updates and any other little thing that will break the Report Writer reports. Hooray!
EDIT 7/8/2021:
Here’s the link to all the files AND the missing RM Customer Statement! SSRS.zip
Legend, thanks for the share.