February 11, 2019 at 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
February 11, 2019 at 2:14 pm
Shabbaranks - Monday, February 11, 2019 9:15 AMHi,
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 onCould 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
February 13, 2019 at 1:24 am
Sue_H - Monday, February 11, 2019 2:14 PMShabbaranks - Monday, February 11, 2019 9:15 AMHi,
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 onCould 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?
February 13, 2019 at 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 querySELECT
[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
February 13, 2019 at 8:30 am
Shabbaranks - Wednesday, February 13, 2019 6:52 AMI thought I would add a snippet of my query which may help me to explain what Im trying to achieve. So below is my querySELECT
[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
March 1, 2019 at 9:22 am
Hi,
Any reason why my SWITCH statement isn't returning any values?
=SWITCH(Fields!PAL_product.Value = "R00505", "TEST")
March 13, 2019 at 1:43 pm
Shabbaranks - Friday, March 1, 2019 9:22 AMHi,
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)
March 15, 2019 at 10:14 am
sgmunson - Wednesday, March 13, 2019 1:43 PMShabbaranks - Friday, March 1, 2019 9:22 AMHi,
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 🙁
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply