Refreshable Excel Reports Mean Right-to-the-Minute Data

Written By: Nina Clarke

from December 19, 2012

A high priority on every CFO’s task list is equipping employees with the best tools to gather the correct and most current information in order to make the best decisions with real time results. Using Microsoft Dynamics GP with Smartlist Builder to publish refreshable Microsoft Office Excel reports into Microsoft Outlook is a fantastic way to put a check on that list. With the ability to format the reports to include graphs, arrange information so its best understood by the user, and refresh at any time to gather the most current data – making an informed decision goes from burdening task to smooth solution. How it Works: SmartList Builder installed in Microsoft Dynamics GP provides an extra set of tools that allow the creation of specialized reports. The User creating the report must be one of the following:

• SA user or DYNSA user • Any user in the db_owner database role, SysAdmin fixed server role, or dbCreator fixed server role in SQL

Start by going to the Microsoft Dynamics GP menu and select Tools, click SmartList Builder, click Excel Report Builder, and choose Excel Report Builder. Choose which tables from Microsoft Dynamics GP to pull together to create the report. Notice that the table and field names in SmartList Builder are in simple and easy to understand terms just like the screens in Microsoft Dynamics GP. (Knowing the SQL coding behind the report is not necessary.) To Pivot or to List: Next, decide what format to display the data in. For a Go To or Drill Down link, use a List format. To summarize and control the look of the report, use a Pivot. Remember there will not be any Columns. Columns will be arranged in Excel since Excel Calculations and Restrictions look the same and work the same as Smartlist builder. Publishing Reports: Once all the tables are added, publish the reports. Be sure to set up a secure location for saving the files. Select the secure location and set up the desired permissions. When setting up permissions it can be a two part process if there are external users. Have the IT team set up external users in the right groups within SQL. Then choose which groups have access to the reports by using the check boxes listed. Getting Reports into Outlook: First, have the IT team create a public folder using the Mail and Post Items option in Outlook. Next post the reports Excel file in that public folder by going to Outlook. Click New Items, click More Items, and choose Post in this Folder. An Outlook screen opens to the file created. That’s it! Refresh the results each time the report is opened. The security of the Outlook folder and who has access to the data in the Excel reports can be controlled. This makes sure numbers are up to date, allows the most informed decision to be made, and keeps the boss happy!