Tag Archive for: SQL Server 2008

One new SSRS 2008 feature that international users will undoubtedly appreciate is parameterized parameter prompts.

Previously, the parameter prompts were static. In SSRS 2008, you will notice the familiar function symbol (fx) next to the parameter prompt which means that the prompts can be expression-based. This allows the report author to change the parameter prompt caption at runtime. For example, you can retrieve the localized version of the parameter prompt from an external resource assembly based on the user language.

UPDATE 03/27/2009 This feature didn’t make it to the release version of SSRS 2008. SSRS 2008 doesn’t support expression-based parameter prompts. The feature has been slated to a future release.

One of the major themes in SSRS 2008 is scalability. A significant effort will be made to ensure that SSRS scales well with large reports. Why is this needed? Recall that in SSRS 2005 (and 2000), report processing is instance-based. This means that the report engine would process the entire report as a snapshot, including textbox values, styles, etc. As a result, SSRS 2005 reports are memory-bound. In general, the memory consumed by SSRS 2005 is proportional to the size of the report. The bigger the report, the more memory it would take. Therefore, large reports won’t scale well.

In contrast, the SSRS 2008 processing engine processes the reports on-demand. It pre-computes and saves only certain invariants, such as grouping, sorting, filtering, and aggregates. However, textbox values are calculated on-demand every time the containing page is rendered. To test this, drop a textbox on the report and set its value to =Now(). In SSRS 2005, when you page back and forth, the textbox value will not change. In contrast, in SSRS 2008, the textbox value would change to the most current date each time the containing page is rendered.

As a result of the on-demand processing model, the in-memory presentation of the report is much smaller. If you want to learn more about the engine improvements in Katmai, I highly recommend the Chris Baldwin’s excellent Enterprise Scale Report Engine webcast. At this point, you may wonder how much memory the new engine could save. I tested print preview for an existing 1,270 report with the old and new engine and here are the results.

As you can see, in this case the new engine takes 70% less memory which is a pretty remarkable improvement. This is not to encourage you to author such monster reports of course. However, if you have to, for audit, regulatory, or whatever reasons, the new engine should be able to help.

As I mentioned in a previous blog, SQL Server 2008 will probably include an RDL Object Model. This is great news for developers who generate report definitions programmatically. No more custom RDL object models as the one I talked about during my TechEd presentation. The early incarnation of the promised object model is included in the July CTP (CTP4) and resides in the \Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.RdlObjectModel.dll assembly.

DISCLAIMER Before rejoicing too much, recall the usual disclaimer that everything is in a flux and a subject to change. Although here, the RDL Object Model may very well disappear in the final bits.

Note that I disclaimed myself let me introduce you to the RDL Object Model (don’t try to find it in BOL; long live .NET Reflector!).

using System;

using System.IO;

using Microsoft.ReportingServices.RdlObjectModel;

using Microsoft.ReportingServices.RdlObjectModel.Serialization;

namespace RDL

{

class Program

{

static void Main(string[] args)

{

string idef = @”C:\Reports\Sales by Product.rdl”; // input report in RDL 2008 format

string odef = @”C:\Reports\Sales by Product1.rdl”; // output report in RDL 2008 format

Report report = null;

RdlSerializer serializer;

if (!File.Exists(idef)) return;

// deserialize from disk

using (FileStream fs = File.OpenRead(idef))

{

serializer = new RdlSerializer();

report = serializer.Deserialize(fs);

}

report.Author = “Teo Lachev”;

report.Description = “RDL Object Demo”;

// TODO: use and abuse RDL as you wish

// serialize to disk

using (FileStream os = new FileStream(odef, FileMode.Create))

{

serializer.Serialize(os, report);

}

}

}

}

As you can see, using the RDL Object Model is simple. Once you add the reference to (\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.RdlObjectModel.dll, you are work with RDL in object-oriented way. You use the RDLSerializer class to load a report definition (Deserialize method) or get RDL from a report object (Serialize method). Both methods support various overloads to read/write from/to stream, XmlReader, and more. I load an existing Sales by Product report into the Report object using the RDLSerializer.Deserialize() method. Note that it must be saved in the SSRS 2008 format. You need to use the stand-alone Report Designer to do so. If you use the SSRS 2005 format you will get an exception because you will need to upgrade to RDL 2008 (see next paragraph about how to upgrade RDL). From there, I write the report object back to disk as a Sales by Product1.rdl file. What could be easier? Compare this with XmlDom programming and you will start seeing how the RDL Object Model can make your life easier.

There are also methods for upgrading RDL to SSRS 2008 (Microsoft.ReportingServices.ReportProcessing.RDLUpgrader .UpgradeToCurrent) and SSRS 2005 (Microsoft.ReportingServices.ReportProcessing.RDLUpgrader .UpgradeTo2005) formats.

An RDL object model has been long due on my wish list. Kudos to the SSRS team for materializing it.

One of most important enhancements coming up in SQL Server 2008 Reporting Services is the new tablix region. In fact, I dare to predict that many folks will upgrade to SSRS 2008 just to get Tablix. What’s tablix anyway?

Tablix = Table + Matrix

So, tablix combines the flexibility of the table region and the crosstab reporting features of the matrix region. Actually, a tablix is table, matrix, and list all in one but I guess the SSRS team decided to keep the name short. Don’t be fooled by the old toolbar buttons because they just provide entry points to the tablix region. If you click the Matrix button, you will get a tablix region preconfigured for crosstab reporting, when you click the Table button you get a table report, and List button will give you a free-form Tablix. So, this is simple. In SSRS 2008, tablix powers all reports.

Why is Tablix so cool? Because it lifts many of the matrix limitations and add features not possible before. Take for example the following report that demonstrates a couple of the new Tablix features.

First, Tablix lets you have stepped columns with crosstab reports. For example, Product Category and Product Subcategory share the same column. This may not sound very exciting but it wasn’t that easy to implement with the old matrix region.

Second, Tablix supports side-by-side crosstab sections. For example, the report has by year and by region sections side by side. This wasn’t possible with Matrix.

Finally, the snapshot doesn’t show this but you have independent group aggregates with crosstab reports. This means that moving to SSRS 2008 you can forget about the InScope() function and all limitations associated with it and matrix-based reports. These limitations made me abandon the matrix region in one of my projects and replace it with programmatically-generated “pseudo” crosstab reports that used the table region.

To take Tablix for a spin, use the standalone Report Designer. Or, open an existing SSRS report in the standalone Report Designer. As mentioned in my previous post, the VS.NET Report Designer doesn’t support Tablix in CTP4.

Matrix is dead, long live Tablix!