Guided Ad hoc in Excel: A Unique Approach

Brian Carter

Businesses today must find the most effective, yet safe, way to roll out a Business Intelligence application. BI applications have to offer a quick and easy method for users to get the information they need to make intelligent decisions while helping them run the overall business more efficiently. 

It’s easy to position an ad hoc reporting tool with “power users” that understand the complexities of data, but exposing all classes of users to your data in that manner may not be the safest or most efficient mechanism. Not to mention, one must expect significant training costs in educating any class of user on an ad hoc tool.

WebFOCUS specializes in a unique approach called “guided ad hoc,” which is very effective for rolling out applications to all classes of users. The main benefits of guided ad hoc are that users do not have to sift through every field of a database, mistakes are minimized, and training costs are all but eliminated. There are many additional sources of information on the Information Builders’ main web site about guided ad hoc so I won’t go into detail in this article. 

What I’d like to highlight in this article is how guided ad hoc is now available to the Excel user. As hard as IT tries to remove Excel use from the decision-making process, it seems that may be impossible in the near future. The average business user will almost always turn to Excel when it comes to analyzing data and, ironically, he or she will many times use a BI tool (which was suppose to eliminate the need for Excel) to get data into a spreadsheet. Since there may not be any way to avoid that, WebFOCUS introduced a product last year called Quick Data that will undoubtedly help to eliminate the chaos caused by rampant and unregulated Excel use.

WebFOCUS Quick Data is an Excel add-in that enables Office users the ability to create ad hoc queries from within Excel to get the data they need right into their workbook. Quick Data delivers real time information with many safeguards that eliminate spreadsheet errors and ensure data integrity. Data that is retrieved with Quick Data is always contained in a named range, and formulas are generated automatically, which right away eliminates the major causes of incorrect numbers in a spreadsheet. Simply refreshing the query from within Excel using Quick Data ensures up-to-the-minute, accurate results.

The first version of Quick Data allowed users to build queries with Report Assistant, which is actually a scaled down ad hoc reporting tool. While this is a great tool for Excel users who understand the complexities of data, it may not be effective for the average or novice user, leaving many to continue pushing data into spreadsheets and manually maintaining range names and formulas.

In WebFOCUS Version 7.6.5, Quick Data can now retrieve data into Excel via a guided ad hoc form. This unique approach allows Excel users to easily retrieve data by simply selecting from a series of parameters.  Users don’t have to have in-depth knowledge of the data and instead can concentrate on analyzing results, rather than building queries. 

When a user calls an ad hoc form, the data source is already selected and the parameters keep all of the data retrieval in check.  See Screen 1 for an example that shows a simple ad hoc form that offers the user a choice of three dimensions and three measures to retrieve data about coffee sales. 

Another key benefit of using the guided ad hoc method is that each ad hoc form or web page is in a sense its own mini application. There’s no limit to what can be created for the user and these forms can be as simple or as complex as needed. So, if you have users that refuse to leave the confines of Excel for BI and need a little helping hand when it comes to accessing data and creating queries, then consider the unique approach of WebFOCUS Quick Data’s guided ad hoc capabilities.