Report SQL

[kalyan_y]

Hi All,
 
I placed one dimension attribute and 5 facts in the list.
But the List query has got 3 more dimension attributes.
I have prompts on these 3 dimension attributes, and I have optional filters for these.

When I look at the Generated SQL, joins are not happening for the tables which contain these 3 dimension attributes, rather join is happening only for the tables,from which I placed the items on the list.

When I place these 3 dim. attributes on the list, then join is happening. But I dont want to place these dim. items on the list.

Please suggest...

Thanks in advance,

[AussiePete2011]

Hi there.

You can add the query items in as properties in the report.
If its a List, the select the whole list so that the properties are focused on the "list"
Under the Data level in the properties you will see  "Properties"
Click on this level and select the query items you want or need in the SQL statement.

Hope this helps
Cheers
Peter

[PRIT AMRIT]

Or you can simply put these 3 dim. attributes in your query the list report referring to.

[blom0344]

Early 8 versions retained unused dataitems in queries. For some reason Cognos decided to automatically remove unused query items in later 8 versions, forcing us to add them to the layout. Wish they had kept the early 8 behavior. I suppose they do not trust us to efficiently make our selects   ::)

[kalyan_y]

Hi,

I have value prompts with auto-submit set to Yes for these optional filters on the report page.
When I run the report and select a value for any of these prompts, report data is not filtered.
I looked at the Native SQL/Cognos SQL and thought the issue could be due to the joins not happening.

But now, I got this issue resolved.

Problem is, I have my optional filter expression as [a] = ?p_a? and [d] = ?p_d? and [c] = ?p_c?
where a,d,c are my 3 dim attributes,

Instead of one filter exp, I have made 3 optional filter expressions and then it worked!!!!!!!!!
[a] = ?p_a?
[d] = ?p_d?
[c] = ?p_c?

Not quite sure, what is the difference between these, but worked...

Sometimes, Cognos feels alone and likes having our company