Limitations When Filtering Data Using an SAP BW Data Source

If you apply a filter and a sort to an item that corresponds to the leaf-level of a recursive hierarchy, siblings may appear in the report even though the aggregated values are correct. Siblings are characteristic values with the same parent as the filtered member.

Each level in an SAP BW hierarchy has an item with the same name as the level and a role of _businessKey. Such items are known as level identifiers. The level identifier must be an exact value for the operators =, <, and > to work. For example, for the filter [Office] > 'Chicago' to work, the value 'Chicago' must exist in the data source. If you do not know the exact values, you can apply the filter to one of the attribute items associated with the level, such as [OfficeLongName] > 'C'. Filters on non-identifiers are possible, but they are slower because SAP BW data sources are optimized for queries based on level identifiers.

When filtering time-related data, only the level identifier items of the time-related characteristics in SAP BW, such as 0CALDAY and 0CALMONTH, should be used for performing anything other than equality filters. All other (attribute) items in these hierarchies are formatted string representations of the characteristic values with which they are associated. These formatted values sort alphanumerically and not chronologically.

Using Prompt Expressions in Filters

If you create a filter expression using report item attributes, such as the following, no data is returned.

[ report item attribute ] = ?prompt?

To resolve the problem, do one of the following:

• Fully qualify the item by using items from the source tree instead of data items from the report.
• Use prompt controls in the report instead of hand coded prompts.