Group column

[shivani_cog]

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

[HalfBloodPrince]

How many city and state combination you have ?

[shivani_cog]

Actually there are 20 cities in a City column.
I have to create a new data item: state1, state2, state3.
Thanks

[HalfBloodPrince]

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')

[shivani_cog]

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

[shivani_cog]

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

[Lynn]

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:

Code Select Expand


case [Sales (query)].[Retailer site].[Retailer country]
when 'France' then 'Europe'
when 'Belgium' then 'Europe'
etc...
else
'Other'
end


[RobsWalker68]

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

[Lynn]

It looks like your missing the ending quote around Switzerland. 

Eagle Eyes!!!

[RobsWalker68]

I'm having a slow afternoon  ;)

[shivani_cog]

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.