-1

I'd like to add a divisor symbol (%) to the end of my expression shown here:

select 'On-Site Case Rate' Exp1,
isnull(sum(onsite.a) * 100 / count(onsite.casecount), 0) '400',
isnull(sum(onsite.b) * 100 / count(onsite.casecount), 0) '401'
from onsite

How would I go about doing that? Do I need to use a concat and reformat my query or is it possible to insert a " + '%' "+ somewhere?

Here is a sample result, this is for an SSRS report

enter image description here

EDIT1: Here is the design view of my report as well

Design view

Thom A
  • 88,727
  • 11
  • 45
  • 75
TozuPug
  • 17
  • 3
  • Tag your question with the database you are using. Sample data and desired results would also be helpful. – Gordon Linoff Jan 03 '19 at 15:02
  • This should be done in SSRS, not in SQL. See the format or Number Format options in the designer – Mitch Jan 03 '19 at 15:15
  • 2
    Why don't you do it in the SSRS? – Tab Alleman Jan 03 '19 at 15:15
  • 1
    Possible duplicate of [SSRS Format to display as percent](https://stackoverflow.com/questions/26892564/ssrs-format-to-display-as-percent) – Mitch Jan 03 '19 at 15:17
  • Because the way the query is designed, I can't add the '%' symbol to the end of my string, the On-Site Case Rate is part of a subquery and I only want the symbol on some rows, not all of them – TozuPug Jan 03 '19 at 15:25
  • @TozuPug, then it should be two columns in your query – then combine them into a single cell in the SSRS designer. That way, formatting, exporting, grouping, subtotals, client side filtering, and client side sorting all still work. It also permits you to do more advanced formatting like showing a data bar or color scale. – Mitch Jan 03 '19 at 18:29

2 Answers2

1

Considering that you're using SSRS, don't try to add a % sign to the end of your percentage, and convert it to a varchar, leave it as a decimal. Instead, change your display format.

Select the cell(s) that are returning your percentages and press F4. Then, in the now targeted Properties Pane locate the Format Property and change it to 0%. If you want it to display 1 (or more) decimal places then use 0.0%, 0.00%, ... you get the idea.

Note that you need to ensure that your values are returning a decimal value. You're multiplying your values by 100, which implies that you aren't. 15 isn't 15%, it's 1500%. 15% = 0.15.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Unfortunately I can't edit this specific part of my query, this is what the design view of my report looks (see edit 1) – TozuPug Jan 03 '19 at 15:51
  • Why can't you edit that part? I can see the cells in your screenshot. – Thom A Jan 03 '19 at 15:58
  • It's only a few rows that I want to add % symbols too, some of rows like Cases Created and Cases Loaded are not percentages. – TozuPug Jan 03 '19 at 16:08
  • So you're mixing data types in a single column, @TozuPug? – Thom A Jan 03 '19 at 16:11
  • No, everything in that column is of datatype int, it's just that some of it needs to be shown in % – TozuPug Jan 03 '19 at 16:40
  • 1
    But `'10%'` isn't a `int` it's a `varchar`m and 10% expressed as a number is `0.10`, which also isn't an `int` either, it's a `decimal(3,2)`. So you **are** mixing datatypes, @TozuPug no matter what way you're looking at it. – Thom A Jan 03 '19 at 16:56
0

In Sql Server (starting with version 2012) you can use the CONCAT function: 

select 'On-Site Case Rate' Exp1,
CONCAT(isnull(sum(onsite.a) * 100 / count(onsite.casecount), 0), '%') '400',
CONCAT(isnull(sum(onsite.b) * 100 / count(onsite.casecount), 0), '%') '401'
from onsite
Varty
  • 353
  • 1
  • 4
  • I tried that and am getting the error "Conversion failed when converting the varchar value '0%' to data type int." – TozuPug Jan 03 '19 at 15:23