One of our favorite missions used to be getting people to stop using spreadsheets with Dynamics GP and Dynamics SL here with clients in Minneapolis and the surrounding areas.
We would see people keying data in 3 and 4 times to different worksheets because they wanted the data to be in the exact format they were comfortable seeing it or that their boss wanted it. More often than not the data was keyed differently to one source than the other and very difficult to reconcile.
Now with web services and Microsoft’s Refreshable Excel Reports-we find ourselves telling people “Ditch the Spreadsheets!” far less. We still do not want people keying the same data more than once, but Excel now can be the viewing tool for extremely useful information and in formats that are very nice.
Dynamics GP uses SQL authentication and GP encrypts the passwords so that SQL cannot see them. So, if you try to use SQL authentication with your GP Password outside of GP it will not work. If you want to use SQL authentication you will need a new SQL login apart from your GP login.
Please continue reading below to understand Microsoft Dynamics GP Excel Reports better.
Microsoft Dynamics GP and Microsoft Office provide an integration method, called an Office Data Connection (ODC), that allows you to view live Microsoft Dynamics GP data in an Excel worksheet.
ODC provides a link to a system or company database in SQL Server. The ODC connection ensures that when the Excel report is viewed, it contains the most recent data.
Each ODC file supplied with Microsoft Dynamics GP corresponds to a default SmartList object. For example, an ODC file named TWO_Accounts_AccountSummary.odc links to the same data as the Account Summary SmartList object.
For each ODC file there is a corresponding worksheet. The Excel report connects to the ODC file to display data. For example, the Excel report named Accounts_AccountSummary.xlsx displays the data from the ODC file named TWO_Accounts_AccountSummary.odc.
Before users can view Excel reports, a system administrator must perform a process called “deployment” to create the ODC files and corresponding Excel worksheets, and make them available for users to access. There are two ways deploy Excel reports:
- To a shared folder on a secure server location
- To a SharePoint document library
To view the reports, users must meet the following requirements.
- To view the data that is displayed in an ODC file, users must have the appropriate SQL Server access rights. Refer to Setting up security for viewing data connections and Microsoft Excel® reports for more information.
- To open the .xlsx worksheet files, users must have the 2007 Microsoft Office system or the Microsoft Office 2010 system. Alternatively, users can open the files in Microsoft Office 2003 or Microsoft Office XP by installing the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 file formats. The compatibility pack can be downloaded from www.microsoft.com.
- Additional user permissions are required for the shared network folder or SharePoint Server document library, depending on where you choose to deploy the reports.
As part of the deployment process, the system administrator can designate a location where each user can store any Excel reports and data connections they modify or create. This allows each user to customize reports individually, without affecting the reports and data connections available to all users at the system level.
Once the deployment process is complete, both system-level and user-level Excel reports and their corresponding data connections appear automatically in reports lists within Microsoft Dynamics GP.
To view Excel reports and data connections for a particular series, choose the appropriate series button in the navigation pane, then choose the Excel Reports shortcut. To view Excel reports for all series, choose Administration in the navigation pane, then choose the Excel Reports shortcut. See the System User’s Guide (Help >> Contents >> select Using the System) for more information about report lists.
