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
What happens if you set the filter to after auto-aggregation?
I have a system error.
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:
Case
When [COUNTRY]='USA' THEN 'US'
When [COUNTRY]='FRANCE' THEN 'FR'
ELSE NULL
END
QUANTITY
[Measures].[Quantity]
TOTAL_QUANTITY
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?
Thank you for your help.
I think it's probably a way to solve my problem.
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.
Thanks for your help Lynn.
I made something like that but i forgot to apply for each column.
Now it's ok.