I have a new requirement. I have to create a group by State by which cities are contained. City1, City2, City3 will be in State1(grouped in). City1_1 and City1_2 will be in State2. City2_1 will be in State3. Is crosstab or list type report work on this requirement? Thanks.
For example:
there are columns:
City people
City1 10
City2 20
City3 30
City1_1 15
City1_2 10
City1_3 21
City2_1 15
Final result should look like this: either in crosstab or list
State1:
City1 10
City2 20
City3 30
State2:
City1_1 15
City1_2 10
City1_3 21
State3
City2_1 15
How many city and state combination you have ?
Actually there are 20 cities in a City column.
I have to create a new data item: state1, state2, state3.
Thanks
you can try this, but its better if you handled it by ETL
If(city in('city 1', 'city 2', 'city 3') then
('State1')
else
if(city in('city _1', 'city_2', 'city_3') then
('State2')
else
('State3')
Thanks for the hint. Yes, it could have been a lot easier if this one has created in ETL.
I tried using IF statement.
I got an error.
Do I have create this code in [Retailer Country] expression?
case when ([Sales (query)].[Retailer site].[Retailer country] in('France', 'Belgium' , 'Denmark', 'Switzerland,'United Kingdom', 'Sweden', 'Spain', 'Netherlands', 'Finland', 'Italy' ) then
('Europe')
when
( [Sales (query)].[Retailer site].[Retailer country] in('China' , 'Japan', 'Korea','Singapore') then
('Asia')
else
('United States')
end
I missed the () but I am still getting an error
case when ([Sales (query)].[Retailer site].[Retailer country] in('France', 'Belgium' , 'Denmark', 'Switzerland,'United Kingdom', 'Sweden', 'Spain', 'Netherlands', 'Finland', 'Italy' ) ) then
('Europe')
when
( [Sales (query)].[Retailer site].[Retailer country] in('China' , 'Japan', 'Korea','Singapore') ) then
('Asia')
when ([Sales (query)].[Retailer site].[Retailer country] in ('United States'))
then ('United States')
else null
end
What is the error?
You could try a null string (e.g., '') instead of the word null in the else portion of the case. Personally I'd use something like 'Other' or 'Unknown' for an undefined translation.
Another option might be:
case [Sales (query)].[Retailer site].[Retailer country]
when 'France' then 'Europe'
when 'Belgium' then 'Europe'
etc...
else
'Other'
end
Hi,
It looks like your missing the ending quote around Switzerland. That would raise a syntax error and also as Lynn mentioned for clarity add an 'unknown' or 'other' instead of null.
Cheers
Rob
Quote from: RobsWalker68 on 28 Nov 2011 08:22:38 AM
It looks like your missing the ending quote around Switzerland.
Eagle Eyes!!!
I'm having a slow afternoon ;)
Hi Guys,
Thanks for the feedback.
RobsWalker68: Yes I found that missing " ' " later on.
LYNN: I already used the Case Statement before exactly the way you showed here.
1) I created data item for each grouping countries
Data items: Asia, Europe, America
2) In cross tab report , in rows, I pulled the Data items.
3) It showed me based on what the countries are grouped by Asia, Europe, and America.
The issue here is that there are also showing an empty name row with an extra values. Where are those values coming from. All values from each country is showing correctly. What am I missing here. I used the Case Statement.
Report type is a crosstab.