Cognos Improvement by narrowing query

[slapha]

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.

[slapha]

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.

[aniketdavkhar]

Hi, I have applied cognos side filtering but that cause lot of time to display result set.

For Ex. I have created a QML for ECN which in backend perform operation select * from WtChangeOrder2;

I want this query to run for specefic ECN number such as select * from WtChangeOrder2 where number='12345';

I have 10,000 ECN's in my Windchill system and I have applied cognos side fitering for ECN Number.

Cognos side filtering drill through 10,000 row and finally filter out the results on report output for sepecfied ecn number.

My current requirement is to pass the specefied ECN Number to Windchill QML and get that ECN Number in cognos. I don't want my cognos query to drill through 10,000 ECN's first and finally display the output for specified ECN.

Any help highly appreciated!!