December 12, 2013 at 9:20 am
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?
December 13, 2013 at 8:45 am
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
December 16, 2013 at 3:45 pm
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.
December 16, 2013 at 11:08 pm
Can you provide a picture of what you want as output? How does the picture I provided differ from your original request?
Fitz
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply