Filter Pages without prompt or extra queries; case/if expression instead?

[DistillaTruant]

Hi All,

We're migrating our system from Business Objects to Cognos (Version 10.2.2) and of course it's really easy to filter in BO but not as user friendly in the Report Studio when needing to create multiple tab/pages.

I was wondering if anyone has found a way to filter using custom expressions or such like? We're just trying to avoid multiple queries and visable prompts.

Example of what I'm trying to achieve;

Report is running data on North America, I need the first page to show total data split by location (or whatever I set it to) for all of NA but the second tab I need the same output but for USA only and the third tab/page I need that output again but to only show the data and figures for Canada.

I hope this is clear but appreciate any guidance!!
Thanks.
DT

[Robl]

There's a few strategies.
Are you using DMR or relational?

For DMR you can look at copy/pasting the same object onto each page and then just changing the members or set.

For Relational you're looking at creating a number of lists or crosstabs with different levels of grouping or summary and page breaks on the groups.
or possibly a different query for each page.

[DistillaTruant]

There's a few strategies.
Are you using DMR or relational?

For DMR you can look at copy/pasting the same object onto each page and then just changing the members or set.

For Relational you're looking at creating a number of lists or crosstabs with different levels of grouping or summary and page breaks on the groups.
or possibly a different query for each page.

Hey,

Thanks for the reply; unfortunately I'm not sure what you mean! We're kinda figuring it out as we go - Can run basic queries/reports fine but it's this filtering by tab that's stumping us at the moment. At the risk of sounding silly, what's DMR?

Is there a way of telling our revenue (for example) data item to look at if <report page name>= "US", then sum <Revenue> for "US", or if report page name = this.. and so on? After trying to mess with render variables and 'hardcoded' prompts, this is the next thing we can think of as a possibility. We have to try and replicate all of our old reports on to Cognos.

thanks

[Lynn]

At the risk of sounding silly, what's DMR?

There is an FAQ post that should help you answer this question. Take a look at number 5 on the first post. It is an important question because techniques can vary quite dramatically depending on whether your source is relational or dimensional. Dimensional generally refers to a cube or OLAP source of some type. DMR is just one type of dimensional source which stands for "dimensionally modeled relational" (or something along those lines) and I believe is more accurately defined as a ROLAP source.

For relational sources, Robl suggested page breaks or page sets which would separate data by country if that's what you've grouped on. Chapter 20 in the user guide is all about working with multiple pages.

If you want pages as tabs then you can't use page sets. In that instance I'd suggest a base query that contains all your logic and then a query reference which is filtered for a particular country. After you create your base query, drag in a new query from the toolbox and then drag the base query alongside of it on the right. You'll see an arrow pointing from the base query to the new query. The new query can then include any elements from the base query and may have a filter to retrieve only one country.