Archive for category: Blog

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.

When Report Builder 2.0 shipped , it introduced local and server preview modes. If the report uses local references only, such an embedded data source(s), Report Builder 2.0 executes the query and renders the report locally just like the BIDS Report Designer. However, when the report uses a server reference, such as shared data source(s) or subreports that were saved on the server, Report Builder 2.0 would transparently upload and process the report on the server just like the Report Builder 1.0.

Report Builder 3.0 improves the performance of server preview mode by introducing edit sessions. An edit session is implicitly created when the user runs the report for the first time. Behind the scenes, Report Builder 3.0 calls the new CreateReportEditSession API to instruct the server to create an edit session. The server caches the report dataset(s) in the ReportServerTempDB database (not in memory) and uses the cached data for subsequent previews. In this respect, you can think of edit sessions as the *.data files that the BIDS Report Designer generates to cache the report data in order to speed up report preview. As long as you don’t make changes to the report that affect the data, such as changes to the dataset itself, data source, or parameters, the cached copy of the data is used by the report. This means that the user may not see changes that might have taken place in the database for the duration of the edit session. To make sure that the latest data is used, click the Refresh button on the Report Builder 3.0 toolbar. Refreshing the report discards the edit session and executes the database queries.

The administrator can configure two new server properties to manage edit sessions:

• EditSessonCacheLimit – By default, the data cache can hold up to five datasets per report and user. Note that if the dataset is parameterized, each parameter combination counts as one dataset. If you use many different combinations of parameter values, the report might need more cached dataset copies. The administrator can allow this by increasing the EditSessonCacheLimit setting.
• EditSessionTimeout – By default, an edit session lasts 7200 seconds (2 hours). The servers resets the session every two hours when the user previews the report.

Report Builder 3.0 edit sessions are a performance-related enhancement that makes server-side report preview more efficient.

Here is a little gem for developers programming Reporting Services. SQL Server 2008 R2 brings a new web service endpoint, ReportService2010, which merges the functionalities of the ReportService2005 and ReportService2006 endpoints. The new endpoint can manage objects on a report server that that are configured for either native or SharePoint integrated mode. Previously, the ReportService2005 endpoint was used for managing objects on a report server that is configured for native mode and the ReportService2006 endpoint was used for managing objects on a report server that is configured for SharePoint integrated mode. This required code changes and retesting if the report server was re-configured in a different mode. These endpoints are now deprecated but still supported. The ReportService2010 endpoint includes the functionalities of both endpoints. When unsupported APIs in ReportService2010 are invoked, such as invoking the CreateLinkedReport API in SharePoint mode which doesn’t support linked reports, the endpoint will return an error that the operation is not supported.