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
LVL 67
Jim HornMicrosoft SQL Server Data DudeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
Hi,

You can do this

SSRS Category Property
As shown in the Image you can set the label property of the category group property.

Hope this will help you

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nico BontenbalCommented:
Yes. I think the solution is in this dialog. But I think you need to set the label to 'label' and the sorting on 'score'.

But you don't even need SQL Server to calculate the score for you. You can set the Label and Group on expressions to:
=iif( Fields!score.Value<=20,Fields!score.Value,"20+")

Open in new window


I tested it and I didn't have a problem with the sorting (see attached example). But if the sorting is wrong you can set the sorting also in the dialog Vikas Garg shows in his answer.
Jim HornMicrosoft SQL Server Data DudeAuthor Commented:
@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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

LEARN MORE
Jim HornMicrosoft SQL Server Data DudeAuthor Commented:
>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 HornMicrosoft SQL Server Data DudeAuthor Commented:
A few things to deal with, but I'm taking off for two weeks in a couple of hours, so splitting points here.

Thanks.
Nico BontenbalCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.