Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sorting in stack column Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 9:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:07 PM
Points: 169, Visits: 506
I am trying to sort the stack chart based on the individual volumes.
I am displaying Desktop, Laptops ,Ipads per manager.
X axis has managers and Y has stack bars of data for the 3 for each manager.
I want to show each manager with the three values according to the volume.
Which ever is more should come on the top , then , 2nd and then 3rd. How can I achieve this?
Post #1522371
Posted Friday, December 13, 2013 8:45 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:10 PM
Points: 3,015, Visits: 1,250
This is something I answered in a course a few weeks ago. Not the most elegant of charts at the end but it was what the client wanted.

Example source query:
select 'Manager A' as Manager, 'Laptop' as CType, 100 as Number
union all select 'Manager A' as Manager, 'iPad' as CType, 120 as Number
union all select 'Manager A' as Manager, 'Desktop' as CType, 60 as Number
union all select 'Manager B' as Manager, 'Laptop' as CType, 80 as Number
union all select 'Manager B' as Manager, 'iPad' as CType, 40 as Number
union all select 'Manager B' as Manager, 'Desktop' as CType, 100 as Number
union all select 'Manager C' as Manager, 'Laptop' as CType, 100 as Number
union all select 'Manager C' as Manager, 'iPad' as CType, 50 as Number
union all select 'Manager C' as Manager, 'Desktop' as CType, 40 as Number

Expanded query:
select * ,
row_number() over (partition by Manager order by Number asc) as SegmentOrder

from (

select 'Manager A' as Manager, 'Laptop' as CType, 100 as Number
union all select 'Manager A' as Manager, 'iPad' as CType, 120 as Number
union all select 'Manager A' as Manager, 'Desktop' as CType, 60 as Number
union all select 'Manager B' as Manager, 'Laptop' as CType, 80 as Number
union all select 'Manager B' as Manager, 'iPad' as CType, 40 as Number
union all select 'Manager B' as Manager, 'Desktop' as CType, 100 as Number
union all select 'Manager C' as Manager, 'Laptop' as CType, 100 as Number
union all select 'Manager C' as Manager, 'iPad' as CType, 50 as Number
union all select 'Manager C' as Manager, 'Desktop' as CType, 40 as Number

) as A


Steps within chart:
1) Create a clustered column chart
- Values = Number
- Category = Manager
- Series = Segment

2) Change the series properties as follows:
- Fill colour : (colour choice up to you)
=switch(Fields!CType.Value="iPad","Yellow",Fields!CType.Value="Desktop","Red",1=1,"LimeGreen")

- Legend text :
=Fields!CType.Value

Done.

Fitz


  Post Attachments 
ChartExample.jpg (6 views, 42.08 KB)
Post #1522728
Posted Monday, December 16, 2013 3:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:07 PM
Points: 169, Visits: 506
Thaks you for the reply.
My query shows the sequence in correct order in management studio. However , its in report builder that it messes up.
P.S : I am trying to sort the column in the category group properties. Since I have lot of columns in on the x axis the switch is not the option for me. Instead I am using the stacked approach.
Post #1523471
Posted Monday, December 16, 2013 11:08 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:10 PM
Points: 3,015, Visits: 1,250
Can you provide a picture of what you want as output? How does the picture I provided differ from your original request?

Fitz
Post #1523530
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse