Apply different filters in each page with only one query

[daniel1409z]

Hi everyone,

Is it possible using only one query to create more than one page with specific filters that applies to those pages only

I'm using a relation package right now.

Thanks in advance

[MFGF]

Hi everyone,

Is it possible using only one query to create more than one page with specific filters that applies to those pages only

I'm using a relation package right now.

Thanks in advance

Hi,

Can you describe the use case for this, eg why it's important to use only one query? Filters are part of query processing in a report, so the simple and easy solution is to use separate queries for the different containers on each page.

There is a PageNumber() function, but it is a report function designed for use in a layout calculation, not a query expression. You might be able to hack together an expression that uses this (or possibly not), but it will probably be a lot more work and hassle than just using different queries.

Cheers!

MF.

[daniel1409z]

Can you describe the use case for this, eg why it's important to use only one query? Filters are part of query processing in a report, so the simple and easy solution is to use separate queries for the different containers on each page.

Thanks for the answer!!

Basically to make the report run faster. I've created other reports with multiple querys for multiple pages before but the execution time was pretty slow this way (maybe it was not properly optimized, idk).

Anyway, I found this solution in a similar post.

1. Create your base query which contains your logic without any filters.
2. Create Query 1 and drag base query to the right of it to create a query reference. Add your filter.
3. Create Query 2 and drag base query to the right of it to create a query reference. Add your filter.
Page 1 will use Query 1, and Page 2 uses Query 2.

I'll try it to see if it runs better.

[dougp]

So you want only one query in the report, or you want Cognos to run only one query?

As you say, you may be able to have Cognos run one query, locally cache the results, and use that query in multiple downstream queries, each with different filters, each feeding the visualizations on a different report page.  The problem here is that the first query would need to return all of the data.  With the filters open too wide, you may get a lot of rows at once.  That could lead to performance trade-offs as Cognos is trying to compile the results and deliver them all to the browser.  You'd need to test to see if this is really any faster.

[MFGF]

Thanks for the answer!!

Basically to make the report run faster. I've created other reports with multiple querys for multiple pages before but the execution time was pretty slow this way (maybe it was not properly optimized, idk).

Anyway, I found this solution in a similar post.

1. Create your base query which contains your logic without any filters.
2. Create Query 1 and drag base query to the right of it to create a query reference. Add your filter.
3. Create Query 2 and drag base query to the right of it to create a query reference. Add your filter.
Page 1 will use Query 1, and Page 2 uses Query 2.

I'll try it to see if it runs better.

To add to Doug's reply, it's not as simple as you might imagine to get Cognos to run one query and have the results feed multiple containers. Even if a report is written to have multiple objects use the same single query, the default behaviour is that the query will fire multiple times - once for each container that uses it.
If you have list, repeater or repeater table in your report, in the properties of the object (eg List properties) you will see a property called "Share result set". This allows the object to use the results from when its query fired previously - which sounds like what you are trying to achieve. The downside is that this is only available for lists, repeaters and repeater tables - not for crosstabs, charts etc.

Cheers!

MF.

[Cheeks100]

I keep banging on about datasets.  I've been using Cognos Report Studio for about 10 years with all the joy and frustrations that brings.

Last 6 months I've been using datasets and data modules and it's been a bit of a game changer in terms of report performance and usability.

So if you have a report with slow performance.  Datasets all the way.