none
multivalue parameters using a progress db

    Question

  • Hi all,

    I'm so close to cracking a report I've been working on for a while and a lot of it is down to the help I've recieved here so I have one last question.

    I'd like to be able to run multi value parameters but these aren't supported by an ODBC connection to a Progress DB, I've tried all the recommendations I can find of joining them as a string within the query but never mind how I try it I'm getting a "Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)" error.

    Does anyone have any ideas?

    This is my query as it stands.

    ="SELECT Pub.Customer.Address3, Pub.Customer.Address4, Pub.Customer.Address5, Pub.Customer.CreditLimit, Pub.Customer.OverdueHold, Pub.Customer.LetterType, Pub.SalesRep.SalesRepName, Pub.Customer.AccContactName, Pub.Customer.AccTelephoneNo, Pub.Customer.TerritoryCode, Pub.Customer.PaymentTermCode, Pub.CustomerTotals.Balance, Pub.Currency.Description AS CurrencyDescrip, Pub.Territory.Description AS TerritoryDescrip, Pub.AROpenTran.Reference, Pub.AROpenTran.TransCode, Pub.AROpenTran.TransactionDate, Pub.AROpenTran.DueDate, Pub.Customer.CurrencyCode AS CustCurrency, Pub.PaymentTerms.Description AS PayTermDescrip,
    CASE WHEN TIMESTAMPDIFF(sql_tsi_day, " & Parameters!datetype.Value & ", Now()) <= 0 
    THEN " & Parameters!currencytype.Value & " 
    ELSE 0 
    END AS future ,
    CASE WHEN TIMESTAMPDIFF(sql_tsi_day, " & Parameters!datetype.Value & ", Now()) BETWEEN 0 AND " & 
    Parameters!ageingone.Value & " 
    THEN " & Parameters!currencytype.Value & " 
    ELSE 0 
    END AS balance_1_30 , 
    CASE WHEN TIMESTAMPDIFF(sql_tsi_day, " & Parameters!datetype.Value & ", Now()) BETWEEN " & 
    Parameters!ageingone.Value & " AND " & Parameters!ageingtwo.Value & " 
    THEN " & Parameters!currencytype.Value & " 
    ELSE 0 
    END AS balance_30_60 , 
    CASE WHEN TIMESTAMPDIFF(sql_tsi_day, " & Parameters!datetype.Value & ", Now()) BETWEEN " & 
    Parameters!ageingtwo.Value & " AND " & Parameters!ageingthree.Value & " 
    THEN " & Parameters!currencytype.Value & " 
    ELSE 0 
    END AS balance_60_90 , 
    CASE WHEN TIMESTAMPDIFF(sql_tsi_day, " & Parameters!datetype.Value & ", Now()) > " & Parameters!ageingthree.Value & " 
    THEN " & Parameters!currencytype.Value & " 
    ELSE 0 
    END AS over " &
    "FROM " &
    "Pub.Customer " &
    "INNER JOIN " &
    "Pub.AROpenTran " &
    "ON " &
    "Pub.Customer.CustomerCode=Pub.AROpenTran.CustomerCode " &
    "INNER JOIN " &
    "Pub.CustomerTotals " &
    "ON " &
    "Pub.Customer.CustomerCode=Pub.CustomerTotals.CustomerCode " &
    "INNER JOIN " &
    "Pub.SalesRep " &
    "ON " &
    "Pub.Customer.SalesRepCode=Pub.SalesRep.SalesRepCode " &
    "INNER JOIN " &
    "Pub.Currency " &
    "ON " &
    "Pub.AROpenTran.CurrencyCode=Pub.Currency.CurrencyCode " &
    "INNER JOIN " &
    "Pub.Territory " &
    "ON " &
    "Pub.Customer.TerritoryCode=Pub.Territory.TerritoryCode " &
    "INNER JOIN " &
    "Pub.PaymentTerms " &
    "ON " &
    "Pub.Customer.PaymentTermCode=Pub.PaymentTerms.PaymentTermCode " &
    
    "WHERE " &
    "Pub.Customer.CustomerCode BETWEEN ? AND ? " &
    "AND " &
    "Pub.AROpenTran.Year <= ? " &
    "AND " &
    "Pub.AROpenTran.Period <= ? " &
    "AND " &
    "Pub.Customer.CustomerType BETWEEN ? AND ? " &
    "AND " &
    "Pub.AROpenTran.CurrencyCode BETWEEN ? AND ? " &
    "AND " &
    "Pub.Territory.TerritoryCode IN '" & Join(Parameters!territory.Value,"','") & "'"


    • Edited by tom2163 Tuesday, January 08, 2013 4:59 PM
    Tuesday, January 08, 2013 4:58 PM

Answers

  • Hi Tom,

    At quick glance, the last line on your code, the "IN" statement syntax should be IN (value1, value2, value3).

    So last line should be --

    "Pub.Territory.TerritoryCode IN ('"  & Join(Parameters!territory.Value, "','") & "')"

    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    • Marked as answer by tom2163 Tuesday, January 08, 2013 5:14 PM
    Tuesday, January 08, 2013 5:11 PM

All replies

  • Hi Tom,

    At quick glance, the last line on your code, the "IN" statement syntax should be IN (value1, value2, value3).

    So last line should be --

    "Pub.Territory.TerritoryCode IN ('"  & Join(Parameters!territory.Value, "','") & "')"

    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    • Marked as answer by tom2163 Tuesday, January 08, 2013 5:14 PM
    Tuesday, January 08, 2013 5:11 PM
  • Hi Tom,

    At quick glance, the last line on your code, the "IN" statement syntax should be IN (value1, value2, value3).

    So last line should be --

    "Pub.Territory.TerritoryCode IN ('"  & Join(Parameters!territory.Value, "','") & "')"

    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    You're an absolute star! I knew there was a syntax error in there somewhere just couldn't figure it out.

    It's pretty much down to you that this report works.

    Tuesday, January 08, 2013 5:14 PM
  • Glad I can help!

    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    Tuesday, January 08, 2013 5:18 PM