I have a report requirement to show and hide columns in a list report.
I used a prompt and render variables to show and hide the columns. I noticed that even when columns did not render, they were still being included in the query and effecting the results. I was under the mistaken impression that when a column was not conditionally rendered, the query item would not be included in the query that was executed.
I worked around it by putting a case statement that would return the data item when the column was chosen and returning null otherwise.
Does anyone know of a different way to not include a data item in the query when the column is not rendered?
Hi,
If it is cognos 10, you no need to create additional variable. Its there in list properties called Render. and then List Properties Under Data select properties, in that you select only as columns you want.
thanks,
Suresh.
??? The idea of posting in a Cognos 8 forum is, that it ain't Cognos 10, Suresh.. ;)
I'm not sure I understand the requirement. Are you expecting another execution plan from not fetching the dataitem? I/O benefits?
The hidden column (non rendered) column being included in the query is causing duplicated records.
My impression was that render variable will prevent execution of query, not columns within the query. So, if there are 2 queries in the Page (one for List Report and another for a Crosstab) and based on the selection from the Prompt, if the 2nd query is not to be rendered, then Cognos will execute only the 1st query.
As I understand it rendering only relates to the items displayed on the page or to the pages that get dispalyed, it doesn't remove columns from the query.
If you only need to have a few columns shown/hidden and not included in the query I think the other option is to have different queries on different pages and render the page based on a prompt.
Hi,
When you have 20 columns in your list, and you have used Render variable property on every column, these columns will be included in your query even though the columns are not rendered in your output. You may get repeating/duplicate records in your output because of this issue.
Instead of render variable, you chose style variable and use box type = none property if you dont want the column to be rendered (this will not be included in your query when not rendered).
Regards,
Manish | +919673990644
www.infocepts.com | mtirpude@infocepts.com