Extending SSRS Data Sources


 


Introduction


SQL Server Reporting Services comes out of the box with a good few data sources. Microsoft SQL Server and Microsoft Analysis Services are the main ones I use, and you can pretty much connect to any other database product using OLEDB/ODBC. What can you do when you want to display in reporting services that does not come from a database ?

There are two common methods used to provide data extensibility with reporting services:
















Method


Description


Pros/cons


XML Data Source


The XML data provider flattens the XML structure into a data set that can be accessed by the reporting engine. The flattening and integration with diverse Web services, Web pages, and arbitrary XML documents is powerful, but sometimes confusing.


Pros


Best choice for Web Services


Can model non “flat” data structures


Custom Dot.Net Code can be exposed as web service through ASMX


Does not need rely on SQL Server


 


Cons


Needs to deploy to an IIS web server. I find this really annoying as I like a report to encapsulate all it needs without bits hanging off.


Not as intuitive to work with as Dataset based sources


 


custom Data Processing Extension


Dot.Net Library that enables you to bridge a data source and a dot.net dataset.


Modeled after a subset of the .Net Framework data provider but has some extensions such as server side aggregates


One the Extension is deployed and registered in the configuration file it appears in the list of data sources in report designer.


A simplified data access architecture, often with better maintainability and improved performance.


The ability to directly expose extension-specific functionality to consumers.


Best choice for complex extension which will be re-usable. Such as exposing a suite of data from non ODBC compatible source.


 


Cons


Complex to develop and fiddly to deploy, especially in web farm (deployed to each node)


Not easily compatible with Report Builder.


Rather than pull your data from reporting services you could just use the extensibility features of a supported DataSource to push the data to reporting services. There are two common routes here:
















Method


Description


Pros/Cons


Sql CLR / stored procedures


Visual Studio “Database” Project used to write dot.net classes that can be registered in sql server 2005 as first class objects. Stored procedures, Table Values Functions, Scalar Functions are useful here.


The DLL is loaded into the host database via the “create assembly” command and is then hosted within SQL Server.


SQL Server creates one thread pool per user per assembly.


You can also provide COM and external calls using legacy extended stored procedures (xp_*) in SQL Server. I generally avoid this in favour of sqlclr, although xp_cmdshell is still very handy.


Pros


Written in common dot.net language


Best choice for Flat Data


Integrated into T-SQL Query Language


Hides complexity from Report Designer


Data Source can be exposed to Report Builder through a Report Model.


Scripted Deployment via t-sql


 


Cons


Requires sqlclr to be enabled (disabled by default)


Largely limited to the 12 safe namespaces


By their nature sqlclr functions that access heterogeneous data tend to not be “safe” so some CAS issues when loading into database.


More suited to relational tabular format.


 


SSAS Stored Procedures


Dot.Net class library aka MDX extensions can be loaded into the “Assemblies” section of Analysis Services using the xmla <ObjectDefinition><Assembly> (See sample CubeInfo.xmla in example code)


 


These functions can then be exposed within MDX to be used in the same way as calculated measures using XMLA


 


 


Pros


Written in common dot.net language


Best choice for integrating with other OLAP data sources using MDX.


Integrated into MDX Query Language


Hides complexity from Report Designer, normal Analysis Services Data Source used.


Deployed to SSAS Server rather than individual database


Scripted deployment through xmla


 


Cons


Requires Assembly to be registered within Analysis Services.


Data is exposed through MDX, which may be less familiar to report writers than SQL.


 


 


The last and I guess most obvious way to get non standard data into a report is to data warehouse it: e.g write an ETL or custom code to transform the data into a relational format where reporting services can suck it up natively. The architectural “pivot point” on this approach is how real time you want the data and if you can feasibly maintain a copy for reporting.

Recently I have been trying quite a seemingly simple task: to display the currently running SSIS packages on a report. It would have been really cool if I could have brought this into SQL Server as sqlclr Table Valued Function so we could have syntax like “Select * FROM RunningDtsPackages(@Server)”, but alas this is not possible as the ManangedDTS namespace is not in the list of allowed external namespaces (even though it is technically part of the SQL product !), assumably this is because it references the windows.forms and many other namespaces itself. I’m working on implementing as a web service to expose XML instead.

Example


This example looks at how to take data only retrievable through dot.net and return in to Reporting Services through an Analysis Services Stored Procedure. The best use case for this is of course data that you want to join to olap data via MDX.

In this case I want to return meat data on an OLAP cube such as: Last Processed Time, Data Source, Server Name, Database Name, Build Number and current user.

This can pretty much be implemented in any one of the methods above, but for real time access to cube meta data it makes sense to use an Analysis Services stored procedure.

CubeInfo SSAS Stored Procedure (vb.net)



  • Open Visual Studio 2005
  • Start a New Class Library Project called “CubeInfoMDX”
  • Add a reference to Microsoft.Analysis.AdoMdServer (msmgdsrv.dll). This is used to use the “Context” object to refer to current analysis services session.
  • Add a reference to Microsoft.AnalysisServices (Microsoft.AnalysisServices.dll). This is used to create a new AMO session.
  • Enter code to use AMO to return Last Processed Time





Imports System


Imports System.Collections.Generic


Imports System.Text


Imports Microsoft.AnalysisServices


Imports Microsoft.AnalysisServices.AdomdServer


 


Namespace ASStoredProcs


    Public Class CubeInfo


 


        ''' <summary>


        ''' Returns Last Processed Date


        ''' </summary>


        Public Shared Function GetCubeLastProcessedDate() As DateTime


            Dim oServer As New Microsoft.AnalysisServices.Server


            Try


                oServer.Connect("Data Source=" + Context.CurrentServerID)


                Dim dtTemp As DateTime = oServer.Databases.GetByName(Context.CurrentDatabaseName).Cubes.GetByName(Context.CurrentCube.Name).LastProcessed


                oServer.Disconnect()


                Return dtTemp


            Catch ex As Exception


                Throw


            Finally


                oServer.Dispose()


            End Try


            '//return Context.CurrentCube.LastProcessed; //this doesn't work because of a bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606


        End Function


 


      End Class


End Namespace


 



  • Compile Project
  • Connect to SQL Server Management Studio\Analysis Server
  • Right Click on Assemblies and select New
  • Enter Assembly path with security of “unrestricted”
  • Test the MDX extension with the following MDX Query
  • with member [Measures].[LastProcessed]





with member [Measures].[LastProcessed]


 as [CubeInfoMDX].GetCubeLastProcessedDate()


 


 select {[Measures].[LastProcessed]


}


 on columns


from [Adventure Works]


 


We Can Now Make Up Report:


  • Add a new VS Project of Type “Report Server Project Wizard”
  • Add shared data source called “AdventureWorksMDX”

  • Click Design Mode in Query Builder and enter your MDX directly. Note that you can use the Graphical Query Designer if you add calculated measures in your cube for the extended measures. However I prefer not to tie the cube to the extensions.

  • Add your column to report and call Report “CubeInfoMDX”
  • You can now deploy and use you report.

In My sample project (attached) I've added some more meta data that you might want to bring into MDX using AMO such as: server name, database name, cube name, Data Source Name.


Conclusion


Microsoft SQL Server 2005 Reporting Services offers integration with heterogeneous environments through lots of routes, giving you some nice options for accessing non relational heterogeneous data.

There are some great samples of using Analysis Services Stores Procedure on CodePlex at:

http://www.codeplex.com/ASStoredProcedures

For more information:


Microsoft SQL Server Developer Center

Reporting Services: Using XML and Web Service Data Sources

http://msdn2.microsoft.com/en-us/library/aa964129.aspx

Reporting Services: Extending with SQLCLR

http://msdn2.microsoft.com/en-us/library/bb293147.aspx

Data Sources Supported by Reporting Services

http://msdn2.microsoft.com/en-us/library/ms159219.aspx

Tutorial: Using XML Data in a Report 

http://msdn2.microsoft.com/en-us/library/ms345334.aspx

Implementing a Data Processing Extension

http://msdn2.microsoft.com/en-us/library/ms154655.aspx

Using an External Data Source with Reporting Services

http://msdn2.microsoft.com/en-us/library/ms152917.aspx

CubeInfoMDX.zip

Skip to main content