Report Studio: Filter on Report

[cslange]

Hi all,

I am in the middle of moving from BusinessObjects to Cognos and so far I am quite happy with what I see.
However there is one thing that bothers that I would like to hear from you:

In BO I can make one query for my report and do the filtering on the report itself.

E.g. I have a query with sales rep and sales revenue.
I make a report with 2 tables, 1 showing all sales reps with sales revenue < 100.000 and 1 showing all sales reps with sales revenue >= 100.000
In BO I can do the filtering on the report and only need to make one query that goes to the database.

In Cognos it looks like (as far as my knowledge goes) I need to make 2 queries, 1 for sales revenue < 100.000 and 1 for sales revenue >= 100.000.
For 2 queries, this is not really a big issue, but for a report I am planning to make I will need at least 8 queries instead of 1.

Or is it also possible in Cognos to query all the data and do the filtering on report level?

[Lynn]

There are a number of different strategies you could use in your situation, although I'm not exactly sure I understand the details of your requirement. I assume that when you say "report" you are referring to a "layout" such as a crosstab or chart or list.

Do you want the ability to run a report with a parameter so that you can choose to run it for the >= scenario sometimes or for the < scenario other times? Or do you want the report to run and show two separate layout containers side by side, one for the >= and the other for the <?

I think another question that will help you get the best advice is to mention if you have a relational or a dimensional source. Take a look at the Forum Etiquette post to understand how the proper details will help members help you more effectively.

I do believe it is possible to have one query that satisfies different scenarios, although that doesn't necessarily mean that only one request will go to the database. Different techniques might be filtering/slicing based on a prompt or creating calculated data items for use in filtering or conditional display or perhaps a query reference that lets you use a base query with slight modification for one use vs. another. The right approach for you may depend on details of your requirement.

[MFGF]

Hi all,

I am in the middle of moving from BusinessObjects to Cognos and so far I am quite happy with what I see.
However there is one thing that bothers that I would like to hear from you:

In BO I can make one query for my report and do the filtering on the report itself.

E.g. I have a query with sales rep and sales revenue.
I make a report with 2 tables, 1 showing all sales reps with sales revenue < 100.000 and 1 showing all sales reps with sales revenue >= 100.000
In BO I can do the filtering on the report and only need to make one query that goes to the database.

In Cognos it looks like (as far as my knowledge goes) I need to make 2 queries, 1 for sales revenue < 100.000 and 1 for sales revenue >= 100.000.
For 2 queries, this is not really a big issue, but for a report I am planning to make I will need at least 8 queries instead of 1.

Or is it also possible in Cognos to query all the data and do the filtering on report level?

I'm curious why this is an issue? Wouldn't you just focus on the results you need rather than manually constructing queries? Simply drag a second list/crosstab/chart into the report containing the first, drag in the data items you want from the package, then filter as desired?

In Cognos 10 you can specify a single query driving multiple objects if you wish, but when the report is executed the query is executed as many times as it is used in different objects, so there's no performance gain in doing this. As you have realised, you can't specify different filters for each object sharing a query as the query contains the filter. In your case it's easier to simply leave the default behaviour which is to have a different query for each object. Since Report Studio is building and managing the queries for you automatically it doesn't take any extra effort on your part. Your post implies you think you need to manually create a query then add a reporting container (list/crosstab/chart/map etc) on top of that? You could use this approach if you are feeling massochistic, but there is a much easier way :) Simply drag in a list/crosstab/chart etc from the toolbox into your report and then polulate this from the package. The query is automatically created and populated.

Cheers!

MF.

[Lynn]

Sometimes I try to use a single query to satisfy multiple similar layouts because then there is less code to maintain. If a requirement changes it can often impact both queries in the same way, so if you have multiple queries that simply deliver a different flavor of the same thing then the same change would be needed for every query. If there is only one query then these changes can be simpler to make and to test. This isn't always the way things go, but sometimes it works out that way.

[MFGF]

Sometimes I try to use a single query to satisfy multiple similar layouts because then there is less code to maintain. If a requirement changes it can often impact both queries in the same way, so if you have multiple queries that simply deliver a different flavor of the same thing then the same change would be needed for every query. If there is only one query then these changes can be simpler to make and to test. This isn't always the way things go, but sometimes it works out that way.

Yep - I completely concur. With differently filtered requirements for multiple objects in a relational report it makes things difficult, though. It's always a balance between maintenance vs report complexity. I generally tend to go for whatever will mean less work in the long run :)

I would take Lynn's advice over mine any day! She knows her stuff, whereas I'm a muppet :)

MF.

[cslange]

Thank you for your replies.

I am building the report (or should I say layout) on a dimensional model.

It is an issue for me as I am surprised that filtering on the report itself with the result from one query to the database looks not as trivial as it is in BO.
In BO I make 1 database query which selects all the sales reps and sales revenue.

E.g:

select sales_rep, sum(sales_revenue)
from sales_fact, sales_rep_dim
where
sales_fact.sales_rep_id=sales_rep.id
group by sales_rep

The filtering can take place on the report

If I want to so the same in Cognos, Cognos will create 2 queries:

select sales_rep, sum(sales_revenue)
from sales_fact, sales_rep_dim
where
sales_fact.sales_rep_id=sales_rep.id
and sales_revenue<10000
group by sales_rep

and

select sales_rep, sum(sales_revenue)
from sales_fact, sales_rep_dim
where
sales_fact.sales_rep_id=sales_rep.id
and sales_revenue>=10000
group by sales_rep

I would like Cognos to do the same query to the database and let me do the filtering on layout level.

For this example it would be no problem for me to do it with 2 queries, but it gets more troublesome when it gets more complex.
(E.g.: working on a report with a date dimension with days_ago, weeks_ago, months_ago attributes.
If I want to show results from yesterday (days_ago=1), current week (weeks_ago=0), last week (weeks_ago=1) I need to do more effort)

[MFGF]

Thank you for your replies.

I am building the report (or should I say layout) on a dimensional model.

It is an issue for me as I am surprised that filtering on the report itself with the result from one query to the database looks not as trivial as it is in BO.
In BO I make 1 database query which selects all the sales reps and sales revenue.

E.g:

select sales_rep, sum(sales_revenue)
from sales_fact, sales_rep_dim
where
sales_fact.sales_rep_id=sales_rep.id
group by sales_rep

The filtering can take place on the report

If I want to so the same in Cognos, Cognos will create 2 queries:

select sales_rep, sum(sales_revenue)
from sales_fact, sales_rep_dim
where
sales_fact.sales_rep_id=sales_rep.id
and sales_revenue<10000
group by sales_rep

and

select sales_rep, sum(sales_revenue)
from sales_fact, sales_rep_dim
where
sales_fact.sales_rep_id=sales_rep.id
and sales_revenue>=10000
group by sales_rep

I would like Cognos to do the same query to the database and let me do the filtering on layout level.

For this example it would be no problem for me to do it with 2 queries, but it gets more troublesome when it gets more complex.
(E.g.: working on a report with a date dimension with days_ago, weeks_ago, months_ago attributes.
If I want to show results from yesterday (days_ago=1), current week (weeks_ago=0), last week (weeks_ago=1) I need to do more effort)

Hi,

A dimensional model puts a different slant on things. You wouldn't use detail filters at all in that case. You would use dimensional expressions that retrieve the desired sets of members from your source, and you could easily use a single query.

I would like Cognos to do the same query to the database and let me do the filtering on layout level.

Sadly this is not how things work in the Cognos world. You're not going to end up writing filters at all with a dimensional source, but instead writing expressions that ask for the desired members.

The recommended approach with a dimensional package is to drag query calculations into your crosstab (for example) that reference the member or members you are looking for

eg you could use the lag() function to get hold of the day member one day before the selected day, or the parallelPeriod() function to get hold of the same month in the prior quarter. You can add all of these to the same crosstab if you like - no need for separate tables or separate queries.

If you can give us examples of what you need to achieve we can most likely steer you in the right direction to most easily get there in Report Studio.

Cheers!

MF.

[navissar]

I'm struggling to understand the "why". If BO works the way you describe (didn't work with BO for a while) that's just absolutely terrible. If you're querying a fact of 10 million rows, you'll have to get all the data, store it (in memory or on disk) and then perform local processing on it. RDBMS, where  filters are used, are built in such a manner that two (or seventy) filtered queries would probably perform better then one huge query and then a bunch of local processing. Why count queries? Count performance.

[cslange]

I'm struggling to understand the "why". If BO works the way you describe (didn't work with BO for a while) that's just absolutely terrible. If you're querying a fact of 10 million rows, you'll have to get all the data, store it (in memory or on disk) and then perform local processing on it. RDBMS, where  filters are used, are built in such a manner that two (or seventy) filtered queries would probably perform better then one huge query and then a bunch of local processing. Why count queries? Count performance.

Dear Nimrod, I do not agree with you on that.
I do not select all 10 million rows, I only select the sales revenue per sales rep, e.g. I am performing a group by, not a full table scan.
BO will create one query, where as Cognos might create 8 queries, if I count performance, I would say that that one query will put less pressure on my database than the 8 from Cognos.

[cslange]

Dear MFGF,

Your earlier comments opened up my eyes and change my behavior when using Cognos and stop working as how I worked when I was using BO.

I should focus on the what I want in my report, not on how (amount of queries).

Besides that, I will take a look at the query calculations and see how they can help me.

[MFGF]

Dear MFGF,

Your earlier comments opened up my eyes and change my behavior when using Cognos and stop working as how I worked when I was using BO.

I should focus on the what I want in my report, not on how (amount of queries).

Besides that, I will take a look at the query calculations and see how they can help me.

I honestly think that's the best strategy. While BO and Cognos are similar in some ways, they are very different in others. I can also see Nimrod's point too - while issuing a single query might seem like a good idea because it is less demanding on the database, it moves a lot of work to the Cognos server, and could potentially result in worse performance that issuing several, targeted, efficient queries to retrieve just the required rows from the database.

Welcome to the wonderful world of Cognos BI! :)

MF.