First, I had hoped to have videos by now to show the ad-hoc queries in action. However, the person who is working on the videos had a bicycle accident last week and cracked a couple of ribs. He's ok except that he has allergies and it's pollen season – a sneeze is now an incredibly painful event for him. So he's recuperating at home and will be back this week to do the videos.
We originally struggled with this quite a bit. We had a drill-down tag but it was a mess where in the tag you defined the items to pass down to the soon to be created detail template. The tag required setting everything up for a template that did not exist and then creating the detail template. The order of work made sense based on how people thing of the problem – define the main template, then define the details you drill down into. But it meant a very complex U.I. in practice.
Then one of the developers proposed that users first define the detail template, then add the tag to the master template. And with that the entire process became very simple. The user points at the detail template and AutoTag then reads the template, determines what variables need to be defined to run the report, and then prompts the user to define those templates. And does so with the variables in the master template all available in drop-downs. So we have a very simple and straightforward way to define the drill-down tag.
BTW – If you're wondering about the skin in the screenshot, the default skin setting will use seasonal skins at different times of the year. For the month of May it is the springtime skin (this can of course be overridden). We also have seasonal skins for the 2 weeks before Valentine's Day, the 2 weeks before Halloween, and the months of July (summer) and December (holiday).
So we're good – no? Actually no.
We first tried placing the links in the template as "filename.docx /wr our parameters". Office does not like parameters it does not recognize. And even if it did, there is no way to get the parameters a document was loaded with. (You can get the parameters Office starts with, but we need this for a document loaded after Word/Excel/PPT has started.) This was our situation at the start of this week – a great system except we had no way to handle the link.
So we tried a number of approaches:
One of my primary rules as a developer is that when I'm not sure what to do – ask for advice. So I asked around and one person recommended something that is perfect for our needs – a custom protocol handler. This is a very simple way to define a program to be called when the protocol for a link is a specific string. So we changed the protocol from file: to wrddp: (Windward Reports Drill Down Protocol) and we now have exactly what we want.
Word/Excel/PPT handle the link identically to an http:// or file:// link. It looks the same, it responds the same. But when the user clicks on it, the url is fed to our application and we then pass it back to AutoTag via named pipes. And then in AutoTag we then run the detail template passed in using the data values passed. And display the generated detail output.
What I like about this approach is we're implementing this using functionality in Windows and Office that was designed for exactly this use. So the solution is simple & solid.
We have about another 2 – 3 weeks to finish this up and complete internal testing, and then we'll be pushing it out for alpha testing (existing customers only). This will be the first real test of our approach to B.I. – actually using the product. Many love the concept, but it's in use where the rubber meets the road. With that said, based on our internal testing, I think people will find this so easy that it'll be faster drilling than the gopher in Caddy Shack.
For more, please follow me on Twitter.