SQL Server Data Tools (SSDT) and RDL Versions
By Garth Jones
You built your report using SQL Server Data Tools (SSDT) which saves the RDL in the SQL Server 2016 format. That’s great, but now you need the native RDL in the SQL Server 2008 R2 format. In this blog post I will show you where to find the SQL Server 2008 R2 RDL in SSDT.
While I was attending the Midwest Management Summit at the Mall of America (MMSMOA) in May, I gave a presentation on SQL Server Data Tools (SSDT) 2016. I talked about how I love using the new SSDT because you can use it with different versions of SQL Server (2008 to 2016) when creating dashboards and reports.
After finishing a report or dashboard with SSDT, you can then upload/publish it to your SQL Server Reporting Services (SSRS) reporting site/website. SSDT will do this by uploading the appropriate version of your RDL to your SSRS website. The report will appear within the ConfigMgr console about 10 minutes later.
During the presentation I mentioned that RDLs for projects created with SSDT are saved in the SQL Server 2016 format and a native SQL Server 2016 RDL can’t be uploaded to a 2014 or older SSRS website without first being converted to the older formats by using the SSDT publish feature.
Now this is where I tell you that I can’t see the forest for the trees! Doug Wilson stopped me afterwards and told me that RDLs for older SQL Server versions are created by SSDT and can be found within the debug folder!
I forgot that in Visual Studio the debug folder stores a copy of the RDL. The RDL stored within this folder is automatically created for the appropriate SSRS website.
Where is this folder found?
Within SSDT, in the Solution Explorer, expand your solution and project. In the example above, the solution is called EWR-Test Reports and the project is called Computer Details. Right-click on the project and select properties.
In the OutputPath a subdirectory (folder) will be listed. In my case it is bin\DebugLocal. This folder is where you will find a copy of your RDL(s) when building your project.
When you browse to this folder you will see the RDLs. Keep in mind that it will only have a copy of successfully built reports. I’m saying that because if you look at my earlier screenshots you will see three reports, but only two of them are within the DebugLocal folder. Why? I didn’t have time to add Role-Based Administration (RBA – smsresource.dll) to this version of SSDT yet.
If you are not sure about what version of SSRS to select when you are looking at the project’s properties, select Detect Version within the TargetServerVersion option. SSDT will go to your SSRS server and detect exactly what version of the RDL to create for you.
I often take for granted that everyone will know what every SSRS term means, so in case you were wondering, RDL stands for Report Definition Language. If you’d like to read more about RDLs, here’s the link to RDLs on the Microsoft docs site.
Do you have an idea for a blog post about a ConfigMgr query or reporting topic? Let me know. Your idea might become the focus of my next blog post!