I generally like having custom reports within the system but it can be spendy to do a bunch of customized reports for trivial tasks. One issue I faced was with reports (in version ~9) where the data was good, but the layout wasn’t in a dataview (Vouchers Payable, Aging reports, etc). I could get around this myself by running a query inside of SQL Server Management Studio, but that got me thinking: why not just allow users themselves to execute the stored procedures that generate the data from within Excel? This gets around two issues, first is that it is not a customized report as the user is running the same routine that CSI does and secondly they needed to get it into Excel anyway, so in a way I’m skipping the “middle man”.

The first thing to do is to collect the list of parameters needed to run the report. This can be gotten rather easily by “printing” the report and then going to Background Task History and pulling the task parameters:

I then make a note of those and execute the stored procedure inside of SSMS using those parameters, but an important note here: do not leave any of the values as “null” as Excel cannot pass null values to the stored procedure (as I am doing it without any code). After some cleanup I have something like this:

This is also good because the parameters are in the sequence needed for Excel, as we’ll see in this next step where we will put the parameters on a separate tab. Although ranges are required for Excel, and it “kinda sorta” doesn’t do nulls, I’d found that putting a double single quote in the cell accomplishes what I need it to (note: shows up as a single ‘ when viewing the spreadsheet):

Next we can put the query together in Excel, be sure to use “From Microsoft Query” because otherwise we will not be able to pass parameters (dynamically) to the stored procedure:

If needed on the next step add a connector to the database server (“New Data Source”), then select the connector and click OK. On the next step click Cancel and then Yes to edit it in Microsoft Query. Click Close and then click the SQL button:

Then click the Definition tab and then Edit Query. The formatting on this can be a smidge tricky since there are multiple parameters, but you’ll want a question mark for each parameter going to the stored procedure, so in this case I’ll enter a query like this:

{call Rpt_VouchersPayableSp (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}

After you click OK you will get a warning that it can’t be represented graphically, click OK again and you will be prompted to fill out the parameters, just click OK on all of them (leaving them blank). Even if it comes back with no data that’s okay, just go up to File->Return Data to Excel.

When it goes to Excel it will re-prompt for parameters. In this case, it will more than likely want real data or it will error out and dump the query and you’ll have to restart from the Data Connector step. I will usually key this in and fix it afterward, though you can set it up properly at this step by selecting the cells on the Parameters sheet, just be sure to click ‘Use this value’:

Hopefully that ran through okay, but this then gets into a permission issue that I ran into. For users running these stored procedures the only database access I wanted them to have was to the stored procedures themselves. However, when I tried to run it as the user I would get ODBC errors in Excel and in SQL I would get errors like:

Get DataType failed for StoredProcedureParameter
Property DataType is not available for StoredProcedureParameter

This post put me on the right path. The issue is that many CSI stored procedures have custom data types and the user needs to have View Definition permission to the schema where the data types are scoped to, in this case ‘dbo’. So I give the user Execute permission on the stored procedure and View Definition to the dbo schema and then the user can run the query inside of Excel with minimal permissions.

[Note that some of the data returned by these stored procedures can be ungainly. For the Vouchers Payable report I ended up making a new copy of the stored procedure and cleaning up the outputs. This is rather trivial and the advantage here is that as the stored procedure changes, the Excel output will change accordingly without having to change anything on the spreadsheet].