Focal Point - "Right-Click Export to Excel" Builds Web Queries

	Give us feedback
	Register your copy of Developer Studio
	Developer Studio Workshop

"Right Click Export to Excel" builds Web Queries
By Urszula Politowicz , Accounting Supervisor, and Susannah Jones, Information Architect at Elie Tahari, Ltd.

A very special and exploitable relationship exists between an html page and Excel XP. The Finance Group in the Millburn, NJ, warehouse of fashion design firm, Elie Tahari , Ltd, exploits this feature, and discovered a way to save 12 manhours every day.

Our WebFOCUS BI site , called InSeam, is Microsoft thru and thru. (a self-serve site running on a win2k server calling version 5.2.5 via isapi)

Here’s what we do:
…Position the cursor on an html table ,
…Right Click, a menu opens
…Choose “Export to Excel”
(figure 1)
…An Excel window opens, separately, and then populates itself with your table. (figure 2)

figure 1

So nice. This feature is called RCXX, ‘right click export to excel’; And you can have as many excel sheets open at once as you wish.

There’s more…

But first, how do we guarantee that this export works?

--The launch page in the self-serve site must be summoning WebFOCUS with METHOD = GET. Post won’t work. We use isapi for our WebFOCUS engine, (we’re Microsoft to the core), but it works with servlet as well, as long as method is specified as GET. Our understanding is that servlet’s method is post , by default.

-- All our users are in XP, using IE6.

-- Alas, no pictures. This export method trips over image tags.

-- And also, there seems to be a recordlimit. Over 830 records, and this export truncates. (And remember that ACROSS statements add to the virtual record count.)

We don’t know why*, but we’ll live with it, because here comes the good part ….

The WebQuery

When you use RCXX, the web query goes along with the export! That means you can save your resulting worksheet, sign off, go home, come back and open just the spreadsheet, and click excel’s “Refresh Data” (the little red exclamation mark ! . see figure 2 ) and the spreadsheet runs your WebFOCUS query and brings back this morning’s new data. That’s pretty swell!

figure 2

Webqueries aren’t new…you’ve been building them into excel sheets for years. But what rocks with WebFOCUS is that your user can build his/her own webquery … with no pain … without even knowing it … with just a right mouse click.

The location of the WebFOCUS engine, the fexname and all the selected parameters have been stored as a webquery right in the saved excel document, just like a drill-down. You can examine this webquery by clicking on Data/Import External Data/Edit Query .

Ula and her Finance Group colleagues in Elie Tahari ’s Millburn , NJ , facility have built a complex and crafty Excel workbook to perform their daily financial analysis, and the source data page at the back of the workbook is fed entirely by a refreshable webquery to WebFOCUS .

Range names are created automatically. Your lookup and formula-intensive front pages can reference the entire area populated by this webquery with the rangename “ExternalData_1”. This “ExternalData_1” will contain headers , footers, the entire page. But even better, your user can highlight just the desired data area in the webquery page, give it a rangename, save the sheet, and that given rangename remains when the data get refreshed. Your formulae in your other pages can be more efficient, referencing your own rangenames..

3-in-1

Ula’s colleague, Lia Ramirez, figured out how to store three separate WebFOCUS webqueries in one sheet. Here’s how:

... RCXX your first table, save the sheet. Close it.

... RCXX your second table, save the sheet. Close it.

... continue till done.

... close Excel.

Next, open all 3 sheets again.

Either, move the tabs into 1 workbook with 3 sheets…
Then refresh each page separately

….or….

Copy and paste the table from sheet 2 in workbook 2 onto sheet 1 in workbook 1.

Repeat for Sheet 3. Now workbook 1 will contain all 3 webqueries.

To refresh all 3, highlight the entire page, and click the ! Range names are now “ExternalData_1”, “ExternalData_2”, and “ExternalData_3”, and user-created rangenames continue to be respected, as well.

A 12-hour task reduced to 12 seconds

Sue, the developer, likes this method because it removes all tedium, but leaves complete ownership of the analytical product with the user group. All users, any user, can create a WebFOCUS-fed analytical product, anytime they want, all by themselves.

Ula, the customer, likes this method because a task which required 12 man hours each day is now done with 4 mouse clicks, one to open, one to refresh, one to save, an one to email. If you, dear reader, have thoughts*, questions, or updates, write to us at inseam@elietahari.com.

System Details:
Production:Win2k WF version 5.2.5
Tested: Win2003, WF version 5.3.3
Server call: webapi.dll, with method=get in the launch page.
Customers: Excel xp.
Cautions: large data sets and/or across statements; Be careful to check the data; The technique may appear to work, but on large data sets, the result may be incomplete.
Prohibitions: image tags; servlet w/ method=post;
Enable Export to Excel in Windows: If your RCXX feature is not enabled, you need to run a .reg program. You can copy and paste this code in a text file with a .reg extension:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\MenuExt\E&xport to Microsoft Excel]
@="res://C:\\PROGRA~1\\MICROS~2\\Office10\\EXCEL.EXE/3000"
"Contexts"=dword:00000001