Dynamic query data visualizer

[0001] Many organizations use business management software to manage core business functions including, for example, financials, sales, customer relationship management, inventory, and operations. Users of the business management software often run reports or otherwise extract data from the software application for different business purposes such as setting targets, measuring performance, and forecasting future events. These reports and data extractions are generated based on data included in a business management system.

[0002] These queries, however, may be static queries that are configured and then executed. The data satisfying the query criteria is then typically represented in a grid or static chart. If the user wants to change an aspect of the query or the data included in the query, the user must return to the query designer to change the query, after which time the query is re-run, and the grid or static chart is recreated. This process is cumbersome and inefficient as the user must switch to and from the query designer to change the query and have the query re-executed.

[0003] There is a need for additional functionality in business management systems that enables users to restructure query results and change the output format of query results in real time without having to switch program components to change the original query.

[0008] One or more modules may provide an interface and tools for dynamically generating additional query terms that may be appended to existing queries in a business management system and change visual output format of query results in real time. The modules may be included as a stand-alone component that may be added onto an existing business management system to provide this added functionality. The modules may also include tools enabling the module to read queries from the business management system and then append additional terms to the read query without actually changing the query in the business management system. The queries that included the appended terms may then be executed and the results may be outputted.

[0009] The modules may also include an interface, such as a graphical user interface, providing several options for a user to select different criteria and options for additional terms appended to the original query without requiring the user to re-program or re-write the query in a particular query programming language. Thus, for example, a user may be able to generate an appended query in an Structured Query Language (SQL) by selecting different criteria and options without having to know the semantics and syntax of SQL.

[0010] The interface may also include an output display area for displaying a result or output of an executed query. In some instances, the output display area may include the ability to display the raw query results in a table format in which one or more fields of the query results may be displayed in the interface. The output display area may also display a chart, graph, or other visual representation of the query results.

[0011] The interface may also include different menus having different options for selecting the data that is to be displayed in the output display area. These menus may include a filter menu, a first dimension menu, and a second dimension menu. The filter menu may include a list of fields that may be restricted to a particular value or range of values. Selecting one or more of these fields may provide the user with an opportunity to select the value or range of values to which the results of the query displayed in the output display area are to be restricted.

[0012] The first dimension menu may include a list of fields that may be shown on a first dimension of a chart, graph, grid, or other visual representation of the query results in the output display area. The second dimension menu may include a list of functions applied to the query results that may be shown on a second dimension of the chart, graph, grid, or other visual representation of the query results in the output display area. In some instances, the first and second dimensions may be either horizontal or vertical dimensions. For example, if the first dimension corresponds to a horizontal axis of a graph, then the second dimension may correspond to a vertical axis, and vice versa.

[0013] FIG. 1 shows an exemplary process. The process may be executed by a processing device. A set of instructions for executing the process may be stored in a computer readable medium. In box 101, a query in the business management system may be identified. The query may be identified using a query, report, or other identifier. The query may also be selected from a list or other source.

[0014] In box 102, a partial execution of the identified query may be triggered. The partial execution of the identified query may generate a partial query output of the fields and/or field types included in a result set without actually outputting any data. For example, the identified query may be executed to only generate a list of fields and fields types included in the query output. At this point, the execution of the identified query may cease. The generated list of field types may be used to classify each of the fields. In box 103, each field type may be classified, using a processing device, as one of a text, a numerical, and a date type (or any other data type) based on the type definition provided during the partial execution of the identified query. In instances where the field type includes a number type, including, but not limited to, an integer type, real number type, fixed-point type, or floating-point type, the fields may be classified as numerical.

[0015] In instances where the field type includes a date type, those fields that consist of data in a valid date format may be classified as date. Any recognized date format may be used.

[0016] In instance where the field type does not consist of either a numerical or date type, the field may be classified as text. Boxes 104, 105, and 106 may apply to those fields classified as text fields. In box 104, each respective field classified as text may be added to a filter menu in the interface. In box 105, each respective field classified as text may also be designated as selectable in a first dimension menu of the interface.

[0017] Finally, in box 106, a selectable count function may be added to a second dimension menu for each respective field classified as text. The selectable count function may, if selected, count a number of instances that one or more unique field values occur in a respective field.

[0018] Box 107 may apply to those fields classified as numerical fields. In box 107, one or more selectable mathematical functions may be added to the second dimension menu of the interface for each field classified as numerical. Each mathematical function may, if selected, perform an arithmetic, logical, statistical, or other computation on data in each respective field. The mathematical functions may include one or more of a sum function, an average function, a minimum function, and a maximum function for each field designated as numerical. Each of these functions may be independently selectable. The sum function may aggregate the data in its field. The average function may average the data in its field. The minimum function may return a lowest data value in its field. The maximum function may return a high data value in its field.

[0019] Box 108 may apply to those field classified as date fields. In box 108, each field classified as date may be added to the filter menu.

[0020] Once each field has been classified as one of text, numerical, and date in box 103 and then processed in boxes 104 to 108, each of the menus, including the first dimension menu, the second menu, and the filter menu, may be presented to a user through the interface. The user may then select one or more functions or fields from one or more of the menus.

[0021] In some instances, a range of possible data values associated with each field added to the filter menu may be identified. The range of possible data values may be obtained by looking up actual data values in each respective field. The range of possible data values may also be obtained by identifying a restriction of data values associated with respective field. For example, if the respective field is designated as a Yes/No or On/Off field, then the data values may be limited to one of these two possible values. On the other hand, if the actual data values of a field only include fifteen unique values, then the range of possible data values may only include these fifteen possible choices.

[0022] Once the range of values has been identified, an option may be provided in the filter menu to enable the user to select at least one value in the identified range of values associated with at least one field added to the filter menu. Once the user make a selection of one or more valued in the identified range and this selection is received, the selected values may be designated as the subset of data from the at least one selected field in the filter menu.

[0023] In box 109, a selection of function(s) and/or field(s) made by the user from the first dimension, second dimension, and/or filter menus may be received. This selection may include a selection of at least one of the selectable sum, average, minimum, maximum, and count functions.

[0024] In box 110, one or more additional query terms may be appended to the identified query to apply the selected function(s) to the identified query. For example, a received selection of at least one of the functions from the second dimension menu may be applied to the subset of data from the at least one selected field in the filter menu and/or according to the selected field(s) from the first dimension menu.

[0025] In box 111, data from one or more fields selected from the first dimension menu may be displayed in a first dimension of the interface. This first dimension may occur along a vertical or horizontal axis of a chart, graph, grid, or other visual representation of the query results in the output display area.

[0026] In box 112, a query result from executing the appended query in box 110 may be displayed in a second dimension of the interface. This query result may include an output from applying the selected function(s) to data displayed in the first dimension. The second dimension may also occur along a vertical or horizontal axis of a chart, graph, grid, or other visual representation of the query results in the output display area.

[0027] The visual representation of the query results may include displaying data in the first and second dimensions in a graphical format. The graphical format may be switchable between a grid format or any other defined chart format, which may include a bar chart format, a line chart format, a scatter plot format, a pie chart format, a three dimensional chart format, and/or a stacked chart format. If the first dimension is along a vertical axis, then the second dimension may be along the horizontal axis, and vice versa. In some instances, the first dimension may be parallel to a horizontal axis of a graph displayed in the output display area and the second dimension may be parallel to a vertical axis of the graph. In other instances the first and/or second dimensions may be along a radial or axial direction. In other situations, including, but not limited to three dimensional situations, the first or second dimensions may include a direction going into or out of a two-dimensional page. Other directions may also be used in different embodiments.

[0028] In some instances, a sorting menu may also be displayed in the interface. The sorting menu may include an ascending sorting option, a descending sorting option, and an option to select at least one field in the first dimension menu that is to be sorted according to either the ascending sorting option or the descending sorting option. A user may then select either one of the ascending or descending sorting options as well as at least a field in the first dimension menu that is to be sorted according to the selected ascending or descending sorting option.

[0029] Once the user's selection of the at least one field included in the first dimension menu that is to be sorted and the user's selection of either the ascending sorting option or the descending sorting option is received, an instruction may be appended to the query to sort a query result according to the received selection of the at least one field and the received selection of either the ascending sorting option or the descending sorting option. The appended instruction may then be executed when the query is executed. This may cause the query result to be sorted according to selected sorting options appended to the query.

[0030] In some instances, a limiting menu may be displayed in the interface. The limiting menu may limit a number of query results to a top number N of results. The top number N may be selectable by a user. For example, if a user selects the top number N to be 10, then only the top ten query results may be shown in the output display area of the interface.

[0031] Once a selection of the top number N has been received, an instruction may be appended to the query so that only the top number N of results are included in an outputted result of the query. In some instances, this top number N may be a user selectable number between 1 and 100.

[0032] In some instance, a format menu may be displayed in the interface. The format menu may provide the user with an option to select one of a plurality of data formats for an output. In some instances, the output may be a graph, chart, or other visual representation of the query results shown in the output display area. In other instances, the output may be the query results or other data generated from an outputted result of the query.

[0033] Once the user selects one of the data formats and the selection of the one data format is received, the output, including, but not limited to, the graph, grid, chart, or other visual representation, may be generated in the selected data format. Examples of data formats include a .NET format and a HTML5 format. In some instances, the output, including, but not limited to, the graph, chart, or other visual representation, may exported in the selected data format for use by another program.

[0034] FIG. 2 shows an exemplary execution of a process. In box 210, an initial query may be selected. In this case, the query to be selected is a query in a structured query language (SQL), though in different embodiments different queries types, languages, and structures may also be selected. Once the initial query has been identified, at least a partial execution of the identified query may be triggered to identify the fields and/or field types in the query result.

[0035] The fields and field types are shown in box 220. In this example, the fields ItemCode 221, ItemName 222, OnOrder 223, OrderDate 224, OrderPrice 225, and DeliverDate 226 are included in the partial result 220. Also, in this example, only the fields and field types are outputted as the partial result 220.

[0036] Once the partial results have been generated, each field 221 to 226 in the first row of the partial result may be classified 225 as one of text, numerical, and date according to its respective field type. Since only the OnOrder 223 and OrderPrice 225 fields contain data consisting of number types, in this case Integer type in OnOrder field 223 and fixed-point type in OrderPrice field 225, only these fields 223 and 225 may be classified as numerical. Since only the OrderDate 224 and DeliverDate 226 fields contain data in a recognized date format, in this case the date and/or time database field types, only these fields 224 and 226 may be classified as dates. Since the remaining fields ItemCode 221 and ItemName 222 do not satisfy the criteria for being classified as numerical or date, these fields 221 and 222 may be classified as text.

[0037] Once each of the fields 221 to 226 in the partial results have been classified, the generation of the user interface 230 may then start. The user interface 230 may include three menus containing one or more user selectable options. The three menus include a first dimension menu 260, a second dimension menu 250, and a filter menu 240. Once the user selects one or more options from the first dimension menu 260 and the second dimension menu 250, a chart, graph 280, or other visual representation of result of a full query execution subject to the options selected from each of the three menus 240, 250, and 260.

[0038] The options that are shown in each of three menus 240, 250, and 260 may vary depending on how each of the query result fields 221 to 226 have been classified. For those fields classified as text: (i) each respective field may be added to the filter menu 240 in the interface, (ii) each respective field may be designated as selectable in the first dimension menu 260 of the interface, and (iii) a selectable count function for each respective field may be added to the second dimension menu 250 of the interface. The count function may count a number of instances of a unique field value. Thus, as shown in FIG. 2, the text fields ItemCode 221 and Item 222 have been added to the first dimension menu 260 and filter menus 240 of the interface 230. Additionally, a COUNT function for counting a number of instances of a unique field value in each of the ItemCode 221 and ItemName 222 fields has also been added to the second dimension menu 250.

[0039] For those fields 223 and 225 classified as numerical, a selectable mathematical function operative on data in each respective field may be added to the second dimension menu 250 of the interface 230. In the example shown in FIG. 2, a sum, average, minimum, and maximum selectable functions have been added to the second dimension menu 250 for each of the OnOrder 223 and OrderPrice 225 fields that have been classified as numerical.

[0040] For those fields classified as date, each respective field may be added to the filter menu 240. In the example shown in FIG. 2, the OrderDate 224 and DeliverDate 226 fields, which are the only fields classified as date, have been added to the filter menu 240 shown in the interface.

[0041] Once the respective menus 240, 250, and 260 have been created, a user may select one or more options from each of the menus 240, 250, and 260. In the example shown in FIG. 2, the user has selected the ItemCode field 221 in the first dimension menu 260, the OrderDate field 224 in the filter menu 240, and the Sum function for the OnOrder field 223 in the second dimension menu 250 for adding all of the data in the OnOrder field 223. In addition to selecting the OrderDate field 224 in the filter menu, the user may also select a further criterion, value, or range of values (not shown) for further limiting the returned query results to the selected criterion, value, or range of values. Thus, in the example of FIG. 2, if the user selected to limit the OrderDate field 224 to only those results having a date in the OrderDate field 224 that is within the past year (not shown), the results of query shown in the chart, graph 280, or other visual representation of results may be limited to those results with an OrderDate field 224 date within the past year.

[0042] Once the user's selection is received, the identified query may be appended to apply the at least one selected function in the second dimension menu 250 (in this case the SUM function to add the data in the OnOrder field 223) according to the field(s) selected from the first dimension menu.

[0043] Once the full results of the query have been generated, the chart, grid, graph 280, or other visual representation of results may be displayed. In the example shown in FIG. 2, the visual representation is a graph 280 having a first dimension 265 corresponding to a horizontal axis and a second dimension 255 corresponding to a vertical axis. Since the ItemCode field 221 was selected from the first dimension menu, each of the item codes included in the ItemCode field 221 of the query results may be displayed along the horizontal axis. In this case, the item code includes the item codes TR-123, AN-456, JG-789, XY-012, and PQ-345. Thus, data from the field(s) selected from the first dimension menu 260 may be displayed in the first dimension 265 of the visual representation 280 included in the interface 230.

[0044] Since the SUM function for the OnOrder field 223 was selected in the second dimension menu, the results of adding all of the OnOrder 223 field data for each unique ItemCode 221 field having an OrderDate 223 date within the past year may be displayed along the vertical dimension 225 for each of the unique ItemCodes 221. Thus, a result of applying the selected function(s) from the second dimension menu 250 in the appended query to the field data displayed in the first dimension 265 may be displayed in the second dimension 255 of the visual representation 280 included in the interface 230.

[0045] FIG. 3 shows an alternative view of an exemplary interface 230 if a user selects more than one field in the first dimension menu 260. In this example, a user has selected both the ItemCode 221 and ItemName 223 fields in the first dimension menu 260. In this case, the interface may show the results of the selected functions from the second dimension menu 250 as applied to each of the selected fields in the first dimension menu 260. In this example, the results are shown in both a stacked chart graph 281 and unstacked chart graph 282. In some instances, the interface 230 may include an option to toggle between the stacked view 281 and the unstacked view 282. Although this example shows only two fields being selected in the first dimension menu, in other instances a different number of fields greater than or less than that shown may be selected.

[0046] FIG. 4 shows an exemplary architecture. Query interface system 310 may include a computer readable medium 515 storing application modules that may include a field classification module 320, interface generation module 330, and a query interactivity module 340. In some instances, these modules 320, 330, 340, and/or other modules or components of the query interface system 310 may be stored in a memory 503 or data structure 505 that is separate from the computer readable medium 515 and/or the query interface system 310.

[0047] The query interactivity module 340 may include functionality for identifying a query in the business management system, triggering at least a partial execution of the identified query to identify a field type of each field in a query result, and appending the identified query, all of which may occur through the communications device, which may include hardware enabling connectivity with the business management system.

[0048] The field classification module 330 may include functionality for classifying each field in the first row of the query result as one of text, numerical, and date based on its respective field type.

[0049] The interface generation module 320 may include functionality, for those fields classified as text, for adding each respective field to a filter menu in a user interface, designating each respective field as selectable in a first dimension menu of the user interface, and adding a selectable count function for each respective field to a second dimension menu of the user interface. The count function may count a number of instances of a unique field value.

[0050] The interface generation module 320 may also include functionality, for those fields classified as numerical, for adding a selectable mathematical function operative on data in each respective field to the second dimension menu of the interface. The interface generation module 320 may also include functionality, for those fields classified as date, for adding each respective field to the filter menu.

[0051] The interface generation module 320 may also include functionality for receiving a selection of at least one field in the first dimension menu and at least one function in the second dimension menu; displaying in a first dimension of the user interface data from the at least one selected field from the first dimension menu; and displaying in a second dimension of the user interface a result of applying a selected function in the appended query to data displayed in the first dimension. The modifying of the identified query may also include applying at least one selected function added in the second dimension menu according to the at least one selected field in the first dimension menu.

[0052] Query interface system 310 may be connected to a network 550. Network 550 may include a LAN, WAN, bus, or the Internet. Query interface system 310 may interface with other systems and components depending on the application. For example, a network/data storage device 560 may be used to store the different types of data structures, including business management system data and/or data object 371, as well as queries 372 to be executed on the business management system data and/or data objects 371. The storage device 560 may be a part of a business management system 370.

[0053] In some embodiments the network storage device 560 may also be separate from the business management system 370 but connected to it through network 550. The storage device 560 may contain a hard disk drive, flash memory, or other computer readable media capable of storing data. Other external systems and data sources may also be connected to network 550. These other systems may be used to supply additional data or information used by the query interface system 310 or business management system 370, such as, for example, financial, customer, or other organizational data.

[0054] Each of the systems, clients, and devices in FIG. 4 may contain a processing device 502, memory 503 storing loaded data or a loaded data structure 505, and a communications device 504, all of which may be interconnected via a system bus. In various embodiments, each of the systems 310 and 370 may have an architecture with modular hardware and/or software systems that include additional and/or different systems communicating through one or more networks. The modular design may enable a business to add, exchange, and upgrade systems, including using systems from different vendors in some embodiments. Because of the highly customized nature of these systems, different embodiments may have different types, quantities, and configurations of systems depending on the environment and organizational demands.

[0055] Communications device 504 may enable connectivity between the processing devices 502 in each of the systems 310 and 370 and the network 550 by encoding data to be sent from the processing device 502 to another system over the network 550 and decoding data received from another system over the network 550 for the processing device 502.

[0056] In an embodiment, memory 503 may contain different components for retrieving, presenting, changing, and saving data and may include the computer readable medium 515. Memory 503 may include a variety of memory devices, for example, Dynamic Random Access Memory (DRAM), Static RAM (SRAM), flash memory, cache memory, and other memory devices. Additionally, for example, memory 503 and processing device(s) 502 may be distributed across several different computers that collectively comprise a system.

[0057] Processing device 502 may perform computation and control functions of a system and comprises a suitable central processing unit (CPU). Processing device 502 may include a single integrated circuit, such as a microprocessing device, or may include any suitable number of integrated circuit devices and/or circuit boards working in cooperation to accomplish the functions of a processing device. Processing device 502 may execute computer programs, such as object-oriented computer programs, within memory 503.

[0058] The foregoing description has been presented for purposes of illustration and description. It is not exhaustive and does not limit embodiments of the invention to the precise forms disclosed. Modifications and variations are possible in light of the above teachings or may be acquired from the practicing embodiments consistent with the invention. For example, although the data access system 510 is shown as a separate system from the data object storage system 570, in some instances the data object storage system 570 and the data access system 510 may be functionally integrated into a single system.