New to SSRS, Need Direction, How Best to Utilize

[jpquinn]

Hello,

I have recently had a new to me SQL Server upgraded from 2014 to 2016, was running only SSDE, SSIS, and SSAS Tabular, now I have had an SSAS MD instance created as well as had SSRS installed and turned on for the first time. We currently have an Azure Gateway that hosts our on premise SSAS Tab cubes, and access the cubes data source in Power BI Services and have built a number of Dashboard and Reports with that method and format.

Once SSRS was installed and running, I was given a Web URL to the our SSRS Instance. I went through some security and access issues that have been resolved. So I'm seeing SSRS for the first time, and as I poke around, I get the impression that it's basically used to show case a completed report. I don't see any reporting type tools anywhere.

Am I to download/install another tool (Report Builder?) to use on my PC in concert with the SSRS Web link in order to build Dashboards or reports in SSRS so it can host them?

Am I to use Visual Studio to connect to SSRS to design/create the report there, and then someone deploy it and bring the report into SSRS that way? Is that that the way most folks use it?

As far as advise, guidance and direction, my original idea, as we have a lot of complex reports written in freehand SQL that pull from a Teradata data warehouse that go into an Excel file and emailed/share-drive/SharePoint, I thought, I could take that SQL code and place into a SSRS report and source the data warehouse, bring that report output into the SSRS world instead, and from there, use Power BI to read that report, and use the Power BI Services as the main central report/dashboard repository, and this would be good use of having SSRS (and Report Builder?) to get away from freehand SQL, Excel and eMail. I'm open to best practice suggestions that best utilize my current systems and tools.

Thanks,

jpquinn

[Seth_C_Bauer · Accepted solution]

@jpquinn You are covering quite a bit of ground here, so I'll try to keep my answers short.

Once SSRS was installed and running, I was given a Web URL to the our SSRS Instance. I went through some security and access issues that have been resolved. So I'm seeing SSRS for the first time, and as I poke around, I get the impression that it's basically used to show case a completed report. I don't see any reporting type tools anywhere. You are correct, the report server is for supporting end users in accessing and running SSRS reports. It also handles subscriptions (email report to end users). With 2016, it also offers the ability to store PBIX files, but this is a really rough rev 1 implementation.

Am I to download/install another tool (Report Builder?) to use on my PC in concert with the SSRS Web link in order to build Dashboards or reports in SSRS so it can host them? You can user Report Builder as the tool to create your SSRS reports, but if you have access to Visual Studio, I would use report projects to build/deploy/store your SSRS reports.

Am I to use Visual Studio to connect to SSRS to design/create the report there, and then someone deploy it and bring the report into SSRS that way? Is that that the way most folks use it? You can do all of the above in a Report Project. If you also include TFS (Team Foundation Server) - you would have a platform for change control and wider access by multiple users. I would highly recommend this route. You would need to download SSDT (SQL Server Data Tools) as a VS add-in. TFS is a seperate server/product.

As far as advise, guidance and direction, my original idea, as we have a lot of complex reports written in freehand SQL that pull from a Teradata data warehouse that go into an Excel file and emailed/share-drive/SharePoint, I thought, I could take that SQL code and place into a SSRS report and source the data warehouse, bring that report output into the SSRS world instead, and from there, use Power BI to read that report, and use the Power BI Services as the main central report/dashboard repository, and this would be good use of having SSRS (and Report Builder?) to get away from freehand SQL, Excel and eMail. I'm open to best practice suggestions that best utilize my current systems and tools. Consolidating reporting into SSRS is a good goal to no longer rely on Excel files and random emails. You can build a complete reporting solution to manage development, and also have a mechanism in subscriptions to handle email. But Power BI and SSRS serve two different purposes, I would suggest you think about how they will work together in the future, and not try to replace one with the other. Long term the integration between the two tools is going to be awesome, but they both look at data in different ways. In addition, Power BI can't connect to an SSRS report and use it as a datasource, it would be able to connect to the data sources that the SSRS report does though.

[Seth_C_Bauer · Accepted solution]

@jpquinn You are covering quite a bit of ground here, so I'll try to keep my answers short.

Once SSRS was installed and running, I was given a Web URL to the our SSRS Instance. I went through some security and access issues that have been resolved. So I'm seeing SSRS for the first time, and as I poke around, I get the impression that it's basically used to show case a completed report. I don't see any reporting type tools anywhere. You are correct, the report server is for supporting end users in accessing and running SSRS reports. It also handles subscriptions (email report to end users). With 2016, it also offers the ability to store PBIX files, but this is a really rough rev 1 implementation.

Am I to download/install another tool (Report Builder?) to use on my PC in concert with the SSRS Web link in order to build Dashboards or reports in SSRS so it can host them? You can user Report Builder as the tool to create your SSRS reports, but if you have access to Visual Studio, I would use report projects to build/deploy/store your SSRS reports.

Am I to use Visual Studio to connect to SSRS to design/create the report there, and then someone deploy it and bring the report into SSRS that way? Is that that the way most folks use it? You can do all of the above in a Report Project. If you also include TFS (Team Foundation Server) - you would have a platform for change control and wider access by multiple users. I would highly recommend this route. You would need to download SSDT (SQL Server Data Tools) as a VS add-in. TFS is a seperate server/product.

As far as advise, guidance and direction, my original idea, as we have a lot of complex reports written in freehand SQL that pull from a Teradata data warehouse that go into an Excel file and emailed/share-drive/SharePoint, I thought, I could take that SQL code and place into a SSRS report and source the data warehouse, bring that report output into the SSRS world instead, and from there, use Power BI to read that report, and use the Power BI Services as the main central report/dashboard repository, and this would be good use of having SSRS (and Report Builder?) to get away from freehand SQL, Excel and eMail. I'm open to best practice suggestions that best utilize my current systems and tools. Consolidating reporting into SSRS is a good goal to no longer rely on Excel files and random emails. You can build a complete reporting solution to manage development, and also have a mechanism in subscriptions to handle email. But Power BI and SSRS serve two different purposes, I would suggest you think about how they will work together in the future, and not try to replace one with the other. Long term the integration between the two tools is going to be awesome, but they both look at data in different ways. In addition, Power BI can't connect to an SSRS report and use it as a datasource, it would be able to connect to the data sources that the SSRS report does though.

[jpquinn]

"Consolidating reporting into SSRS is a good goal to no longer rely on Excel files and random emails. You can build a complete reporting solution to manage development, and also have a mechanism in subscriptions to handle email. But Power BI and SSRS serve two different purposes, I would suggest you think about how they will work together in the future, and not try to replace one with the other. Long term the integration between the two tools is going to be awesome, but they both look at data in different ways. In addition, Power BI can't connect to an SSRS report and use it as a datasource, it would be able to connect to the data sources that the SSRS report does though."

Can you please breifly describe the differences of reporting between PBI and SSRS reports? Are Vs Reports of a more complex detailed nature, and PBI is more Dashboard and one click drilling, and more of visualizing data?

I had planned to keep the PBI reporting to be built primarily from Cubes, and SSRS reports built primarily from SQL sourcing a data warehouse.

[Seth_C_Bauer · Accepted solution]

@jpquinn You hit the nail on the head. PBI is  great at analysis, looking at visuals to gain insights into large datasets to find hidden meaning, get an overall view of the business area, etc, etc. SSRS is fantastic for detailed, "form" like layouts, detailed reports that print well, etc. etc. You can highly customize look/layout and form for multiple forms of export/email/print.

There is some crossover, but the two tools complement, not replace, each other in those aspects.

But another thing I would point out, is it depends on what type of information is needed and who is using it that should dicate what tool you use. If you are sourcing your data from a warehouse, you could build quick models to support larger portions of the business that allow them to visualize that in Power BI. Then end users could work off a model for multiple report purposes and you woudn't be required to build a new SSRS report for every request. Just tweak a model for new things, and have end users do more "self service".

Power BI really opens a lot of doors to offload a ton of reporting onto the business users by giving them the ability to find their own answers.