US 9,811,572 B2
Multi-axis expand/collapse for relational databases
Paul Duffy, Lake Forest, CA (US); and Amir Kashani, Irvine, CA (US)
Assigned to INSIGHTSOFTWARE.COM INTERNATIONAL, Bray (IE)
Filed by InsightSoftware.com International, Bray, County Wicklow (IE)
Filed on Mar. 21, 2017, as Appl. No. 15/465,346.
Claims priority of provisional application 62/319,230, filed on Apr. 6, 2016.
Claims priority of provisional application 62/451,581, filed on Jan. 27, 2017.
Prior Publication US 2017/0293664 A1, Oct. 12, 2017
Int. Cl. G06F 17/30 (2006.01)
CPC G06F 17/30554 (2013.01) [G06F 17/30315 (2013.01); G06F 17/30483 (2013.01)] 9 Claims
OG exemplary drawing
 
1. A relational database device for performing multi-axis expansion of a row or column of an existing report, where newly-visible data is sourced from a relational database, the device comprising:
a processor with one or more processing components therein;
a memory coupled to the processor;
a multi-axis expander module stored on the memory and executed by the processor to:
form an existing report based on a structured query language (SQL) database in response to a first request received from a client computer via a network, the existing report comprising rows and columns of data, row headings for the rows, and column headings for the columns;
receive at a server computer a second request from the client computer, wherein the second request is for expansion of a row or column, where a column or row, respectively, has already been expanded; and
create one or more sub queries, where a number of the one or more sub queries corresponds to a number of levels of already-expanded nodes in an axis opposite to an axis where the expansion is requested, wherein the create the one or more sub queries includes measuring a depth of expansion, in the axis opposite, of a tree hierarchy for the existing report, the tree hierarchy stored in memory, wherein each sub query of the one or more sub queries includes:
a SELECT filter, the SELECT filter being the same for each sub query of the one or more sub queries, the SELECT filter including children of the node being expanded and nodes from one or more levels of the axis opposite related to any expanded nodes of the axis opposite, and
a WHERE filter, different for each sub query of the one or more sub queries, the WHERE filter including the node being expanded and any parent nodes of the node being expanded as well as nodes from one or more levels of the axis opposite related to any expanded nodes of the axis opposite;
execute the one or more sub queries to form a results set for each sub query of the one or more sub queries;
combine the results sets into a new report, the new report including data and headings to populate new blank columns and rows in the existing report, the new report configured for stitching into the blank columns and rows in the existing report; and
return the new report to the client computer for stitching the new report into the existing report on the client computer to form a final report, or stitching the new report into the existing report on the server computer to form the final report and returning the final report to the client computer.