Creating a custom field and creating an expression to populate it

Question

Creating a custom field and creating an expression to populate it

Shabbaranks

SSC Eights!

Points: 880
More actions
February 11, 2019 at 9:15 am

#416686

Hi,
I'm looking to create a custom field and am not 100% sure if I can use an SQL statement to create the expression or does the code need to be in a specific format to the report builder? For example I wish to query a column which is part of my report and if the column contains ABC then equal 1 and EFG then equal 2 and so on
Could anyone help me with this please?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Shabbaranks - Monday, February 11, 2019 9:15 AM
Hi,
I'm looking to create a custom field and am not 100% sure if I can use an SQL statement to create the expression or does the code need to be in a specific format to the report builder? For example I wish to query a column which is part of my report and if the column contains ABC then equal 1 and EFG then equal 2 and so on
Could anyone help me with this please?
Thanks

Expressions in SSRS written in Visual Basic so you can use VB functions. You can find a lot of examples in this documentation:
Expression Examples (Report Builder and SSRS)

You can use nested Ifs (IIF) or SWITCH for determining conditional values. For something like your example, you could use an expression similar to:
=SWITCH(Fields!YourColumnName.Value = "ABC", "1", Fields!YourColumnName.Value = "EFG", "2", Fields!YourColumnName.Value = "HIJ", "3", Fields!YourColumnName.Value = "KLM", "4")

Sue

Shabbaranks SSC Eights! Points: 880 More actions · Answer 2

Sue_H - Monday, February 11, 2019 2:14 PM
Shabbaranks - Monday, February 11, 2019 9:15 AM
Hi,
I'm looking to create a custom field and am not 100% sure if I can use an SQL statement to create the expression or does the code need to be in a specific format to the report builder? For example I wish to query a column which is part of my report and if the column contains ABC then equal 1 and EFG then equal 2 and so on
Could anyone help me with this please?
Thanks
Expressions in SSRS written in Visual Basic so you can use VB functions. You can find a lot of examples in this documentation:
Expression Examples (Report Builder and SSRS)
You can use nested Ifs (IIF) or SWITCH for determining conditional values. For something like your example, you could use an expression similar to:
=SWITCH(Fields!YourColumnName.Value = "ABC", "1", Fields!YourColumnName.Value = "EFG", "2", Fields!YourColumnName.Value = "HIJ", "3", Fields!YourColumnName.Value = "KLM", "4")
Sue

Thanks Sue - Im getting there 🙂 can you use a select statement with a like statement?

Shabbaranks SSC Eights! Points: 880 More actions · Answer 3

I thought I would add a snippet of my query which may help me to explain what Im trying to achieve. So below is my query
SELECT [SS-zzReport].order_no ,[SS-zzReport].Expr1 ,[SS-zzReport].status ,[SS-zzReport].alpha ,[SS-zzReport].customer ,[SS-zzReport].address1 ,[SS-zzReport].address2 ,[SS-zzReport].address3 ,[SS-zzReport].address4 ,[SS-zzReport].address5 ,[SS-zzReport].product ,[SS-zzReport].description ,[SS-zzReport].order_qty ,[SS-zzReport].allocated_qty ,[SS-zzReport].despatched_qty ,[SS-zzReport].customer_order_no ,[SS-zzReport].date_despatched FROM [SS-zzReport] Where [SS-zzReport].date_despatched between @StartDate and @EndDate AND [SS-zzReport].address1 LIKE '%@Company%'

I have created a manual list parameter which allows the user to select a simplified name this is the "@Company" from the selection Im trying to use the above SQL query to take the selection lets say for example car and to only return the results with the letters CAR in the address1 column eg Carpool
Is this possible?
Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Shabbaranks - Wednesday, February 13, 2019 6:52 AM
I thought I would add a snippet of my query which may help me to explain what Im trying to achieve. So below is my query
SELECT [SS-zzReport].order_no ,[SS-zzReport].Expr1 ,[SS-zzReport].status ,[SS-zzReport].alpha ,[SS-zzReport].customer ,[SS-zzReport].address1 ,[SS-zzReport].address2 ,[SS-zzReport].address3 ,[SS-zzReport].address4 ,[SS-zzReport].address5 ,[SS-zzReport].product ,[SS-zzReport].description ,[SS-zzReport].order_qty ,[SS-zzReport].allocated_qty ,[SS-zzReport].despatched_qty ,[SS-zzReport].customer_order_no ,[SS-zzReport].date_despatched FROM [SS-zzReport] Where [SS-zzReport].date_despatched between @StartDate and @EndDate AND [SS-zzReport].address1 LIKE '%@Company%'
I have created a manual list parameter which allows the user to select a simplified name this is the "@Company" from the selection Im trying to use the above SQL query to take the selection lets say for example car and to only return the results with the letters CAR in the address1 column eg Carpool
Is this possible?
Thanks

If you need to wildcard parameters like that in SSRS, you can use something like this in the query:
[SS-zzReport].address1 LIKE '%' + @Company + '%'

Sue

Shabbaranks SSC Eights! Points: 880 More actions · Answer 5

Hi,
Any reason why my SWITCH statement isn't returning any values?

=SWITCH(Fields!PAL_product.Value = "R00505", "TEST")

sgmunson SSC Guru Points: 110639 More actions · Answer 6

Shabbaranks - Friday, March 1, 2019 9:22 AM
Hi,
Any reason why my SWITCH statement isn't returning any values?

=SWITCH(Fields!PAL_product.Value = "R00505", "TEST")

You only provided it with one option, so if that field does not contain that specified value, there's no other option than a NULL value.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Shabbaranks SSC Eights! Points: 880 More actions · Answer 7

sgmunson - Wednesday, March 13, 2019 1:43 PM
Shabbaranks - Friday, March 1, 2019 9:22 AM
Hi,
Any reason why my SWITCH statement isn't returning any values?

=SWITCH(Fields!PAL_product.Value = "R00505", "TEST")
You only provided it with one option, so if that field does not contain that specified value, there's no other option than a NULL value.

Hi, I don't suppose you can elaborate on this please? I have tried to follow numerous examples of switch and IIF all seem to fail.
Im trying to say if column 1 equals a value then put the value of another column else put the value of a third column
=IIF(Fields!supplier.Value=TEST,"Fields!users_text05.Value","Fields!users_text02.Value")
Obviously Im not using the correct syntax 🙁