At the traditional annual “BI Power Hour” sessions several teams contributing 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 Sea Battle / Battleship, Hangman, report manager as report, etch-a-sketch, mastermind, an executive dashboard (aka tic-tac-toe), and others.  At Tech Ed 2009 and SQL PASS 2009, Thierry D’hers and I showed a new BI Power Hour report demo that we shall call “Sales Strategy” – you can watch a video here.

The goal of the game is to strategically move your sales force to new regions to expand your market share, while keeping a small base staff in your existing regions to maintain sales. As in real life however, your competition doesn’t sleep and will react to your moves – so really managing risk is the name of the game! The competition in the game is implemented as artificial intelligence – although a fairly basic algorithm, it is actually quite difficult to win against it in less than six minutes, and there is considerable risk that you lose eventually after a long battle with decreasing sales force and diminishing market share …

Due to popular request, I’m posting the details of my implementation of the RS Power Hour 2009 turn-based game I built with a little help by my colleagues Chris Hays and Brian Hartman.  In a separate upcoming posting I will explain how to implement double buffering for report viewer to achieve completely seamless report interactivity similar to what you get when playing the game in Report Builder 3.0 (e.g. no visible refresh when the map click/drillthrough is happening).

The report makes use of several new features in RS 2008 R2, such as Map visualizations.  In this blog posting, I want to provide the sources as well as call out several aspects of the report design in more detail.

Automatically Refreshing Report Contents
This feature existed since the first version of Reporting Services, although it seems it is not that widely known.  Under Report, there is a AutoRefresh property which specifies the automatic report refresh interval, i.e. the report automatically reruns with identical report parameter values.  In RS 2008 R2 we enabled expression-based dynamic auto refresh.  If the expression evaluates to 0, the report doesn’t refresh.  If the expression result is greater than 0, the report refreshes. 
This can be useful in business reports to dynamically adjust the report refresh rate. For example, refresh a report every 10 seconds during peak hours, while refreshing only every five minutes (300 seconds) the rest of the time.

In the particular Sales Strategy demo report, the AutoRefresh property is utilized to enable turn-based gaming.  Basically, the game report has two alternating phases at runtime:

• In the first phase, you select a region and then an adjacent region to move sales people (this is implemented as drillthroughs to the report itself).  Expanding to additional regions spreads your sales force thin, at which point you want to hire new staff by clicking the Hire New Staff button (which is again a drillthrough to the report itself).
  
   
• The second phase is triggered by hiring new staff.  The competition moves on a AutoRefresh cycle so that each sales force adjustment by the competition is visualized in the map step-by-step, until the artificial intelligence determines no further moves are possible or the risk is too high, at which point the AutoRefresh cycle goes back to 0 (the report doesn’t automatically update), and the report is back in the first phase. 
  

Dynamic Drillthrough on Map Regions
Similar to dynamic drillthrough on chart data points (explained here), we also enabled this capability on Map Layers.  The screenshots below show that by clicking on the map on the Report Builder design surface, the Map Layers menu opens, which provides access to the Polygon Properties dialog.  The Polygon properties are used for setting up:

• dynamic drillthrough on map regions, e.g. if a particular region is selected, only directly adjacent regions are enabled for drillthrough
• dynamic region fill colors, e.g. if the game is in the second phase of the competition moving, then a vertically striped fill pattern with plum color is applied to visualize the region from which the competition moved sales people from, with a solid fill pattern for the target region.

Artificial Intelligence (Risk Strategy)
While the turn-based state machine aspect has been implemented as custom code inside the report, the artificial intelligence for the strategy of the opponent is implemented in stored procedures inside the database used by the report.

Enough said.  I know you want to participate in the fun and play yourself! 

Instructions to setup the demo on your own RS 2008 R2 report server:

1. Unzip the attachment
2. Restore the database backup (US.bak) on a SQL Server 2008 R2 database server
3. Publish the report (US Sales Strategy.rdl) to your RS 2008 R2 report server (note: a report server is required at this point)
4. After publishing the report, set stored credentials on the data source of the report
   I strongly recommend using stored credentials.  That way, only the stored user needs access to the US database and you won’t run into any potential integrated security double-hop issues. 
5. Play the game with Report Builder 3.0, by opening the report from the report server in connected mode of Report Builder.
   Note that the dynamic auto refresh requires the report to be run via a report server.
   Btw, you can build a small Winforms application, utilizing the Visual Studio 2008 or 2010 ReportViewer control in remote mode, that provides the same seamless interactivity as shown in Report Builder 3.0 in this case. In an upcoming posting, I will explain how to implement double buffering for the report viewer to achieve completely seamless report interactivity (drillthrough) when selecting a region in the map in this report, and when the competition is reacting to your strategy moves.

Hope you are going to enjoy this RS Power Hour demo!

Q&A and disclaimers:

• Reporting Services was not designed as a gaming platform
  This results in some limitations in the game design.  Furthermore, the demo evolved a little bit over time (shown at multiple conferences) 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. I may also make use of writable and serializable report variables introduced in RS 2008 R2 RTM.
• Could this be built using a local mode report viewer control?
  No, currently not.  The report makes use of several new RDL 2010 features (Map, expression-based dynamic AutoRefresh), which are available in the recently released RS 2008 R2 version, but weren’t available at the time VS 2010 report viewer was released.