topic Re: SmallInt parameter in paginated report won't work as available values and default values in Report Server

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

Neka — Tue, 11 Jul 2023 20:48:01 GMT

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!

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

d_gosbell — Fri, 14 Jul 2023 02:38:14 GMT

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

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

Neka — Wed, 19 Jul 2023 15:11:50 GMT

Thank you this fixed the issue.