Getting more Advanced with Excel Refreshable Reports

Hello Community, 

Last week we started discussing the Excel Refreshable Reports that are available with GP. 

These reports are awesome, but they tend to be missing columns that you can add in SmartLists. The good news is that the Excel Refreshable Reports are all built off similar views, and usually have the exact same column. All you must do, is tell Excel to pull more information from the view. 

How do I Edit the Default Excel Refreshable Reports?

Here is an example of what to do to edit the Item Quantities report to add the Item Class Code column. These steps should work for any report you may need to change, just look for the right report!

1. Open the Excel Report
2. Click the Data tab at the top
3. Click the Queries & Connections button
4. This should open either a new window, or a bar on the side depending on your Office version
5. Right click on the connection and select properties
6. When this window opens, make sure you are on the Definition tab. This will tell you what SQL command is being ran:

7. All of the default reports are built off of views, in this case the ItemQuantities view. This SQL script, like most of our Excel reports, is only pulling a limited part of the view.

8. You can pull up this view in SQL to see all the options you have for columns. These views are found in each company. Simply right click and use the “Select Top 1000 Rows” option to see what a script pulling the entire list of columns would look like.

9. In this case the Item Class Code field is a little way down:

10. To add this to the Excel report, simply copy the entire field as shown above. This means everything from [ to ], including the symbols themselves.
11. Then head over to the Definition in Excel from step 6.
12. Paste the field in on a new line right before the ‘from’. Make sure to add a comma to the end of the line above.

Copied out it should look like this:

/*ItemQuantities Default**/

select

[Item Number],

[Location Code],

[Record Type],

[QTY On Order],

[QTY On Hand],

[QTY Allocated],

[QTY Available],

[Item Description],

[Item Class Code]

from ItemQuantities

order by [Item Number]

13. You can then click OK and see your updated report.

NOTE: As you have edited the connection, it will no longer match our default one. Excel will know this and warn you that it will break the link between the two. You can just click Yes on this error.

At this point the sheet should update with your new columns. If you know SQL you can also add in filters or sorts into the script in the Description from step 6. This means that you can have reports that filter before they even open. As always, you can just use Excel to sort as well.

This should work for just about every Default Excel Refreshable Report.

What happens, however, if you want a report that isn’t based on one of the default ones? Perhaps you found a great View to pull in something we don’t have in SmartList.

This leads us to creating custom Excel Refreshable Reports from scratch, which will be here 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