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:
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.
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
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.
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:
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.
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
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
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
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.
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
Public Class CubeInfo
''' Returns Last Processed Date
Public Shared Function GetCubeLastProcessedDate() As DateTime
Dim oServer As New Microsoft.AnalysisServices.Server
oServer.Connect("Data Source=" + Context.CurrentServerID)
Dim dtTemp As DateTime = oServer.Databases.GetByName(Context.CurrentDatabaseName).Cubes.GetByName(Context.CurrentCube.Name).LastProcessed
Catch ex As Exception
'//return Context.CurrentCube.LastProcessed; //this doesn't work because of a bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606
- 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]
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.
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:
Reporting Services: Using XML and Web Service Data Sources
Reporting Services: Extending with SQLCLR
Data Sources Supported by Reporting Services
Tutorial: Using XML Data in a Report
Implementing a Data Processing Extension
Using an External Data Source with Reporting Services