Creating a custom field and creating an expression to populate it

  • Shabbaranks

    Say Hey Kid

    Points: 712

    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

  • Sue_H

    SSC Guru

    Points: 89811

    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

    Say Hey Kid

    Points: 712

    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

    Say Hey Kid

    Points: 712

    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: 89811

    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

    Say Hey Kid

    Points: 712

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


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

  • sgmunson

    SSC Guru

    Points: 110424

    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)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Shabbaranks

    Say Hey Kid

    Points: 712

    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 🙁

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

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