SSRS - Hidden expression contains an error: Overload resolution failed

Jeremy Poisson
Jeremy Poisson used Ask the Experts™
on
Hi Experts,

  I'm receiving the error below after changing a single-value parameter to allow multiple values and I have not been able to get past it for weeks. Oddly enough, I have another parameter (@state) that I've set to allow multiple values, and it works perfectly fine.

Under the error is the primary data set code, as well as the ones used for suppliers (@Manufacturer) and states (@state). I have also included a number of screenshots that show the column visibility expressions, the single tablix filter for the ship2_state column and screenshots with the errors, column filters and parameter details.

I have attempted to use joins - e.g. =IIF(Join(Parameters!QueryParameterType.Value,",").Contains("1"), False,True) or =Switch(

Parameters!QueryParameterType.Value(0) = "2", False,

Array.IndexOf(Parameters!QueryParameterType.Value,"1") > -1, False,

True, True

)

and nothing is working. I really need someone to help take a look and show me what I'm missing. I've outlined everything in the SSRS report.

ERROR:

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)


DataInfo dataset source (main body of report):


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
)




Dataset1 source (@statecode parameter - working fine with multiple values allowed):


select statecode from states


suppliers source (used for @Manufacturer parameter - working fine with single value):

SELECT distinct supplier_name
FROM supplier
where supplier_name is not null and supplier_name <> ''
and delete_flag <> 'Y'
order by supplier_name asc
SSRS-Overload-Resolution-Error-Argum.png
SSRS-Manufacturer-Parameter.png
SSRS-Manufacturer-Parameter-Details.png
SSRS-Tablix-Filter-Parameter.png
SSRS-Column-Visibility-ShipTo.png
SSRS-Column-Visibility-BillTo.png
SSRS-Column-Filter.png
SSRS-Manufacturer-Parameter-Availabl.png
SSRS-State-Parameter-Available-Value.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Please show a picture how parameter @QueryParameterType is defined, in some cases you are comparing this parameter to an integer, in other places you are comparing it to a string.  If it allows multi-values then some expressions are incorrect.
Jeremy PoissonPresident

Author

Commented:
The QueryTypeParameter is actually defined in the first SQL query. You manually enter 1, 2 or 3, then contingent upon that, the column filters kick in and are shown or hidden. The field is text but I've tried with integer and still the same error. I'm traveling so I had to use imgur. Here's what it looks like: https://imgur.com/a/HNOlVr5
One or more expression to hide columns/rows/tablix/whatever is incorrect.  I think there is a data-type mismatch in this picture because QueryParameterType is defined as Text:
IIF(Parameter!QueryParameterType.Value = 2, True, False)
should be
IIF(Parameter!QueryParameterType.Value = "2", True, False)

Open in new window

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Jeremy PoissonPresident

Author

Commented:
Thanks again, John. I updated and still no change. I'm truly at a loss.
Would you be able to attach the rdl/rdlc file?  I would open it up in NotePad++, search for each occurrence of Parameter!, and visually inspect each expression (especially hide-expressions).
Jeremy PoissonPresident

Author

Commented:
Hi John - thanks again for sticking with me on this. I've looked at this at least 200 times. I'm praying you find something that I overlooked every time! This is literally the last thing that needs to be done on this report. Please let me know if you can access the file here:

http://s000.tinyupload.com/index.php?file_id=00343863555879744432
The following hidden expression is incorrect, Manufacturer allows multi-value (array-object), which cannot be compared to a scalar-value:

<Hidden>=Parameters!Manufacturer.Value&lt;&gt;"low"</Hidden>

I also think these are incorrect because the Imgur photo shows QueryParameterType defined as text, maybe change it to Integer:

<Hidden>=IIF(Parameters!QueryParameterType.Value = 1, True, False)</Hidden>
<Hidden>=IIF(Parameters!QueryParameterType.Value = 2, True, False)</Hidden>
Jeremy PoissonPresident

Author

Commented:
So what should I change these to? I've tried all that I know but maybe I'm not doing it in the proper order.
If you are trying to find a partial-match in the Manufacturer multi-value parameter then create a function in the custom-code area that cycles though the array and uses FINDSTRING

If you are trying to find an exact-match in the Manufacturer multi-value parameter:
=IIF( Array.IndexOf(Split(Parameters!Manufacturer.Value.ToString(), ","), "low") = -1, True, False)

Open in new window

Jeremy PoissonPresident

Author

Commented:
Thanks John - I'm looking to be able to select multiple manufacturers from the drop down and be able to hide columns based on the QueryTypeParameter entered. If the latter expression you posted will do this, then that is what I need. I'm just curious how it's going to work since there are 2 different values (1 and 2) that dictate which columns are hidden.
I haven't verified anything in SSRS/ReportBuilder, but I think you have enough information to go further.  I would definitely change QueryParameterType to Integer because all of your hide-expressions are using integer.

I would comment-out the following hide-expression, hoping to see fewer/no errors, and then fix it later on.

Hidden>=Parameters!Manufacturer.Value&lt;&gt;"low"</Hidden>
Jeremy PoissonPresident

Author

Commented:
Thanks John - I'll have a go at it and follow-up with results.

Again, many thanks for all of your time and expertise.
Jeremy PoissonPresident

Author

Commented:
Phenomenal - I commented out the line in the RDL and changed QueryTypeParameter to integer and it now works like a champion.

John, I can't thank you enough.
SSRS-POS-By-ShipTo-Multi-Manu-RESOLV.png
Jeremy PoissonPresident

Author

Commented:
First, let me say that unless every expert here is independently wealthy (or robots) the fact that they take so much time is incredible. And, in this case, John Vidmar was exceptional with both his time and expertise. Clearly, this was an odd issue, and he was the ONLY expert that reached out to help. Well, not only did he help, but he did in fact, resolve the issue.

If not for John, I'd still be flailing in the wind, literally 1 step from the finish line.

Well done for Mr. John Vidmar


Jeremy

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial