If you’ve been reading this blog over time, you probably noticed quite a bit of my development was done with Report Builder. Yes, that’s the report model shipped with SQL Server 2005. Though my team is currently working on SQL Server 2008 R2, we’re only now starting to develop BI solutions with other tools. We’re set for a bit of a headache with our Report Builder 1.0 in SQL Server 2014.
Report Model has been one of a set of deprecated features on SQL Server 2012 (and obviously also on SQL Server 2014). SQL Server has released PowerView in SQL Server 2012 in aim to use it as a replacement to the abilities Report Builder 1.0 offered before. Obviously, the fact that PowerPivot existed even before, should have signaled the end of use for the SMDL. So now for deprecated features you can clearly see that “SQL Server 2014 Reporting Services does not include tools for creating or updating report models”.
The way we worked with our projects with Team System in BIDS is that we had one solution for all the projects linked to that subject. That means that the SSAS and SSRS and Report Model projects I had for Financing were all linked under the same solution file in Team System in our Visual Studio. Looking at the breaking changes in SQL Server Reporting Services in SQL Server 2014 I understood that will no longer be an option. As the article states: “SQL Server Data Tools (SSDT) no longer supports report model projects”, which means that:
We’ll need to have installed BIDS for SQL Server 2008 R2 in parallel to SSDT for SQL Server 2014
We’ll need to have two solutions for the same subject: one will be for all the projects we developed so far which have a Report Builder 1.0 project attached to them (almost all of them). The second solution for the same project will be for all the new developments in SQL Server 2014 for that same subject.
Opening a 2008 R2 solution in SSDT prompts you to upgrade it to 2014. The automatic upgrade breaks the RB project attached to that solution and means that you can no longer open that solution again in BIDS 2008 R2. Hence the need for two solutions.
We’ll need to change the data source for our RB project. Our databases are moving to SQL Server 2014 as well. Obviously, in SQL Server 2008 R2 we used a data source connection based on a Native OLE DB Provider of SQL Server Native Client 10.0
But that wouldn’t do if we need to connect from BIDS 2008 R2 to SQL Server 2014. So I changed the connection of the data source to be of type .Net Providers of SQLClient Data Provider and that worked out fine.
So, it seems that using Report Builder in SQL Server 2014 will require us to stretch quite a bit (as I’m sure is the case for anyone using it in SQL Server 2012), but I guess we’ll prove ourselves limber till we move our projects to a more updated environment.