Tech Tips More GP Data than Excel Can Handle? If you are reading this Tech Tip, I’m assuming you like/love working in Excel OR you feel like you know Excel well enough to be your own “report programmer.” Either way, I applaud you for taking control of reporting or business analytics. The world, technology, and our economy are changing so rapidly that being able to act and react immediately is crucial for ALL businesses. I know I’m not telling you anything that you don’t already know, so let’s get down to your options. What are Excel Refreshable Reports? With the release of version 10 of Microsoft Dynamics GP many years ago came this AMAZING module called “Excel Refreshable Reports.” In short, it’s the SmartList objects that were already built in GP (out of the box), placed directly in Excel. The advantage of this is that you can format Excel and save the formatting of that Excel file. The next time you need to access the data, you simply pull up the Excel file (with the formatting already done) and click Data (from the menu bar) then Refresh (from the ribbon), and the file will be updated with all the latest data from GP
. Once the reports are deployed, you’ll see two (2) files for each report. Once is an ODC, and one is an XLSX. The XLSX is the Excel file. The Excel file created uses the ODC (Office Data Connection) file created to know how to connect to the GP data, which is stored in Microsoft SQL Server. What are Excel Data Limitations? Which version of Excel should you use? You’ll want to be on Excel Version 2007 or 2010. Why? Column and row capacities in older versions cannot accommodate some of the files. Below is a table that shows size limitations of various versions of Excel. You’ll see that Excel 2007 and Excel 2010 have much larger row and column maximums than the older versions. You should always work with supported versions of software; this includes both GP AND Excel. Version
Max. Rows
Max. Columns
Max Columns by letter
Excel 2010
1,048,576
16,384
XFD
Excel 2007
1,048,576
16,384
Excel 2003
65,536
256
IV
Excel 2002 (XP)
65,536
256
IV
Excel 2000
65,536
256
IV
XFD
Excel 97
65,536
256
IV
Excel 95
16,384
256
IV
Excel 5
16,384
256
IV
Over a million records sounds like a lot, but it still might not be able to handle your needs, so you’ll need to address your Excel needs differently. Let’s review some options: What is the difference between an Excel Table and an Excel Worksheet Think of a table as being an “Excel Database.” In an Excel worksheet, you can put a single record on many lines if you like (like the following).
A table will be setup so each row is one complete record and each record is only on 1 row (like the following.)
Both of the above examples provide the same information, but by placing the data in a table rather than a worksheet:
Excel will apply default formatting to any new data added. Excel will automatically apply the Filter setting. The headings for filters will remain when you scroll down. You can easily add totals to the bottom with the check box in the Table Tools Design tab.
If you create a formula in a column, new data rows will have the formulas automatically copied to the new record. (This is a powerful feature if you use a calculated column in a Pivot Table.) This also means Pivot Tables will automatically capture the new data as well.
Understanding and using Tables is an important and necessary element to Excel Reporting.
Excel Report Builder (From SmartList Builder Module) The first method you should consider is the Excel Report Builder, which comes with SmartList Builder. Master Records Master Records (GL Account numbers, customers, vendors, employees, inventory items, fixed assets, etc.) can often be reduced in volume by excluding inactive or temporary records (in the case of inventory, discontinued items). Anytime you are creating a SmartList favorite and one of the first things you do is exclude master records by these or some other criteria, you can exclude these.
Transaction Records Transactions records can (and usually should) be set to exclude Voids and might also include restrictions such as posting status, document types or date ranges (years.)
This is “Minor League” Programming When you read the next paragraph and you become nervous about what you are ready to do, call your GP Partner to assist. If you fail to connect data properly, you’ll end up with an unreadable report with bad information on it. The purpose of this document is to make you a Reporting Superstar, so obtaining assistance for initial setup will be worth it! The great thing about SmartList is that all the tables in GP are linked together properly for you, and some of the fields are interpreted for you. This makes using it much easier. If you use SmartList Builder or Excel Report Builder, you’ll have to perform the linking yourself. Cool Things about Excel Report Builder 1. Excel Reports built with Excel Report Builder can easily be setup for multiple GP databases. This means you can report on multiple companies without manually combining them. 2. You can have totals automatically appear at the bottom and can even have summary worksheets added to the workbook.
3. You can create a drill back into GP. For example, if I build an Excel Report for Purchase Orders that need approval, I can create the Excel report so when I click on the PO number, the PO will open in GP for me in a window that I approve. Back in Excel, I just click Data-Refresh and the report is updated.
4. Restrictions can be added ahead of time, limiting the number of records that appear in Excel.
5. Calculated fields can be added. Imagine pulling up an AP Open Transaction Excel report that automatically provides the document amount, but the effect on AP As well. So Invoices show up as positive, and returns, credit memos and payments show up as negative. 6. You can control permissions, even giving non-GP users certain reports. 7. You can have the data appear as an Excel Table OR an Excel Pivot Table. Having Excel access GP data directly You can create your own Excel Reports directly from Excel, if you like, by creating your own ODC or Office Data Connection File. From the Excel menu, choose “Data” then choose “From Other Sources” from the ribbon. From the drop down menu, select “From Data Connection Wizard.”
You can select “From SQL Server” also. Either way, you can refresh the data, or it’s the same screens. I like the Data Connection Wizard because it assures me that I have “refreshing” capabilities. As you can see from the screenshot below, accessing data this way takes you directly to the tables, so you must know:
What tables(s) you need to access. How to link them together. What certain fields represent and how to create formulas in Excel to make them understandable (e.g. the following is the GL Account Master; although I can see each segment and the description in the screenshot below, I do not have the entire account numbers as I enter it.) SQL Permissions must be setup up correctly.
This is where using your GP Partner to assist will be beneficial. They know the tables, how to link them and how to edit fields, so you can see “Balance Sheet” for ACCTTYPE rather than “1.”
Still too much data? None of the options above address what to do if your data exceeds or is approaching the row limit of 1,048,576 records. So here are some options for you:
Using Excel Report Builder, create Pivot Tables directly, not lists or tables. Using the Excel Data Connection Wizard, create Pivot Tables directly, not lists or tables. Use Microsoft Query to create your own Excel table or Pivot table using MS Query to narrow down what you want see BEFORE it is dumped into Excel. With Excel 2010, utilize the free downloadable tool called “PowerPivot.” It works similarly to Microsoft Query BUT with the ability to link data from multiple sources.
Advice from Belinda 1. Always use the most recent version of Microsoft Excel. 2. Always use a “Microsoft” supported version of Dynamics GP. 3. Review which SmartList favorites or objects you use the most. Answer the following questions for these objects to guide you in the decision of whether or not this would be better suited as a stand-alone Excel report. a. What is in the criteria? b. How often do you print them? c. What do you use them for? d. Who gets the reports? If recipients accessed the report themselves, would it save you time? 4. Exclude all VOIDS from transaction records. This will reduce not only record volume but also bad information if VOIDS are not handled correctly. 5. Utilize your GP Partner. They can build a few SQL views and/or Excel Reports (or Pivot Tables) for you that will allow you to take control of your Ad Hoc reporting. Often if you are reporting on Sales Transactions, there may be a specified number of fields you may choose to report on (it doesn’t matter if it’s 10 or 100) and a single View or Excel Report/Pivot Table can handle all of your Ad Hoc or reporting needs. One good record (view) from your GP Partner will provide you with hundreds of reporting options. 6. If you love Pivot Tables, learn more about Analysis Cubes for Dynamics GP. http://download.microsoft.com/download/E/9/B/E9BB517C-4845-409C-B1E50FD85077D7F1/GP_AnalysisCubes.pdf 7. Consider using Microsoft Query. It’s a free Office tool that has been around a long time. It’s an oldie, but a goody! http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-dataHA010099664.aspx If using Microsoft Excel 2010, utilize the free downloadable tool, PowerPivot. This is another reason to keep About the Author: Belinda Allen (Microsoft MVP) is both a Microsoft Certified Professional for Dynamics GP (MCP) and a Microsoft Certified Trainer (MCT). She is also the co-founder of Smith & Allen Consulting, Inc. (www.saci.com), a business management software consulting firm for over 21 years. Belinda is also well known for her Blog www.BelindaTheGPcsi.com. Her blog is about sharing really useful information about the product quickly and succinctly. She has earned the nickname GP CSI because she excels at reviewing GP problems and figuring what went wrong and why. With followers all over the Globe she is able to share knowledge and achieve her mission: “To Improve the Lives and Business Success of my Followers.”
