How to compare the DMR Data item with Relational Data item

[Kiran Kandavalli]

Team,

I have created a data item with Descendants function which need to compared with Relational Data item.

For Example

1) the Descendants data item will bring all the levels
2) The Relation Data item will bring only the last level in each category

3) I need to compare DMR Data item = Relational Data item then I have to show Values
    else I need to show blank row with that Level Name.

I Tried as below


1) Descendants Data item
2) cast(caption([Descendants Data item]),varchar(100))
3) case
        when [Caption Data item]=[Relational Data item] then (value)
        else NULL
    end
4) it is throwing me the below error.

ERROR

The query is unsupported because there is a post auto-aggregate detail filter involving a relational function. You must replace the relational function with a dimensional function.

Can anyone suggest me the solution for this please.

Thanks!
Kiran

[AnalyticsWithJay]

Don't use cast on dimensional items :) Also, cast, along with any other function that has (!) beside it is processed locally -- meaning that Cognos will retrieve the data and then do post processing. These should be avoided whenever possible.

Since you're using it on a dimensional/dmr item, Cognos would have to retrieve/build a cube with aggregates. It's telling you it's unsupported because you're using a relational function (cast) AFTER it has auto aggregated (built cube, etc).