cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Cognos Improvement by narrowing query

Highlighted
Level 11

Cognos Improvement by narrowing query

I'm starting to explore Cognos for this report management wants ASAP. I'm trying to generate reports based on Query Builder queries and I'm running into what looks like some performance issues. I think I've narrowed down the issue to pulling too much data from the database and only filtering once it's on the Cognos side.

 

I'm basically trying to pull together a summary report of Change Request and Change Notice information by entering the CN number at a prompt page. Trying to do this without JSP pages or resource bundles only because of some limitations we have, but I'm open to all suggestions.

 

When I looked at the PSM PurePaths it showed

SELECT * FROM Site#<query name>

When I was hoping to pull a select of specific fields with a WHERE clause in there for the CN number.

 

I tried setting up a SQL query based on the report template but I'm not able to get the formatting just right to pull on the query builder queries. I'm looking through all the Cognos documentaiton but still haven't found exactly what I'm looking for.

 

Does anyone have any experience with this and maybe know a trick or two?

 

Right now I have it set up with 5 different Query builder tables. Each has CN number as the first column (Which I might change to OID eventually). then the columns follow with whatever data is being pulled on. So for the most case it's just assigning CN number to the other object rows.  Like Affected end item on CR, here is tied through the CN on the query builder side. Again, I'm sure there are better ways with Info*Engine tasks. It just seemed to have a bit steeper of a learning curve to get it working short term.

1 REPLY 1

Re: Cognos Improvement by narrowing query

Found a partial answer, works for smaller sets of data, but it is still having issues with larger sets of data.

 

 

select *
 from "Site#<Query Builder Report Name>" <Table Nickname>
 where (<Table Nickname>.<Column> = #prompt("<Variable Name>", "string")# )

Where <...> can vary depending on your use case.

There has to be a better way, but this has gotten me a little closer to victory.

Cognos is super powerful, but I'm also starting to see why so few use custom reports with Windchill.