10 min read

In this article by Belinda Allen, author of the book Building Dashboards with Microsoft Dynamics GP 2016 – Second Edition, we would learn how refreshable Excel reports are easy to deploy, easy to update, and easy to work with. That’s why, they make a great foundation for an Excel 2016 dashboard. In this article, you will learn how to manage security to Excel reports and run these Excel reports from Dynamics GP 2016 as well as Excel 2016.

(For more resources related to this topic, see here.)

Security

By default, users can view Excel reports and data connections only if they have administrative credentials on the server that is running the SQL Server and if they have access to the network share. Since this isn’t a normal setup, users typically need reporting privileges in the SQL Server before they can view the Microsoft Dynamics GP data that is displayed in data connections and Excel reports.

There are three areas of security around Excel reports deployed to a network share or local drive:

  • Security to the network share/local folder
  • Security at the database level
  • Security around Excel

We’ll spend a few minutes on each one.

Network share security

Realistically, network share security is normally going to be set by a network administrator. To make a shortcut for administrators, the minimum required security on the shared folder is:

  • Change option for the share tab
  • Read option for the security tab

Now, for those of you who want the version that is longer than a (as Mark Polino would say) Latvian wiener dog, follow these steps:

  1. In Windows Explorer, right-click on the folder where you deployed the Excel reports and then click on Sharing and Security…
  2. On the Sharing tab, click on Advanced Sharing… and select Share this folder.
  3. Click on Permissions.
  4. If the user or group already exists in this window, you can skip to the next step. Otherwise, follow these steps:
    • Click on Add…
    • In the Select Users, Computers, or Groups window, enter the group or users to whom you want to provide access to the shared reports
    • Click on OK
  5. Select the user or group to apply permission to in the Group or user names area.
  6. Select the Allow checkbox for the Change permission and then click on OK. The Change permission is the minimum required permission.

  7. Click on the Security tab.
  8. In the Groups or user names area, click on Add.
  9. If the user or group already exists in this window, you can skip to the next step. Otherwise, follow these steps:
    • In the Select Users, Computers, or Groups window, enter the group or the users to whom you want to provide access to the shared reports
    • Click on OK
  10. In the Groups or user names area, select each group or user, and then click on the permission that you want the group or the user to have. The minimum required permission is Read.
  11. Click on OK.

    These instructions will vary depending on the version of the Windows server used on the network or the user’s version of Windows on a local drive. If you are unsure about setting this up, consult your IT department.

By default, Dynamics GP 2016 deploys reports related to each company and each functional area in their own network folder. This makes it easy to apply different permission levels to sensitive areas such as payroll.

Database-level security

Access to information in the Dynamics GP 2016 database is handled a little differently. A set of fixed security roles is created automatically in the SQL Server when Excel reports are deployed. All of these roles start with rpt_. These roles provide access to the underlying tables and views. The process to assign security is to add a user or group to the SQL Server and give them access to the appropriate roles. The users that get added are not Dynamics GP users. They are either SQL Server users (different from the GP login IDs) or active directory users and groups.

To connect the SQL role with an Excel report to ensure that a user has appropriate access, you really need the spreadsheet from Microsoft that links the two together. You can find it at https://mbs.microsoft.com/fileexchange/?fileID=e4bb6958-0f07-4451-b72c-f02784e484df.

This spreadsheet is from version GP 10, but it still works for GP 2016.

In our example, we need access to the Account Summary Default Excel sheet. This sheet uses the Account Summary view. On the spreadsheet, we see a number of roles that include the appropriate access:

For our example, we’ll give a user access to the rpt_accounting manager role. In practice, it’s not unusual to add all GP users to a single active directory group and give that group access to all the fixed reporting roles. This is particularly true for companies that don’t use payroll and that don’t have other sensitive reporting requirements.

To grant database permission using the built-in roles, we have to add the user or group to the SQL Server and then assign the appropriate role(s).

To add a user to SQL Server, follow these steps:

  1. Open SQL Server Management Studio and log in using either Windows Authentication or SQL Server Authentication.
  2. Go to Security | Logins.
  3. Right-click on Logins and select New Login…
  4. Click on Search.
  5. Enter the domain and user you want to add or enter the group that you want to add to the SQL Server. For my example, I’m entering my domain and user name—Njevityballen. This could also be a group of users such as GPUSERS, for example:

  6. Click on Check Names to validate the entry and click twice on OK to finish.

The user has now been added to the SQL Server. Our example used a domain user, but you can also set up a SQL user. In general, a domain user is preferred, because it eliminates the need for the user to manage multiple logins and passwords for reporting. Using a domain login also provides additional control to administrators. If an employee leaves, for example, removing them from the domain removes both their network access and their reporting access in one step.

To grant access to the reporting roles, follow these steps:

  1. Go to Security | Logins, double-click the user or group that you just created.
  2. Select User Mapping on the left-hand side.
  3. In the upper-center section labeled Users mapped to this login:, select the box next to the company that you want to grant report access to. For our example, select TWO.

    In the lower-center section named Database role membership for: TWO, select the box next to rpt_Accounting Manager:

  4. Click on OK to continue.

The user now has rights to access the TWO AccountSummary default report that we’ve been working with and any other reports available as part of the rpt_Accounting Manager role.

Excel 2016 security

As you connect with database connections in Excel, a security bar may pop up with the message SECURITY WARNING External Data Connections have been disabled:

This is an Excel security feature designed to prevent malicious code from running without the user’s knowledge. In our case, however, we deployed the reports. We are now running them on our network and controlling access. This is about as secure as it’s going to get, and the message is really annoying for users. Let’s turn it off.

To disable the Excel security message for these files, follow these steps:

  1. Open Microsoft Excel 2016 and go to File | Options | Trust Center.
  2. Go to Trusted Center Settings | Trusted Locations.
  3. Click on Add new location.
  4. Browse to the location where you deployed the Excel reports. In my example, I used C:GP2016XL. Click on OK.
  5. Select the box marked Subfolders of this location are also trusted and click on OK:

  6. Click on OK twice to exit.

Now, when you run the Excel reports in the next section, the reports will open in Excel 2016 without the security warning.

Microsoft offers a great knowledge base article on Excel reports and security at http://support.microsoft.com/kb/949524 for GP 10, but this portion of security remains the same.

Running Excel reports

Our next step is to run an Excel report. These reports can be run from Dynamics GP 2016, or they can be directly opened in Excel 2016. We will look at both these options.

From Dynamics GP 2016

To run an Excel report from within Dynamics GP, follow these steps:

  1. In the navigation pane on the left-hand side, click on Financial. The list pane above will change to show financial items.
  2. In the list pane, click on Excel Reports.
  3. In the navigation list in the center, select TWO AccountSummary Default. Make sure that you select the Option column’s options that includes Reports:

    Options that contain the word Reports open Excel reports. Options with Data Connections in the string indicate the data connector to build a new report, not an actual report. You can limit the Excel reports list to just Reports or Data Connections with the Add Filter button just above the Excel reports list.

  4. Double-click on the TWO AccountSummary Default item.
  5. We disabled the security warning earlier, but just in case, if Excel 2016 opens with a security warning at the top of the worksheet, click on Enable Content.
  6. Excel will open with live data from Microsoft Dynamics GP:

  7. As a test, highlight rows seven through 10 (7–10) on the left-hand side and press the Delete key.
  8. Go to Data | Refresh All on the ribbon. Excel 2016 will reconnect to Dynamics GP and bring back in the latest data.

    Saving the report with a different name in the same folder as the GP deployed reports will make that report visible in the list of Excel reports in GP.

From Excel 2016

To accomplish this same task (run a GP Excel refreshable report) from Excel 2016, follow these steps:

  1. Open Windows Explorer and navigate to the location where you deployed the reports at the beginning of this article. In my example, the reports were deployed to C:GP2016XL.
  2. Drill down through the folders to Reports | TWO | Financial. This represents the report storage for the sample company’s (TWO) financial reports:

  3. Double-click on TWO AccountSummary Default.xlsx.
  4. Excel 2016 will open with live data from Dynamics GP.

Manual versus auto refresh

Excel reports are refreshable, but that doesn’t mean that they have to refresh automatically.

Often accountants ask about saving a static version of the file. They love the idea of refreshing data, but they want it to happen on their terms. Most accountants prefer information that doesn’t change once it’s been finalized, so this request is perfectly natural. By default, the Dynamics GP 2016 connections are designed to refresh automatically when the file is opened, but you can control this.

To understand how to control the refresh options, follow these steps:

  1. Start with the TWO AccountSummary Default Excel file that you already have open.
  2. In Excel, select the Data tab and then go to Connections | Properties:

  3. Uncheck the Refresh data when opening the file box and click on OK.
  4. Click on Close to return to the worksheet in Excel.
  5. To validate that this worked, select rows seven through 10 (7–10) in the Excel sheet and press Delete.
  6. Save the Excel sheet to your desktop as TWO AccountSummary Default Manual Refresh and close Excel 2016.
  7. To reopen the file, double-click on TWO AccountSummary Default Manual Refresh on the desktop.
  8. Excel will open with data, and rows seven through 10 (7–10) will be blank. The sheet did not refresh automatically.

To manually refresh the sheet, right-click anywhere in the data area and click Refresh or select Data | Refresh All.

Summary

We’ve looked at one of the best methods for getting data for our dashboard. We’ve deployed, secured, run, and built Excel reports. Now that we’ve thoroughly explored one of the best ways to get real-time data out of Dynamics GP 2016 and into Microsoft Excel

Resources for Article:

Further resources on this subject:

Packt

LEAVE A REPLY

Please enter your comment!
Please enter your name here