Easiest way to remove query items that are no longer used?

[Ogram]

Hey gurus,

I've been working on a rather complex active report for some time now and due to several changes to an already agreed on design and functionality and prolific use of cut/copy/paste (deadlines  :-\) my current report is left riddled with a considerable amount of query items that are no longer needed on a report. As the report is as good as complete (finally), I'd like to optimise the queries as well as possible. The first step would be to clear out all the unneeded query items. My question is - is there an easy way to do this? I could always check them item-by-item by searching for query references but... let's just say that there are a lot of query items to go through.

Regards,
Jussi

[pumccg]

Their presence might make the query look busy, but redundant query items don't actually get requested from the database anyway. If the query engine can't see a use for a query data item it is excluded from the query actually sent to the database.

[Lynn]

Even though they don't get sent to the database I'd suggest the effort of cleaning them up is worth the effort to improve maintainability. Should issues or requirement changes arise down the road you'll save yourself, or someone else, some effort by clearing out the clutter.

I don't know of any way to do this other than to go through them one by one. Typically I will first identify the ones I'm using and organize them in an order within the query that follows the layout(s) or otherwise groups them in some logical fashion. Then I go through any remaining ones to verify if they are used or not. Sometimes they might be in a conditional style or a variable or something not immediately visible in the layout.

Of course doing this as you are building the report is really the best thing if you can manage it. Kind of like "clean as you go" when cooking :)

[CognosPaul]

Some of my reports tend to have macros that dynamically pick columns in the query. If your report is like that, then don't use the solution I'm about to suggest.

Create a new page. In that page, drag a query in from the insertable objects panel. Click on the left-most column, and shift click on the right most column. Press the delete button. If a field is being referenced anywhere, it should not be deleted.  Run and test extensively to make sure you didn't break anything.

I believe there is also an option under tools->options to automatically delete unreferenced data items, but I'm not at a Cognos install so I might be lying.

[navissar]

Just my two pence: To add on top of what Lynn said, it is also not entirely true that unused queries have zero impact on the report. They impact the size of XML sent to the query engine for processing. This may seem like a minute effect, but it can in fact be quite identifiable, especially if there are many such queries and your servers are busy.

[pumccg]

There's no doubt that unused data items in the queries should be removed as, if nothing else, they cause future confusion. There is a feature as CognosPaul suggested in Tool Options Report called "delete unreferenced query items", which it selected by default. However from memory it doesn't delete them from the query in Query Explorer. I think this is what enforces deletion of unreferenced query items when the query is finally submitted by the query engine.

I don't think there is any simple way within Report Studio to do it. It's trial and error. Another such way is to compare the final projection list query items for each query in Tools "Show SQL/MDX" with the query items back in the Query Explorer. The latter will include unreferenced items, whereas the former won't (I think).

[a3nCognosUser]

I know this is an old post but I am still struggling with this same problem. In the 10.2.2 version that I use you can find out field by field that they are not used anywhere but I am looking for the nice red button that would identify all those and remove them all at once.

Perhaps there is a third party app that can do this? I was hoping Motio could do it but I did not find it in there either.

Any new ideas for the same (age old) problem?

Thank you. Adrienne

[Lynn]

I think the solution Paul posted works perfectly, although you'd have to do it for one query at a time:

• Create a new page
• Drag a list onto the page and associate the list to the query that needs tidying
• Go to the data items tab of the insertable objects pane and drag every item into the list
• Click the first column and ctrl+click the last column, then click the delete key
• Remove the page when complete

The above steps will remove all unreferenced query items, including summary items, that are not referenced anywhere else. Note that this is true only when the option "Delete unreferenced query objects" is selected, which it is by default. To find this setting, go to Tools > Options, and then click on the Report tab.