Tag Archive for: Reporting Services

After covering the cool new stuff in R2, it’s time to talk about the uncool. One R2 change that really vexes me is RDLOM. RDLOM, short for Report Definition Language Object Model, lets developers program RDL in object-oriented way as opposed to using XML. RDLOM could save you a lot of effort when you have to meet more advanced requirements that involve pre-processing RDL. For example, in one of my projects we had to implement a wizard that would walk business users through steps to generate an ad-hoc report. Behind the scenes, the wizard would generate the report definition by using a custom RDLOM which we developed since back then Microsoft didn’t have their own. I included a scaled-down version of our implementation in my TechEd 2007 code sample. Although it took us a while to get our RDLOM implemented, it turned out to be invaluable down the road. Since we didn’t have to use XML API to manipulate RDL, it simplified programming RDL. For another project, we used RDLOM to let the customer specify which sections they want to see on the report. Once the selection was made, we would remove the unwanted sections from a report template. The list of real-life scenarios that could take advantage of RDLOM goes on …

Apparently, I wasn’t the only one bugging Microsoft about RDLOM that ships with SSRS and they promised that this will happen at TechED 2007 USA. Sure enough, SSRS 2008 brought us an unsupported version which I blogged about and covered more extensively in my Applied SSRS 2008 book. Although unsupported, the SSRS 2008 RDLOM was very useful. It was implemented in a single assembly (Microsoft.ReportingServices.RdlObjectModel.dll), exposed all RDL elements as objects, and supported serializing and deserializing RDL.

Alas, this will come to pass with R2. Upgrading to R2, you’ll find that the classes in Microsoft.ReportingServices.RdlObjectModel are marked as internal so you cannot use this assembly. Instead, RDLOM got moved into the Microsoft.ReportingServices.Designer.Controls.dll assembly. The good news is that there are rumors that RDLOM may get documented. Now the bad news:

1. Clearly, R2 RDLOM was not meant to be distributed. While you need to reference explicitly only Microsoft.ReportingServices.Designer.Controls and Microsoft.ReportingServices.RichText assemblies you’ll find that they are dependent on other assemblies and you need to distribute a total of 25 assemblies resulting in a whopping 25 MB of referenced code. One way to avoid shipping so many assemblies to deploy your application in the BIDS or Report Builder 3.0 folders.

1. R2 RDLOM will be 32-bit only because BIDS and Report Builder are 32-bit only.
2. R2 RDLOM doesn’t support serializing the object model. I found a hack that uses .NET reflection to invoke the internal Serialize method of the old RDLOM 2008.

The RDLOM R2 C# console application bundled in this code sample demonstrates how you can use the “new” RDLOM. Besides referencing the new assemblies, you need to use the Load method to deserialize RDL to the object model:

report = Report.Load(fs);

And the above-mentioned hack to serialize it back to disk or stream.

using (FileStream os = new FileStream(ReportPath, FileMode.Create)) {

Assembly assembly = Assembly.GetAssembly(typeof(Report));

object mc = assembly.CreateInstance(“Microsoft.ReportingServices.RdlObjectModel.Serialization.RdlSerializer”);

Type t = mc.GetType();

MethodInfo mi = t.GetMethod(“Serialize”, new Type[] { typeof(Stream), typeof(Report) });

report = (Report)mi.Invoke(mc, new object[] { os, report });

}

Other than that, RDLOM remains virtually unchanged from 2008 excluding that it now supports the R2 enhancements to RDL. What if you don’t need this overhead but you need to make small changes to RDL that don’t necessarily require an object-oriented layer. I’ve found that the easiest way to do this and avoid XML programming is to use LINQ to XML. The LINQ to XML project included in the same sample demonstrates this approach. For example, the following code changes the data source reference of the first data source.

static void Main(string[] args) {

XElement report = XElement.Load(“report.rdl”);

string dns = “{“ + report.GetDefaultNamespace() + “}”;

XElement ds = report.Element(dns + “DataSources”).Element(dns + “DataSource”).Element(dns + “DataSourceReference”);

ds.Value = “/Data Sources/AdventureWorksAS2008”;

report.Save(“report1.rdl”); }

Not happy about the RDLOM story? Join the movement and vote for my suggestion on connect.microsoft.com to have a supported RDLOM.

Two new data providers will be included in the SQL Server 2008 R2 release of Reporting Services, Microsoft SQL Azure and Microsoft SQL Server Parallel Data Warehouse, that will let you report from SQL Azure and SQL Server 2008 R2 Parallel Data Warehouse (code named Madison) databases respectively. I decided to take the Microsoft SQL Azure data provider for a spin. As you probably know by now, SQL Azure is a part of the Microsoft Windows Azure SaaS cloud offering. SQL Azure lets you host your SQL Server relational databases in the Microsoft data centers that provide scalability and fault tolerance services. This is great for companies of all sizes which don’t have resources or expertise to purchase and configure expensive hardware and software in order bo build scalable and highly available solutions. IMO, SQL Azure will be increasingly important, especially after Reporting Services and Analysis Services become available on SQL Azure although Microsoft doesn’t say yet when this will happen. At this point, you can only use Reporting Services as a client to author reports from SQL Azure-hosted databases.

Strictly, speaking if your database is hosted in SQL Azure, you can use the SQL Server or OLE DB providers (already included in Reporting Services) to send queries to SQL Azure. Just like on-premise solutions, SQL Server Azure clients retrieve data using the native SQL Server Tabular Data Stream (TDS). However, if you decide to use the native SQL Server providers, you will run into some issues with retrieving SQL Azure metadata. Hence, Microsoft added the SQL Azure data provider. To test it out, I followed these steps:

1. I signed up for SQL Azure. If you MSDN Universal subscriber, you can sign up to test SQL Azure for eight months for free.
2. I deployed the AdventureWorksDW2008R2 and AdventureWorksLT2008R2 databases to my SQL Azure server. The Adventure Works database deployment scripts for SQL Azure are available on codeplex. They will create AdventureWorksDWAZ2008R2 and AdventureWorksLTAZ2008R2 database on your SQL Azure server.
3. Installed SQL Server 2008 R2 Release Candidate which is not publicly available.
4. Created a new report in BIDS. As usual, I added a data source and selected the Microsoft SQL Azure provider.
5. In the Connection Properties dialog box, I entered the name of my SQL Azure server and my standard security credentials (Windows security is not supported on SQL Azure). For some reason, the database dropdown is not automatically populated so I had to enter the name of the database AdventureWorksDWAZ2008R2 manually.

6. Once connectivity has been taken care of, life is as usual. The Graphical Query Designer (toggle the Edit as Text button if the generic query designer starts first) shows the database schema.

   

   One thing that surprised me was the performance of data retrieval. Even when I opted to get all data from FactInternetSales, which has thousands of rows, the query executed pretty fast.  One caveat though with this approach is that you have open port 1433 on your corporate network because this is the port SQL Server uses for client connections. This is not required if your application is installed on Windows Azure and connects to the SQL Azure database on the server.

A couple of Report Builder enhancements in SharePoint 2010 integration mode caught my attention. First, Report Builder 3.0 now launches by default when the Open in Report Builder action is initiated inside SharePoint 2010. Previously, Report Builder 1.0 would be launched.

Second, if the report includes a drillthrough action to another report, the action now works when the action is initiated from Report Builder 3.0 preview mode. As it turns out, a drillthrough action is forwarded directly to SharePoint. Previously, a report drillthrough action will result in an error because the Report Builder would attempt to resolve the action locally.