RESOLVED: Tabular View Results vs. Layout Results - Extra Data in List on Page

[adam_mc]

Simple Query Problem.
Data is relational.
Cognos 10.2.1.2

T1 contains multiple Weeks (and multiple entries for the same week), Product Details, Store Details, and measures for those weeks.
T2 is modeled so that it only contains a single data item and a single row for the Week that I need.
The Query Subjects are joined appropriately in Framework Manager.
The idea being that the relationship/join in FM should drive the results of the singly week that I need.

For this message board, I have a Query that simply contains the Week from both tables.
When I run to tabular data, I am seeing the results I expect:

Result:
T1 Week   T2 Week
----------    -----------
201401     201401

If I put both data items in a list, I am again seeing the results I expect - A single Week.

Result:
T1 Week   T2 Week
----------    -----------
201401     201401

However, as both weeks are the same, and "logically" I choose not to add the T2.Week data item to my list.
I now get ALL Weeks from T1.
In looking at the SQL, it has removed the use of and the join to T2 completely.

Result:
T1 Week
-----------
201301
201302
....
201401
... etc


I understand that the join is no longer needed to produce the report layout, but it is needed to produce my query results (as shown correctly in Tabular View)!

Is there a way to prevent this from happening so that in effect uses the data provided in Tabular View?
I have got round this by adding both week data items to the list and then "hiding" the T2.Week Data Item.
However, I would think there is a more constructive way to do this without having to do the extra report formatting.

Any thoughts would be greatly appreciated.
Thanks in advance,
Adam.

[Lynn]

Try adding the T2 week data item in the "Properties" property of your list.

[adam_mc]

Thanks Lynn...

I found that too this morning.
It solves the problem of having to do the additional formatting, which I like.
But to be honest, I still don't like the fact that it's forcing the list to get the same results as the Query, rather than have the Query drive the results in the list.

However, as the Rolling Stones "kinda-sorta" said:

"You can't always get what you want,
But if you try sometimes you just might find,
You get what you need (if you fiddle around with the properties)"

Thanks again,
Adam.

[Lynn]

Yes, the Stones were big Cognos fans, unlike The Doors who were distinctly in the BO camp ;)

I can see your point and it is certainly true that the query exists only to feed the layout so the SQL is generated based on what the layout calls for. Nothing more, nothing less. The upside is that you can have more than one layout share the same query, each using only the elements it needs to fulfill its mission. Sometimes this can minimize your code base and reduce maintenance effort.

Glad you got what you needed!