Microsoft Dynamics GP Business Analyzer: Part 2
As you saw in last week’s blog, Business Analyzer can be used to select and view SQL Server Reporting Services Reports from your desktop. To view the reports though, you must have security access to the Microsoft SQL Server Reporting Services reports in Report Manager or the Microsoft SharePoint site and you also need access in SQL Server Management Studio. SQL Server Reporting Services Reports and Business Analyzer all use the same security, so once you have the security set up for the SSRS reports, it doesn’t have to be set up again to view these reports within Business Analyzer.
There are two sides that need to be set up for security for the SSRS reports:
1) Report Access: This is setup in Report Manager (Native Mode) or within your Microsoft SharePoint reports library (SharePoint Integrated Mode).
a) Log on as an Administrator to the Report Manager site (e.g. servername:port/Reports) or within your SharePoint site where your reports library is that you deployed your SSRS reports to.
b) Click on the Folder Settings button or the blue Properties button (Native Mode). If you deployed the reports in SharePoint integrated mode, you will need to go to Site Settings from the home page and then click on Security.
c) Add the domain account or domain group that you want to give access to. They need to at least be given access to the Browse role (at the very minimum).
d) For SharePoint Integrated mode, you will also need to click Home to open the Home page. Next, click on the Folder Settings tab. Click New Role Assignment to open the New Role Assignment page. Enter the name of the user or group account. Then select one or more roles until the combined set of tasks describe the actions that the user should be allowed to perform.
e) These users will now be able to access this site and see the folders/reports, but they will not get any data back when they run the reports. That is where the SQL Access comes into play.
2) SQL Access: This is setup in SQL Server Management Studio and provides access to the data itself in the SSRS reports.
a) Open SQL Management Studio and connect to your Dynamics GP instance.
b) Expand Security > Logins and add the domain user/group you added in Report manager above.
c) Click on the User Mapping tab and grant this user access to the GP databases they need to report against.
d) In the bottom half of this window you'll see the database roles you can add the user to. The rpt_* roles are used for the SSRS reports. You need to map each user to at least rpt_all users in DYNAMICS and then whatever groups they need in each company database.
To view what rpt role(s) give access to what reports, you can download the MDGP10_SRReports_Roles from the following location:
https://mbs.microsoft.com/fileexchange/?fileID=80628e60-729d-4e47-bbd5-37af74ea39c7
Once you've done this, the same security will apply whether a user is trying to run the SQL Server Reporting Services reports from Report Manager (Native Mode) or within your SharePoint site within a Reports Library (SharePoint Integrated Mode), Business Analyzer or from within Microsoft Dynamics GP itself.
After security has been set up for the SSRS reports, you will be able to view them within Business Analyzer.
Enjoy!
Sarah