Avatar of Jim Horn
Jim Horn
Flag 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. data-set-horizontal-axis-20-plus.jpg
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..) ssrs-designer-good.jpg
Thanks in advance.
Jim
SSRSMicrosoft SQL Server

Avatar of undefined
Last Comment
Nico Bontenbal

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vikas Garg

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Nico Bontenbal

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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.
Jim Horn

ASKER
>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.
Jim Horn

ASKER
A few things to deal with, but I'm taking off for two weeks in a couple of hours, so splitting points here.

Thanks.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Nico Bontenbal

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