IF EQUALS Query in Report Builder

Question

IF EQUALS Query in Report Builder

bzoom100

Ten Centuries

Points: 1092
More actions
December 28, 2016 at 7:33 am

#320009

I am using Microsoft SQL Server Report Builder 3.0.
I have a table called "tblDataPermit"
My DataSet1 includes the column "Type"
The Type column has numeric values 1-54.
In my report if I use the Expression =First(Fields!Type.Value, "DataSet1") it will show me the numeric value in the Type column. I need my report to convert this value to a word, such as:
If Type = 1 Then "One"
If Type = 2 Then "Two"
I have struggled with SELECT and CASE but am stuck. Thank you in advance for any assistance you can offer.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

bzoom100 (12/28/2016)
I am using Microsoft SQL Server Report Builder 3.0.
I have a table called "tblDataPermit"
My DataSet1 includes the column "Type"
The Type column has numeric values 1-54.
In my report if I use the Expression =First(Fields!Type.Value, "DataSet1") it will show me the numeric value in the Type column. I need my report to convert this value to a word, such as:
If Type = 1 Then "One"
If Type = 2 Then "Two"
I have struggled with SELECT and CASE but am stuck. Thank you in advance for any assistance you can offer.

I would change my dataset to pull from a query, which joins tblDataPermit and a lookup table containing the TypeDescription.

then you could display the TypeDescription instead of the Type, and avoidthe issue in the presentation layer altogether.

SELECT tblDataPermit.*,

TypeLookup.TypeDescription,

FROM tblDataPermit

LEFT JOIN TypeLookup

ON tblDataPermit.Type = TypeLookup.Type

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Thom A SSC Guru Points: 99130 More actions · Answer 2

This one interested me, so I had a Google. A user had an interesting solution on asp.net: https://forums.asp.net/t/1995435.aspx?How+to+convert+Number+to+word+using+Sql+Query+with+standard+US+as+UK+Fromat.

CREATE FUNCTION fnIntegerToWords(@Number as BIGINT)

RETURNS VARCHAR(1024)

AS

/*

Taken froma post by a user, Rion Williams,on asp.net

https://forums.asp.net/t/1995435.aspx?How+to+convert+Number+to+word+using+Sql+Query+with+standard+US+as+UK+Fromat

*/

BEGIN

DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

INSERT @Below20 (Word) VALUES

( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

('Eighteen' ), ( 'Nineteen' )

INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

declare @belowHundred as varchar(126)

if @Number > 99 begin

select @belowHundred = dbo.fnIntegerToWords( @Number % 100)

end

DECLARE @English varchar(1024) =

(

SELECT Case

WHEN @Number = 0 THEN ''

WHEN @Number BETWEEN 1 AND 19

THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

WHEN @Number BETWEEN 20 AND 99

THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +

dbo.fnIntegerToWords( @Number % 10)

WHEN @Number BETWEEN 100 AND 999

THEN (dbo.fnIntegerToWords( @Number / 100)) +' Hundred '+

Case WHEN @belowHundred <> '' THEN 'and ' + @belowHundred else @belowHundred end

WHEN @Number BETWEEN 1000 AND 999999

THEN (dbo.fnIntegerToWords( @Number / 1000))+' Thousand '+

dbo.fnIntegerToWords( @Number % 1000)

WHEN @Number BETWEEN 1000000 AND 999999999

THEN (dbo.fnIntegerToWords( @Number / 1000000))+' Million '+

dbo.fnIntegerToWords( @Number % 1000000)

WHEN @Number BETWEEN 1000000000 AND 999999999999

THEN (dbo.fnIntegerToWords( @Number / 1000000000))+' Billion '+

dbo.fnIntegerToWords( @Number % 1000000000)

ELSE ' INVALID INPUT' END

)

SELECT @English = RTRIM(@English)

SELECT @English = RTRIM(LEFT(@English,len(@English)-1))

WHERE RIGHT(@English,1)='-'

RETURN (@English)

END

I would, however, suggest that you create a lookup table using this, like lowell suggested. It took my Desktop running SQL server vNext on Linux (8GB RAM, 2.1Ghz i3) 34 seconds to process 6166 rows using the function within a dataset.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

bzoom100 Ten Centuries Points: 1092 More actions · Answer 3

Based on Lowell's reply I came up with the following and it worked! I created a new table tblNumbersApplication with the columns Number and Label. Thank you!

SELECT tblDataPermit.Type,

tblNumbersApplication.Label

FROM tblDataPermit

LEFT JOIN tblNumbersApplication

ON tblDataPermit.Type = tblNumbersApplication.Number

WHERE tblDataPermit.ApplicationNumber = 00001054

P.S. I have one more question that I will be posting 🙂