Crosstab can't SUM on database

[Invisi]

I'm making a new report on a relational model. For this I use one measure and two dimension attributes of two different dimensions. When I put this in a list, the aggregation of the measure with a total is done fine. When I use the same three items in a crosstab, I get this error:
UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.UDA-SQL-0475 The OLAP function 'sum' contains a clause (AT or PREFILTER) that is not supported by the database.

Why does the same base query require a different type of SUM function? Also, what I notice it's talking about an OLAP function while it's a relational model. How can I make Cognos behave to use a database SUM?

[MFGF]

I'm making a new report on a relational model. For this I use one measure and two dimension attributes of two different dimensions. When I put this in a list, the aggregation of the measure with a total is done fine. When I use the same three items in a crosstab, I get this error:
UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.UDA-SQL-0475 The OLAP function 'sum' contains a clause (AT or PREFILTER) that is not supported by the database.

Why does the same base query require a different type of SUM function? Also, what I notice it's talking about an OLAP function while it's a relational model. How can I make Cognos behave to use a database SUM?

Hi,

Is the measure a real column from the underlying database or is it a value that is derived via a calculation in the Framework Manager model?

Does your crosstab have row and column summaries?

Cheers!

MF.

[Invisi]

The measure is a real column. I've made a test package from the database layer and I'm just putting three field together.

What do you mean with row and column summaries?

[Invisi]

Hello MFGF, will you answer so I can clarify or investigate?

[Lynn]

The measure is a real column. I've made a test package from the database layer and I'm just putting three field together.

What do you mean with row and column summaries?

If this is a test package then perhaps you have an issue in the model. Your design should be a star schema. Your fact query subject should be on the "n" side of all relationships. Might be worth checking the aggregate function for your measures. Check the generated SQL to determine if it is doing what you expect. Is this a DQM or CQM package? You could presumably enable local processing in your model, but it doesn't sound like something this simple would require that, which makes me think there are other issues in your model.

When you use a crosstab or chart layout in a report the query engine may spin up a mini cube which would explain the differences in query generation and reference to OLAP.

Did you create a summary on your crosstab either on rows or on columns? That is what MFGF is asking. If you did, does it work without summaries?

[khabaleshri]

 i hope setting datasource query processing property to 'limited local' should solve your problem.