Interactive Sub-Summaries In Database Applications

TECHNICAL FIELD

p-0002This disclosure relates generally to database applications.

BACKGROUND

p-0003Many modern database tools integrate a database engine with a graphical user interface (GUI). In the GUI, the content of the database is formatted and displayed according to a layout. To modify the displayed content in conventional database tools, a user either updates the content in the database using a database editing tool or updates the layout using a layout editor. The content is formatted again using the updated data and updated layout. When multiple users use the same layout, one user's update in the layout is propagated to all users. Therefore, the conventional database tools are not suitable when a user intends to experiment with various layouts without affecting other users.

SUMMARY

p-0004In some implementations, a system displays database content formatted using a cached layout. Unlike conventional database tools, a user can modify data and a display layout directly on the display without using components such as a database editor or a layout editor. The modifications of the data and the layout can be stored locally. Therefore, a user can make layout changes without propagating the changes to other users. The changes can be reflected immediately on the display, without the user having to run another database query or perform a complete reformat the data. The system can create, update, and delete sub-summary data fields interactively in response to the user's changes.

p-0005In some implementations, a system displays content from a database in a formatted view. The formatted view includes one or more data fields arranged in one or more data sections. Each of the data sections includes a sub-summary display field. The system receives a request to modify data in one of the data fields. In response, the system reformats a displayed portion of the formatted view. The reformatting includes moving the data field from a first data section to a second data section and updating the sub-summary display field in the first data section and the sub-summary display field in the second data section. The system displays the reformatted view.

p-0006In some implementations, a system displays database content in a formatted view. The formatted view is formatted based on a main layout. The formatted view includes one or more data fields. Upon receiving a request to add a new data field, the system updates a cached layout associated with the formatted view based on the request. Updating the cached layout includes creating a new layout node in the cached layout to represent the new data field; identifying layout nodes that are relevant to the new data field from the main layout; and copying the identified layout nodes from the main layout to the cached layout. The system reformats the formatted view according to the cached layout.

p-0007The details of one or more implementations of interactive sub-summaries are set forth in the accompanying drawings and the description below. Other features, aspects, and advantages of interactive sub-summaries will become apparent from the description, the drawings, and the claims.

BRIEF DESCRIPTION OF THE DRAWINGS

p-0008<figref idrefs="DRAWINGS">FIGS. 1A and 1B</figref> are flowcharts illustrating an exemplary process for modifying data in a conventional database reporting tool and an exemplary process using interactive sub-summary techniques, respectively.

p-0009<figref idrefs="DRAWINGS">FIGS. 2A and 2B</figref> illustrate an exemplary implementation of interactive sub-summary techniques.

p-0010<figref idrefs="DRAWINGS">FIGS. 3A and 3B</figref> are flowcharts illustrating exemplary processes for displaying database content using interactive sub-summary techniques.

p-0011<figref idrefs="DRAWINGS">FIGS. 4A and 4B</figref> illustrate exemplary layouts for formatting display views as used in interactive sub-summaries.

p-0012<figref idrefs="DRAWINGS">FIG. 4C</figref> is a flowchart illustrating an exemplary process for adding a new data display field using interactive sub-summary techniques.

p-0013<figref idrefs="DRAWINGS">FIGS. 5A and 5B</figref> illustrate an exemplary implementation of a scrollable layout that contains scrollable interactive sub-summary parts.

p-0014<figref idrefs="DRAWINGS">FIG. 6</figref> illustrates an example computer system that performs an enhanced compilation as described in reference to <figref idrefs="DRAWINGS">FIGS. 1-5</figref>.

p-0015Like reference symbols in the various drawings indicate like elements.

DETAILED DESCRIPTION

Interactive Sub-Summary Overview

p-0016<figref idrefs="DRAWINGS">FIGS. 1A and 1B</figref> are flowcharts illustrating an exemplary process for modifying data in a conventional database reporting tool and an exemplary process using interactive sub-summary techniques. For convenience, the exemplary process will be described with respect to a system that performs the techniques, and a user using a database application program.

p-0017<figref idrefs="DRAWINGS">FIG. 1A</figref> is a flowchart illustrating an exemplary process <b>100</b> for modifying data in a conventional database reporting tool. Modern database applications permit a user to view formatted data through reporting tools instead of directly accessing raw data stored in the database. In a conventional database application program, a software component for generating formatted display (e.g., a database reporting tool) is separate from a software component for modifying data (e.g., a database editing tool). The separation of the reporting tool and forms tool is logical because a user of the database reporting tool (e.g., an analyst or a manager) can be different from a user of the database editing tool (e.g., a customer service representative). Various users can have various access privileges (e.g., an analyst or a manager is not permitted to modify data, but a customer service representative can). Therefore, the reporting tool can be incapable of changing data, and the editing too can be incapable of viewing formatted displays.

p-0018However, the user sometimes needs to modify the data that the user is viewing, e.g., to correct a particular number while viewing the formatted data in a display view in a database reporting tool. In a conventional database application program, the user is required to access <b>110</b> the database editing tool, which can be an application program that is different from the application program that the user is viewing. The conventional database application needs to authenticate the user and determine that the user has sufficient privilege to edit the database. If the user does not have sufficient privilege to access the database editing functions, the user cannot continue. After the conventional database application authenticates the user and determines that the user has sufficient privilege to edit the database, the user can modify <b>120</b> database content in the database editing tool. After modifying the database content, the user returns to the database reporting tool, re-runs the user's database queries to retrieve the updated content, and uses the database reporting tool to format <b>130</b> the updated content. The conventional database reporting tool includes a main layout for the database application the user is running. The user can further edit the main layout to change the way the database content is displayed. The updated content is formatted according to the edited main layout. As a result, the conventional database application generates <b>140</b> a new display view. The user can repeat the process to further edit the database content or edit the main layout. In the conventional process <b>100</b>, the user's data editing is committed to the database. The user's modification of the main layout changes the main layout. The changes made by the user are therefore propagated to other database applications and other users that access the same content or use the same layout.

p-0019<figref idrefs="DRAWINGS">FIG. 1B</figref> is a flowchart illustrating an exemplary process <b>150</b> for modifying data using interactive sub-summary techniques. The user needs to modify the data that the user is viewing in a database reporting tool without leaving the reporting tool and accessing a database editing tool. For example, the user needs to correct a particular number, while viewing the formatted data in a display view in the database reporting tool. Instead of accessing a separate database editing program, the user edits <b>160</b> the data directly in a database report displayed in the reporting tool. The edited data are pushed back into the database. The database reporting tool formats <b>170</b> the edited data using a display layout. A displayed portion of the database report is formatted to reflect changes associated with the edited data (e.g., a record is added to or removed from the displayed portion). The display layout can be selected and copied from a main layout. The user can further edit the display layout to change the way the edited data are displayed. The system generates a new display view on the edited data.

Example Process for Modifying Data in a Data Field

p-0020<figref idrefs="DRAWINGS">FIGS. 2A and 2B</figref> illustrate an exemplary implementation of interactive sub-summary techniques. <figref idrefs="DRAWINGS">FIG. 2A</figref> illustrates an example table view <b>200</b> of an example database application program in which data can be edited using interactive sub-summary techniques. Table view <b>200</b> is a display view in a database application program. The table view <b>210</b> has columns “Region,” “Sales Rep,” and “Sales.” In those columns, the table view <b>200</b> displays a number of sales (“Sales”) for each sales representative (“Sales Rep”), grouped by region. The example table view <b>200</b> displays data retrieved from the following example database table “sales”:

p-0021<tables id="TABLE-US-00001" num="00001"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="217pt" align="center" /><thead><row><entry namest="1" nameend="1" rowsep="1">TABLE 1</entry></row></thead><tbody valign="top"><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row><row><entry>sales</entry></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="4"><colspec colname="offset" colwidth="14pt" align="left" /><colspec colname="1" colwidth="42pt" align="left" /><colspec colname="2" colwidth="133pt" align="left" /><colspec colname="3" colwidth="28pt" align="left" /><tbody valign="top"><row><entry /><entry>FIELD</entry><entry>TYPE</entry><entry>KEY</entry></row><row><entry /><entry namest="offset" nameend="3" align="center" rowsep="1" /></row><row><entry /><entry>row_id</entry><entry>INT NOT NULL AUTO_INCREMENT</entry><entry>YES</entry></row><row><entry /><entry>region</entry><entry>VARCHAR(100)</entry></row><row><entry /><entry>sales_rep</entry><entry>VARCHAR(100)</entry></row><row><entry /><entry>num_sales</entry><entry>INT(11)</entry></row><row><entry /><entry namest="offset" nameend="3" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

p-0022In example table view <b>200</b>, a sub-summary is displayed for each region. A sub-summary can be a summary field that summarizes a particular data column based on a particular grouping. For example, sub-summary field <b>204</b>, which shows a value “600,” is a sub-summary representing a summation on the column “num_sales” and based on a grouping by region “A.” Similarly, sub-summary field <b>206</b> summarizes total sales for region “B.” In various implementations, the sub-summary field can be created by a database statement. For example, a Structured Query Language (“SQL”) statement “SELECT SUM(num_sales) FROM sales GROUP BY region;” can be used. The sub-summary field can also be created by parsing the database result set returned from the database in response to a query or parsing cached database result sets. In some implementations, a sub-summary field can also be a field that represents an average, a median, a minimum, a maximum, or other calculations on the data column.

p-0023A user can edit a field <b>202</b> directly in the database reporting tool. For example, sales representative “Carl” is in region group “A.” The user can change the region of “Carl” from region “A” to region “B.” In some implementations, the changes are stored in a data cache area. The saved changes can be committed to the database upon a user request (e.g., by pressing a button that invokes a “commit” of the data). In some implementations, the changes are immediately committed to the database without further user interference. If committing the changes to the database is unsuccessful (e.g., when the data field is a result of a calculation that cannot be modified, when database field is protected, or when the user does not have privilege to update the database), an error message can be displayed.

p-0024<figref idrefs="DRAWINGS">FIG. 2B</figref> illustrates an example table view <b>210</b> of the example database application program in which data have been edited using interactive sub-summary techniques. Similar to the table view <b>200</b> in <figref idrefs="DRAWINGS">FIG. 2A</figref>, the table view <b>210</b> includes columns “Region,” “Sales Rep,” and “Sales.” However, because sales representative “Carl” is now in region “B” according to the cached data, the record <b>212</b> of “Carl” is moved from region group “A” into region group “B.” The move can be accomplished by applying the layout to the cached data. Also, the sub-summary fields <b>214</b> for region group “A” and <b>216</b> for region group “B” have been updated to reflect the fact that the sales number for “Carl” now counts for region group “B” rather than region group “A.”

p-0025The example display views <b>200</b> and <b>210</b> in <figref idrefs="DRAWINGS">FIGS. 2A and 2B</figref> are table views. In a table view, the data from the database are displayed in tabular format, e.g., with rows and columns. In various implementations, a displayed view can be a list view, a browser view, or other forms of view. A list view can differ from a table view in that, for example, the data need not be organized in a tabular format, and the regions “A,” “B,” and “C” need not be repeated for each sales representative. In some implementations, where object-oriented technologies are employed, a table view class can be a subclass of a list view class.

p-0026<figref idrefs="DRAWINGS">FIGS. 3A and 3B</figref> are flowcharts illustrating exemplary processes <b>300</b> and <b>350</b> for displaying database content using interactive sub-summary techniques. For convenience, the exemplary processes <b>300</b> and <b>350</b> will be described with respect to a system that performs operations in the processes <b>300</b> and <b>350</b>.

p-0027<figref idrefs="DRAWINGS">FIG. 3A</figref> is a flowchart illustrating an exemplary process <b>300</b> for displaying database content using interactive sub-summary techniques. In step <b>300</b>, the system displays content from a database in a formatted view. The formatted view can be a table view, a list view, or another form of view that contains one or more data fields arranged in data columns in one or more data sections (e.g., groups). Each of the data sections can have one or more sub-summary display fields. Each of the sub-summary display fields can display a summary of a particular data column (e.g., amount of sales, number of sales) that is grouped by a group field (e.g., region). In some implementations, the group field can be a field that is used in the “GROUP BY” clause of a SQL statement. The group field can also be a field on which the database content is sorted.

p-0028In some implementations, more than one data columns can be used as multi-level group fields, separately or in combination. For example, a database table can include country data (e.g., “US,” “Canada,” etc.), state data (e.g., “California,” “British Columbia,” etc.), in addition to the region data (e.g., “A,” “B,” etc.). Sub-summaries can be calculated at each level of grouping. For example, a first level sub-summary can be calculated for each region, a second level sub-summary can be calculated for each state, and a third level sub-summary can be calculated for each country.

p-0029In some implementations, the more than one group fields can be used in conjunction as a single level group field. For example, a database table can contain data on sales representatives, regions of the sales representatives, and managers of the sales representatives. The data columns “region” and “manager” can be combined to form a single group field (e.g., by a SQL clause “GROUP BY region, manager”). A sub-summary can be calculated for each of the groups grouped by “region, manager.”

p-0030In step <b>320</b>, the system receives a first request to modify data in a data field in the displayed data fields. In some implementations, the request to modify data is a user clicking on a data field. For example, referring to <figref idrefs="DRAWINGS">FIG. 2A</figref>, the data column “Region” displays three data fields of “As,” two data fields of “Bs,” and two data fields of “Cs.” The user can click on the data field <b>202</b> to edit the data in the data field <b>202</b>.

p-0031In step <b>325</b>, upon receiving the user's editing, the system can commit the edited data into the database. In various implementations, the database reporting tool sends data manipulating statements through a database interface to retrieve data. A database interface can include database drivers that contain specific details with regard to the database. Examples of database interface are database-specific Application Programming Interface (“API”) (e.g., TransactSQL), language-specific API (e.g., Java Database Connectivity (“JDBC”)), and standard procedural API (e.g., Open Database Connectivity (“ODBC”)). The retrieved data can be organized in various data structures as results sets. In some implementations, JDBC is used. In the example code below, a query is executed to update the database.

p-0032<tables id="TABLE-US-00002" num="00002"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="3"><colspec colname="offset" colwidth="35pt" align="left" /><colspec colname="1" colwidth="154pt" align="left" /><colspec colname="2" colwidth="28pt" align="right" /><thead><row><entry /><entry namest="offset" nameend="2" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /><entry>Statement stmt = con.createStatement( );</entry><entry /></row><row><entry /><entry>stmt.executeQuery(“UPDATE sales SET region=’B’</entry></row><row><entry /><entry>       WHERE row_id=3”);</entry><entry>(1)</entry></row><row><entry /><entry namest="offset" nameend="2" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

p-0033In the example code (1), a “row_id” field is used to identify the row in the database that needs to be updated. In various implementations, a key data field (e.g., a data field whose value can be used to uniquely identify a row in a database table) is tracked and associated with the displayed rows in the formatted view (e.g., table view <b>200</b>), although the key data field itself may not be displayed. Also in example code (1), a “region” data field is updated. The system can identify which data field to update based on a corresponding display field in the display view. For example, a “Region” column in table view <b>200</b> corresponds to the “region” data field in the database table. The correspondence between a data field and a display field can be determined by the layout.

p-0034In step <b>330</b>, the system reformats the formatted view (e.g., table view <b>200</b>). In various implementations, reformatting the formatted view can include moving the edited data field from an original group section to a target group section, and updating the sub-summary display field of the original group section and the target group section (e.g., as in table view <b>210</b>).

p-0035To reformat the formatted view, the system applies a layout to the edited data. When the sub-summary fields are live and interactive (e.g., can change based on user editing), it is possible that the sub-summary field moves during the live interaction (e.g., in views <b>200</b> and <b>210</b>, sub-summary <b>204</b> for region “A” moves from a position in the fourth place in the body of the table in view <b>200</b> to a position in the third place in view <b>210</b>). The move can be determined by the layout. In database reporting tools, a formatter program can apply a layout to the data retrieved from the database and place certain data fields in certain places in certain format. For example, the layout can determine that the data are displayed in a size 12 font, in table cells that is 40 pixels high and 100 pixels wide, etc. In addition, a layout can determine the relative positions of various fields, e.g., where a sub-summary field is displayed on a screen.

p-0036Moving the edited data field from an original data section to a target data section can include creating a new data section. For example, in editing table view <b>200</b>, a user can change the data in data field <b>202</b> from “A” to “D.” Table view <b>200</b> does not include a region “D.” Therefore, the system can create a separate data section “D” in table view <b>210</b> to accommodate the edited data. The system can put the newly created data section in a proper place among the existing data sections by re-sorting the data sections based on the new data value in the edited data field if the original data sections were sorted on the data column of the edited data field. A new sub-summary field can also be created for the new data section.

p-0037Moving a data field from an original data section to a target data section can include deleting an entire data section. For example, in editing table view <b>200</b>, a user can change data in all “Region” data fields in section “A” from “A” to “B.” In response to the change, the system removes data section for region “A” after deleting all records from the region “A” section and inserting the records into the region “B” section. In some implementations, the system identifies the original data section in which the data field being edited belongs. After editing, the system adds the edited data field to a target data section (which can be a new data section). The system examines the original data section. If there are no more records (e.g., data fields) in the original data section, the original data section is removed. The sub-summary section for the original data section is also removed.

p-0038A conventional database reporting tool can have a main layout for a database reporting application, where the main layout applies to all reports. The conventional database reporting tool maintains a main layout and applies the main layout to reformat the formatted view. In some implementations of interactive sub-summary techniques, in addition to a main layout (which can be shared by many users), the system maintains a cached layout (which can be stored locally). The cached layout is copied from the main layout. The system can reformat the formatted view using the cached layout. More details on the cached layout will be described below with respect to <figref idrefs="DRAWINGS">FIG. 3B</figref>.

p-0039In some implementations, reformat the formatted view includes reformatting a viewable area of the formatted view instead of re-generating the entire formatted view. Unlike a traditional database reporting tool that regenerates a database report in its entirety, the system can reformat only a relevant portion of the report (e.g., the viewable area). The system can determine which area is viewable by calculating a height variance of a current view. For example, if a user is viewing a portion (e.g., 20 rows) in a large (e.g., an 8,000-row) report, the user's changing a value can cause a particular row to be moved from the viewable portion to another area of the report, and/or a regeneration of the sub-summary data fields. The system can determine which rows and which sub-summary data fields to display on screen by shifting the rows already displayed up or down, instead of regenerating an entirely new report. More details on formatting a display area of the formatted report is described below with respect to <figref idrefs="DRAWINGS">FIGS. 5A and 5B</figref>.

p-0040In step <b>340</b>, the system displays the reformatted view on a display device. A user can perform further editing on the reformatted view.

Example Process for Modifying a Layout

p-0041<figref idrefs="DRAWINGS">FIG. 3B</figref> is a flow chart illustrating an example process for modifying a layout in a display view. To change the display format (e.g., enlarging table cells or changing cell positions), conventional database reporting tools require a user to edit the main layout. After the layout is edited, the data are reformatted based on the edited main layout. Interactive sub-summary techniques permit a user to edit a cached layout of a display view “on-the-fly,” without editing the main layout.

p-0042In step <b>352</b>, the system receives a request to position a data field (e.g., a sub-summary display field) in a display view. In various implementations, the request can be in the forms of a mouse click on a display button (e.g., “underline text in all selected data fields”), a drag-and-drop of a data field (e.g., a total summary field) or data column (e.g., the column header “Sales” of table view <b>200</b>), or a command from a “modify view” dialog box, etc. The request can be re-ordering data in data columns (e.g., change a sorting “from high to low” into “from low to high”) or re-ordering the data columns themselves. For example, in table view <b>200</b>, a user can click on the “Sales” data column which is displayed on the right side of data column “Sales Rep,” drag the “Sales” data column to the left side of the data column “Sales Rep,” and drops it there. The “Sales” data column now is displayed in the display view on the left of the data column “Sales Rep.”

p-0043The system can also reposition a label (e.g., a title of a table displayed at the top of the table view), a summary field (e.g., a total sales field at the bottom of the table view), or a sub-summary field by drag-and-drop. If the field being repositioned is a sub-summary field, the system can limit the place where the sub-summary field can be dropped.

p-0044In step <b>354</b>, the system identifies a data field of which a sub-summary display field being repositioned is a sum. In a display view, sub-summary sections can be tied to the data fields that they summarize. For example, in table view <b>200</b>, a user can drag sub-summary field <b>204</b> and drop it to another place in the table view. The sub-summary field <b>204</b> is a sum in the “Sales” data column on three data fields (the sales numbers of “Adam,” “Betty,” and “Carl”).

p-0045In step <b>356</b>, regardless of where the user drops the sub-summary field, the system positions the sub-summary display field above or below the identified data field vertically, and at the same position as the identified data field horizontally. In some implementations, the system positions the sub-summary display fields in the same data column as the data fields the sub-summary display fields summarize. Such positioning can avoid user confusion on what data the sub-summary display fields represent. For example, in table views <b>200</b> and <b>210</b>, sub-summary display fields <b>202</b>, <b>206</b>, <b>214</b>, and <b>216</b> represent the sums of number of sales. If the table views <b>200</b> and <b>210</b> each contains an additional data column “amount of sales” and the user is allowed to position the sub-summary display fields in the data column “amount of sales,” the user may later be confused because the sub-summary display fields represent the sums of “number of sales” and do not represent the sums of “amount of sales.” Therefore, the system can automatically move the sub-summary display fields back to the data column that represents “number of sales.”

p-0046Within a data column, the sub-summary display field can be positioned above or below the data fields that the sub-summary display field summarizes. A sub-summary display field that is placed above the data fields can be designated as a leading sub-summary display field. A sub-summary display field that is placed below the data fields can be designated as a trailing sub-summary display field. For example, in table view <b>200</b>, a user can drag and drop the sub-summary display field <b>204</b> to a position above record <b>201</b> (“A,” “Adam,” and “100”). The sub-summary display fields for number of sales can then be placed above the number of sales data fields for each sales rep. In various implementations, when a user drops the sub-summary display field into the repeating data fields (e.g., below record <b>201</b> but above data field <b>202</b>), the system can alternatively ignore the drop, reposition the sub-summary display fields, or determine whether to reposition the sub-summary display fields based on distances between the drop position and the leading or trailing positions.

p-0047In various implementations, the system can position the sub-summary display fields in manners similar to the techniques described above regardless of which particular instance of sub-summary display field the user attempts to reposition. Repositioning a single sub-summary display field can cause all related sub-summary fields to be repositioned. For example, in table view <b>200</b>, the system can reposition the sub-summary display fields on “Sales” regardless whether the user requests to move sub-summary display field <b>204</b> or sub-summary display field <b>206</b>.

p-0048<figref idrefs="DRAWINGS">FIGS. 4A and 4B</figref> illustrate exemplary layouts for formatting display views as used in interactive sub-summaries. In various implementations, the system maintains a cached layout to implement the process described above with respect to <figref idrefs="DRAWINGS">FIG. 3B</figref>. The repositioning of data fields and sub-summary display fields on the display view can be accomplished by modifying the cached layout that is generated from the main layout and is distinct from the main layout. Therefore, a user can adjust the display view without affecting other users using the same main layout.

p-0049<figref idrefs="DRAWINGS">FIG. 4A</figref> illustrates an exemplary main layout <b>400</b>. A user can go to a layout editor program to add display fields, including leading sub-summary display fields and trailing sub-summary display fields. The example main layout <b>400</b> contains various parts. For example, the main layout <b>400</b> can contain a leading grand summary part <b>402</b>, a leading sub-summary part <b>404</b>, field definition parts <b>405</b>, <b>406</b>, and <b>408</b>, a body part <b>410</b>, and a trailing sub-summary part <b>412</b>. The main layout can also contain various boilerplate fields (e.g., text that is not retrieved from a database). The body part <b>410</b> is a place where data fields repeat to reflect the multiple records in a database table. Each repetition of body part <b>410</b> can be an instance of the body part <b>410</b>. Similarly, the sub-summary parts <b>404</b> and <b>412</b> can have multiple instances. A table view can be generated when the main layout is saved.

p-0050<figref idrefs="DRAWINGS">FIG. 4B</figref> illustrates an exemplary table layout <b>420</b> for a table view. A table view can include a hidden table layout <b>420</b>, which caches the various parts in the main layout. In the table layout <b>420</b>, data fields are arranged in a table format. The field definition parts <b>405</b>, <b>406</b>, and <b>408</b> can define columns “Region,” “Name,” and “Sales,” arranged in columnar format in the table layout <b>420</b>. Leading sub-summary part <b>424</b>, body part <b>430</b>, and trailing sub-summary part <b>412</b> are arranged in a tabular format. Both the main layout and the cached layout can be stored on a storage device in a tree structure, in which various parts of the data display fields are represented by nodes in the tree.

p-0051To generate the table layout <b>420</b>, the system copies parts from the main layout to the table layout. Parts are copied in an order in which the parts appear on the main layout. For example, leading sub-summary part <b>404</b> can be copied first, then the body part <b>410</b>, and then the trailing sub-summary part <b>412</b>. The system can process the leading sub-summary part <b>404</b> and the trailing sub-summary part <b>412</b> to confirm that sub-summary data fields are placed in the sub-summary parts. The system can ensure that a sub-summary data field (e.g., “SUM(num_sales)”) is placed in one of the sub-summary parts, and that non-summary data fields (e.g., “name”) are placed in other parts of the layout.

p-0052After determining that the sub-summary parts contain sub-summary data fields and sub-summary data fields are in sub-summary parts, the system can also examine whether the data fields being summarized are in the body part <b>410</b>. If the data fields being summarized are in the body part <b>410</b>, the system can acquire the coordinates of the data field. The system can use the coordinates to place the sub-summary display field. For example, if a data field in the body part <b>410</b> has (x, y) coordinates, the system can place the sub-summary display field that summarizes the data field at horizontal coordinate x and vertically at the top of the body part <b>410</b> so that the sub-summary display field aligns with the data fields in the body part <b>410</b>. If the sub-summary part is a leading sub-summary part <b>404</b>, the sub-summary display field can be placed at the bottom of the leading sub-summary part <b>404</b>. If the sub-summary part is a trailing sub-summary part <b>412</b>, the sub-summary display field can be placed at the top of the trailing sub-summary part <b>412</b>.

p-0053A user can have more than one level of sub-summary display fields. For example, the user can have a level of sub-summary display fields for regions and a level of sub-summary display fields for states. In such cases, the system can expand the sub-summary parts and stack the sub-summary fields one on top of another in the table view <b>420</b>.

p-0054When a user edits a display view, the hidden table layout <b>420</b> is modified. For example, when the user resizes or reorders data columns in the display view, the sizes and positions of the columns in the table layout <b>420</b> changes. The coordinates of the sub-summary display fields can move with the data columns that have been moved.

p-0055An example table layout <b>420</b> is shown in <figref idrefs="DRAWINGS">FIG. 4B</figref>. The table layout can correspond to a table view. Other forms of layouts, such as a list layout that can correspond to a list view, can be implemented similarly.

p-0056<figref idrefs="DRAWINGS">FIG. 4C</figref> is a flowchart illustrating an exemplary process <b>450</b> for adding a new data display field using interactive sub-summary techniques. A user can create a sub-summary part <b>404</b> that contains a sub-summary field in a main layout <b>400</b> without including a corresponding data field that the sub-summary field summarizes in the body part <b>410</b>. For example, the user can have a leading sub-summary part <b>404</b> containing a sub-summary data field that summarizes the number of sales. The user can leave out the number of sales column in the field definitions. In some implementations, the system hides the sub-summary display field in the display view if the underlying data of the sub-summary display field are not displayed. Hiding the sub-summary display field can include not copying the sub-summary part <b>404</b> from the main view <b>400</b> to the table view <b>420</b>. However, because the display view is interactive, a user can add a data column while viewing the display view formatted using the main layout <b>400</b> and table layout <b>420</b>. If the added data column contains the underlying data of the sub-summary data field, the system can adjust the table layout <b>420</b> and display the formerly hidden sub-summary display field.

p-0057In step <b>455</b>, the system displays database content in a formatted view based on a main layout (e.g., <b>400</b>). The formatted view can be a table view containing a cached layout (e.g., a hidden table layout <b>420</b>). The cached table layout <b>420</b> can be generated from the main layout <b>400</b>.

p-0058In step <b>460</b>, the system receives a request to add a new data field. The request can be received through a display view modification interface (e.g., a “modify table view” dialog box and an “add fields” dialog box). Adding a new data field can result in a new data column being added to the display view. In some implementations, the system permits the user to add a data field (e.g., “num_sales”) that exists in a database table from which the database content is retrieved. If a user attempts to add a new data field that is not in a database table, the system permits the user to add the new data field after automatically or manually editing the cached data by creating a corresponding data field. In addition to adding a data field in the database or the cache, the user can also add a sub-summary field or a grand summary field.

p-0059In step <b>465</b>, the system updates the cached layout <b>420</b> associated with the formatted view based on the request. The system can create one or more new layout nodes in the cached layout <b>420</b> to represent the new data fields and the new data column. The new layout nodes can contain coordinates of the new data fields. In some implementations, the coordinates of the new layout nodes are determined by coordinates of existing layout nodes. For example, if the user adds a new sub-summary field for data column “Sales” in the display view, the new node in the table layout <b>420</b> representing the added sub-summary field can be determined by the coordinates of the data fields representing “num_sales” in the cached table layout.

p-0060In some implementations, the system identifies layout nodes that are relevant to the new data field from the main layout <b>400</b> and copies the identified layout nodes from the main layout <b>400</b> to the cached layout <b>420</b>. Copying the nodes can happen when, in addition to the user added data fields, the system needs to display otherwise undisplayed data fields from the main layout <b>400</b> based on the addition. For example, a main layout <b>400</b> can include a sub-summary part that displays sub-summaries on “number of sales.” In some implementations, the sub-summaries on number of sales are not copied to the cached table layout originally, because the sub-summaries on an undisplayed field are not displayed (e.g., when a user removes the “number of sales” column from a display view, causing a regeneration of the cached layout <b>420</b>). However, after the user adds the data field that displays number of sales back into the display view, the sub-summaries on number of sales can be displayed. Therefore, after the user adds the display field, the system reads the main layout and determines which data fields can be displayed as a result of adding the display field, and copies the corresponding layout nodes to the cached table layout <b>420</b>.

p-0061In step <b>470</b>, the system reformats the formatted view according to the updated table layout <b>420</b>. The process <b>450</b> can be repeated, allowing a user to add multiple data fields in the display view in multiple rounds of editing. Similarly, processes can be implemented to allow user to delete data fields from the display view. When a user deletes a particular data column (e.g., “Sales”), the data fields and the sub-summary fields in the data column can be removed together. The nodes representing the data field and the sub-summary field are removed from the cached table layout <b>420</b>.

p-0062In some implementations, the main layout is not modified by a user adding, editing, and deleting data fields in the displayed view. Only cached layouts are modified. Therefore, multiple users can each have a display view customized to the user's need. Each display view can have a cached layout. The editing of the display view can be contained in the cached layout.

Formatting Viewable Layout

p-0063<figref idrefs="DRAWINGS">FIGS. 5A and 5B</figref> illustrate an exemplary implementation of a scrollable layout that contains scrollable interactive sub-summary parts. In various implementations of interactive sum-summaries, a portion of a formatted view (instead of the entire database report) is re-formatted after a user modification. <figref idrefs="DRAWINGS">FIG. 5A</figref> illustrates an exemplary layout <b>500</b> that contains interactive sub-summary parts. A display view can include many rows that cannot be displayed on a single display screen all at once. In various implementations, a user can view a viewable portion of the display view, limited by the user's display screen. When a user scrolls on the screen to view various viewable portions of the display view, and when a user edits a value of a particular data field, the system can calculate which portion (e.g., which rows of data, which sub-summary fields) to be displayed on screen. Determining the portion to display can include calculating the total height of the display view and determining where the user is scrolling (e.g., where the scrollbar “grip” is), and which rows need to be displayed based on the user edit.

p-0064To calculate which rows need to be displayed, the system can calculate a new origin based on a window, a display view, and a scroll offset. The origin can be a top cursor, which is the first visible row cursor at the top of the view. Based on the top cursor, the system can determine what rows and associated sub-summaries parts are to be displayed. The system can make the determination by building a part instance list. Building a part instance list will be explained in more details below with respect to <figref idrefs="DRAWINGS">FIG. 5B</figref>.

p-0065In some implementations, a table view is a kind of a list view. The table view can be implemented using a special hidden layout that can be rendered in list view. The hidden table can be generated automatically when the layout is saved.

p-0066In a conventional, non-interactive display view, the total height of the display view is fixed because the total number of rows returned from the database (which determines the total height of the display view) is fixed when the data are retrieved from the database. After the data are retrieved, the system knows how many rows of records are to be displayed. In a display view where interactive sub-summary techniques are applied, the total height of the display view is dynamic because user modification in the data fields can cause new sub-summary fields to be created or deleted (e.g., when a user changes data in field <b>202</b> from region “A” to region “D,” a new data section and a new sub-summary field for the new region “D” is created). The number of records displayed is dependent on the content in the data (e.g., how many distinct regions there are) and user editing. Therefore, techniques for dynamically calculating the height of the display view need to be employed.

p-0067As an example, the layout <b>500</b> in <figref idrefs="DRAWINGS">FIG. 5A</figref> can be a table layout or a list layout. The layout <b>500</b> contains multiple parts, including a title header part <b>502</b>, a header part <b>504</b>, a leading grand summary part <b>506</b> (e.g., a total number of sales of all regions), a leading high-level sub-summary part <b>508</b> (e.g., number of sales of each country), a leading mid-level sub-summary part <b>510</b> (e.g., number of sales of each state), and a leading low-level sub-summary part <b>512</b> (e.g., number of sales of each region). There can be other levels of leading sub-summary parts.

p-0068The layout <b>500</b> can also include a body part <b>514</b> which contains repeating data fields (e.g., rows corresponding to the rows in the database tables), multiple levels of trailing sub-summary parts <b>516</b>, <b>518</b>, and <b>520</b>, trailing grand summary part <b>522</b>, footer <b>524</b>, and title footer <b>526</b>. Each part can contain one or more objects (e.g., boilerplate text, images, or data fields). The parts and the objects can be stored as nodes in a tree structure. The geographical information of the objects (e.g., size, position) can be stored in the nodes.

p-0069In various implementations, the system imposes various rules on the placement of objects in the parts. For example, a placement rule can specify that objects in a leading part (e.g., a leading grand summary part or a leading sub-summary part) are placed at the bottom of the part, and objects in a trailing part are placed at the top of the trailing part. This placement rule can make the objects appear close to the body part, which contains data that the object summarizes, in the final display view. A sizing rule can specify that the size of each of the leading and trailing sub-summary parts, measured by rows that represent a number of objects in the part, be one more than the number of objects in the part. An alignment rule can specify that the text in a sub-summary field be aligned using the same text alignment rule as in the field whose data the sub-summary field summarizes.

p-0070Each part in the layout <b>500</b> can have multiple instances. For example, the body part <b>514</b> can have one instance for each data record (e.g., row of data) returned from the database. The leading sub-summary part <b>512</b>, which sub-summarizes data for each region, can have one instance for each region. The instances of the parts that are viewable on a screen can be stored in a part instance list <b>550</b>, as described below with respect to <figref idrefs="DRAWINGS">FIG. 5B</figref>.

p-0071<figref idrefs="DRAWINGS">FIG. 5B</figref> illustrates an exemplary scrollable layout that contains scrollable interactive sub-summary parts. The scrollable layout in <figref idrefs="DRAWINGS">FIG. 5B</figref> allows the system to update a visible portion of a display view when a user modifies data, without reformatting the entire database report. A part instance list <b>550</b> is a list of scrollable layout parts that are in view. In part instance list <b>550</b>, each part instance can contain a part detail record, which can describe layout information of the part instance. Each part detail record can be represented individually. Each part detail record can be linked to a slide manager (“Slide Info”) through a pair key. The slide manager can determine the size and position the part instance on a display device when the part instance is displayed. The key can include a part index, which specifies to which part (e.g., leading sub-summary, body, etc.) the instance belongs, and a row key which specifies which row in the part the body instance or a sub-summary instance is in.

p-0072The system can build the part instance list <b>550</b> in various manners. In some implementations when the displayed data are not sorted, the system can iterate through the data records in the displayed data starting from the top cursor. For each record iterated, the system can create a new instance of a part instance description object, initialize the object with information of the body part <b>514</b>, and insert the part instance description object into the part instance list. The iterations continue until the total vertical space of all the added parts is greater than a predefined limit (e.g., the height of a viewing rectangle plus a max record height).

p-0073In some other implementations, the displayed data are sorted into one or more levels. Data records and information on the data records can be grouped into bins (e.g., groups of country “US,” country “Canada,” and sub-groups of region “A,” region “B,” etc.) The system can iterate through each level, starting from the top cursor and lowest level of sorting (e.g., region), and determine whether to add sub-summary parts (leading and trailing) to the part instance list <b>550</b>. The system can make the determination by examining whether the sorting level matches a sub-summary defined in the layout <b>500</b>. For example, if a currently traversed sorting level is “region” and a sub-summary field for “region” is defined in the layout <b>500</b>, a sub-summary part is inserted into the part instance list <b>550</b>. When the system reaches an end of a current traversal level, the system moves to a next higher traversal level and repeats the process. The iterations continue until the total vertical space of all the added parts is greater than a predefined limit (e.g., the height of a viewing rectangle plus a max record height).

p-0074In some implementations, when a user scrolls through the display view or edits data in the display view, the system can redraw the screen by calling a draw list view function. During a redrawing of the screen, the draw list view function can activate a part instance manager software component. The system can check the part instance manager to determine if the part instance list <b>550</b> needs to be updated. If the part instance list <b>550</b> needs to be updated, the system rebuilds the part instance list <b>550</b>. During the rebuild, the system can activate a slide manager software component for each part instance. A slide manager can contain information on the height of a part instance. Each part instance can have a distinct height. Therefore, each part instance can be associated with a distinct slide manager. The system can compute bounds for the object in the part instance using the slide manager associated with the part instance. If there is no slide manager associated with the part instance, the system can create a slide manager by instantiating a slide manager class object.

p-0075In some implementations, the slide managers are cached in a Slide Info Map <b>570</b>, separately from the part instances. To improve efficiency, the system can cache the slide manager software component instead of instantiating and destroying it each time the part instance list <b>550</b> is rebuilt. The system can store instances of the slide manager separately in the map <b>570</b> and associate each part instance in the part instance list <b>550</b> with an entry in the map <b>570</b> using the pair key. On a data change notification, the system can access the map <b>570</b> and mark the corresponding slide manager as outdated. The actual updating of the outdated slide manager can occur during a draw cycle.

p-0076To determine where the user is scrolling, the system can determine a relative position of a “grip” on a scrollbar. The grip, alternatively known as a thumb, a wiper, or a slide, is a display bar that a user can drag and move along a track in order to move to various parts of a display view. In some implementations, the length of the track in a vertical scrollbar on the display view corresponds to a scrollbar height. The scrollbar height can be determined by multiplying a number of records (e.g., number of rows returned from a database query) by a maximum record size. The maximum record size can be a sum of leading and trailing sub-summary parts defined for a given layout. When the user scrolls vertically in a table view or a list view, the system can calculate a scrollbar delta (e.g., how many records the user scrolled over according to the distance the grip has moved and the height of the scrollbar). The system can use the scrollbar delta to determine which record or records the viewable portion needs to display, and update the part instance list <b>550</b> accordingly.

Example System Architecture

p-0077<figref idrefs="DRAWINGS">FIG. 6</figref> is a block diagram of a system architecture <b>600</b> for implementing the features and operations described in reference to <figref idrefs="DRAWINGS">FIGS. 1-5</figref>. Other architectures are possible, including architectures with more or fewer components. In some implementations, the architecture <b>600</b> includes one or more processors <b>602</b> (e.g., dual-core Intel® Xeon® Processors), one or more output devices <b>604</b> (e.g., LCD), one or more network interfaces <b>606</b>, one or more input devices <b>608</b> (e.g., mouse, keyboard, touch-sensitive display) and one or more computer-readable mediums <b>612</b> (e.g., RAM, ROM, SDRAM, hard disk, optical disk, flash memory, etc.). These components can exchange communications and data over one or more communication channels <b>610</b> (e.g., buses), which can utilize various hardware and software for facilitating the transfer of data and control signals between components.

p-0078The term “computer-readable medium” refers to any medium that participates in providing instructions to a processor <b>602</b> for execution, including without limitation, non-volatile media (e.g., optical or magnetic disks), volatile media (e.g., memory) and transmission media. Transmission media includes, without limitation, coaxial cables, copper wire and fiber optics.

p-0079The computer-readable medium <b>612</b> further includes an operating system <b>614</b> (e.g., Mac OS® server, Windows® NT server), a network communication module <b>616</b>, a database interface <b>620</b>, a formatter <b>630</b>, a view manager <b>640</b>, a graphical user interface <b>650</b>, and a cached layout <b>660</b>, as described in reference to <figref idrefs="DRAWINGS">FIGS. 1-5</figref>. The operating system <b>614</b> can be multi-user, multiprocessing, multitasking, multithreading, real time, etc. The operating system <b>614</b> performs basic tasks, including but not limited to: recognizing input from and providing output to the devices <b>606</b>, <b>608</b>; keeping track and managing files and directories on computer-readable mediums <b>612</b> (e.g., memory or a storage device); controlling peripheral devices; and managing traffic on the one or more communication channels <b>610</b>. The network communications module <b>616</b> includes various components for establishing and maintaining network connections (e.g., software for implementing communication protocols, such as TCP/IP, HTTP, etc.). The view manager <b>640</b> can include one or more part instance managers, one or more slide managers, one or more part instance lists <b>550</b>, and one or more slide info maps <b>570</b>.

p-0080The architecture <b>600</b> is one example of a suitable architecture for hosting a browser application having audio controls. Other architectures are possible, which include more or fewer components. The architecture <b>600</b> can be included in any device capable of hosting a database application program. The architecture <b>600</b> can be implemented in a parallel processing or peer-to-peer infrastructure or on a single device with one or more processors. Software can include multiple software components or can be a single body of code.

p-0081The described features can be implemented advantageously in one or more computer programs that are executable on a programmable system including at least one programmable processor coupled to receive data and instructions from, and to transmit data and instructions to, a data storage system, at least one input device, and at least one output device. A computer program is a set of instructions that can be used, directly or indirectly, in a computer to perform a certain activity or bring about a certain result. A computer program can be written in any form of programming language (e.g., Objective-C, Java), including compiled or interpreted languages, and it can be deployed in any form, including as a stand-alone program or as a module, component, subroutine, or other unit suitable for use in a computing environment.

p-0082Suitable processors for the execution of a program of instructions include, by way of example, both general and special purpose microprocessors, and the sole processor or one of multiple processors or cores, of any kind of computer. Generally, a processor will receive instructions and data from a read-only memory or a random access memory or both. The essential elements of a computer are a processor for executing instructions and one or more memories for storing instructions and data. Generally, a computer will also include, or be operatively coupled to communicate with, one or more mass storage devices for storing data files; such devices include magnetic disks, such as internal hard disks and removable disks; magneto-optical disks; and optical disks. Storage devices suitable for tangibly embodying computer program instructions and data include all forms of non-volatile memory, including by way of example semiconductor memory devices, such as EPROM, EEPROM, and flash memory devices; magnetic disks such as internal hard disks and removable disks; magneto-optical disks; and CD-ROM and DVD-ROM disks. The processor and the memory can be supplemented by, or incorporated in, ASICs (application-specific integrated circuits).

p-0083To provide for interaction with a user, the features can be implemented on a computer having a display device such as a CRT (cathode ray tube) or LCD (liquid crystal display) monitor for displaying information to the user and a keyboard and a pointing device such as a mouse or a trackball by which the user can provide input to the computer.

p-0084The features can be implemented in a computer system that includes a back-end component, such as a data server, or that includes a middleware component, such as an application server or an Internet server, or that includes a front-end component, such as a client computer having a graphical user interface or an Internet browser, or any combination of them. The components of the system can be connected by any form or medium of digital data communication such as a communication network. Examples of communication networks include, e.g., a LAN, a WAN, and the computers and networks forming the Internet.

p-0085The computer system can include clients and servers. A client and server are generally remote from each other and typically interact through a network. The relationship of client and server arises by virtue of computer programs running on the respective computers and having a client-server relationship to each other.

p-0086A number of implementations of the invention have been described. Nevertheless, it will be understood that various modifications can be made without departing from the spirit and scope of the invention. Accordingly, other implementations are within the scope of the following claims.

p-0087For example, updating data in a database is described using SQL as an example. It is understood that various databases, including relational databases, object-oriented database, and other databases can be utilized as a backend database engine to implement the interactive sub-summary techniques.