Method and system for guiding drilling in a report generated by a reporting system6801910Abstract A method and system for guiding drilling into a report are disclosed. Through the use of the invention, a user can drill within a first report dimension or from the first report dimension into a second report dimension. Additionally, a drill map providing a plurality of drilling paths is associated with each report. The system of the invention includes drill transformation tools and drill map management tools for assisting a user in navigating through report instances. Claims What is claimed is: Description FIELD OF THE INVENTION
Constant name Meaning Examples
DssDrillToTemplate The new report instance Replace the original
will have a template that template with
is unrelated to the template T2.
original report instance.
DssDrillToUnit The new report instance Add the "City"
will contain a specific attribute to the
template unit that was template.
not present in the Add the "Time"
original report dimension to the
instance. template.
Add the "Ratios"
consolidation to the
template.
DssDrillToChild This is a variant of Add the "Store"
DssDrillToUnit. It attribute to the
implies that the new unit template.
is a child of the existing
unit.
DssDrillToParent This is a variant of Add the "Country"
DssDrillToUnit. It attribute to the
implies that the new unit template.
is a parent of the existing
unit.
DssDrillToForm The new report instance Add the "Description"
will extend some form to the City
template unit so that it attribute.
displays one or more Add the "Image"
attribute forms that were form to any attribute
not present in the elements that appear
original report instance. under the "Interesting"
custom group.
DssDrillToDetails The new report instance Break the "Profit"
will show more metric into its
information about a constituent parts.
template unit selected by Expand the elements
the user. in the consolidation
"Ratios" into their
underlying consolida-
tion elements.
Expand the elements
in the "Interesting"
custom group
into their underlying
groupings.
DssDrillToMetric The new report instance Add the "Profit"
will contain a specified metric to the metrics
metric. unit.
DssDrillFixTemplate The new report instance The template in
will have the same unchanged.
template as the original
report instance.
1. Drill to Template A drill-to-template action means that the original template is completely replaced with a specified template. Drill-to-template will generally be used along with a DrillElements selection that gets transferred into the filter. A user would use it to get a completely different set of information about something in the original report. In order to specify a drill-to-template action it is only necessary to select a template to drill. In this particular case it is not really necessary to set the DrillType property. The drill action knows that it is a drill-to-template action when the NewObject property is set to be a template. Select drill to template DI.Action.DrillType=DssDrillToTemplate Specify the template T2 Set DI.Action.NewObject=T2 DrillTemplateUnit Property The user's selection of a template unit has no effect. DrillElements Collection The user's selection of an elements collection has no effect. Other Drill Action Properties None 2. Drill to Unit The drill-to-unit action means that a new template unit is added to the original template. Any type of template unit could be added to a template using such an action. However it will most commonly be used to add an attribute to the template. In order to specify a drill-to-unit action, all we need to know which DSS Object appears in the new template unit, so that the object can be put in the NewObject property of the drill action. In fact it is not necessary to set the DrillType property, since the drill action can deduce that it is a drill-to-unit from the type of the object assigned to the NewObject property. Select drill to unit DI.Action.DrillType=DssDrillToUnit Specify that we want to drill to the "Description" of the "City" attribute Set DI.Action.NewObject=AttributeCity DI.Action.Forms.Add FormDescription Two other drill-to-unit actions include drill-to-child and drill-to-parent. They correspond to `drill down` and `drill up` respectively. These actions are distinguished by different DrillType values (DssDrillToChild and DssDrillToParent) but otherwise behave in exactly the same way as drill-to-unit. Their purpose is to cue a user in to the `direction` of the drill. DrillTemplateUnit Property The user's selection of a template unit determines where in the template to put the new unit. By default the new unit replaces the template unit in the DrillTemplateUnit property. If the user has not selected a DrillTemplateUnit, then the new unit is placed as the last unit on the row axis. DrillElements Collection The user's selection of a DrillElements collection has no effect. Other Drill Action Properties Forms If this collection is empty, then the new template unit will have whatever display forms are the default for the object in NewObject. However most template units have a Forms collection, which can be used to overwrite the default selection of forms when displaying attributes. If this collection is non-empty, then its contents will be transferred into the new template unit. This collection defaults to empty. KeepOriginal False=Replace the user's selection of a template unit. (This is the default value.) True=Keep the user's selection of a template unit. The new template will be added immediately after it on the same axis. SortChange This is an enumeration property that records how to treat Sorts defined on the original unit. It has the following values: DssDrillKeepSortDefinition=Do not modify the original sort definition. Keeping the sort definition may change the order of the rows though. For example if the report used to list regions, sorted by some metric over regions, and the user drills down to cities, then the new report will sort the cities by the same metric. There is no reason why cities in the same region will still be adjacent to each other. (This is the default value.) DssDrillKeepSortOrder=Modify the sort in an attempt to keep rows in their original order. In the case above the sort will be modified to sort at the region level, and then to sort the cities within the region. Note that it would not be possible to do this unless KeepOriginal is set to `True`, because the sort needs to know the value at the region level. Inline True=Attempt to perform the drill only on selected elements, leaving the other elements alone. This drill transformation requires the report server 120 to contain a custom group over the old and new attributes with members from each. It is unlikely to be implemented until a later release. This option only makes sense if KeepOriginal is `True`. False=Do not inline the drill. (This is the default value.) Limitations Drill-to-unit can be used to introduce attribute, dimension, consolidation or custom group units. Use the drill-to-metric action to introduce a metrics unit. If the original template already contains the selected unit, or another template unit that is equivalent to it, then drill-to-unit will remove the old unit before inserting this unit. Two units can be different, but equivalent, if they could generate elements from the same attribute. 3. Drill to Form The drill-to-form action includes adding an additional form to a unit on the original template. For example a user might want to drill on a "User" attribute to see additional forms for each user. For example the drill action could add the "Christian name", "Surname" and "Age" forms to users, that used to be displayed with only the "Full name" form. In order to specify a drill-to-form action, it is only necessary to specify the form, or forms to drill. These forms are placed in the drill action's Forms collection. The DrillType enumeration should be set to DssDrillToForm. Select drill to form DI.Action.DrillType=DssDrillToForm Specify that we want to drill to the "Surname" and "Age" forms DI.Action.Forms.Clear DI.Action.Forms.Add FormSurname DI.Action.Forms.Add FormAge DrillTemplateUnit Property The user's selection of a template unit determines to which unit in the template the forms are added. If the user does not select a template unit, then this drill action will have no effect on the template. Similarly if the selected unit already contains the forms, then the drill action will not modify the template. DrillElements Collection The user's selection of a DrillElements collection has no effect. Other Drill Action Properties None Limitations Currently only the attribute, dimension and custom group template units have a Forms collection. Thus this action can only be applied to template units of this type. If the chosen template unit used to use its default forms, then the report server 120 will replace it with a template unit that uses the list of default forms, and also the forms listed in the drill action. In the case of a dimension, the report server 120 will have to assume it knows which attribute will replace the dimension. 4. Drill to Details A drill-to-details action asks the report server 120 to expand one of the template units to show more details about how the template unit is defined. There are three situations in which this expansion makes sense: The template unit is the metrics unit. Each metric is defined with a formula. If the formula contains other metrics, then the metrics unit can be expanded to include the other metrics. The template unit is a consolidation unit. Some consolidation elements are defined as a formula in terms of other consolidation elements. These elements can be expanded to show their base elements. Also it is possible to completely replace the consolidation with the attribute or attributes that it consolidates. The template unit is a custom group. Some groups in a custom group can be expanded into smaller groups. Again it is possible to replace the custom group with the attributes over which it was defined. To do to specify a drill-to-details action, the user sets the DrillType property to DssDrillToDetails. There are a few other properties, discussed below, that modify its effect. Select the drill to DI.Action.DrillType=DssDrillToDetails DrillTemplateUnit Property The user's selection of a template unit determines which unit in the template is expanded. If the user does not select a template unit, then this drill action will have no effect on the template. DrillElements Collection If the Elements collection contains elements from the user's DrillTemplateUnit, then the report server 120 will attempt to restrict the expansion to the chosen elements. In the case of metrics this is feasible, since metrics are listed individually in the metrics unit of a template. For consolidations the report server 120 can do this, if exactly one element is selected, since it will make this the Focus element of the consolidation on the template. The element selection has no effect on the template transformation for a custom group. Other Drill Action Properties Depth 0=Do not expand the template unit. 1=Expand the template unit by one level. 2=Expand the template unit by two levels, etc. -1=Expand the template unit as far as possible. This property defaults to 1. KeepOriginal False=If the unit is already expanded as far as possible, and if there are other units (they are always attributes) that this unit is built from, then replace this unit with the attributes. True=Never replace this unit with other units. This property defaults to True for a drill-to-details action. Limitations It is not possible, for example, to keep a consolidation unit defined over cities, and also show the complete list of cities. Such reports are forbidden, because each consolidation element is restricted to the cities from which it is calculated. 5. Drill to Metric A drill-to-metric action transforms the report instance by adding a metric to the report instance. It is similar to drill-to-unit, but is not identical since all metrics on a template appear in the metrics unit, instead of having a unit of their own. All this is necessary to define a drill-to-metric action is to specify the metric, which is put in the NewObject property. In fact it is not necessary to set the DrillType property, since the drill action can deduce that it is a drill-to-metric from the type of the object assigned to the NewObject property. Select drill to metric DI.Action.DrillType=DssDrillToMetric Specify that we want to drill to the "Profit" metric Set DI.Action.NewObject=MetricProfit DrillTemplateUnit Property If the template already contains a metrics unit, then the new metric is added to the existing metrics unit. In this case the user's DrillTemplateUnit has no effect. However if there is no metrics unit in the template, then it is added after the user's selected DrillTemplateUnit. If neither of these statements is true, then the metrics unit is added at the end of the row axis. DrillElements Collection The user's selection of a DrillElements collection has no effect. Other Drill Action Properties None Limitations If the chosen metric is already on the template, then the drill transformation has no effect on the template. If the transformation does add the metric, then it puts it at the end of the metrics unit. 6. Fix Template A template transformation DssDrillFixTemplate specifies that the action should not modify the template. A drill action that does not modify the template could still be useful, since the drill transformation would still modify the filter. It allows a user to select part of a template, and drill, to display a smaller report that only shows the selected part of the template. This template change has no parameters. All this is necessary is to assign the value DssDrillFixTemplate to the DrillType property. Specify that the drill does not modify the template DI.Action.DrillType=DssDrillFixTemplate Limitations In the fix template option, the drill action does not specify any major change to the template. However there are situations in which the template is still changed. For example if FilterMetrics is set to `True` and the user selects some of the metrics in the template, then the template will be modified to only show the selected template. 7. Common Properties The drill action properties described in this section apply to all values of the DrillType property. FilterMetrics True=Modify the template so that it only includes those metrics whose header elements appear in the Elements collection. If the collection does not contain any metric elements, then the metrics on the template are not changed. False=Do not filter the template to only show the selected metrics. This property defaults to True. B. Filter Transformations Drill action properties also effect the transformation of the filter part of the new report instance. As with the template transformation, there are a range of options that vary from making no change at all to the filter, to completely replacing it with a filter specified in the drill action. When the report server 120 applies a drill transformation it considers up to three filters. The drill action properties describe which of these filters it uses, and in some cases how to combine them. These are the three filters.
Name Meaning
DI.DrillElements The DrillElements collection on the
drill instance represents the header
elements of the grid that the user has
selected. These elements are
interpreted as a Filter, which will then
by merged into the transformed report
instance. The effect is that the
transformed report is limited to the
user's selection.
DI.BaseInstance.Filter This is the DSS Filter in the original
report instance.
DI.DrillAction.Filter The drill action may also contain a
filter of its own. This filter is used to
"govern" the drill transformation. That
is it is an extra restriction applied as
part of the drill.
1. Typical Filter Transformation Settings The following discussion includes detail about how the transformation filter in calculated, and what properties can be used to modify it. Code snippets assist in selecting the most useful combinations of properties. First of all the default settings intersect the user's selection with the old filter, and attempt to handle this in as smart a manner as possible. Set the Filter property if additional drill governing is required. Reset the drill action to its default settings DI.Action.Clear This code specifies that we want to discard the original filter. The transformed filter will be based solely on the user's selection. Discard the original filter DI.Action.ReportFilter=DssReportFilterIgnore By ignoring both filters the drill action can ask to completely replace the filter with a filter hard-coded into the drill action. In this case we want to move to filter F1. Force the transformation to use the given filter DI.Action.UserFilter=DssUserFilterIgnore DI.Action.ReportFilter=DssReportFilterIgnore Set DI.Action.Filter=F1 2. Filter from User Selection The first source of a filter is to take it from the user's selection of DrillElements from the grid. The algorithm for converting the user's selection into a filter is provided below, so that a designer can understand what it means to change the drill action properties (UserFilter, UseSchema and Relationship) that modify this process. However in most situations the default values for these properties will be the best values. Each individual element corresponds to a filter. The following table describes how each type of element is converted into a filter. The type of an element can be read from its ElementType property.
Element type(s) Filter conversion Example
DssElementConcrete A concrete element is an The element
element from an attribute. "New Haven"
A concrete element can be of the "City"
converted directly into a attribute would
filter. become
"City =
New Haven"
DssElementConsolidation A consolidation element is A consolida-
ultimately an expression tion element
over a set of joint defined as
attributes. The "[1996, DC]-
corresponding filter is the [1997, NY]"
disjunction of each set of would become
joint attributes. "(Year = 1996
and City = DC)
or
(Year = 1997
and
City = NY)"
DssElementCustomGroup All of these element types For example
DssElementResidue are produced from custom a custom
DssElementBand groups. In each case the group element
DssElementFilter element was defined using derived from
DssElementJoint a filter-type expression in a filter
DssElementSubexpression the custom group. This (DssElement-
expression is used for the Filter) is
element. converted to
that filter.
DssElementMetric These elements cannot be Not applicable
DssElementSubtotal meaningfully converted
DssElementDimension into a filter. If the user
selects them, they are
ignored.
DssElementFirst These element types never Not applicable
DssElementLast appear in a result grid, so
the user cannot select them.
If the DrillElements collection is empty, or if it only contains elements that do not have an associated filter, then the user's filter is the empty filter. The empty filter is the filter defined by the empty expression. It does not apply any restriction to the data. If the DrillElements collection only contains one element or at least only one element with a corresponding filter, then that filter is the user's filter. Otherwise several element filters combine in order to obtain the user's filter. One approach is to AND together any selected elements that are both headers of the same cell. This produces a set of conjunctions of individual filters. We then OR all of these conjunctions together to obtain the user's filter. When the user has selected elements that lie together in a tree, we can simplify the expression, because the AND and OR operators are distributive over each other. The following diagram makes this process clear.
Header elements
(The shaded elements are selected) Combined filter
F.sub.1 (F.sub.4 and F.sub.8) or F.sub.10 or
F.sub.11
or
(F.sub.3 and F.sub.7 and (F.sub.14 or
F.sub.15))
F.sub.2 F.sub.3
F.sub.4 F.sub.5 F.sub.6 F.sub.7
F.sub.8 F.sub.9 F.sub.10 F.sub.11 F.sub.12 F.sub.13 F.sub.14
F.sub.15
This process describes how to make the user's filter. In many cases it is possible to simplify the filter that would be obtained by dogmatically following this algorithm. For example instead of writing "City=DC or City=New York" the filter would say "City IN (DC, New York)". The drill action property UserFilter specifies how the report server 120 uses the user's filter. There are three options. Basically they come down to either discarding the filter, using it as it is, or attempting to handle it in a smart manner. DssUserFilterIgnore. The user filter is not considered when constructing the transformed filter. Obviously if this setting is chosen the report server 120 will not construct the user filter. DssUserFilterApply. The user filter, possibly intersected with other filters, will appear in the transformed filter. DssUserFilterRelation. (This is the default setting of UserFilter.) The report server 120 may apply a relationship operator to the user filter before including it in the transformed filter. An expression containing this operator, possibly intersected with other filters, will appear in the transformed filter. DssUserFilterEvaluate. This option is similar to DssUserFilterRelation. The report server 120 will apply (if necessary) a relationship operator to the user filter. It will then execute a report based on the modified filter to obtain a list of elements. It will place the list of elements it obtains, possibly intersected with other filters, in the transformed filter. The third case (DssUserFilterRelation) requires more explanation. In most cases it behaves exactly the same as DssUserFilterApply. However it has a special behavior when the template transformation adds an attribute unit to the template. Suppose that a user selects the city elements "DC", "New Haven" and "New York". This will generate a filter whose expression is something like "City IN (DC, New Haven, New York)". Now suppose that drill action specifies that the "Region" attribute should be added to the report and that the user has chosen to drill on the "City" attribute. In other words the user is drilling up from the "City" attribute to the "Region" attribute. Then we really want the user's filter to refer to regions, in this case the region "North East USA", instead of the cities. Indeed if the user's filter is not changed then the wrong result will be obtained, since the user will confuse a metric value calculated for the three cities with the value for the entire region. The report server 120 is not able to determine which region corresponds to the cities, since this cannot be determined without examining a look-up table in the warehouse. Instead it constructs a relationship filter which, paraphrased, states "restrict the data to the region(s) that corresponds to this embedded filter". It uses the user filter that we described above as the sub-filter. By default the relationship is established using the relationships between attributes in the schema. However it is also possible to override this by directly specifying a relationship. The drill action object has two properties, Relationship and UseSchema that can be used to specify alternative relationship. The application sets UseSchema to `False` to indicate that it does not want to use the schema relationship. After doing that it can set the properties on the Relationship object to specify the relationship. (When UseSchema is `True` the Relationship property always returns `Nothing`). specify a non-standard relationship for this drill action DI.Action.UseSchema=False Set DI.Action.Relationship.Guide=FactUnitsSold The fourth case (DssUserFilterEvaluate) is very similar to the third case. It instructs the report server 120 to do the relationship work at drill transformation time, rather than leave it for when the report is executed. This may significantly slow drill transformation (since it now requires a report execution) but it may result in a filter that can be evaluated much faster, and which in some cases may be smaller. 3. Filter from Original Report Instance The second source of a filter is from the original report instance. This will always have a filter, although of course the filter could be empty. As with the user filter, there is a property (ReportFilter) with three settings that indicate what to do with it. Again the settings come down to discarding the filter, using it as it is, or attempting to use it in a smart manner. DssReportFilterIgnore. The report filter is not considered when constructing the transformed filter. DssReportFilterApply. The report filter, possibly intersected with other filters, will appear in the transformed filter. DssReportFilterSmart. (This is the default setting of ReportFilter.) The report server 120 will attempt to remove anything from the original filter which `overlaps` with the user filter. Again the third property needs more explanation. The original report filter may contain a complex expression that (for example) resolves into a list of cities. The user selects a couple of cities, and then drills down to something else. It is very inefficient to include the complex expression in the transformed filter, since listing the user's selection of cities would lead to the same result and could be much simpler. However since the relationship between different filter expressions can be very subtle, there will be situations in which the report server 120 will not notice that part of the original filter could have been removed. Engine-light support is desirable to help make this simplification. Another issue is what to do about metric qualifications that occur in the original filter. The problem is that it is ambiguous about what a metric qualification means in the transformed filter. The enumeration property QualificationChange is used to specify what to do. Of course if the report filter is ignored, then this property is irrelevant. Suppose that the original report contains a metric qualification at the region level, and the user drills from region to city. For example the qualification asks for regions whose profit is at least 100. Then the following table lists what is done to the qualification.
Constant name Meaning
DssDrillQualOld The qualification is remains at its
original level. Thus the user is
shown cities in regions whose
profit is at least 100.
DssDrillQualNew The qualification is moved to the
new level. Thus the user is
shown cities whose profit is at
least 100.
DssDrillQualBoth The qualification is applied at
both levels. The user is shown
cities whose profit is at least 100,
in regions whose profits are also
at least 100.
In this example the DssDrillQualNew and the DssDrillQualBoth options would lead to the same result. However if the qualification at been a BETWEEN qualification, then all three cases lead to different results. 4. Drill Governing Filter The third source of a filter is on the drill action itself. The drill action object has a Filter property. If this property is assigned to a filter, then the filter will be intersected with the other filters in order to make the transformation filter. There are no flags, since if the designer of a drill action does not want to use the filter, then she would simply set the drill action's filter to `Nothing`. Specify an additional filtering restriction on the drill action Set DI.Action.Filter=FilterQualificationSalesGreaterHundred We expect this third filter to be used for drill governing. It could either be included in a drill path of some drill action or it might be added to the drill action immediately before it is applied. The former case corresponds to a situation in which the designer of a custom drill path has specified a governing filter to be specifically applied if the user takes the path. The latter case corresponds to a user who decides to apply an additional governing filter as part of her selection of the drill she wants to perform. IV. Specifying Drill Paths Drill Transformations were described above as drill action objects. A drill action is used to specify what changes are made to a report instance when a drill transformation is applied to it. It is possible for an application to perform a drill on a report instance by constructing a new report instance (the drill instance), populating it with the user's selection of a template unit and elements, and specifying the drill action properties. However this approach means that the application must know which drill actions are appropriate for the original report instance. In many cases the designer of a report definition would like to include a list of the potential drill actions for the report as part of its definition. The designer does this by associating a drill map with (the objects in) the report. The drill map contains a list of drill paths. Each drill path contains a drill action to apply if the user wants to follow the path, description information to help the user decide which is the correct path and criteria to help the report server 120 decide whether or not the path is applicable to a particular drill instance. These concepts are described below in a bottom up order, beginning with drill paths, then how a drill map can be defined from drill paths, and finally how a drill map is obtained from a report instance. A. Importance Enumeration Constant Every drill path contains an enumeration constant called Importance. This constant is used to mark out which paths the designer considers to be important. The report server 120 uses the importance constant when it is searching a path map to determine which paths to offer to the user as possible drill actions. As described above with regard to the method SearchForPaths, an application calls on a report instance to ask the report server 120 to suggest drill paths and takes a parameter. This parameter specifies whether the application wants to see one path, some paths or many paths. The designer of a drill path uses its Importance parameter to tell the report server 120 how to treat a path. The following enumeration lists the possibilities.
Constant name Meaning
DssDrillImportanceHigh The report server 120 will include this
path for all type of path searches.
DssDrillImportanceMedium The report server 120 will include this
path when searching for a few paths, or
many paths, but not when it is searching
for a single path.
DssDrillImportanceLow The report server 120 will only include
(This is the default setting.) this path when searching for many paths.
Note that when the report server 120 is searching for exactly one path, then it will return the first high importance path that it finds. Accordingly, there is no point in storing more than one high importance path in a drill map. So for example, if the designer of a drill path only wants the path to be listed as one of several paths, but does not want the path to be used for a "double-click" action then the designer needs to change the Importance property. The following code suffices. Specify that the drill path is only used when searching for more than one path DrillPath.Importance=DssDrillImportanceMedium In addition to the Importance property there are two other effects at work that modify the paths offered to a user: The way that the report server 120 searches 120 for drill maps uses the user's choice of a template unit to modify which drill map it searches. Thus there is no need for a way to modify the paths depending on the user's selection of a template unit. The report server 120 will always apply a "makes sense" criteria. This means that if the report server 120 is able to determine that for any reason a certain drill path cannot be followed, then it will exclude the drill path. For example if the drill path's action is "drill to attribute City" and City is already on the template, then the path cannot be followed. The report server 120 will automatically exclude the path from consideration, rather than bother the user with invalid paths. Other reasons to exclude a path include security settings or that the path takes the user outside the data contained within a base report. There may be situations in which a path cannot be followed, but the report server 120 will not always be able to make this determination. B. Path Presentation The point of a user selecting between drill paths is that the user is selecting a drill action. However the user is unlikely to understand, or want to understand, the various properties used to define a drill action. Instead a drill path has several textual properties whose values are intended to help a user select between different drill paths. There are three presentation properties, which are the strings listed below along with the meaning intended for each property.
String name Meaning Examples
Name A short name that encapsulates the drill "City"
path. This string should be suitable to be "Stock details"
used in a menu or list box to represent the
path. For example a drill-to-unit action
might use the name of the object that
defines the unit.
Description A detailed description of the drill path. "Drill to the
This string should be suitable to be used City attribute"
in a status bar, a tool tip, or some other "Show detailed
text box to give an uncertain user more information
information about the path. about the
history of
the selected
stocks"
SetName This string is used to group the paths "Geography"
into sets. Its value is the name of ""
the set. Two paths that have the same
SetName are considered to be in the
same set. Some paths may not have a
set name.
These properties are not used within the application program interface (API) to distinguish between different paths. Their sole purpose is to help the user distinguish between paths. Thus it is not an error if one or more of these properties are the empty string, or if two paths happen to have the same values for these properties. However this is not advisable since the user would be unable to distinguish between the paths. In addition to the string properties, we draw an application author's attention to the DrillType property of the Action on a drill path. This property, whose values are enumerated above, distinguishes between drill actions by whether they are drill-to-template, drill-to-metric, drill-to-child (i.e., drill down) etc. A user may want to view the drill paths grouped by this course enumeration. It is up to an application to determine what use, if any, to make of these properties. Preferably, the user should be first invited to select between different types of drill action. Having chosen a drill type, the user can select a set. Finally the user selects a drill path by name within the set. The user should have the option of examining the description of the path. In order to facilitate this representation, paths in a drill instance's DrillPaths collection are listed in the suggested order. That is they are ordered lexicographically by Action.DrillType, then SetName, and finally by Name. Thus the following table shows a possible set of drill paths found by the report server 120 from some drill map.
Drill Type Set Name Name
Drill up Country
Drill up Region
Drill down Store
Drill to unit Product Class
Drill to unit Product Item
Drill to unit Time Year
Drill to unit Time Month
Drill to unit Time Day
Drill to template Details Stocks
Drill to template Details City performance
Drill to template Overview Whole region
Drill to template Overview Region
contribution
C. Drill Maps A drill map is an object (drill map object 190) that holds an ordered list of drill paths. When the application wants to obtain a list of drill paths to offer to the user, it gets the list by asking the report server 120 to search through a drill map object 190. The report server 120 walks through the paths in the map, and picks out those paths that whose criteria satisfy the user's selection as recorded in a drill instance. There are two ways to specify drill paths. The simplest way is to explicitly specify all the properties of the drill path. This creates a custom drill path. However we also permit a drill map to contain constructs that the report server 120 interprets as multiple drill paths. These are virtual drill paths. 1. Custom Drill Paths The simplest type of drill path in a drill map to understand is a custom drill path. A custom drill path is defined by assigning values to the path criteria and presentation properties. Assign the properties of the path's Action property to specify the transformation caused by following the path. Suppose that DrillMap is a drill map object. This object uses an IDSSDrillMap interface. It is a collection interface that holds an ordered collection of drill path objects. The Add method creates a new drill path in the usual manner, and adds it to the collection. When a user adds a drill path to a drill map it is a custom drill path by default. The following code creates a new a new custom path. We give it a name as we create it. Make a new drill path called "Stock details" at the start of the collection Dim DrillPath As IDSSDrillPath Set DrillPath=DrillMap.Add "Stock details".1 Having made the path we need to populate its definition. The most important part of the path is its Action property. This returns a drill action object used to specify the action associated with this path. For a simple drill to template, suppose that TemplateStockDetails is a template that shows another report that lists stock details. The default filter transformation is satisfactory. Accordingly, a drill action is as follows. Specify the drill action to drill to a particular template DrillPath.Action.DrillType=DssDrillToTemplate Set DrillPath.Action.NewObject=TemplateStockDetails In order for the user to determine the meaning of the path, the presentation properties (Name, Description and SetName) of the custom drill path must be set. Specify a description and set name. (We named the drill path as we created it) DrillPath.Description="Show detailed information about the history of the selected stocks" DrillPath.SetName=" " The Importance property is optional. If the user does not set it, then it will default to low importance. This property controls whether or not the path is included when the GUI is unable to display the complete list of drill paths. Add Importance to the custom path DrillPath.Importance=DssDrillImportanceLow 3. Virtual Drill Paths The problem with custom drill paths is that they require the designer of the drill map to create a path for every possible drill action that a user can take. In many cases the designer could want a large number of very similar drill paths. It is a waste of resources to create all of these paths. Virtual drill paths alleviate this problem. A virtual drill path is added into the drill map's collection like a custom drill path. However when the report server 120 sees a virtual drill path it interprets it as several drill paths--each with a different action and presentation properties, and (usually) with different criteria. Virtual drill paths are never written into the Paths collection of a drill instance. When the report server 120 receives a match on paths in a virtual drill path, it constructs the appropriate custom drill paths to write in the drill instance. The canonical use for a virtual drill path is to specify that the drill map contains all of the drill paths defined in some (user or system) dimension. To describe a dimension with ten attributes as a drill map would take ninety custom drill paths (since there would be a path from every attribute to every other attribute). However a single virtual drill path can encapsulate all of the paths in one object by simply listing the dimension object. The report server 120 will determine to which custom paths the virtual path maps. There are several types of virtual paths. They are distinguished from each other (and from a custom path) by the PathType property. The following table lists the types of virtual paths.
PathType Meaning Example
DssDrillPathCustom This is a custom path. Include the
"Stock details"
custom drill
path.
DssDrillPathDimension The virtual drill path is Include all the
specified using a drill paths defined
dimension. It contains by the system
drill-to-unit custom dimension.
paths for each pair of
attributes in the
dimension.
DssDrillPathDetail The virtual drill path is Include all the
specified using the drill-to-form
standard template paths for forms
unit criteria. It in the "City"
contains custom paths attribute.
that give more informa- - OR -
tion about the selected Include all drill-
units. to-form paths for
These include drill- all forms in all
to-details and drill- attributes on the
to-forms actions. report instance's
template.
DssDrillPathUseMap The virtual drill path Include the
consists of a reference "Project default
to another drill map map" drill map
object. We want to object.
include all of the drill
paths in the other
drill map.
The report server 120 and specifically drill map management tools 140 treat each virtual drill path as if it were a collection of custom drill paths. In the following sections we will discuss each type of virtual path in turn, and specify what properties the report server 120 assigns to the custom paths it makes from them. A virtual path has the same drill path properties as a custom path. As we have listed below the report server 120 assigns some of these properties when it makes the custom paths. Those properties that we don't mention below are given whatever value was in the virtual path. For example the report server 120 does not assign a value to the KeepOriginal property of a drill action made from a dimension virtual path. So the designer of the drill map can specify whether or not a dimension path always keeps the original template unit on drilling, or never does. However if the designer wants to keep the original unit for some dills inside the dimension, but not for others, then the designer cannot use virtual drill paths. Instead, the designer must put in a custom drill path which explicitly specifies every desired action. a. Dimension Virtual Path The dimension virtual path specifies that we want to include all of the paths defined between attributes in a dimension. The dimension can be the system dimension (that contains the schema relationships between attributes) or it may be a user dimension (that contains an arbitrary hierarchy on attributes). There are only two things that need to be done to insert a dimension virtual path into a drill map. First the path needs to be added. We specify the path type instead of a name. Then we need to set the dimension of the new path, by assigning it to the PathObject property. If desired, other properties of the virtual path may also be set to non-default values. Insert a dimension virtual path at the end of the map's collection Set DrillPath=DrillMap.Add DssDrillPathDimension Specify the dimension to be the (user defined) Geography dimension Set Drill Path.PathObject=DimensionGeography The report server 120 treats a dimension virtual path as if it contains a custom drill path for every order pair of distinct attributes in the dimension. The path is a drill-to-unit path from the first attribute in the pair, to the second attribute. It also contains custom drill paths from every attribute that appears on the template to every (different) attribute in the dimension. Observe that since a user dimension does not have to be symmetric, it is possible that A is a parent of B, while B is not a parent of A. This will not happen for the system dimension. The following table lists the properties of the custom drill paths that are assigned by the report server 120, and how it determines them. All of the other properties of the custom drill path are given the same value as on the virtual drill path.
Property Value Source
Action.NewObject A DSS Attribute object. Every attribute in the
This is the attribute dimension.
that will be added to
the template.
Action.DrillType The direction of the drill Unless there is a
action. It is one of the (possibly indirect)
following values: - parent-child
DssDrillToParent relationship between
DssDrillToChild Action.NewObject and
DssDrillToUnit. UnitDetail then this
property is set to drill-
to-unit.
Action.Forms The collection of display This is read from Forms
forms for the new collection of the new
attribute. attribute on the
Dimension object.
Importance Indicates if the path can If the new attribute
be found by a search for is the default child
a single path. It is one of the original attribute
of the following values: - then we use the high
DssDrillImportanceHigh setting.
DssDrillImportanceLow Otherwise we use the
low setting.
Name The name of the new Read from the attribute.
attribute.
Description The description of the Read from the attribute.
new attribute.
Detail Virtual path. All that needs to be done to create a detail virtual path is to specify its type. The detail is applied to all applicable objects. That is, if this virtual path appears on a map on an object, then it only applies to the object. If it appears on the main map of a template, or on the map of the project object, then it applies to all objects in the template. Thus the following code inserts a virtual drill path based on the `City` attribute. This virtual path will be treated as drill-to-form paths for each of the (not already displayed) forms of the `City` attribute. Add the virtual path to the map Set DrillPath=DrillMap.Add DssDrillPathDetail Attach the map to the City, so it on only applies to cities Set AttributeCity.DrillMap=DrillMap For a detail virtual path the report server 120 constructs custom paths separately for each appropriate template unit. The following table describes how it does this for attribute unit. We create one drill-to-form path for each form that the attribute supports.
Property Value Source
Action.DrillType Always set to Drilling to forms is the
DssDrillToForms. only way to increase the
detail on an attribute.
Action.Forms Always contains a single Read from the collection
DSS Attribute Form object. of forms supported by
the attribute.
Name The name of the new form. Read from the form.
Description The description of the Read from the form.
new form.
A consolidation only has a drill-to-detail action, since consolidation elements do not have forms. There is no point in using a virtual path, unless it applies to all consolidations.
Property Value Source
Action.DrillType Always set to Drilling to details is the
DssDrillToDetails. only way to increase the
detail on a consolidation.
Name The name of the Read from the
consolidation consolidation.
Description The description of Read from the
the consolidation. consolidation.
Custom groups are treated in the same manner as consolidations.
Property Value Source
Action.DrillType Always set to Drilling to details is the
DssDrillToDetails. only way to increase the
detail on a custom group.
Name The name of the custom Read from the custom
group group.
Description The description of the Read from the custom
custom group. group.
c. Map Virtual Path The map virtual path specifies that we want to include all the drill paths defined from some other drill map object. This feature allows a designer to construct one drill map that contains the `standard` drill paths, and than use a specialized map to add a few additional paths, and then revert to the original map. To specify a map virtual path, the user must first create a drill path in the usual manner, and specify that it is a map path. Then use the PathObject property to specify the drill map for inclusion. Insert a map virtual path at the end of the map's collection Set DrillPath=DrillMap.Add DssDrillPathDetail Specify that the MapDefault should be searched Set DrillPath.PathObject=MapDefault When the drill map management tools 140 of the report server 120 encounter a map virtual path, they will examine the virtual path's criteria. If the criteria match the current drill instance settings, the drill map management tools 140 will search the map next. After completing the map search, the drill map management tools 140 will return to the original map object, and start searching at the next place after the map virtual path. If the drill map management tools 140 skip over a map, without searching it, if the criteria don't match. For example the following code modifies the drill path so that the MapDefault object is only searched if the user has selected an attribute. Specify that the user'template unit selection must be an attribute DrillPath.UnitCriterion=DssDrillUnitType DrillPath.UnitDetail=DssTemplateAttribute Once the drill map management tools 140 have decided to search another map object, they treat the paths in the object in exactly the same manner as the paths in the original map object. All of the properties of the custom drill paths that the drill map management tools 140 obtain eventually obtains come from the dependent map object. In particular the contents of the virtual path's Action, Name etc. properties are not used. 3. Drill Map Selection A drill map is an object that is saved into metadata as a separate object. This allows a designer to share drill maps between reports. A Drill Map object is made in the usual manner by calling the NewObject method on the DSS Object Server, and asking for a DssTypeDrillMap object. Dim DrillMap As IDSSDrillMap Set DrillMap=ObjectServer.NewObject DssTypeDrillMap Clearly, the first path of using a drill map is that it needs to be created, populated, and saved. However, since a project may contain many drill maps, the report server 120 needs to know which drill map to use when it executes a particular report. The application may pass a drill map directly to the report server 120 as a parameter when it asks to perform the search. However this approach is only intended for situations in which the application has some non-standard method to determine what drill map to use for a particular drill instance. The drill map management tolls 140 of the report server 120 find a drill map by looking in several places. Each place may or may not contain a map. As soon as the report server 120 examines a place that does contain a map, it will select that map. Once it has found a map it does not go on to examine the other places in the sequence. The report server 120 searches in a different place if there is a template unit, than if there isn't one. If there is a template unit, the drill map management tools 140 look in the following places: the DrillMap property of the template unit on the original report instance that the user selected. If the unit is based on a single object (attribute, dimension, consolidation or custom group) then the drill map management tools 140 will look at the DrillMap property of that object. This isn't possible if the user drills on the metrics template unit. Finally the drill map management tools 140 look at the UnitDrillMap property of the project object. Alternatively if the user has not selected a template unit then the drill map management tools 140 look in the following places: The DrillMap property of the template. The DrillMap property of the project object. For example if a designer wishes to specify a drill map for a template, the designer would execute the following code. The report server 120 would use this map provided that the user has not selected a template unit. Specify the drill map of a template Set Template DrillMap=MyFavouriteDrillMap 4. Default Drill Paths Different measures must be taken if the drill map management tools 140 cannot find a drill map, or if the drill map on the project object. If an application dislikes this behavior it should provide its own drill maps at either the level of the object being drilled upon, or at the template level. a. No Drill Map at All The absence of a drill map should be an infrequent occurrence. The behavior of the report server 120 depends on the type of template unit that the user is drilling into. Attribute. The report server 120 will pretend that it found a drill map whose sole contents was a dimension virtual path based on the system dimension. This will permit old style drilling to other attributes, but nothing else. The attributes will not be grouped by dimension, since the system dimension contains all of the attributes. It is likely that the user will not get a good drilling experience. Custom group or consolidation. The report server 120 will pretend that it found a drill map that contains drill paths to the underlying attributes of the custom group or consolidation object. It will identify these as drill-to-child maps. Metric. The report server 120 will generate a DrillToDetails path for any metric added to the collection of drill elements. None. The report server will also pretend that it found a drill map with one dimension virtual path based on the system dimension. In other words it will offer a drill-to-unit for each attribute. As usual a drill path will not be shown if it would lead to an invalid report because of repeated use of the same attribute. When drilling from an attribute a drill-to-unit will be shown as a drill-to-child or drill-to-parent as appropriate. b. Project Level Drill Path More typically, the report server 120 will find a drill map on the DSS Project object. This drill map will contain a list of dimensions as virtual drill paths. These are the dimensions that the administrator wants to use when defining drill paths. Any type of dimension can be used as a virtual drill path. Again the behavior depends on the template unit from which the user wants to drill. Attribute. The report server 120 will list drill paths to all attributes that appear in the listed dimensions. Where appropriate it will use drill-to-child or drill-to-parent, but most of them will appear as drill-to-unit. Custom group or consolidation. The report server 120 will draw drill paths from two sources. From the drill map it will obtain a list of attributes. These will be shown as drill-to-unit paths. From the custom group or consolidation it will also obtain a list of attributes, which it will show as drill-to-child paths. The latter will be at the higher priority. Metric. The report server 120 will generate a DrillToDetails path for any metric added to the collection of drill elements. In other words the drill map is not used. None. The report server 120 will offer a drill-to-unit for every attribute in a dimension in the drill map. As usual a drill path will not be shown if it would lead to an invalid report because of repeated use of the same attribute. The above-mentioned operations can be performed on both base reports and subset reports. A subset report is a report that is defined as containing a subset of the data of some other report, known as its base report. A subset report is evaluated by first evaluating its base report on the RDBMS in the usual manner. Then the Analytical Engine extracts the subset report from the base report, without using the RDBMS at all. The advantage of this approach is that if many subsets are defined off the base report, considerable RDBMS work can be avoided. The report server 120 understands the subset report mechanism, and by default will attempt to make use of it. This is controlled by the drill instance property UseSubsetReports, which defaults to `True`. If an application wants to turn off the special behavior described later in this section it must modify this property before calling the report server 120. Disable report subsenting support in the report server DrillInstance.UseSubsetReports=False The report server 120's support for subset reports takes two forms. Note that this support only applies to subset reports. Drilling on a base report will always obtain another base report, regardless of the setting of UseSubsetReports. 1) When searching for drill paths on a subset report, the report server 120 will not suggest drill paths that cannot be followed using the data in the base report. 2) When applying a drill action to a subset report, the report server 120 will construct the new report instance as a subset report of the same base report that the original report used. The objective of (2) is speed. Since the base report was recently executed (to make the original report), it is highly likely that the Report Server 120 will find it in its cache. So the drilled report can be generated immediately by analytical engine 104 from cached data. The objective of (1) is not to confuse the user by offering a drill path that cannot be followed properly. For example, suppose that a base report contains `Country`, `Region` and `City` attributes, and that the `Store` attribute is a child of `City` in the schema. A subset report of the base report contains `Country` and `Region`, but misses out `City`. The drill contains a virtual path for the entire dimension. The following table shows what happens.
With UseSubsetReports
Default set to `False`
Drill to `City` Available Available
(City is in the base
report)
Drill to `Store` Not available Available
(Store is not in the base (The base report is irrelevant)
report)
The report server 120 will always construct a subset report when drilling into a subset report with UseSubsetReports set to `True`. It is possible that the drill instance's DrillAction leads to the production of a report that cannot be evaluated using data in the base report, because either the application turned off UseSubsetReports when searching the drill map, or it modified the action itself. In these cases the drilled report will execute, but it will be returned with `faked` data. This feature is provided so that a user can examine the format of a report drilled "outside the box" before deciding whether or not to execute the report against the RDBMS, If a user wants to drill on a report instance that was itself made by drilling into a report instance, two questions require consideration: what drill map does the new report definition use, and what support is available for remembering the drill history of the new report. There is no difference as a report between a report instance made directly from a report definition (or a filter and template) and a report instance made by applying a drill transform to another report instance. In both cases the report instance is defined from a report definition object, which in turn contains a filter, and one (or more) templates, although when a report is made by a drill transformation the DSS Objects will not be saved in a metadata repository. Of course the second report instance (the drill instance) will have non-trivial values in its Drill . . . properties, which record the settings used to make the report definition, filter and template. The report sever 120 does not consider the origin of a report instance when drilling into it. Its sole concern is to examine the definition of the objects that it finds in the report instance, and apply a drill action to them. So to explain what happens on a subsequent drill, all we need to do is to explain what drill map the report server 120 attaches to the new report instance. The report server 120 does the following things when it makes a new report instance: If the drill type is drill-to-template, then the new template will be the specified template. The report server 120 will not edit the template. Otherwise it will attempt to carry forward the drill maps from the original template to the new template. The template's DrillMap is carried directly over. The drill maps on the template units are carried over if some template unit appears in both templates. If a new template unit appears in the new template, then it will never have a drill map. The report server 120 will use the default map of the object in the template unit if the user drills again on the new template unit. A report instance that was made by a drill transformation records how it was made. This allows an application to support a `drill back` functionality in which a report instance is replaced by the report instance from which it was drilled. However when a report definition is saved into metadata from a report instance, this information is not saved with it. The drill history of a report instance can be found by examining its Drill . . . properties (DrillTemplateUnit, DrillElements etc.) On a usual report instance they are empty. If a report instance was made by a drill transformation, then they will contain the properties used to specify the drill transformation. The BaseInstance property returns the original report instance. Normally this property is used to contain the base instance of a subset report instance. However if a report instance was made by drilling, then either it is not a subset report, or its base report is the same base report as its original report used. So the following function implements a single step `drill back` functionality. It returns `Nothing` if the report instance was not made by a drill action. Sub DrillBack(RI As IDSSReportInstance) As IDSSReportInstance If RI Is Nothing Then Exit Sub Was this report instance made by drilling? If RI.DrillAction Is Nothing Then Set DrillBack=Nothing Else Read the original report instance from the drill instance Set DrillBack=RI.BaseInstance End If EndSub Executing Drillback(DrillBack(RI)) would move back two steps in the drill history of RI. It would return `Nothing` if RI was not made by two successive drills. The system stores the entire drill instance so that an application has access to the complete history of the drill. Thus in addition to obtaining the original report, the application can also discover the drill action, and the user's selection of elements and template unit. When a report instance is moved around, or stored, we will store the drill history with the report instance. This means that we will also store all the previous report instances used to make the report instance. However we will not store the data obtained from running the report instances, since this would make the top report instance far too large. The data will almost certainly be in the report cache since the user must have recently executed the report instances to obtain the report instance into which she drilled. The drill history is part of the report instance, not part of the report definition. This is because drilling is performed as an analysis action, not as report construction. If a user chooses to save a drilled report instance as a new report object, the drill history will not be saved as part of the new report. The new report will behave exactly as if the user had constructed it directly. FIG. 6 is a diagram summarizing the properties on the interfaces introduced in this document. This diagram shows the IDSSReportInstance and the IDSSDrillAction interfaces. FIG. 6 only shows the report instance properties that are used for drilling. A drill instance 400 is associated with a drill action 402. The drill action 402 is shown in conjunction with a new object 404, forms 406, a selected form 408, a filter 410, and a relationship 412. Also shown are a report instance 414, a template 416, a template unit 418, drill elements 420, a selected element 422, drill paths 424, and a selected drill path 426. FIG. 7 shows the IDSSDrillMap and IDSSDrillPath interfaces. Observe that IDSSDrillMap is really just a collection of drill paths. However since a drill map is a DSS Object, it has, as do all DSS Objects an IDSSObjectInfo interface. FIG. 7 shows a drill map 500, a drill path 502, an action 504, an element 506, a path object 508, and info 510. In summary, in order to support drilling, the system contains facilities to construct a new report instance, based on an existing report instance and a user's selection of elements and template units the report instance. This process is called a drill transformation. Since there are many different ways to transform a report instance into another report instance, the system provides a way for the creator of a report to associate many possible drill actions with a report at definition time. When a user wants to drill a report, the user is invited to choose between these drill paths. Since a complete specification of the drill paths on a report can be complex, the system provides default drill paths based on the definitions of objects used in the report. The present invention is not to be limited in scope by the specific embodiments described herein. Indeed, various modifications of the present invention, in addition to those described herein, will be apparent to those of ordinary skill in the art from the foregoing description and accompanying drawings. Thus, such modifications are int | ||||||