Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Neka
Helper I
Helper I

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

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'.

Neka_2-1689106623232.png

 

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.

Neka_4-1689106678253.png

 

Neka_1-1689106599710.png

 

 

The query is simple, it's basically this:

Select DISTINCT paramItems

FROM table

 

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

Neka_0-1689106417272.png

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!

 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

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

View solution in original post

2 REPLIES 2
Neka
Helper I
Helper I

Thank you this fixed the issue.

d_gosbell
Super User
Super User

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Kudoed Authors