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:
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:
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:
Hope you are going to enjoy this RS Power Hour demo!
Q&A and disclaimers:
Is the 'data environment' just a view of the query result from the database? And if so, does the environment get refreshed or requeried each time you make an edit?
The database used by the report keeps some of the state information of the game. Specifically it gets updated as sales people are moved to new regions. Each edit causes an update with requerying. The report datasets select the current view of the regions and who owns it, and the report draws the map visualization accordingly.