Getting Custom with Excel Refreshable Reports

Hello Community,

Over the last few weeks we have been diving into the Excel Refreshable Reports available for GP.

These reports are awesome, but sometimes you just need something special, something created just for your company and its reporting needs, something… Custom.  

This leads us to:

How do I create Custom Excel Refreshable Reports?

Overall creating the reports are easy if you only want data from a single SQL Table, or you have a view already in SQL. Here is an example using a view, although any SQL script should work:

1. Open Excel
2. Click the ‘Data’ tab at the top
3. Click the ‘Get Data’ button
4. Select ‘From Database’
5. Then ‘From SQL Server Database’

6. This will open the following window, click the ‘Advanced options’ link to show the SQL statement that we will be running:

7. For the Server, we will want to use the full server name, as if you were connecting using SSMS. For example, this is what shows in SSMS, and is exactly what I put in for the Server:

8. For the database, it is required that you put in the company Database you want to use. Without this, you will be unable to enter in the SQL statement below. You can work around this, but you only need to do this if you want to run the same command against different databases. I, instead, recommend using different connections (i.e. do these steps for each database and script). I am using the default TWO company.
   
   
9. Under the Advanced fold is were we are putting the SQL script we want to use. You can pull in entire tables, or even use views. There are many members of the GP community that have great views that you can use here. To use a view simply run it against the company to create the view, then run a select against it like it is a table. I am using the default ‘popWorkTransactions’ view.
   
   
10. Once everything looks good, click OK. Here is what my example ended up looking like:

11. This will pull up a window that will show a test pull of data. Make sure you are getting some data that you expect. In the bottom right is a load button, you can click the button to create a new sheet with all of the data coming in, or can click the arrow and then the ‘Load To…’ option to pull the data to a location you choose like my example:

12. Once you click Load (or OK if you clicked Load To), it should create a table with your data already populated:

At this point you are basically done, and now you can get creative with your use of the data. You can pull a bunch of tables/views into different sheets, then pull them all onto one Pivot table. Or you can filter using Excel, or sort using whatever column you want. You have the full functionality of Excel at your fingertips. Just make sure you save the Excel Workbook before you close it!

If you are interested in creating a Pivot table, you can find more information here:
https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

If you are interested in learning about all the settings you can setup to refresh the data, you can look at the link below. By default, the data will refresh in the background whenever you use the Refresh All button. Be aware that setting this to refresh on opening the file will increase the time it takes to open the file. That said, this is my favorite setting to use as it makes sure the user is seeing the most recent data when they open the report.
https://support.microsoft.com/en-us/office/refresh-an-external-data-connection-in-excel-1524175f-777a-48fc-8fc7-c8514b984440
 

As a note, the Dynamics GP Support Team has limited ability to assist with these custom reports once they are in Excel. As always, we will do our best. That said, the community may be a better resource when it comes to Pivot Tables, or other Excel magic.
https://support.microsoft.com/en-us/help/850201/guidelines-that-microsoft-support-professionals-use-to-determine-when

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