Jim
Calle Hedberg wrote:One of our applications - a "Web Pivot Reporter" that functionally mimicks Excel Pivot tables in a browser environments
Very interesting. Can you tell us more about your implemention of it and how/where it is
used now that your people have had more time to learn it? What does it look like? Do you
add a special row or column for the pivoting or href links from the individual data
values, or something else? Is the logic all server-side or is there extensive javascript?
It was originally designed for one of the largest insurance companies in SA, and they use it extensively. HISP put in some additional development resources (about USD 50,000 or so) & thus turned it into a FOSS app. It's mainly running on ASP/SQL Server at the moment, but I would like to see it ported to java at some stage - we are gradually moving from an MS environment to a java environment with DBMS independence. We've also tested it on MySQL 4.1 (it used embedded SQL extensively), and it seems to be running fine (couple of smaller issues to iron out).
Almost ANY structured data base can be set up as a data source for it - the data source definition is just an XML file. (I don't think this list accepts attachments, but I'll send you a sample xml file and some sample output directly).
The internal engine quite complex, with a mix of server-side and client-side logic.
Simplistically, the conceptual approach for users is as follows:
- User selects a data "Package" from a set of packages defined in the XML data source
- Each "package" can contain one or more databases
- each database contains various components
- user can combine components (point and click)
- the resulting "list" format output can be pivoted using an Excel Pivot like interface
- Final pivot report is flexible (expand fields etc)
Pivot reports can then be displayed (OK), printed (problem), exported to Excel (OK), or "published" to an integrated web portal where it can be made available to all other users with the right access privileges.
The main problem we've had has been related to the printing - SA health managers demand paper reports, period. We're still some years away from broadband & wireless access everywhere etc....
regards calle
- was initially rejected by many users due to messed up lay-out as soon as the dynamically created "pivot tables" extended over several pages. Their primary requirement was that reports should be easy to print, since they in general prefer to deal with information on paper and not on screen.
Some users certainly prefer paper, but that is fading as on-screen display of data becomes
more conveniently accessible and richly interactive and as users become more accustomed to it.
I presume many others have faced similar problems - anybody know of FOSS
solutions that are simple and robust without requiring a lot of fiddling by
users?
Our dynamically generated tables all have a page size limit (user controllable) that
determines the maximum number of rows of data to be displayed per page and a continuation
link as needed to view the next page. In addition to providing an easy means for users to
avoid page overflow when printing, this helps to prevent long delays and wasted processing
in delivering potentially huge tables that users might not really want.
A simple trick that might help indirectly is to optionally change the content-type of a
generated table from text/html to something associated with your user's spreadsheet
application. For instance, we have found that for Excel users, changing the content-type
of HTML tables to application/msexcel causes them to be opened as spreadsheets directly in
Excel.
--------------------------------------- Jim Self Systems Architect, Lead Developer VMTH Computer Services, UC Davis (http://www.vmth.ucdavis.edu/us/jaself)
