Abstract:
A system includes reception of a query filtered on an object of a hierarchical list of values and comprising a first filter value, generation of an SQL script based on the first filter value and at least one ancestor value of the first filter value in the hierarchical list of values, and retrieval of a data set associated with the query based on the SQL script. 
     In some aspects, reception of the query includes reception of a command to refresh a document including the query and a prompt on a filter value, presentation of the prompt and the hierarchical list of values, and reception of the first filter value.

Description:
BACKGROUND 
       [0001]    Enterprise software systems receive, generate, and store data related to many aspects of a business enterprise. In some implementations, this data may relate to marketing, sales, customer relations, supplier relations, inventory, human resources, and/or finances. Reporting systems are commonly used to present such enterprise data to users in useful formats. 
         [0002]    Reporting systems may generate reports based on user-created report specifications. A report specification may include a layout of one or more report parts (e.g., sections, tables, charts, header, footer, etc.) and queries to specify the data that is to populate the report parts. In order to generate a report based on a report specification, a reporting system retrieves stored enterprise data based on the queries, populates the report parts with the retrieved data, and assembles the populated report parts according to the layout. 
         [0003]    To facilitate creation of a report specification, reporting systems may interact with a semantic layer including a set of abstract entities known as business objects. Each business object associates one or more physical entities (e.g., a physical database table, associated columns of one or more database tables) of one or more enterprise data sources with user-friendly names. The user-friendly names may represent business entities, such as customers, time periods, financial figures, etc. Business objects may be classified as dimensions along which one may want to perform an analysis or report (e.g., Year, Country, Product), details (e.g., additional information on dimensions), and measures (e.g., Sales, Profit) whose values can be determined for a given combination of dimension values. A report designer may therefore create the queries of a report specification using these user-friendly names instead of references to specific physical entities of the data sources. 
         [0004]    A semantic layer may also include a hierarchical list of values (LOV) to facilitate the selection of filter values during the creation of a query. A hierarchical LOV is associated with business objects of the semantic layer. For example, a hierarchical LOV may be associated with the Year, Quarter and Month business objects and may reflect the hierarchy Year &gt;Quarter &gt;Month. Each business object therefore represents a different “level” of the hierarchical LOV. 
         [0005]      FIG. 1  illustrates the creation of a query using a hierarchical LOV according to some systems. Specifically, a user has operated query panel  100  in order to create a query that is filtered on the Month object and which returns result objects Year, Quarter, Month and Unit Sales. Hierarchical LOV  110  is presented in tree form to allow the user to intuitively select filter values from the tree. As shown, the user has selected the filter values January (of year 1997) and July (of year 1998). 
         [0006]    As mentioned above, conventional reporting systems retrieve stored data based on such user-generated queries. For example, a conventional reporting system may receive the query created in query panel  100  and generate the following Structured Query Language (SQL) script in response: 
         [0000]    SELECT time.year_name, time.quarter_name, time.month_name, sum(sales.unit_sales)
 
FROM time INNER JOIN sales on (time.time_id=sales.time_id)
 
WHERE time.month_name IN (‘January’, ‘July’)
 
         [0007]    The SQL script is then passed to an SQL server to retrieve a data set from a corresponding data store.  FIG. 2  is a tabular representation of such a retrieved data set according to the present example. Due to the above SQL script, data set  200  includes record for January of year 1998 and July of year 1997 even though those months were not selected in hierarchical LOV  110 . Conventional systems therefore fail to preserve the uniqueness of the selected filter values in the hierarchy, resulting in the retrieval of undesired data. 
         [0008]    Systems are desired to facilitate the creation and execution of a query based on a hierarchical LOV. 
     
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
         [0009]      FIG. 1  is a view of a user interface to define a query. 
           [0010]      FIG. 2  is a tabular representation of a data set. 
           [0011]      FIG. 3  is a block diagram of a system according to some embodiments. 
           [0012]      FIG. 4  illustrates a flow diagram of a process according to some embodiments. 
           [0013]      FIG. 5  is a view of a user interface to define a query according to some embodiments. 
           [0014]      FIG. 6  is a tabular representation of a data set according to some embodiments. 
           [0015]      FIG. 7  illustrates a flow diagram of a process according to some embodiments. 
           [0016]      FIG. 8  is a view of a user interface to define a query according to some embodiments. 
           [0017]      FIG. 9  is a tabular representation of a data set according to some embodiments. 
           [0018]      FIG. 10  is a view of a user interface to define a query according to some embodiments. 
           [0019]      FIG. 11  is a tabular representation of a data set according to some embodiments. 
           [0020]      FIG. 12  is a view of a user interface to define a query according to some embodiments. 
           [0021]      FIG. 13  is a tabular representation of a data set according to some embodiments. 
           [0022]      FIG. 14  illustrates a flow diagram of a process according to some embodiments. 
           [0023]      FIG. 15  is a view of a user interface to define a query according to some embodiments. 
           [0024]      FIG. 16  is a view of a user interface to select filter values according to some embodiments. 
           [0025]      FIG. 17  is a block diagram of an apparatus according to some embodiments. 
       
    
    
     DETAILED DESCRIPTION 
       [0026]    The following description is provided to enable any person in the art to make and use the described embodiments and sets forth the best mode contemplated for carrying out some embodiments. Various modifications, however, will remain readily apparent to those in the art. 
         [0027]      FIG. 3  is a block diagram of system  300  according to some embodiments. System  300  includes data source  310 , reporting client  320 , information engine  330  and repository  340 . Each element of system  300  may be implemented by any suitable combination of hardware (e.g., one or more processors) and/or software (e.g., processor-executable program code). System  300  may include elements in addition to those illustrated, and some embodiments may omit one or more elements of system  300 . 
         [0028]    Reporting client  320  may comprise a device executing program code of a software application for object-based report creation. System  300  may include any number of reporting clients of one or more types according to some embodiments. 
         [0029]    Repository  340  comprises semantic layer metadata  342 , hierarchical LOV metadata  344 , report specifications  346 , and reports  348 . Semantic layer metadata  342  defines objects mapped to logical entities of data source  310 . Hierarchical LOV metadata  344  defines hierarchical LOVs associated with the defined objects. Report specifications  346  define report layouts and queries for populating the report layouts, and reports  348  include reports which have been created and populated based on report specifications  346  and data stored in data source  310 . 
         [0030]    Data source  310  may comprise any query-responsive data source or sources that are or become known, including but not limited to a structured-query language (SQL) relational database management system. Embodiments are not limited to any number or types of data sources. For example, data source  310  may comprise one or more Online Analytical Processing databases, spreadsheets, etc. The data stored in data source  310  may be received from disparate sources (not shown). 
         [0031]    Information engine  330  generally provides data of data source  310  to reporting client  320  based on metadata of repository  340 . In some embodiments, information engine  330  may communicate with reporting client  320  to allow a user to generate a report specification based on metadata  342  and  344 . The report specification may include a query which is filtered on an object of a hierarchical LOV defined by metadata  344  and which specifies a filter value. Information engine  330  receives the query, generates an SQL script based on the query, and forwards the SQL script to data source  310 . Data source  310  returns a data set based on the SQL script, and information engine  330  creates a report based on the report specification and the returned data set. The report specification and the populated report may be saved in repository  340  for later access. 
         [0032]      FIG. 4  is a flow diagram of process  400  according to some embodiments. Process  400  may be used to retrieve a data set based on a query filtered on an object of a hierarchical LOV. Process  400 , and all other processes described herein, may be embodied in program code stored on a non-transitory computer-readable medium and/or implemented by a computer in response to execution of program code by a processor of the computer, but embodiments are not limited thereto. 
         [0033]    Initially, at S 405 , a query is received. The query is filtered on a filter object of a hierarchical LOV and includes at least one filter value.  FIG. 5  is an outward view of user interface  500  to define a query filtered on a filter object of a hierarchical LOV according to some embodiments. 
         [0034]    The filter object and the hierarchical LOV must be defined prior to S 405 . In a brief example, a user operates a standalone, Web-based or other application to establish a connection to data source  310  using any suitable database connection (e.g., Java Database Connector, QT/Connection Server), defines business objects based on the underlying schema of data source  310 , and then defines one or more hierarchical LOVs associated with the business objects. As mentioned above, the definitions of the business objects may be stored in semantic layer metadata  342  and the definitions of the hierarchical LOVs may be stored in hierarchical LOV metadata  344 . 
         [0035]    User interface  500  may be presented by any reporting client capable of consuming hierarchical LOVs through query filters or query filter prompts. In the illustrated example, the query is filtered on the Month object and returns result objects Year, Quarter, Month and Unit Sales. The user has selected the filter value “January” (of year “FY1997”) from hierarchical LOV  510 . Hierarchical LOV  510  is associated with the Year, Quarter and Month business objects and reflects the hierarchy Year &gt;Quarter &gt;Month. 
         [0036]    The query is received at S 405  upon user selection of OK icon  520 . For the present example, it will be assumed that the query is received by information engine  330 , although embodiments are not limited thereto. Next, at S 410 , information engine  330  generates an SQL script based on the filter value and on at least one ancestor value of the filter value in the hierarchical LOV. 
         [0037]    Examples of such a generated SQL script according to some embodiments are provided below. Generally, due to the specific instance of “January” selected in hierarchical LOV  510 , the SQL script is generated at S 410  to select a data set which is associated with the Month “January” of Quarter “Q1” and Year “FY1997”. This is in contrast to the SQL script described in the Background, which was not generated based on ancestor values of the selected “January” filter value. 
         [0038]      FIG. 6  is a tabular representation of data set  600  retrieved at S 415  based on the generated SQL script. As shown, data set  600  reflects the intention of the query designer. 
         [0039]    More specifically, and in contrast to data set  200 , data set  600  does not include data associated with the Month “January” of Year “FY1998”. 
         [0040]    A report specification including the query may be saved among report specifications  346  at S 420 . Also at S 420 , the data set may be formatted into a report based on the report specification, and the report may be saved among reports  348 . Thereafter, any user having appropriate authorization may operate any reporting client to request the report from information engine  330 . 
         [0041]    Flow pauses at S 425  until a request to refresh the report is received. Upon receipt of such a request, flow returns to S 410  and continues as described above to generate an SQL script based on the query and to retrieve a data set based on the SQL script. The retrieved data set will reflect any changes made to the requested data since a last refresh of the report. 
         [0042]      FIG. 7  illustrates process  700  according to some embodiments. Process  700  may comprise an implementation of step S 410  of process  400 , but embodiments of S 410  are not limited thereto. 
         [0043]    Process  700  begins at S 710  at which it is determined whether the filter object of the query received at S 405  is at the same level of the hierarchical LOV as all selected filter values. Initially, it will be assumed that  FIG. 5  depicts the received query, with the filter object Month and on selected filter value “January”. 
         [0044]    According to some embodiments, information engine  330  receives several properties associated with the selected filter value at S 405 . These properties may include VALUE_NAME, VALUE_KEY, VALUE_DEPTH and VALUE_ANCESTORS. VALUE_NAME specifies the name of the selected value, and VALUE_KEY specifies a key (or unique name) of the selected value, if available. In this regard, some hierarchical LOVs can provide keys associated with their values (e.g., if the hierarchical LOV is based on objects exhibiting primary key index awareness). If the filter object of the query also exhibits primary key index awareness, information engine  330  may use these associated keys to select appropriate data from data source  310  as described below. 
         [0045]    The VALUE_DEPTH property indicates the distance of the value from the root of the hierarchy, with the root having a VALUE_DEPTH of “0”. The VALUE_ANCESTORS property is an array specifying the ancestors of the selected filter value in the hierarchal LOV. The VALUE_DEPTH and VALUE_ANCESTORS properties may only be received at S 405  if the filter value is selected from a hierarchical LOV, and not for filter values selected from a flat or tabular LOV. 
         [0046]    Continuing with the present example, it will be assumed that the following information is received at S 405  based on user selection of filter value “January” as depicted in  FIG. 5 . 
         [0000]    
       
         
               
               
             
               
               
               
               
               
               
               
             
               
               
               
               
               
               
               
             
           
               
                   
               
               
                   
                 VALUE_ANCESTORS 
               
             
          
           
               
                 selected item 
                 VALUE_NAME 
                 VALUE_KEY 
                 VALUE_DEPTH 
                 name 
                 Key 
                 depth 
               
               
                   
               
             
          
           
               
                 FY1997/Q1/January 
                 January 
                 19970101 
                 2 
                 FY1997 
                 1997 
                 0 
               
               
                   
                   
                   
                   
                 Q1 
                 199701 
                 1 
               
               
                   
               
             
          
         
       
     
         [0047]    Therefore, at S 710 , and because the hierarchical LOV is defined as a sorted collection of business objects, information engine  330  is able to determine the hierarchical level of the object to which the received filter value belongs based on the value of VALUE_DEPTH. For instance, the VALUE_DEPTH “2” received with the January value indicates the Month object in the hierarchy Year &gt;Quarter &gt;Month. Since this object is identical to the filter object (i.e., Month), it is determined at S 710  that the filter object and the filter value are at the same level of the hierarchical LOV. 
         [0048]    Next, at S 720 , it is determined whether the filtered object exhibits primary key index awareness. Currently assuming that the Month object is primary key aware, flow continues to S 730  to generate an SQL script. The generated SQL script includes a WHERE statement including a key corresponding to the selected filter value. The key may be obtained from the VALUE_KEY property received at S 405 , and its identity is based on and reflects ancestors of the selected filter value. For example, the following SQL script may be generated at S 730 : 
         [0000]    
       
         
               
               
             
               
             
           
               
                   
               
             
             
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                 time.month_id = 19970101 
               
             
          
           
               
                 /* where 19970101 is the key of “January” descending from “FY1997” 
               
               
                 and “Q1” */ 
               
               
                   
               
             
          
         
       
     
         [0049]    Flow then proceeds to S 415  and continues as described above to retrieve data set  600 . Returning to S 720 , it is now assumed that the Month object is not primary key aware and, consequently, the received filter value is not associated with a key value (i.e., the value of VALUE_KEY property is null). Accordingly, flow proceeds to S 740  to determine ancestor values of the filter value. In the present example of  FIG. 5 , the ancestor values of “January” in the hierarchical LOV are “Q1” (belonging to the Quarter level) and “FY1997” (belonging to the Year level), and may be determined from the received VALUE_ANCESTORS property. 
         [0050]    Determination of the ancestor values allows the SQL script to specify the uniqueness of the selected filter value. More specifically, an SQL script is generated at S 750  including a WHERE statement which includes a clause specifying the ancestor values of the filter value, as well as the filter value. As described in  FIG. 7 , if more than one filter value was received, the WHERE statement would include separate clauses for each filter value and its ancestors, with the clauses separated by the OR operator. An example of this scenario is provided below. 
         [0051]    With respect to the currently-described single filter value case, the following SQL script may be generated at S 750  according to some embodiments: 
         [0000]    
       
         
               
               
             
               
             
           
               
                   
               
             
             
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                 (time.month_name= ‘January’ AND 
               
               
                   
                 time.quarter_name= ‘Q1’ AND time.year_name= ‘FY1997’) 
               
             
          
           
               
                 /* where time.quarter_name is the binding column used by the Quarter 
               
               
                 object, and time.year_name is the binding column used by the 
               
               
                 Year object */ 
               
               
                   
               
             
          
         
       
     
         [0052]      FIG. 8  is an outward view of user interface  800  defining a query filtered on the Month object and including the filter value “Q3” (of year “FY1997”) from hierarchical LOV  510 . According to some embodiments, a user can select any value at any level of a hierarchical LOV only if the filter operators are  INLIST  or  NOT INLIST . For the other operators (e.g.,  EQUAL, NOT EQUAL, GREATER THAN, LESS THAN , etc.), a user may only select filter values of the same level as the filter object. 
         [0053]    To continue the description of process  700 , it will be assumed that this query is received at S 405  with the following properties: 
         [0000]    
       
         
               
               
             
               
               
               
               
               
               
               
             
           
               
                   
               
               
                   
                 VALUE_ANCESTORS 
               
             
          
           
               
                 selected item 
                 VALUE_NAME 
                 VALUE_KEY 
                 VALUE_DEPTH 
                 name 
                 Key 
                 depth 
               
               
                   
               
               
                 FY1997/Q3 
                 Q3 
                 199703 
                 1 
                 FY1997 
                 1997 
                 0 
               
               
                   
               
             
          
         
       
     
         [0054]    At S 710 , and based on the value of VALUE_DEPTH (i.e., “1”) it is determined that the selected filter value is associated with the Quarter object of the hierarchy Year &gt;Quarter &gt;Month. Since the filter object is Month, it is also determined at S 710  that the filter object and the filter value are not at the same level of the hierarchical LOV. 
         [0055]    Next, at S 760 , it is determined whether the filtered object exhibits primary key index awareness. Assuming that the Month object is primary key aware (and therefore the VALUE_KEY property is non-null), flow continues to S 765 . At S 765 , ancestor values of all filter values which are not on the same level as the filter object are determined. In the present example, the ancestor value “FY1997” of the filter value “Q3” is determined at S 765  because the filter value “Q3” is not on the same level as the filter object Month. 
         [0056]    An SQL script is then generated at S 770 . The SQL script includes a sub-query WHERE statement including keys corresponding to any selected filter values which correspond to the filter object (i.e., Month), and separate clauses for each other filter value and its ancestors. With respect to the  FIG. 8  example, the following SQL script may be generated at S 770 : 
         [0000]    
       
         
               
               
             
               
               
             
               
             
           
               
                   
               
             
             
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                 time.month_id IN 
               
             
          
           
               
                   
                 ( 
               
               
                   
                  /* sub-query clause to get the subset of all months of the 
               
               
                   
                  Q3 quarter */ 
               
               
                   
                  SELECT time.month_id 
               
               
                   
                  FROM  time 
               
               
                   
                  WHERE time.quarter_name = ‘Q3’ 
               
               
                   
                 ) 
               
             
          
           
               
                 /* where time.quarter_name is the binding column used by the Quarter 
               
               
                 object */ 
               
               
                   
               
             
          
         
       
     
         [0057]    Since no selected filter value is at the same level as the filter object, the WHERE statement of the sub-query does not include a clause for value keys. A data set is retrieved at S 415  based on the generated SQL script.  FIG. 9  illustrates data set  900  which may be retrieved at S 415  based on the  FIG. 8  query. Advantageously, data set  900  does not include rows corresponding to Q3 of FY1998, even though the selected filter value is “Q3”. 
         [0058]    In a further example, it is now assumed that the query of  FIG. 8  is received at S 405  but that the Month object is not primary key aware. Therefore, the received filter value (i.e., “Q3”) is not associated with a key value (i.e., the value of VALUE_KEY property is null). Flow therefore proceeds from S 710  through S 760  to S 780 . 
         [0059]    At S 780 , ancestor values of the selected filter value are determined. The filter value “Q3” has only the ancestor value “FY1997” in the hierarchal LOV. The ancestor value(s) may be determined from the VALUE_ANCESTORS property received at S 405 . 
         [0060]    Next, at S 790 , an SQL script is generated including a sub-query WHERE statement which includes a clause specifying the ancestor values of the filter value, as well as the filter value. If more than one filter value was received at S 405 , the WHERE statement would include separate clauses for each common set of ancestor values, with the clauses separated by the OR operator. Details of a corresponding example will be described below. 
         [0061]    According to the present example, the following SQL script may be generated at S 790 : 
         [0000]    
       
         
               
               
             
               
             
           
               
                   
               
             
             
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                 time.year_name + “ + time.quarter_name + ” + 
               
               
                   
                 time.month_name IN 
               
             
          
           
               
                  ( 
               
               
                  /* using sub-query to get all year-quarter-month tuples of the Q3 quarter 
               
               
                  of FY1997 */ 
               
               
                  SELECT time.year_name + “ + time.quarter_name + 
               
               
                  ” + time.month_name 
               
               
                  FROM  time 
               
               
                  WHERE 
               
               
                   (time.quarter_name = ‘Q3’ AND time.year_name = ‘FY1997’) 
               
               
                  ) 
               
               
                 /* where time.quarter_name is the binding column used by the Quarter 
               
               
                 object, and time.year_name is the binding column used by the Year 
               
               
                 object */ 
               
               
                   
               
             
          
         
       
     
         [0062]    The foregoing SQL script uses the concatenation function (i.e., time.year_name+“+time.quarter_name+”+time.month_name) in the sub-query to preserve the ancestor values of the filter value, and to thereby preserve the uniqueness of the selected filter value (i.e., “Q3”) within the hierarchical LOV. 
         [0063]    Data set  900  is then retrieved at S 415  based on the generated SQL script. 
         [0064]    The foregoing examples related to the selection of single filter values. In some embodiments, a user may select more than one filter value at any level of the hierarchical LOV.  FIG. 10  illustrates user interface  1000  in which a user has created a query and selected two filter values (i.e., “January” and “July”) of hierarchical LOV  510 . 
         [0065]    Process  700  may be used to generate an SQL script based on receipt of such a multi-valued query. The received query may include the following property values: 
         [0000]    
       
         
               
               
             
               
               
               
               
               
               
               
             
               
               
               
               
               
               
               
             
           
               
                   
               
               
                   
                 VALUE_ANCESTORS 
               
             
          
           
               
                 selected item 
                 VALUE_NAME 
                 VALUE_KEY 
                 VALUE_DEPTH 
                 name 
                 Key 
                 depth 
               
               
                   
               
             
          
           
               
                 FY1997/Q1/January 
                 January 
                 19970101 
                 2 
                 FY1997 
                 1997 
                 0 
               
               
                   
                   
                   
                   
                 Q1 
                 199701 
                 1 
               
               
                 FY1998/Q3/July 
                 July 
                 19980307 
                 2 
                 FY1998 
                 1998 
                 0 
               
               
                   
                   
                   
                   
                 Q3 
                 199803 
                 1 
               
               
                   
               
             
          
         
       
     
         [0066]    At S 710 , it is determined whether the filter object (i.e., Month) is at the same level as all the selected filter values. Each selected filter value is associated with the VALUE_DEPTH “2”, indicating the Month object in the hierarchy Year &gt;Quarter &gt;Month. Since the filter object is also the Month object, it is determined at S 710  that the filter object and all the selected filter values are at the same level of the hierarchical LOV. 
         [0067]    Next, at S 720 , it is determined whether the filtered object exhibits primary key index awareness. Assuming that the Month object is primary key aware, an SQL script is generated at S 730 . The generated SQL script includes a WHERE statement including keys corresponding to all selected filter values. The keys may be obtained from the VALUE_KEY property received at S 405 , and reflect the ancestors of the selected filter value. For example, the following SQL script may be generated at S 730 : 
         [0000]    
       
         
               
               
             
           
               
                   
               
             
             
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                 time.month_id IN (19970101, 19980307) 
               
               
                   
               
             
          
         
       
     
         [0068]    Flow then proceeds to S 415  and continues as described above to retrieve data set  1100 . In contrast to data set  200  described in the Background, which was retrieved based on the same selected filter values (i.e., “January” and “July”), data set  1100  correctly omits data corresponding to January 1998 or July 1997. 
         [0069]    If, at S 720 , it was determined that the Month object is not primary key aware (e.g., by determining that the value of the VALUE_KEY property is null), flow proceeds to S 740  to determine ancestor values of the filter values. The ancestor values may be determined from the received VALUE_ANCESTORS property. In the present example of  FIG. 10 , the ancestor values of selected value “January” in the hierarchical LOV are “Q1” and “FY1997”, and the ancestor values of selected value “July” are “Q3” and “FY1998”. 
         [0070]    An SQL script is generated at S 750  including a WHERE statement which includes a separate OR clause for each filter value and its ancestor values. With respect to the present example, the following SQL script may be generated at S 750  according to some embodiments: 
         [0000]    
       
         
               
               
             
               
             
           
               
                   
               
             
             
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                   
               
             
          
           
               
                  ( 
               
               
                  (time.month_name= ‘January’ AND time.quarter_name= ‘Q1’ AND 
               
               
                 time.year_name= ‘FY1997’) 
               
               
                  OR 
               
               
                  (time.month_name= ‘July’ AND time.quarter_name= ‘Q3’ AND 
               
               
                 time.year_name= ‘FY1998’) 
               
               
                  ) 
               
               
                   
               
             
          
         
       
     
         [0071]    This SQL script may then be used at S 415  to retrieve data set  1100 . 
         [0072]    User interface  1200  of  FIG. 12  defines a query filtered on the Month object and including several filter values selected from hierarchical LOV  510 . According to some embodiments, this query is received at S 405  with the following properties: 
         [0000]    
       
         
               
               
             
               
               
               
               
               
               
               
             
               
               
               
               
               
               
               
             
           
               
                   
               
               
                   
                 VALUE_ANCESTORS 
               
             
          
           
               
                 selected item 
                 VALUE_NAME 
                 VALUE_KEY 
                 VALUE_DEPTH 
                 name 
                 Key 
                 depth 
               
               
                   
               
             
          
           
               
                 FY1997/Q1/January 
                 January 
                 19970101 
                 2 
                 FY1997 
                 1997 
                 0 
               
               
                   
                   
                   
                   
                 Q1 
                 199701 
                 1 
               
               
                 FY1997/Q1/March 
                 March 
                 19970103 
                 2 
                 FY1997 
                 1997 
                 0 
               
               
                   
                   
                   
                   
                 Q1 
                 199701 
                 1 
               
               
                 FY1997/Q3 
                 Q3 
                 199703 
                 1 
                 FY1997 
                 1997 
                 0 
               
               
                 FY1998/Q3/July 
                 July 
                 19980307 
                 2 
                 FY1998 
                 1998 
                 0 
               
               
                   
                   
                   
                   
                 Q3 
                 199803 
                 1 
               
               
                   
               
             
          
         
       
     
         [0073]    At S 710 , and based on the VALUE_DEPTH property, it is determined that the selected filter values “January”, March”, “Q3” and “July” are associated with the objects Month, Month, Quarter and Month, respectively, of the hierarchy Year &gt;Quarter &gt;Month. Since the filter object is Month, it is also determined at S 710  that the filter object and the filter values are not all at the same level of the hierarchical LOV. 
         [0074]    Therefore, at S 760 , it is determined whether the filtered object exhibits primary key index awareness. Again assuming that the Month object is primary key aware, flow continues to S 765 . At S 765 , ancestor values of all filter values which are not on the same level as the filter object are determined. In the present example, the ancestor value “FY1997” of the filter value “Q3” is determined at S 765  because the filter value “Q3” is the only selected filter value which is not on the same level as the filter object Month. 
         [0075]    An SQL script is then generated at S 770 . The SQL script includes a sub-query WHERE statement including keys corresponding to any selected filter values which correspond to the filter object (i.e., Month), and separate clauses for each other filter value and its ancestors. The keys may be obtained from the VALUE_KEY property, and their identity is based on and reflects the ancestors of the corresponding filter values. Continuing with the  FIG. 12  example, the following SQL script may be generated at S 770 : 
         [0000]    
       
         
               
               
             
               
             
           
               
                   
               
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                 time.month_id IN 
               
               
                   
               
             
             
               
                   
               
             
          
           
               
                  ( 
               
               
                  SELECT time.month_id 
               
               
                  FROM  time 
               
               
                  WHERE 
               
               
                  ( 
               
               
                   (time.month_id IN (19970101, 19970103, 19980307) 
               
               
                   OR 
               
               
                   (time.quarter_name = ‘Q3’ AND time.year = ‘FY1997’) 
               
               
                  ) 
               
               
                  ) 
               
               
                   
               
             
          
         
       
     
         [0076]    A data set such as data set  1300  of  FIG. 13  is then retrieved at S 415  based on the generated SQL script. As shown, data set  1300  includes rows corresponding to January of 1997, March of 1997, July, August and September of 1997 (i.e., selected filter value Q3 filtered by month), and July of 1998. 
         [0077]    It will now be assumed that the query of  FIG. 12  is received at S 405  and that the Month object is not primary key aware. Therefore, none of the received filter values are associated with a key value. Flow therefore proceeds from S 710  to S 780  via S 760 . 
         [0078]    At S 780 , ancestor values of each selected filter value are determined. The ancestor values may be determined from the VALUE_ANCESTORS property received at S 405 . 
         [0079]    At S 790 , an SQL script is generated including a sub-query WHERE statement which includes separate clauses for each common set of ancestor values, with the clauses separated by the OR operator. According to the present example, the following SQL script may be generated at S 790 : 
         [0000]    
       
         
               
               
             
               
               
             
               
             
           
               
                   
               
             
             
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                 time.year_name + “ + time.quarter_name + ” + 
               
               
                   
                 time.month_name IN 
               
               
                  ( 
                   
               
             
          
           
               
                  SELECT 
                 time.year_name + “ + time.quarter_name + ” + 
               
               
                   
                 time.month_name 
               
               
                  FROM 
                 time 
               
             
          
           
               
                  WHERE 
               
               
                  ( 
               
               
                   (time.year = ‘FY1997’ AND time.quarter_name = ‘Q1’ AND 
               
               
                 time.month_name IN (‘January’, ‘March’)) 
               
               
                   OR 
               
               
                   (time.year = ‘FY1997’ AND time.quarter_name = ‘Q3’) 
               
               
                   OR 
               
               
                   (time.year = ‘FY1998’ AND time.quarter_name = ‘Q3’ AND 
               
               
                 time.month_name = ‘July’) 
               
               
                  ) 
               
               
                 ) 
               
               
                   
               
             
          
         
       
     
         [0080]    In the above example, the clause including the common set of ancestor values “FY1997” and “Q1” for selected filter values “January” and “March” uses the INLIST operator for optimization purposes. 
         [0081]    As described previously, the foregoing SQL script uses the concatenation function (i.e., time.year_name+“+time.quarter_name+”+time.month_name) in the sub-query to preserve the ancestor values of the filter value, and to thereby preserve the uniqueness of the selected filter values within the hierarchical LOV. 
         [0082]    Data set  1300  may be retrieved at S 415  based on the generated SQL script. 
         [0083]    The above description relates to execution of a report query including filter values specified by the query/report designer. A query may also be designed to prompt users for such filter values at report time (e.g., when the users refresh the report data). Process  1400  of  FIG. 14  provides presentation of such a prompt with a hierarchical LOV and execution of a resulting query. 
         [0084]      FIG. 15  illustrates user interfaces  1500  and  1550  to define a report query including filter value prompts. Referring to interface  1500 , the query is filtered on the Month object and returns result objects Year, Quarter, Month and Unit Sales. Instead of selecting one or more filter values from a hierarchical LOV as shown in  FIGS. 5 ,  8 ,  10  and  12 , the designer selects icon  1510  to invoke interface  1550 . Interface  1550  allows a designer to specify prompt text  1560  and to select option  1570  to indicate that the prompt should include a list of values. 
         [0085]    After the query is created, information engine  330  generates a corresponding SQL script such as the following: 
         [0000]    
       
         
               
               
             
               
             
           
               
                   
               
             
             
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                 time.month_id IN 
               
             
          
           
               
                  @prompt(‘Enter value(s) for Month’, ‘A’, ‘Fiscal Year\Month’,Multi, 
               
               
                  Primary_Key) 
               
               
                   
               
             
          
         
       
     
         [0086]    The SQL script is then saved with the query among report specifications  346  for later use. The @prompt syntax of the SQL script represents the prompt that will be displayed to users in order to allow the user to select filter values in the hierarchical LOV. The system will replace the @prompt syntax with the selected filter values. 
         [0087]    At S 1405 , a command is received to refresh a report including a query which is filtered on an object of a hierarchical LOV, and which includes a prompt on a filter value. The command may be received from reporting client  310  by information engine  330 , which retrieves a corresponding report specification from report specifications  346 . Next, at S 1410 , information engine  330  presents the prompt and the hierarchical LOV associated with the filter object. 
         [0088]    User interface  1600  presents a prompt and a hierarchical LOV according to some embodiments. User interface  1600 , or a portion thereof, may be transmitted from information engine  330  to reporting client  310  for display to the user who issued the command received at S 1405 . The prompt is presented via prompt text  1610  and filter values may be selected from hierarchical LOV  510 . In the illustrated example, the user has selected filter values “FY1997\Q1\January”, “FY1997\Q1\March”, “FY1997\Q3” and “FY1998\Q3\July”. This selection is received at S 1420 . 
         [0089]    Next, at S 1420 , an SQL script is generated based on the selected filter value(s) and at least one ancestor value of the selected filter value(s) in the hierarchical LOV. S 1420  may proceed in any manner, including those described herein with respect to process  700 . Therefore, if the filter object (i.e., Month) does not exhibit primary key index awareness, the SQL script may be generated as follows: 
         [0000]    
       
         
               
               
             
               
             
               
               
             
               
             
           
               
                   
               
             
             
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                 time.year_name + “ + time.quarter_name + ” + 
               
               
                   
                 time.month_name IN 
               
             
          
           
               
                  /* begin @prompt substitution */ 
               
               
                  ( 
               
             
          
           
               
                  SELECT 
                 time.year_name + “ + time.quarter_name + ” + 
               
               
                   
                 time.month_name 
               
               
                  FROM 
                 time 
               
             
          
           
               
                  WHERE 
               
               
                  ( 
               
               
                   (time.year = ‘FY1997’ AND time.quarter_name = ‘Q1’ AND 
               
               
                 time.month_name IN (‘January’, ‘March’)) 
               
               
                   OR 
               
               
                   (time.year = ‘FY1997’ AND time.quarter_name = ‘Q3’) 
               
               
                   OR 
               
               
                   (time.year = ‘FY1998’ AND time.quarter_name = ‘Q3’ AND 
               
               
                 time.month_name = ‘July’) 
               
               
                  ) 
               
               
                  ) 
               
               
                 /* end @prompt substitution */ 
               
               
                   
               
             
          
         
       
     
         [0090]    Alternatively, if the filter object (i.e., Month) exhibits primary key index awareness, the SQL script may be generated as follows: 
         [0000]    
       
         
               
               
             
               
             
           
               
                   
               
             
             
               
                 SELECT 
                 time.year_name, time.quarter_name, time.month_name, 
               
               
                   
                 sum(sales.unit_sales) 
               
               
                 FROM 
                 time INNER JOIN sales on (time.time_id=sales.time_id) 
               
               
                 WHERE 
                 time.month_id IN 
               
             
          
           
               
                  /* begin @prompt substitution */ 
               
               
                  ( 
               
               
                  SELECT time.month_id 
               
               
                  FROM  time 
               
               
                  WHERE 
               
               
                  ( 
               
               
                   (time.month_id IN (19970101, 19970103, 19980307) 
               
               
                   OR 
               
               
                   (time.quarter_name = ‘Q3’ AND time.year = ‘FY1997’) 
               
               
                  ) 
               
               
                  ) 
               
               
                 /* end @prompt substitution */ 
               
               
                   
               
             
          
         
       
     
         [0091]    Except for the comment portions, the foregoing SQL scripts are identical to those described above with respect to the  FIG. 12  example. This similarity is welcome because the query of the  FIG. 12  example is identical to the present query, in view of the filter values selected in interface  1600 . 
         [0092]    Accordingly, data set  1300  may be retrieved at S 1425  based on either SQL script. The data set may be formatted into a report at S 1430  based on the report specification, and the report may be saved among reports  348 . Thereafter, any user having appropriate authorization may operate any reporting client to request the report from information engine  330 . 
         [0093]    Flow pauses at S 1435  until a request to refresh the report is received. Upon receipt of such a request, flow returns to S 1410  and continues as described above to present a prompt based on the query, receive at least one filter value of a hierarchical LOV based on the prompt, to generate an SQL script based on the query and the at least one filter value, and to retrieve a data set based on the SQL script. The retrieved data set will reflect any changes made to the requested data since a last refresh of the report. 
         [0094]      FIG. 17  is a block diagram of apparatus  1700  according to some embodiments. Apparatus  1700  may comprise a general-purpose computing apparatus and may execute program code to perform any of the functions described herein. Apparatus  1700  may comprise an implementation of information engine  330 . Apparatus  1700  may include other unshown elements according to some embodiments. 
         [0095]    Apparatus  1700  includes processor  1710  operatively coupled to communication device  1720 , data storage device  1730 , one or more input devices  1740 , one or more output devices  1750  and memory  1760 . Communication device  1720  may facilitate communication with external devices, such as a reporting client, or a data storage device. Input device(s)  1740  may comprise, for example, a keyboard, a keypad, a mouse or other pointing device, a microphone, knob or a switch, an infra-red (IR) port, a docking station, and/or a touch screen. Input device(s)  1740  may be used, for example, to enter information into apparatus  1700 . Output device(s)  1750  may comprise, for example, a display (e.g., a display screen) a speaker, and/or a printer. 
         [0096]    Data storage device  1730  may comprise any appropriate persistent storage device, including combinations of magnetic storage devices (e.g., magnetic tape, hard disk drives and flash memory), optical storage devices, Read Only Memory (ROM) devices, etc., while memory  1760  may comprise Random Access Memory (RAM). 
         [0097]    Program code of information engine  1732  may be executed by processor  1710  to cause apparatus  1700  to perform any one or more of the processes described herein. Embodiments are not limited to execution of these processes by a single apparatus. Metadata repository may comprise any of the data described as stored in repository  340 , but is not limited thereto. Data storage device  1730  may also store data and other program code for providing additional functionality and/or which are necessary for operation thereof, such as device drivers, operating system files, etc. 
         [0098]    The foregoing diagrams represent logical architectures for describing processes according to some embodiments, and actual implementations may include more or different components arranged in other manners. Moreover, each system described herein may be implemented by any number of devices in communication via any number of other public and/or private networks. Two or more devices of may be located remote from one another and may communicate with one another via any known manner of network(s) and/or a dedicated connection. Moreover, each device may comprise any number of hardware and/or software elements suitable to provide the functions described herein as well as any other functions. Other topologies may be used in conjunction with other embodiments. 
         [0099]    Embodiments described herein are solely for the purpose of illustration. Those in the art will recognize other embodiments may be practiced with modifications and alterations to that described above.