cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jpquinn Helper I
Helper I

New to SSRS, Need Direction, How Best to Utilize

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: New to SSRS, Need Direction, How Best to Utilize

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

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

Highlighted
Super User III
Super User III

Re: New to SSRS, Need Direction, How Best to Utilize

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

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: New to SSRS, Need Direction, How Best to Utilize

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

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

jpquinn Helper I
Helper I

Re: New to SSRS, Need Direction, How Best to Utilize

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

 

 

Highlighted
Super User III
Super User III

Re: New to SSRS, Need Direction, How Best to Utilize

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

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors