Organizing and Securing SQL Server Reporting Services Reports (SSRS) in Microsoft Dynamics GP
Posted by Mike Giuffre on October 10, 2016
Many people choose to use SQL Server Reporting Services (SSRS) for their Microsoft Dynamics GP reports. SSRS reports tend to be more powerful and versatile than the built in Dynamics GP reporting tool, and the license for SSRS is included with Dynamics GP. Some of the other benefits of using SSRS reports with Dynamics GP are:
- Unlimited number of reports
- View reports in a browser
- Many pre-made SSRS reports
- Accessible and visible from within Dynamics GP
How to Organize SSRS Reports
Let’s take a look at how SSRS reports are organized for use with Dynamics GP. SSRS reports are contained in a separate file called an RDL (Report Definition Language). RDL is the extension used when SSRS reports are created and saved. The reports are stored in folders on the SSRS server, just like files are stored on your computer.
However, in order to make SSRS reports available from inside of Dynamics GP, a specific folder structure must be followed when uploading the reports.
- From the SSRS root folder a folder must be created for each Dynamics GP company. The name of that folder must be the same (upper case) name as the underlying database name for that Dynamics GP company. So if your Dynamics GP company database name is TWO, then the first folder in SSRS for any reports for that company must also be TWO.
- Inside the TWO folder, Dynamics GP will use and honor folder structure corresponding to the Dynamics GP series. Folders can be created for Financial, Sales, Purchases or Inventory, among others. Any reports in those folders will be accessible from the Reporting Services Reports list in each of the series area page.
- All reports together, and any reports in folders other than those corresponding to the Dynamics GP series, will be visible under Reporting Services Reports from the Administration area page.
SSRS Report Security
It is also necessary to apply security to these SSRS folders and grant access so that appropriate users can get to them. You might need to work with your IT department to establish Security Groups in Active Directory. It is these groups that you will use to assign permission to the folders for.
For example, a group called AccountingReportsFinancial could be created to secure the reports in the Financial folder for all companies. Or TWOReportsFinancial could be created to secure reports in the Financial folder of the TWO company. Once established, you can simply add or remove users from the group as needed in Active Directory and users in the group will have access to those reports while others will not have access.
If you need assistance, we have a team of experts to help. Just send us an email at Support@Crestwood.com and we can help set up, organize, and properly secure your SSRS reports.