relational vs. dmr -> Max. retrieved Data

[charon]

HI guys,

im wondering...ive activated a limit for retrieved data in the governour in framework manager, and apparently the kind of report has a huge impact on this restriction.

Environment: C10.2.1
DMR - Model

In report Studio, when i use a crosstab, im geting the error message because the data row limit in the FM.
When i use a list instead a crosstab, the report runs fine. In both cases, im using (detail-)filter expressions for the query.

Now the kicker...ive analyzed the generated SQL and apparently, though the query itself is filtered, the data im using for the rows in the crosstab (e.g. "products" of the product-line hierarchy in the ibm demo db) will not be filtered! Means, it requests a data set for all products, and then the filter (slicer) will be done.

This confuses (and upsets) me a lot...though i understand that cognos generates a temp. cube for the crosstab, i thought the filters of the query are active no matter what...but now it seems, the filter conditions do not apply in a way i have expected.

For me that means, i have to find a way to redesign the report, or take out the 250 k rows restriction...or are there further approaches?

Thx a lot and have a nice day :)
br
charon

[bdbits]

I think your problem may be due to using detail filters on a dimensionally-modeled source. Try using filter() expressions on the data items instead.

[charon]

Hi bdbits,

ty for the reply. I did use the filter -expression in the data item for the rows, but -because i am using a lot! (about 9) of filters with parameters- i dont like that solution that much.

Are filters the only way to filter the result set for the crosstab? :o

[bdbits]

Slicers work, but I must confess I don't much use them as my old brain finds it difficult to construct dynamic expressions that work in a slicer. I am sure it is possible, just never got the hang of it.

The big thing is to not use the detail filters as they will be applied after the retrieve, like you saw. At least, that is my experience.

[charon]

The problem is, the detailfilter works (of course) if i dont use a crosstab but a list, i guess because cognos dont need to run the olap engine for a list and therefore does not create the temp. cube.

Slicer didnt work for me either...i guess because cognos needs to get all members first, and uses the slicers then to "slice" the data you need...

Do you know how to combine multiple filter conditions in one expression?
regards  :P
mike

[Francis aka khayman]

filter(filter(filter([a];[value] > 0); [condition 2] is not null);[still another condition] < 3)

Do you know how to combine multiple filter conditions in one expression?

[bdbits]

Yes I agree the way crosstabs are handled probably explains why the list works but the crosstab does not. I suppose since it is a 'temporary cube' you are right about the slicer processing as well. In a normal cube the slicer gets incorporated into the MDX, but this is a bit different.

A Cognos filter() is defined as filter(set_expression,Boolean_expression). The last part, the boolean_expression, can be anything as long as it evaluates to a boolean. Of course it must make sense for the data items involved. The filter parameters are Cognos expressions, so you can nest expressions or make them as complicated as you like. So for an example, here is one I just pulled from a report based on an actual cube (not just a crosstab) but I think it is a useful example and applicable.

    filter(children(#prompt('pBiennium','memberuniquename','item([PR_Cube].[All Time].[All Time].[Biennium],0)')#),[Payroll Amount] is not null)

This incorporates a prompt macro to pull out member of a user-chosen biennium level (part of a time dim), or if none is chosen the first member (most current). The boolean_expression filters out the set members where the related measure [Payroll Amount] is null. This is a pretty straightforward example, but I could not recall at this moment where I have some of the more complicated boolean expressions to filter based on multiple items. khayman's post would be one way to AND the conditions; as long as the expression evaluates to a boolean you should be fine. And of course the involved data items need to be related via the measure. (Well, you could bind them to an edge item with properties and use them, but I am getting off on a tangent.)

I'm not all that accomplished a report author, I am more of a modeler/infrastructure guy, so if somebody sees something misleading in what I've said, please jump in! This is my general approach and has worked for me.

[charon]

Hi bdbits,

ty for the informational post, nice explanation of the "filter" expression. In the end, i guess il have to evaluate if i have to create complex filter expressions for the rows or if i create a workaround concerning the limiting data retrieving restriction in the governour of the fm model.

thanks you all for sharing your knowledge  ;D
regards
mike

[charon]

Hi,

for some further information on the crossjoin-topic in olap queries, take a look at:
http://public.dhe.ibm.com/software/dw/dm/cognos/reporting/performance_and_tuning/writing_efficient_olap_queries.pdf
BR

charon :P