Need help with case when

[V i n c e n t]

Hi,

I need you help with a case when.

For example :

My database :

[COUNTRY] [QUANTITY]
USA             100
FRANCE         80
GERMANY      120
SPAIN           140

I want that :

COUNTRY QUANTITY TOTAL_QUANTITY
US             100           440
FR              80            440

I use a case when like this :

Case
When [COUNTRY]='USA' THEN 'US'
When [COUNTRY]='FRANCE' THEN 'FR'
ELSE NULL
END

but i have à wrong résult :
COUNTRY QUANTITY TOTAL_QUANTITY
US             100           440
FR              80            440
                 120           440
                 140           440

If i use à filter i cant have the total quantity (to make a ratio for example)

How can i do to solve my problem ?

Thanks for your help

[CognosPaul]

What happens if you set the filter to after auto-aggregation?

[V i n c e n t]

I have a system error.

[CognosPaul]

I just tried it and it's not working.

Instead of doing it that, there's a slightly more ugly solution.

Create two queries. First one would be what you have already:
Countries:

Code Select Expand

Case
When [COUNTRY]='USA' THEN 'US'
When [COUNTRY]='FRANCE' THEN 'FR'
ELSE NULL
END
QUANTITY

Code Select Expand

[Measures].[Quantity]
TOTAL_QUANTITY

Code Select Expand

total([QUANTITY] for report)

The second query should have Query 1 as the source. Just drag Query 1 to the right of Query 2.

You can put the ratio and the filter in Query 2, and it should work fine.

Can anyone think of a less dirty way to do this?

[V i n c e n t]

Thank you for your help.

I think it's probably a way to solve my problem.

[Lynn]

I don't know if this qualifies as less messy, but instead of a query reference you could create a conditional style based on the parameter value.

For example, advanced conditional style expression based on a parameter selection for Country:

[Query1].[Country] not in ParamValue('SelectCountry')

Then set box type to none for this condition. If it is a list report, then select each list column body and apply that style.

Essentially you are not filtering the query so the total can be calculated and then just control what gets displayed based on prompt responses.

[V i n c e n t]

Thanks for your help Lynn.

I made something like that but i forgot to apply  for each column.

Now it's ok.