none
IN(Item, Set) in a calculated field dataset ssrs 2008

    Question

  • hi.
    i am trying to create a calculated field in a dataset where i need to use the experssion IN(item, set) to test if 'Covergate Type' field is equal to one of the below values:
    Cargo
    Contractors All Risks
    Erection All Risks
    PI A&E Single Project
    Single CGL

    Ive tried to write IN(Coverage Type,"Cargo Contractors All Risks","Erection All Risks","PI A&E Single Project","Single CGL")
    but it is not working.
    can any one help me how to write the syntax of IN expression


    thanks

    Wednesday, September 18, 2013 9:17 AM

Answers

All replies

  • Hi,

    I do not know of an IN expression in SSRS, but you can always try using the following:

    =IIF(((Fields!CoverageType.Value = "Cargo Contractors All Risks") OR (Fields!CoverageType.Value = "Erection All Risks") OR (Fields!CoverageType.Value = "PI A&E Single Project") OR (Fields!CoverageType.Value = "Single CGL")),SUM(CalculatedField),0)

    Hope this helps.

    Regards,

    Chris



    • Edited by XybertroniX Wednesday, September 18, 2013 10:20 PM
    • Proposed as answer by XybertroniX Wednesday, September 18, 2013 10:23 PM
    Wednesday, September 18, 2013 9:09 PM
  • hi..
    first of all thank you for your reply..
    i created the calculated field as below:

       

    =IIF(Fields!Layer_Origin.Value="New Business",Fields!USD_Cedent_Premium.Value,CDec(0.00))

    i also wrote a code:

     Dim suma As Decimal = New Decimal()
        
        Public Function SumLookup(ByVal items As Object(),ByVal CountSum As String) As Decimal
        If items Is Nothing Then
        Return Nothing
        End If
        
        Dim ct as Integer = New Integer()
        Dim PolId as Integer = New Integer()
        suma = 0
        ct = 0
        PolId = 0
        For Each item As Object In items
        If (Convert.ToDecimal(item)<>Convert.ToDecimal(0.00))
        suma += Convert.ToDecimal(item)
        End if
        If (CountSum = "SUM") then
        ct += 1
        Else If (PolID <> Convert.ToInt32(item)) then 
        ct += 1
        END If
        PolId = Convert.ToInt32(item)
        Next
        If (ct = 0) Then return 0 else If(CountSum = "SUM") then return suma else return ct 
        End Function
        
        
        Public Function GetMyVal() as Decimal
        GetMyVal = suma 
        End Function

     I am using the below expression to call the code using the caluculated field since im using fields from different datasets:

        =code.SumLookup(LookupSet(MonthName(Fields!Contract_Start_Month.Value) + "-" + Fields!Contract_Cover_Type.Value + "-" + Fields!Primary_LOB1.Value, MonthName(Fields!Contract_Start_Month.Value) + "-" + Fields!Contract_Cover_Type.Value + "-" + Fields!Primary_LOB1.Value, Fields!USDCedPre_New.Value, "DataSet1LastYear"), "SUM")

    however im still getting error in value

     if i call sumlookup code using a not calculated field (for example USD_Cedent_Premium) it will work perfectly

    Also if i use the below expression for the total it will work

    =Sum(Fields!USDCedPre_New.Value, "DataSet1LastYear")
    Please advise
    Friday, September 20, 2013 7:30 AM
  • Also find below screen shot of IN(Item, Set) expression

    Friday, September 20, 2013 8:17 AM
  • Sorry for the late reply.

    I have run a few tests to try and solve this problem and it looks like the expression may be trying to calculate the "-" instead of concatenating it.

    Please try using the following expression:

    =code.SumLookup(LookupSet(MonthName(Fields!Contract_Start_Month.Value) & "-" & Fields!Contract_Cover_Type.Value & "-" & Fields!Primary_LOB1.Value, MonthName(Fields!Contract_Start_Month.Value) & "-" & Fields!Contract_Cover_Type.Value & "-" & Fields!Primary_LOB1.Value, Fields!USDCedPre_New.Value, "DataSet1LastYear"), "SUM")

    You will be replacing the "+" with a "&".

    Please let me know if this works for you?

    Thanks & Kind Regards,

    Chris


    • Edited by XybertroniX Monday, September 23, 2013 11:20 AM
    Monday, September 23, 2013 11:20 AM
  • Hi chris,

    the code is working just fine; my problem was from the calculated field.

    i just solved it as mentioned in the below screen shot:

    thank you for your support

    Georges

    Monday, September 23, 2013 1:57 PM