SSRS - Hidden expression for tablix 'table1' contains an error: Overload resolution failed

apologies, the updated data set query is below (using in instead of = for @manufacturer) still same error:


IF @QueryParameterType=1
(
SELECT Customer_Name
      ,ship2_address1  
      ,ship2_city
      ,ship2_state
      ,LEFT(ship2_postal_code,5) AS ship2_postal_code    


      ,bill2_address1  
      ,bill2_city
      ,bill2_state
      ,LEFT(bill2_postal_code,5) AS bill2_postal_code

           
     
      ,right(invoice.item_id,len(invoice.item_id)-3) as ItemID
      ,supplier_part_no
      ,qty_shipped
      ,invoice_no
            ,convert(varchar(10),invoice_date,101) as invoice_date
      ,round(cogs_amount/qty_shipped,4) as cost
      ,cogs_amount
      ,invoice.customer_id
      ,Stockable_Location
      ,extended_price
      ,isnull(value_string,'') as vendor_customer_number
from
      vHerman_invoice_header_line as Invoice
     
      left join custom_column_data_customer as custom_customer
      on Invoice.customer_id = custom_customer.customer_id
      and area_x_custom_column_uid = 4    
     
      left outer join p21_view_inventory_supplier as supplier
      on invoice.item_id=supplier.item_id

where
            Invoice.Invoice_Date >= @From_Date
            and Invoice.Invoice_Date < (@Thru_Date + 1)
            and left(Invoice.item_id,3) in (left(@Manufacturer,3))
            and (Invoice.customer_id = @CustomerID
               OR @CustomerID = 'ALL')
               and supplier_part_no IS NOT NULL
               and invoice.order_no is not null

)

IF @QueryParameterType=2
(
SELECT Customer_Name
      ,ship2_address1  
      ,ship2_city
      ,ship2_state
      ,LEFT(ship2_postal_code,5) AS ship2_postal_code    


      ,bill2_address1  
      ,bill2_city
      ,bill2_state
      ,LEFT(bill2_postal_code,5) AS bill2_postal_code

           
     
      ,right(invoice.item_id,len(invoice.item_id)-3) as ItemID
      ,supplier_part_no
      ,qty_shipped
      ,invoice_no
            ,convert(varchar(10),invoice_date,101) as invoice_date
      ,round(cogs_amount/qty_shipped,4) as cost
      ,cogs_amount
      ,invoice.customer_id
      ,Stockable_Location
      ,extended_price
      ,isnull(value_string,'') as vendor_customer_number
from
      vHerman_invoice_header_line as Invoice
     
      left join custom_column_data_customer as custom_customer
      on Invoice.customer_id = custom_customer.customer_id
      and area_x_custom_column_uid = 4    
     
      left outer join p21_view_inventory_supplier as supplier
      on invoice.item_id=supplier.item_id

where
            Invoice.Invoice_Date >= @From_Date
            and Invoice.Invoice_Date < (@Thru_Date + 1)
            and left(Invoice.item_id,3) in (left(@Manufacturer,3))
            and (Invoice.customer_id = @CustomerID
               OR @CustomerID = 'ALL')
               and supplier_part_no IS NOT NULL
               and invoice.order_no is not null

)

else if @QueryParameterType=3


(
SELECT
Customer_Name

      ,ship2_address1  
      ,ship2_city
      ,ship2_state
      ,LEFT(ship2_postal_code,5) AS ship2_postal_code    


      ,bill2_address1  
      ,bill2_city
      ,bill2_state
      ,LEFT(bill2_postal_code,5) AS bill2_postal_code
     
      ,right(invoice.item_id,len(invoice.item_id)-3) as ItemID
      ,supplier_part_no
      ,qty_shipped
      ,invoice_no
            ,convert(varchar(10),invoice_date,101) as invoice_date
      ,round(cogs_amount/qty_shipped,4) as cost
      ,cogs_amount
      ,invoice.customer_id
      ,Stockable_Location
      ,extended_price
      ,isnull(value_string,'') as vendor_customer_number
from
      vHerman_invoice_header_line as Invoice
     
      left join custom_column_data_customer as custom_customer
      on Invoice.customer_id = custom_customer.customer_id
      and area_x_custom_column_uid = 4    
     
      left outer join p21_view_inventory_supplier as supplier
      on invoice.item_id=supplier.item_id

where
            Invoice.Invoice_Date >= @From_Date
            and Invoice.Invoice_Date < (@Thru_Date + 1)
            and left(Invoice.item_id,3) in (left(@Manufacturer,3))
            and (Invoice.customer_id = @CustomerID
               OR @CustomerID = 'ALL')
               and supplier_part_no IS NOT NULL
               and invoice.order_no is not null
)

You configured @Manufacturer to allow multiple-values, therefore, it is an object.  LEFT string function does not accept an object (or integer) as its first parameter, the following is incorrect:

left(Invoice.item_id,3) in (left(@Manufacturer,3))

* assumes Invoice.item_id is a char/varchar/nvarchar, if it is an integer then that too is incorrect, you cannot perform LEFT string function on an integer

Thanks John - I'll replace that parameter in the query and try again. I'll follow-up with results.

Ok, I rewrote the query and no longer have the left string function as a challenge however, the error below still persists:

The Hidden expression for the tablix ‘table1’ contains an error: Overload resolution failed because no Public '<>' can be called with these arguments:
    'Public Shared Operator <>(a As String, b As String) As Boolean':
        Argument matching parameter 'a' cannot convert from 'Object()' to 'String'. (rsRuntimeErrorInExpression)


New query:

IF @QueryParameterType=1
(
SELECT Customer_Name
      ,ship2_address1  
      ,ship2_city
      ,ship2_state
      ,LEFT(ship2_postal_code,5) AS ship2_postal_code    
        ,supplier_man.supplier_name as Manufacturer

      ,bill2_address1  
      ,bill2_city
      ,bill2_state
      ,LEFT(bill2_postal_code,5) AS bill2_postal_code

           
     
      ,right(invoice.item_id,len(invoice.item_id)-3) as ItemID
      ,supplier_part_no
      ,qty_shipped
      ,invoice_no
            ,convert(varchar(10),invoice_date,101) as invoice_date
      ,round(cogs_amount/qty_shipped,4) as cost
      ,cogs_amount
      ,invoice.customer_id
      ,Stockable_Location
      ,extended_price
      ,isnull(value_string,'') as vendor_customer_number
from
      vHerman_invoice_header_line as Invoice
     
      left join custom_column_data_customer as custom_customer
      on Invoice.customer_id = custom_customer.customer_id
      and area_x_custom_column_uid = 4    
     
      left outer join p21_view_inventory_supplier as supplier
      on invoice.item_id=supplier.item_id INNER JOIN
      supplier AS supplier_man on supplier.supplier_id = supplier_man.supplier_id

where
            Invoice.Invoice_Date >= @From_Date
            and Invoice.Invoice_Date < (@Thru_Date + 1)
            and supplier_man.supplier_name in (@Manufacturer)
            and (Invoice.customer_id = @CustomerID
               OR @CustomerID = 'ALL')
               and supplier_part_no IS NOT NULL
               and invoice.order_no is not null

)

IF @QueryParameterType=2
(
SELECT Customer_Name
      ,ship2_address1  
      ,ship2_city
      ,ship2_state
      ,LEFT(ship2_postal_code,5) AS ship2_postal_code    
        ,supplier_man.supplier_name as Manufacturer

      ,bill2_address1  
      ,bill2_city
      ,bill2_state
      ,LEFT(bill2_postal_code,5) AS bill2_postal_code

           
     
      ,right(invoice.item_id,len(invoice.item_id)-3) as ItemID
      ,supplier_part_no
      ,qty_shipped
      ,invoice_no
            ,convert(varchar(10),invoice_date,101) as invoice_date
      ,round(cogs_amount/qty_shipped,4) as cost
      ,cogs_amount
      ,invoice.customer_id
      ,Stockable_Location
      ,extended_price
      ,isnull(value_string,'') as vendor_customer_number
from
      vHerman_invoice_header_line as Invoice
     
      left join custom_column_data_customer as custom_customer
      on Invoice.customer_id = custom_customer.customer_id
      and area_x_custom_column_uid = 4    
     
      left outer join p21_view_inventory_supplier as supplier
      on invoice.item_id=supplier.item_id INNER JOIN
      supplier AS supplier_man on supplier.supplier_id = supplier_man.supplier_id

where
            Invoice.Invoice_Date >= @From_Date
            and Invoice.Invoice_Date < (@Thru_Date + 1)
            and supplier_man.supplier_name in (@Manufacturer)
            and (Invoice.customer_id = @CustomerID
               OR @CustomerID = 'ALL')
               and supplier_part_no IS NOT NULL
               and invoice.order_no is not null

)

else if @QueryParameterType=3


(
SELECT
Customer_Name

      ,ship2_address1  
      ,ship2_city
      ,ship2_state
      ,LEFT(ship2_postal_code,5) AS ship2_postal_code    
        ,supplier_man.supplier_name as Manufacturer

      ,bill2_address1  
      ,bill2_city
      ,bill2_state
      ,LEFT(bill2_postal_code,5) AS bill2_postal_code
     
      ,right(invoice.item_id,len(invoice.item_id)-3) as ItemID
      ,supplier_part_no
      ,qty_shipped
      ,invoice_no
            ,convert(varchar(10),invoice_date,101) as invoice_date
      ,round(cogs_amount/qty_shipped,4) as cost
      ,cogs_amount
      ,invoice.customer_id
      ,Stockable_Location
      ,extended_price
      ,isnull(value_string,'') as vendor_customer_number
from
      vHerman_invoice_header_line as Invoice
     
      left join custom_column_data_customer as custom_customer
      on Invoice.customer_id = custom_customer.customer_id
      and area_x_custom_column_uid = 4    
     
      left outer join p21_view_inventory_supplier as supplier
      on invoice.item_id=supplier.item_id INNER JOIN
      supplier AS supplier_man on supplier.supplier_id = supplier_man.supplier_id

where
            Invoice.Invoice_Date >= @From_Date
            and Invoice.Invoice_Date < (@Thru_Date + 1)
            and supplier_man.supplier_name in (@Manufacturer)
            and (Invoice.customer_id = @CustomerID
               OR @CustomerID = 'ALL')
               and supplier_part_no IS NOT NULL
               and invoice.order_no is not null
)

Make a copy of your rdl/rdlc file, and tinker with the copy.

You can have an incorrect expression anywhere, does tablix 'table1' have an expression in the Hidden-property to control whether or not the tablix is visible?  Maybe a row-visibility expression in table1 is wrong?

I'm starting to question whether or not the problem is with SQL, try using one simple select-statement, without any if-statements, or parameters, and replace the where-clause with where 1=2 to see if the problem goes away, I mainly work with Microsoft SQL Server, and the following don't look right:

Invoice.Invoice_Date < (@Thru_Date + 1)
DATEADD-function is needed to increment a date/datetime, and you need to specify which date-part you want to increment (day, year, month, hour, seconds)

Invoice.customer_id = @CustomerID OR @CustomerID = 'ALL'
If Invoice.customer_id is an integer then you cannot compare it to the string 'All'

Hi John - I've boiled it down to changing the @manufacturer parameter to 'allow multiple values' versus a single selection. If this is UNchecked - no error, as soon as I change to allow multi...error. Again, there is an expression on the tabloid for visibility and expressions on multiple columns as well (true/false to show or hide based on QueryTypeParameter). I'm a little bit at a loss since it works perfectly with any single value but kicks the error as soon as I change to allow multiple.

Thanks again for your help and insight on this!

Jeremy

Using scalar-value binary-operator scalar-value is easy to understand (i.e., 'hello' = 'blah' is false;  5 >= 2 is true).  When you added a check-mark to allow multiple values then you've changed your scalar-value to an array-object.  I would think most functions don't work with arrays.  If you would provide an example hidden-expression that is failing then someone could help.

Thanks John,

  I'm using the following as a Column visibility expression:

=IIF(Parameters!QueryParameterType.Value = 2, True, False)

Column visibility expression:

=IIF(Parameters!QueryParameterType.Value = 2, True, False)

I also have Filter on the tablix with the following:

Expression: [ship2_state]

You have not provided the hidden-expression for table1, which was in the pictured error.

If parameter QueryParamterType does not allow multi-value then the column-visibility expression seems valid.

You provided a picture showing the table-filter, which uses an in-operator with the multi-value parameter ship2_state, so that too is correct.

If allowing multi-values for parameter manufacturer is the only thing causing errors then you must investigate/correct every location where you use that parameter.  I would open the rdl/rdlc SSRS report in code-view (XML), do a search for manufacturer, and comment-out each expression.  Preview the report, the goal is to eliminate the error.  Now that your report is error-free, remove the comments from one commented-out expression, and try the preview again.  Keep doing this until you get an error, this will proves which expression is faulty, repair that expression, and repeat this process for each commented-out expression.

Hi John - there is no hidden expression - only the filter expression and visibility. Everything I can find leads me to changing the expression to an array if I want to use multi-value parameters. I'm not familiar enough with expressions to rewrite what I have. I think if I did that the error would subside.