SSRS Report Builder Part 10.6 - Stored Procedure Parameters

[@TheMindverse]

I hadn't used SSRS to create an SP-based report in a while, and this was just what was needed to refresh my memory. Thanks so much.

[@WiseOwlTutorials]

Happy to hear that it helped, thanks for watching!

[@richardekeopara886]

All your SSRS tutorials are awesome! I have watched many of them over the last few weeks and I'm a confident SSRS developer now, thanks to you.

[@WiseOwlTutorials]

Happy to hear that Richard, thank you for watching!

[@GandhiJa]

This was so helpful and easy to follow step by step. Thanks so much!

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@briangubkin474]

Thank you! This tutorial was clear and helped me with my issues.

[@WiseOwlTutorials]

Happy to hear that Brian, thanks for watching!

[@hicksonuk]

Great explanation, thank you 👍

[@WiseOwlTutorials]

Cheers Lee, thanks for watching!

[@enrikekasijas705]

Your videos are so amazing. Right know I am checking VBA ;P I will get to this one at one point. I hate I am just too far behind. I need to catch up. Ps. Your VBA tutorials are out of this world xD love you!;D

[@WiseOwlTutorials]

Ahh, thanks Enrike, we love you too!

[@tsunamio7750]

This is incredible. thanks

[@WiseOwlTutorials]

You're very welcome, I'm happy you liked it!

[@Microgen86]

wise owl the best👌

[@WiseOwlTutorials]

Thanks!

[@caseymccarthy1749]

My stored procedure works using where (Budget = (@Budget) or @Budget is null). However, when I run the report in report builder using a NULL value returns no records instead of all records. Any ideas?

[@WiseOwlTutorials]

Hi Casey! Tricky to tell without seeing what you've got. Have you assigned default values of NULL to the parameters in the stored procedure? Feel free to drop your code in a comment!

[@alexandredantas-c1m]

Is it possible to use Stored Procedure in Fabric ? data source is a Lakehouse or a semantic model in Power Report BI Builder, options Tables and Stored Procedures are disable when the data source is a Lake or Semantic Model. Thanks.

[@WiseOwlTutorials]

Hi! I wouldn't expect to be able to a stored procedure when connecting to a PBI Semantic Model, no. I don't know enough about Fabric Lakehouses to know whether it's possible there. I'd only expect to have access to a stored procedure if you connect directly to the SQL Server database. You could use stored procedures to populate the PBI semantic model. I'm not sure if that helps!

[@likhitaGrandhi]

Hi, Thank you for your amazing tutorials, they are very clear. It has helped me developing many reports and improve my skills. I have question, Can you please explain how to pass a user defined table type to SP in Report builder? I would really be Grateful if you can help me. Thanks.

[@WiseOwlTutorials]

Hi! Happy to hear that you enjoy the tutorials. Sorry but I don't know how you'd pass a user-defined table type from Report Builder to a stored procedure.

[@abdullahquhtani8058]

Is there a way to use server authentication to connect to database and save or embedded username and password or SQL connection string in RDL file ?

[@WiseOwlTutorials]

Hi! You might find these resources helpful learn.microsoft.com/en-us/sql/reporting-services/report-data/store-credentials-in-a-reporting-services-data-source?view=sql-server-ver16 learn.microsoft.com/en-us/sql/reporting-services/report-data/specify-credential-and-connection-information-for-report-data-sources?view=sql-server-ver16

[@gm1684]

I am trying to do it with multi value it's not working. I mean I do have a parameter that have a dropdown list I need to make it null it didn't work why? I am using where ISNULL(NULLIF(w.Complete, ''), '_') in (@JOB). thank you in advanced

[@gm1684]

the parameter is @JOB varchar(200) I have chose that where clause because I do have some null data in w.complete coulmn. thanks

[@WiseOwlTutorials]

Hi! Try video 11.5 in this playlist kzbin.info/aero/PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU I hope it helps!

[@DanielTorres-jz5qo]

Hello! I am very grateful for your channel, I have improved a lot with reports after your classes. I have a situation that I could not solve, if you can help me I would be grateful! Situation: I have a Store Procedure that has an OUTPUT parameter which is an ID to perform a select on another table. when I call this SP in my dataset I can't recover the output parameter, is this possible in the report biulder? Example Code: DECLARE @output INT EXEC Stp_example @DT_example = '20230717' ,@File =0 ,@Output = @output output IF @output > 0 BEGIN SELECT * from tLogEventos WHERE iIDEvento = @output order by dHoraExecucao desc END Yours sincerely

[@WiseOwlTutorials]

Hi Daniel! Sorry for the late reply - I didn't get a notification for this comment from KZbin. Yes, you can get the values of output parameters in the dataset. Here's a simplified example using the Movies database. A stored procedure which accepts a film Title and outputs the FilmID via an output parameter: create or alter proc dbo.uspGetFilmID @FilmName nvarchar(255) ,@FilmID int output as begin set @FilmID = ( select top 1 FilmID from dbo.Film where Title like @FilmName order by ReleaseDate asc ) end In the report, a dataset populated with the following code to return the list of Roles for the captured FilmID: declare @OutputID int exec dbo.uspGetFilmID '%potter%' ,@OutputID output select * from dbo.Role where FilmID = @OutputID It was important not to use named parameters in the EXEC statement, otherwise Report Builder created report parameters automatically. I hope that helps!