Forum Discussion
Paginated Report with Parameter breaks in Service
Hello Experts,
I have created a Paginated report that uses a Stored Procedure with a Date Parameter(Azure SQL) and it works fine in the Report builder tool. Individual SQL credentials were used to create the Data Source
But when published to service, it throws "Data Cant be fetched" error?
We use an On Prem gateway and a shared connection in the service.
Same setup works in Service for a Stored Procedure without a parameter.
Any thougts?
Since the no-parameter proc works, the connection is fine and it's the parameter binding that breaks.
Two likely fixes to try out :First thing to try: in Report Builder, change the dataset query type from Stored Procedure to Text and write
EXEC dbo.YourProc @DateParam = @DateParam
then map @DateParam in the dataset's Parameters tab and republish. The gateway handles parameterized proc calls differently than Report Builder does locally, and making the binding explicit like this fixes it in most cases.
If that doesn't do it, check the parameter type. Make sure both the report parameter and dataset parameter are Date/Time, not Text.
Report Builder uses your local regional settings, the Service doesn't, so a date string that parsed fine on your machine can fail in the cloud.If that was helpful, please give a thumbs up and mark it as the accepted solution.
Thanks,
Shai Karmani
3 Replies
- Shai_Karmani
Super User
Since the no-parameter proc works, the connection is fine and it's the parameter binding that breaks.
Two likely fixes to try out :First thing to try: in Report Builder, change the dataset query type from Stored Procedure to Text and write
EXEC dbo.YourProc @DateParam = @DateParam
then map @DateParam in the dataset's Parameters tab and republish. The gateway handles parameterized proc calls differently than Report Builder does locally, and making the binding explicit like this fixes it in most cases.
If that doesn't do it, check the parameter type. Make sure both the report parameter and dataset parameter are Date/Time, not Text.
Report Builder uses your local regional settings, the Service doesn't, so a date string that parsed fine on your machine can fail in the cloud.If that was helpful, please give a thumbs up and mark it as the accepted solution.
Thanks,
Shai Karmani 
v-menakakotaCommunity Support
Hi klickmasters ,
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank Shai_Karmani , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.Best Regards,
Community Support Team- v-menakakota
Hi klickmasters ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.
Best Regards,
Community Support Team