Forum Discussion
Helper IPaginated report throw an error "Some parameters do not have valid values"
Hello, I have a paginated report with a transitional parameter, that was used to filter a subsequent query.
An example,
User need to enter three prompts:
- Company name: ABC
- Start Date: 1/1/2025
- End Date: 3/1/2025
They are used to filter a main query A, the main query A also has an embeded filter, Product = iPhone.
The combination of the three prompts and the embeded filter will filter the main query A and return a set of values. This main query A is used as a dataset to support a parameter "Param_iPhone"'s default values. The default values of "Param_iPhone" will be used to filter another query B.
If the main query A returns no values (for example, ABC company didn't order any iPhones between 1/1/2025 and 3/1/2025), the report will throw an error:
"Unable to render paginated report
Some parameters do not have valid values. Default values of Param_iPhone are not valid. " Is there way to resolving this error or let the report display a meaningful message instead of an error?
Seems like you need to modify the dataset used for Param_iPhone defaults so it always returns at least one row.
Step 1) Create a “Default Values” dataset that always returns at least 1 row
Let’s say your current dataset A returns candidate values for Param_iPhone.
-- ds_Param_iPhone_Defaults WITH A AS ( SELECT DISTINCT iPhoneValue FROM dbo.Orders o WHERE o.Company = @Company AND o.OrderDate >= @StartDate AND o.OrderDate < DATEADD(day, 1, @EndDate) AND o.Product = 'iPhone' ) SELECT iPhoneValue FROM A UNION ALL SELECT '(None)' WHERE NOT EXISTS (SELECT 1 FROM A)Step 2) Configure the parameter to accept the fallback
In Report Builder → Param_iPhone → Properties:
Available Values
- Get values from a query: ds_Param_iPhone_Defaults
- Value field: iPhoneValue
- Label field: iPhoneValue
Default Values
- Get values from a query: ds_Param_iPhone_Defaults
- Value field: iPhoneValue
Step 3) Update query B to treat “(None)” as “return no data”
-- ds_MainQueryB
SELECT ...
FROM dbo.Sales s
WHERE s.Company = @Company
AND s.OrderDate >= @StartDate
AND s.OrderDate < DATEADD(day, 1, @EndDate)
AND (
param_iPhone <> '(None)'
AND s.iPhoneValue = param_iPhone
)
4 Replies
- cengizhanarslan
Super User
Seems like you need to modify the dataset used for Param_iPhone defaults so it always returns at least one row.
Step 1) Create a “Default Values” dataset that always returns at least 1 row
Let’s say your current dataset A returns candidate values for Param_iPhone.
-- ds_Param_iPhone_Defaults WITH A AS ( SELECT DISTINCT iPhoneValue FROM dbo.Orders o WHERE o.Company = @Company AND o.OrderDate >= @StartDate AND o.OrderDate < DATEADD(day, 1, @EndDate) AND o.Product = 'iPhone' ) SELECT iPhoneValue FROM A UNION ALL SELECT '(None)' WHERE NOT EXISTS (SELECT 1 FROM A)Step 2) Configure the parameter to accept the fallback
In Report Builder → Param_iPhone → Properties:
Available Values
- Get values from a query: ds_Param_iPhone_Defaults
- Value field: iPhoneValue
- Label field: iPhoneValue
Default Values
- Get values from a query: ds_Param_iPhone_Defaults
- Value field: iPhoneValue
Step 3) Update query B to treat “(None)” as “return no data”
-- ds_MainQueryB
SELECT ...
FROM dbo.Sales s
WHERE s.Company = @Company
AND s.OrderDate >= @StartDate
AND s.OrderDate < DATEADD(day, 1, @EndDate)
AND (
param_iPhone <> '(None)'
AND s.iPhoneValue = param_iPhone
) - VijayP
Community Champion
V-yubandi-msftCommunity Support
Hi OceanExplorer ,
cengizhanarslan , has correctly pointed out the issue and shared a few steps to follow. Have you tried those steps, and did they help? If you are still facing any issues or need additional information, please let us know.
Thanks for your valuable response cengizhanarslan .
Regards,
Yugandhar.
- V-yubandi-msft
Hi OceanExplorer ,
We haven’t received a response from your end yet. Please let us know whether the issue has been resolved or if you’re still facing any difficulties. Feel free to reach out if you need further assistance.
Thank you.
