Hello Community!

As time goes on, GP databases are getting larger and larger and users are looking for a great way to pull up the data they need.

This is usually done great through SmartLists, but sometimes you need to pull very large sets of data, and can’t tie up a computer waiting on SmartList to load tens of thousands of lines. Simply put, SmartList is great for moderate amounts of data, but anything over a few thousand is going to take quite some time. That is, if it works at all.


This is where the Excel Refreshable Reports come in. These reports are great as they pull data from SQL views, and only really tie up a single Excel instance.

To that end, I wanted to walk through some items to help you make the most of the Excel Refreshable Reports. Over the next few weeks, we will be walking through everything you need to know; from publishing them, modifying them, to even making custom reports from scratch.

Let’s start off simple:

How do I deploy the Excel Refreshable Reports?

Deploying the reports can be relatively simple, just follow these steps: 

  1. Create a shared location that all users interested in the reports can reach. Usually this is a network share. Everyone will need full rights to this location.
  2. Log into GP as a user with access to the Reporting Tools Setup window, I usually just use ‘sa’ to avoid any permissions issues.
  3. Open the Reporting Tools Setup window
    1. GP -> Tools -> Setup -> System -> Reporting Tools Setup
  4. Click over to the Excel Reports Tab
  5. Select Network Share from the Location dropdown
  6. Enter in the directory you want to use from step 1
    1. A local drive (or mapped drive) would look like C:\Folder\Subfolder
    2. A Network Share would look like \\Server\Folder\Subfolder
  7. At the bottom of the window will be checkboxes for each company, mark each one you wish to deploy reports for.

Note: If you have previously deployed reports and want to deploy them again you can mark the ‘Redeploy all reports for selected companies’ option. This will redeploy EVERYTHING, including SSRS reports.

  1. Click the Deploy Reports button
  2. A status bar will appear in a new window, telling you what report is being published for what company.

This is it! Once the bar completes, you can go out to the report folder you made in step 1 and look at your new reports. These are mostly based on the default SmartLists, so usually you can find something close to what you need. These reports can also be found in the same list as your Navigation Lists on the left side of the each of the different (Financial, Sales, Purchasing) area panes. 

So, what happens if these reports are close, but you are missing a column you added in SmartList?

This leads us to editing the default reports, which we will get into next week!

The Excel Reports blog series:

Starting off with Excel Refreshable Reports
Getting more Advanced with Excel Refreshable Reports
Getting Custom with Excel Refreshable Reports