At the traditional annual “BI Power Hour” sessions, several teams that contribute to the Microsoft Business Intelligence platform show off their technology in a fun way. It is about demonstrating, in perhaps unusual ways, of how business intelligence and various tools of the BI stack can be useful.
In past years, Reporting Services made a number of successful contributions to these events, such as Hangman, report manager as report, etch-a-sketch, mastermind, an executive dashboard (aka tic-tac-toe), and others.
Throughout last year, we showed several variations of a multi-player game that we shall call “Sea Battle”. The goal of the game is to sink all of your opponent ships (for example, a Battleship), before the opponent has a chance to find and sink your ships. The faster you click, the better your chances of winning :)
The overall theme of the report was about interesting new data visualization features in Reporting Services 2008. It uses charts with dynamic drill through actions, as well as data point tooltips. 
Furthermore, a small matrix acts essentially as a data-driven grid control next to the chart to show your own ship positions and the opponent’s hits and misses. The overall health status of the opponent's ships is shown using a gauge control with a custom pointer with an image to simulate a rising water level, as a ship sustains more and more hits.
Successfully sinking a ship is rewarded with a nice animated explosion. The really fun part is the multi-player aspect – we use a basic way of matching up two report users so they can play against each other by simply interacting with their report on a report server.
While Reporting Services was clearly not designed as a gaming platform, this demonstration of the product's flexibility brought out the competitive spirit in the SQL Server BI team. When I made an initial version available for limited beta-testing on an internal report server, the news spread quickly and we immediately had more than 50 people playing, and frantically clicking on the chart’s drill through links trying to win against their human opponents. This created quite a bit of load on the report server that is also used by hundreds of other users, and provided a nice stress test scenario :)
Enough said. I know you want to participate in the fun and play yourself!
Instructions to setup the demo on your own RS 2008 report server:
- Unzip the attachment
- Restore the database backup (SeaBattle.bak) on a SQL Server database server
- Create a new Business Intelligence Development Studio 2008 report project, add the shared data source (SeaBattle.rds), and the two reports of the zip file to the project
- Publish the project to a RS 2008 report server
- Use stored credentials on the shared data source.
I strongly recommend using stored credentials. That way, only the stored user needs access to the SeaBattle database and you won’t run into any potential integrated security double-hop issues.
- In report manager, hide the “SeaBattle” report, so that users just see the “SeaBattle – Start new game” report. Don’t run the SeaBattle report directly; always use the start new game report.
- I recommend playing the game using the http://MachineName/reportserver URL, not through report manager, if you want faster response time and better chances of winning :)
Better yet, you could quickly build a small Winforms application, using the Visual Studio 2008 ReportViewer control in remote mode and connect to the report server.
Hope you are going to enjoy this Power Hour demo!
Q&A and disclaimers:
- Multi-player matching is really basic
The first user that starts the report establishes a new game, the second user that enters is automatically linked with the first user. Since there is no concept of game session implemented, you can end up with abandoned sessions if one of the players stops playing, or starts a new game.
- Reporting Services was not designed as a gaming platform
This results in some limitations in the game design. Furthermore, the demo evolved over time with little bits added here and there – if I were to rebuild the backend database and the report from scratch, it would have a somewhat cleaner design.
- Could this be built using a local mode report viewer control?
Yes, but it is probably an exercise for more advanced developers. Basically, you would build a local mode report viewer application with its own data retrieval, and just access the SeaBattle database on a common SQL Server.
I have been pretty busy lately – working on SQL Server 2008 R2, which is the official name for the next release, formerly known as SQL Server 'Kilimanjaro'.
Earlier today at the Tech Ed 2009 Conference, Thierry D’hers demonstrated one of the many projects I have been working on recently – it is our cool new Reporting Services Power Hour demo, unveiled at the 5th Annual Business Intelligence Power Hour at Tech Ed. It was a lot of fun putting it together! You can read a brief summary and first impressions of the demo on Teo’s blog. Stop by at the Reporting Services booth at Tech Ed if you want to see more about it.
Updates: A video of this new Reporting Services Power Hour demo is now available. I also made up on my promise to provide details and a download for our highly popular Power Hour demo from last year.
Service Pack 1 for SQL Server 2008 is now available for download. The service pack is primarily a roll-up of Cumulative Updates 1, 2, and 3 and minor fixes made in response to requests reported through the SQL Server community.
While there are no new features in this service pack, a notable addition from a Reporting Services point of view is the ability to configure Click Once deployment to either launch Report Builder 1.0 or the updated Report Builder 2.0, directly from Report Manager (native mode) or SharePoint. If you run Reporting Services in SharePoint integrated mode, make sure to also install the updated RS add-in for SharePoint.
Furthermore, the April 2009 Feature Pack is now available and includes an updated version of Report Builder 2.0. You can get it either as Report Builder 2.0 stand-alone download, or as click once deployment from a Report Server with SQL Server 2008 SP1 applied.
Summary of download links:
Other download resources:
Enjoy!
Tablix enables the separation of data groupings and layout header areas. A stepped layout can help with horizontal spacing, because nested groups can then share the same horizontal space, which was previously not possible in a matrix layout. This provides very powerful layout capabilities, and is one among several new features introduced with Tablix.
A webcast about Report Authoring in Reporting Services 2008 by my esteemed colleague Carolyn Chau shows how to design a table or matrix with a stepped group layout by simply selecting an option in the table and matrix wizard.
You can accomplish the same effect by manually converting from a regular blocked to a stepped layout. For the example shown below:
-
right click the Component textbox – insert row – inside group above
-
copy the Component textbox description into the newly created header space
-
right click the Component textbox and select delete columns (delete columns only, not the associated group)
-
select the SubComponent textbox and set the left-padding to 20pt
▼
SQL Data Services (SDS) was shown at PDC in October, utilizing a REST based interface. As described in a previous posting, we also made available a SDS custom data extension for Reporting Services reports.
A few days ago, we announced that besides the REST interface, SDS will also expose SQL Server’s existing network protocol, Tabular Data Stream (TDS), directly as the service protocol and thereby extend the relational model directly to the cloud and enable new capabilities:
This is exciting news for Reporting Services report authors and users!
You will be able to just use the “SQL Server” data source type and point it to SDS to execute queries and stored procedures using the well-familiar T-SQL language. You also get schema discovery with the graphical query designer in Reporting Services.
Just a quick post to reference two interesting reads about designing dynamic drilldown in reports:
Enjoy!
Tablix is the new underlying flexible data region and grouping structure introduced in Reporting Services 2008. Among other features, it enables asymmetric layouts, as demonstrated in the thumbnail screenshot below.
If you use a list, table, matrix, chart, gauge, or custom report item in RS 2008, you are really using a tablix beneath the covers. Ever since the first public revealing of tablix at TechEd conference in June 2007, and making it available shortly after in CTP 4 of SQL Server 2008, a lot has been said about tablix (e.g. see Teo Lachev’s: Tablix – The Crown Jewel posting).
As the developer who implemented tablix in the new on-demand report processing engine of Reporting Services 2008, I wanted to share my perspective on how report authors can get even more out of tablix. This is the first posting in a series of postings, focused on exploring several of the tablix capabilities. In one of the upcoming postings I will explain key building blocks of the report shown above.
So what’s the meaning of the word “tablix”?
Tablix = Table + Matrix
A tablix combines all the features of a table and a matrix. In addition, it adds new capabilities such as:
-
an unlimited number of (un)correlated side-by-side adjacent groups on rows and columns
-
-
subtotals have explicit cell definitions - no need to use InScope to determine the current scope dynamically
-
the matrix corner is now a grid corresponding to the row/column group structure
With that, you no longer have to determine upfront if you need a table or matrix (sometimes also called crosstab) layout. You can change the tablix layout on-the-fly as you refine the report design or adjust for new requirements later.
In a previous post I showed how to quickly find reports that use custom report items (CRIs). The same idea can be applied to subreports as well. As an administrator of a report server, you may access the catalog database directly. Keep in mind that any queries you execute directly against the report server’s catalog database may affect the performance of the overall system. Make sure that you never take any locks on objects in that database, as this is not supported and interferes with the function of the report server.
The query below runs against the current version of the report server catalog database, and may not necessarily work for future RS versions. It uses XQuery and checks for all three different RDL namespaces since the catalog database stores a copy of the original report in exactly the format it was originally uploaded, so that (maybe several years) later, you can still retrieve exactly the report you published as a 2003/10 or 2005/01 report, even if it is internally on-the-fly upgraded to the latest schema version of the report server it is running on.
Use the following query to get a list of reports that reference subreports, including the path of those subreports:
USE ReportServer
/* declare and populate temp table */
DECLARE @tab TABLE (Path NVARCHAR(MAX), xmlColumn XML)
INSERT @tab
SELECT top 1500 Path, CONVERT(XML, CONVERT(VARBINARY(MAX), Content)) FROM Catalog WHERE Type=2;
/* find subreports in RS 2000-based main reports */
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition' as rdl)
SELECT Path as ReportPath,
n.x.value('(//rdl:ReportName)[1]', 'nvarchar(256)') as ReferencedReport
FROM @tab
CROSS APPLY xmlColumn.nodes('//rdl:Subreport') n(x);
/* find subreports in RS 2005-based main reports */
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' as rdl)
SELECT Path as ReportPath,
n.x.value('(//rdl:ReportName)[1]', 'nvarchar(256)') as ReferencedReport
FROM @tab
CROSS APPLY xmlColumn.nodes('//rdl:Subreport') n(x);
/* find subreports in RS 2008-based main reports */
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' as rdl)
SELECT Path as ReportPath,
n.x.value('(//rdl:ReportName)[1]', 'nvarchar(256)') as ReferencedReport
FROM @tab
CROSS APPLY xmlColumn.nodes('//rdl:Subreport') n(x);
Have you ever wondered what API calls Report Manager or Management Studio are performing to accomplish certain operations?
Teo Lachev’s RsTracer helps answer these questions. The tool is available as a CodePlex project and shows the APIs that a Reporting Services client invokes and what arguments it passes to each interface. It also intercepts the server response. Armed with this information, you can easily reproduce the same feature in your custom management application. RsTracer works with all versions of Reporting Services.
I wanted to give some props to MVP Grant Paisley who has setup a site called Report Surfer, where you can directly view, share, and download sample Reporting Services reports.
I met Grant a few months back when he visited Redmond. He was very interested in reports I had built for the RS 2005 chart whitepaper, as well as example reports from my recent blog postings. They are now also available in the report library.
The Report Surfer site runs RS 2008 and thereby supports old and new reports. One very nice aspect is that the current library of reports also includes our previously released “report packs”, which you can view directly on the site without the need to install anything. A free registration is required to use the site for uploading, sharing, and downloading reports.
I recently provided a tip to my Australian colleague David Lean to successfully solve a small report design challenge with tables and charts that use conditional formatting.
The specific goal was to dynamically calculate and assign a color shade so that the minimum value present in the data is shown red, the average value yellow, and the maximum value green, with automatically calculated red-yellow-green shades for numeric values in between.
As it turns out, Dave integrated and expanded this into a great four part blog post series on using conditional formatting and color scales in Reporting Services (applicable to both, RS 2005 and RS 2008):
Enjoy!
I wanted to point out two Reporting Services utilities built by our MVPs and user community that you may find very useful.
BIDS Helper is a CodePlex project: http://www.codeplex.com/bidshelper
In past releases, the focus has been on significantly enhancing Analysis Services and Integration Services BIDS design experiences. Version 1.4 was just released, and for the first time BIDS Helper now also includes small Report Designer enhancements:
- identifying unused datasets in the report (by removing them you may speed up your report)
- deleting dataset cache files (.rdl.data) directly from within BIDS
RS Scripter is a utility for scripting and managing report server catalog items: http://www.sqldbatips.com/showarticle.asp?ID=62
You might find RS Scripter particularly useful for:
- quickly and easily extracting all RDLs from a report server
- generating scripts to load reports, data sources
- script shared schedules, subscriptions, system and item level roles
- migrating contents from one report server to another
If you used the CSV renderer in previous releases of Reporting Services, you might want to take another look in RS 2008. We listened to your feedback: not only does CSV rendering consume significantly less resources and is faster in most cases, the CSV data output is generally more useful as well if you use complex report layouts (e.g. matrix).
As in previous releases, the CSV renderer output is customizable via device info settings and the data output is controlled by the DataElementOutput setting on reportitems and groups. For more information about report design considerations for CSV check the documentation.
One common question I have seen related to CSV output is how to configure different field or record delimiters. We improved this in RS 2008 so that you can specify special characters, such as tab or newline, directly in the report server's configuration file (RSReportServer.config). For example, to configure ASCII encoding and tab field delimiters in RS 2008 use the following settings:
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
<OverrideNames>
<Name Language="en-US">CSV (tab delimited)</Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter>	</FieldDelimiter>
</DeviceInfo>
</Configuration>
</Extension>
Note: when using a server-wide device info configuration, special characters used as delimiters should be specified encoded. While this works fine in RS 2008, for RS 2005 please follow the approach described in the documentation.
We plan to release significantly updated and improved versions of both the Winforms and ASP.NET Report Viewer controls in Visual Studio 2010. This is going to include the long awaited local mode support for the new report processing engine, originally released with SQL Server Reporting Services 2008. Most importantly, this provides RDL 2008 features (e.g. tablix, enhanced charts, gauge, rich text) in local mode without connection to a report server. Furthermore, a number of other new features are coming as well in the Report Viewer controls. Related to this, my esteemed colleague Brian Hartman started blogging recently. His blog is definitely worth keeping an eye on regarding common questions on current and future versions of the Report Viewer.
FAQ: What is the current level of RDL support in Visual Studio 2008 Report Viewer controls?
If you are using local mode, you probably already noticed that attempting to load RDL 2008 based reports results in the following error:
The report definition is not valid.
Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded.
You cannot use RDL 2008 features in VS 2005 or VS 2008 report viewer controls in local mode, because the controls are using the same report processing engine that was shipped with SQL Server 2005 (supporting only the RDL 2005 namespace and feature set). As a side-note, VS 2008 shipped almost 6 months before SQL Server 2008 became available.
If you want to use RDL 2008 features with the report viewer controls available today, server mode is your only viable option, because report processing is performed remotely on a Reporting Services 2008 server. Please check Brian's blog posting about RS 2008 and the Report Viewer controls for more details. A general overview of the differences in functionality between Report Viewer and RS 2008 is available in the documentation as well.
The fourth and final technical note "Reporting Services Performance Optimizations" in our series on Building and Deploying Large Scale SQL Server Reporting Services Environments is now available on SQLCAT.com.
The focus of this final technical note is on how to optimize your Reporting Services architecture for better performance and higher report execution throughput and user loads.
