How to get a total aggregation() on a filtered column/ dataitem/member/s

[srikanthshonti]

I wonder if my title is right for the question I'm asking.

Say I have table with two columns in Report studio

State | Population

CA - 60,000
MI - 20,000
FL - 30,000
LA - 50,000
NE - 40,000
PA - 10,000

Total - 210,000

I would like to see 3 selected states their population and a sum of all the populations of all the states which is 210,000 in this case.

State | Population | All state Pop
CA - 60,000 - 210,000
LA - 50,000 - 210,000
NE - 40,000 - 210,000

When I tried to do a filter on the state for these 3 selected states the total population changes to 150,00 which I'd like to see for 210,000.

Is this possible in Cognos report studio ?

I cannot alter the universe nor can I do a custom sql in the query.
The results come from a single data provider

I tried doing a forall but that didn't helped me.

Hope the question is clear.

Thanks.

[srikanthshonti]

 Can anybody answer this? or else in Cognos can't we do this? not getting the appropriate workaround even? kindly post some comments.

[Lynn]

There are a few different ways you could approach it.

The main point is that you can't filter for specific states and still get the population for all states in a single query. If you understand SQL you'd realize the "where" clause limits data returned.

You need to bring back all the data in order to compute the total population. Now the question becomes how to display just the states you want to show. You can try:

• Create one query for just the states you want and a separate query for the total population and use two layout containers
• Create one query for all data and a query reference to pull just the desired states from it, using the main query for the total population
• Create one query for all data and then conditionally display only the states you want

[srikanthshonti]

Hi All,

Thanks for your reply i created the queries as suggested but getting this error "'fn:tuple' function is not supported in the context in which it is used". FYI I am using a DMR model. A prompt page is created to select the different states the state prompt is a multiselect prompt. Is there any function something smiler to Nofilter etc in cognos that can exclude the applied filters on the query. how about except function if yes how can i use it to incorporate my requirement.

[eliza_jane]

Try using aggregate([Population] within set([all states])) where [all states] is the non filtered states set.