November 24, 2014 
A long awaited new feature is being released today for our GP Reports Viewer – Excel reports. What I think is truly amazing about this new feature is the fact that you can set up an Excel report based on a stored procedure to allow users to enter parameters and also to improve performance of your reports. While it’s not too terribly difficult to do, it does take a number of steps to set up a SQL stored procedure in Excel, so I thought I would demonstrate this in detail.
The steps below should be the same for at least the last few versions of Excel, possibly even older ones. I will use Excel 2010 in my screenshots, since that’s in the middle of the versions our customers seem to currently use. If any of the screenshots are not clear, you can click on them to see a bigger version.
- Create a SQL stored procedure. Even though you might already have a stored procedure in place, you will typically need to modify it, at least a little, for use with Excel reports. I recommend making sure that your results are in the exact order you want to see them in Excel and that your field names are all in the format you will want to see in Excel (ie, with spaces). To have something to use as an example, this morning I posted code for a SQL stored procedure showing all unapplied AR transactions with a parameter that will use ‘customer name contains’.
- I like to start with a new Excel file for this, but there is no reason I can think of that you cannot also do this on a new tab in an already existing workbook. I also like to set up the layout in Excel first, so I add the report name and a sample parameter, and leave the cursor where I will want the data to start (in the example below, that will be cell A5):
- Go to the Data tab and select the From Other Sources dropdown, then choose From Microsoft Query.
- Select (or create) a Data Source for your SQL Server. If you have Dynamics GP installed on the computer you’re working on, you should be able to use the ODBC set up for that. Click OK.
- On the SQL Server Login window, you can do a few different things. If you’re setting this report up to be used with GP Reports Viewer, you can simply use the sa login and password, as that is not saved with the file and GP Reports Viewer will use permissions that you set up inside GP. If you’re going to be using this as a stand-alone Excel report, I typically recommend choosing Use Trusted Connection and creating SQL logins for your network users/groups that will be running the reports. Click the Options button and select the database where you created your stored procedure. Click OK. (Note: If you have the same stored procedure in multiple company databases and are using this with GP Reports Viewer, just pick one of the databases. GP Reports Viewer will automatically switch between databases as needed, depending on the company you’re logged into.)
- The Query Wizard – Choose Columns window will come up, you can click Cancel or close it with the X at the top right. You will get the following pop-up, choose Yes:
- On the Add Tables window click Close.
- Click the SQL button on the Microsoft Query window:
- In the SQL statement box, enter your stored procedure name and number of parameters using the following syntax:
{call YourStoredProcedure (?,?,?)}
YourStoredProcedure will be the stored procedure name, there needs to be a question mark for each parameter (so the example above shows 3 parameters) and those are curly (or squiggly) brackets around the whole thing. Here is what it will look like for the sample stored procedure I am using:
- When you click OK, you will get another pop-up, click OK again:
- I know it seems like there are a lot of steps left, but we’re almost there! Really. 🙂
- You will get a pop up for each parameter in your stored procedure, they won’t have names, but they will be in order, enter a value (preferably valid) for each and click OK:
- After you have entered the last parameter, if you’ve entered parameters that will return data, you will see the results on the screen. If you entered parameters that will not result in any data, you will still see the column headings, so at least you know it’s reading your stored procedure properly.
- As strange as it feels after all this work, click the X at the top right to close the Microsoft Query window, you will see the Import Data window, asking how/where you want to see the data. I recommend Table under how and if you had your cursor in the cell where you want the data to start, it will correctly show that cell under where, if not, you can change it:
- Click Properties on the Import Data window to set additional options and the parameter locations. On the Usage tab, you could select Refresh date when opening the file, although whether you want this or not may depend on your report. I would say most of the time I do select that.
- On the Definition tab, click Parameters to set the location of each of your parameters:
- On the Parameters window, you will see a list of all your parameters on the left, go through each one and select Get value from the following cell, then either type in the cell or click the icon next to the field to select the cell. Once the cell is populated, select Refresh automatically when cell value changes:
- Click OK three times to get out of all the windows. This is where the magic happens, your data is now shown in Excel:
- You will want to format the cells of your table, for example you can see in the screenshot above that dates are including the time and amounts are not formatted nicely. I typically select all the columns of the same type together and change the format using the Number section on the ribbon to make this faster. You can also change the table color on the Design tab and add a logo up top and totals at the bottom:
- If you resized the columns to fit better, click somewhere in the table, go to the Data tab, choose Properties and uncheck Adjust column width so it does not keep resizing automatically when the data refreshes.
- Save the report and you’re done! If you’re interested in getting my Excel report that I created using this example, post a comment below and I will email it to you. If you’d like to set this up in GP Reports Viewer, please follow the steps in the Report Maintenance section of the User Guide.
Victoria Yudin 
excelent thanks for your help
LikeLike
If I want a refreshable excel of a view – like our ever-changing chart of accounts – how do I do that? It’s not a sp, so there are no parameters. Thanks!
LikeLike
Andrew,
Just because there are no parameters does not mean you cannot use a stored procedure. 🙂 If you already know this method and are comfortable with it, there is no reason you cannot use it for all Excel reports.
Alternately, you can point an Excel file to a table or a view directly.
-Victoria
LikeLike
I can’t believe this is the first time I am seeing this information. Deanne Rasmussen is correct, this should be standard with GP. As should Aging,including Historic Aging. Mark Polino has a great post on Excel views with a parameter, but Stored Procedure I have wanted for a while.
Thanks for invaluable information Victoria. Always so informative. a true asset to the GP Community!
LikeLike
This should be a standard option within GP. Everyone will want it! Kudos to Victoria!
LikeLike
Thanks Deanne! 🙂
-Victoria
LikeLike
Yes James you list many of the exact benefits that have been developed. There is nothing like using Excel Query in combination with pivot tables or array formulas. It transformed our office and saved a million dollar investment in GP that was almost mothballed because we couldn’t pull data fast enough or easy enough for end user. However we are now trying to query the views and not having any luck. Rights have been granted but maybe not correctly? Our Microsoft Partner has never been interested in Excel Query and really has not been of much help over the years in this area.
LikeLike
Hi Chase,
You might want to check out my company’s GP Reports Viewer, it can save you a lot of work, especially with permissions on Excel reports and your users can run them directly from GP.
http://www.GPReportsViewer.com/GP-Reports-Viewer
-Victoria
LikeLike
Amazing. Thanks for your detailed post.
LikeLike
I’ve been using this for years …and as a plus its faster than smartlists and you can set it up for end users to run.
A few things to think about:
1) You can use one of Victoria’s SQL queries directly rather than a stored procedure.
2) If you create a macro to refresh the table then create a button on the spreadsheet (from the developers tab) that calls the macro…it becomes very end user friendly.
3) By using the query in tandem with pivot tables you can have a ton of reporting, summary options. Plus you can drill down.
4) Using MS Powerpivot and MS Powerquery add –ins can produce the same results but in a more flexible environment.
5) I usually don’t refresh on file open or when cell values change as you may want to change parameters a few times before the run
One caveat ! You should create a read only SQL user to limit access to just the tables this report needs.
LikeLike
Thanks James!
If you’re using GP Reports Viewer you don’t have to worry about creating SQL users or permissions…it’s all covered. 🙂
-Victoria
LikeLike
Hi James, When you say you can use Victoria’s SQL queries directly, how is that done? I’m new to this, but trying to learn it all.
I created a published report using SmartList Designer and published it, but I’d like to create a report directly from Excel. I’m still learning, so there is a lot being said I don’t understand yet. I’m also a little nervous to just try something because I don’t want to screw up the database. How can I be sure I only have read only access? Does SQL Query only query?
LikeLike
Awesome Victoria!
LikeLike