Blog

Created on Tuesday, 15 May 2007 20:18

Written by Larry Grey

Post-processing Query output in Excel

The question behind this posting came from several different customers in the UK earlier in the month. The use case behind this is that customers want some additional control over what comes up in excel when running a query to excel without requiring the user to perform manual steps to accomplish this.

Options

So, there are several options for accomplishing this, each with its own set of pros and cons. Here they are.

• Creating a tabular report in nVision and using the instance hook in nVision to do this.
• Scheduling a query, sending it to a known folder in the process scheduler, and then having a VBA program open each file, format it, and save it.
• Performing the formatting on the client when the excel file is opened.

Using nVision

Pretty much all you need to know is covered in this blog posting.

Although this works relatively well to solve the problem, it has the following issues.

• An nVision tabular report must be created for each query. This requires a developer to get involved (but does provide granular control over the formatting
• nVision runs only on NT, which means that the reports must be scheduled on an NT box dedicated to it.
• The instancehooks must also be added to each report (versus having a global one for all reports

Scheduling the Query and using an App Engine Program

This approach has quite a few moving parts, and will not work if you want to run the queries to window. Here is my recommendation for doing this:

1. Create a VBA program that looks into a directory for an excel file, opens it, performs the processing, and closes it.
2. Create a process definition for it in process scheduler, so that it can be invoked as part of a job (clone the settings for the scheduled query app engine program, so that it can be kicked off from the same page)
3. Create a job with both process definitions in it. This job can be scheduled from the schedule query run control page.

Add the macros to the client instead of the server.

This approach, I believe, is the easiest to put in place and has the most stability. The key to it working is that whenever excel opens up a spreadsheet, it will fire the auto-open macro in any add-ins that are part of the excel environment. Therefore, by creating an excel add-in with an auto-open macro in it, you can cause the macro to run when the query results are opened.

This sounds easy in practice. However, to be successfull, you need to test to see if the excel file came from query (where you do want to process it), or if it didn't (which means you don't want to touch it).

This is where the tricky part comes in. You see, because drilling was always a part of nVision, we stuffed additional metadata into the resulting file that allows us to do all sorts of cool things with it. However, with query, we never thought to do that (and one concern we had was about keeping the file as small as possible for sizing reasons). Now that I'm no longer running the group, I'm thinking that it would have been really nice to have put that feature in (if I had a time machine, I could probably go back and fix that... Does anybody have one I can borrow?)

Okay. So now we all have to workaround an oversight I had when I owned query. What's the best solution? Well, unfortunately, scheduled query and the online query (which is a servlet on the web server) have different formats of files. Here, let me illustrate.

Here's what it looks like when runnning query online (using the servlet). See if you can find the query name somewhere in the picture.

Now, here's what it looks like when running a query through scheduled query. Again, see if you can find the query name somewhere in the picture.

As you can see, the query name is not in the query results, but is in the title bar of the browser (if hosting in excel). This is a property that is not easily accessible in VBA (although we spent a lot of time and effort figuring that out for our product... you'll have to buy it to get that code from us, though ;-).

Our recommendation to you is to have a standard set of text that you include in your query result, such as a prefix in the query description (because the query description is always put in call A1 regardless of whether its run through scheduled query or online). If you want to put somebody else on the hook for making sure it works right all the time (and also get a lot of other cool features that would take a lot of work, such as drilling, subtotalling, and number formatting), we think our add-in would be a great fit.

Plug for Grey Sparling Excel Add-in

Since we wrote this post (and due to requests from our customers), we ended up building this into our nVision add-in and changing the name of the product to be the "Grey Sparling Excel Add-in". The post processing we do is to apply formatting, such as number formatting, sizing of columns, freezing the coumn headings, applying auto-filters, and applying subtotals. This product also allows users to drill from their queries to pages or other pages. Here's the product page for it. Also, here's a flash demo of the product's features.

Labels: excel, Query