Multi Fact Crosstab errors

[casey.smith]

After working some magic with conformed dimensions in a DMR model, I've finally come down to a stumper...I'm tempted to say it's a bug in Cognos itself but I wanted to run the scenario by the community here and see if someone has some experience/insight with the same problem.

Consider the following scenario:

3 facts need to be reported on a crosstab spanning a geographical and time dimension, but each of the facts is only desired on a subset of the members of the time dimension: i.e. Fact 1 for 3 years ago, Fact 2 for 2 years ago and last year, Fact 3 for this year. The natural layout for authoring the report is to run the geographical dimension down the side, drop each member of the time dimension side-by-side along the top, and nest its associated fact underneath.

This particular layout breaks the report ("GEN-ERR-0001 Expected query result 'R2h0' is not found in the response of 'OlapQueryProvider' RSV-SRV-0042...."). However, a number of variations work just fine (but don't meet the client's reporting requirement).

If the entire time dimension span is used rather than individual members, and all 3 facts are nested beneath it, the report runs fine, with each aggregated properly. But this leaves a ton of extraneous columns for years where certain facts are not desired.

Any 2 of the time dimension members can be included explicitly with their associated facts, but not 3 or we get the error above.

Any 1 of the facts can be associated with 2 or more members of the time dimension so long as the 2nd fact in the query is added without reference to the time dimension at all (which aggregates over the entire timespan, ruining the report but nevertheless showing one more angle that is very close to the desired behavior).

If 2 facts are included in the report, with one associated to 1 or more members of the time dimension and the other associated to 2 or more members, the report is broken with a new error: "RQP-DEF-0177  An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'. UDA-SQL-0358 Line 33: Syntax error near "=". RSV-SRV-0042..."

In all the cases that work I have inspected the SQL for the queries and they provide the exact same result set that would be needed for the final report. It seems to me that all Cognos should do is run the same query and pluck another of the sums to display on the report. For some reason though, it will do that for 2 but not 3+ (facts or dimension members).

I could probably take draconian measures to solve the problem (i.e. create a model query subject for each fact for every year so each can be put in the report w/o reference to the time dimension), but I would prefer not give up like that.

Any help would be greatly appreciated.

[goose]

I tried what you are describing on the "GO Sales" DMR sample package and it was working ok - that is if I understood your question right. Can you try and replicate it with the "GO Sales" package and post the report XML?

I you look at the attachment 2004, 2005 & 2006 have been dragged in seperately and then various measures nested underneath each year (there wasnt a geographic dimension so I used product for rows). Anyhow hope you come right.

[faris]

I am also facing a similar problem.  ???

Instead of adding 3 years,try to add revenue,quantity and Margin under 1 year, say 2007.