Link to home
Create AccountLog in
Avatar of Jim Horn
Jim HornFlag for United States of America

asked on

SSRS chart where horizontal axis has numeric values, but would like to display varchar values

Hi All

I have an SSRS 2012 column chart with a horizontal axis that displays scores 1 to 20, and a final row that is intended to show '20+' for all scores above 20.  

In T-SQL I have a Stored Procedure to pull this off, returning the set in the below image.  For the moment, the entire purpose of the 'label' column is to show a 20+ (green rectangle), as if I don't do this there are scores up to 500 and that really skews the graph and makes the 1-20 scores hard to read. User generated image
Question: In SSRS how can I indicate that the series values / horizontal axis is one column (score, int), but have it display another (label, varchar(5) ?

If I use the score column it shows a 21 for my '20+', and if I use the label column it sorts the graph in varchar order (1, 10, 11, 12) and not numeric (1, 2, 3, 4..) User generated image
Thanks in advance.
Jim
ASKER CERTIFIED SOLUTION
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Jim Horn

ASKER

@Vikas - It worked when I changed both label and Group On to [label].
One side effect is now the bottom labels are staggered over two 'rows', which I'll play with to resolve.

@Nicobo - The expression works when I used it for data points, Chart Series > Label > Label (which also forces Chart Series > Label > UseValueAsLabel='False', but I am unable to locate the property to set this so the horizontal axis labels reflect the expression.

Thanks.
>One side effect is now the bottom labels are staggered over two 'rows', which I'll play with to resolve.
Chart Axis > LabelsAutoFitDisabled = True took care of that.
A few things to deal with, but I'm taking off for two weeks in a couple of hours, so splitting points here.

Thanks.
Sorry. I forgot to attach the example. I created a dataset with the columns score and score_sum. I created a column chart with this data and set the Label and 'Group on' expressions for the Category Group to:
=iif( Fields!score.Value<=20,Fields!score.Value,"20+")

Open in new window

Row11.rdl