SmallInt parameter in paginated report won't work as available values and default values

[Neka]

Hello,

I am working on a paginated report that has a parameter that is a smallint. I originally want the report to load showing all rows without the effect of the parameter.

I want this parameter to have a drop down menu showing all the available values you can select from and I have set up Parameter Properties for Available Values to 'Get values from a query'.

As well, when the report initially loads, I would like the report to show all output before we filter it down using the parameter. So I set up the Parameter Properties for Default Values to be 'Get values from a query'. I have also selected 'Allow multiple values in the 'General' menu of the Parameter Properties.

The query is simple, it's basically this:

Select DISTINCT paramItems

FROM table

However, when I run the report, I get this error:

I have tried converting the field to NVARCHAR within the original SQL query but I still get errors and the paginated report will not show all rows. 

How do I get a smallint parameter to default to showing all the rows when it loads but be able to filter after the load? Thank you!

[Neka]

Thank you this fixed the issue.

[d_gosbell · Accepted solution]

This error sounds like you have a NVARCHAR column that you are trying to insert into an smallint, but some of the values in that column are not valid

if you do the following I suspect you will get the same error. 

Select DISTINCT CAST(paramItems as smallint)

FROM table

You probably need to either fix your data or convert your parameter to a text parameter