There is now another article about the BI Power Hour session that we did at TechEd. I posted the first demo I did a few weeks ago and so, by popular demand, here is the second one, a game of Hangman built entirely in a report. Before I describe how it works, here's a link to the source files. They should work on June CTP or later.
The way to get it working is to create a local SQL server databse called Hangman. Then run the Hangman.SQL file which will create 3 tables tables and a stored procedure. The Answers table contains all of the available answers - you can edit this with any values you want. The Letters table contains all of the letters that have not yet been guessed. And the PuzzleStatus contains a single row with the current state of the game.
The heart of the report is the stored procedure called Puzzle. Here it is:
CREATE PROCEDURE [dbo].[Puzzle] @Guess CHAR = ''''ASBEGIN DECLARE @Answer AS NVARCHAR(200) DECLARE @Display AS NVARCHAR(200) DECLARE @Index AS INT
-- If no guess is specified, reset answer and available letters IF (@Guess = '''') BEGIN DELETE FROM PuzzleStatus DECLARE @NumAnswers AS INT
-- Grab a random answer SET @NumAnswers = (SELECT COUNT(*) FROM Answers) SET @Answer = (SELECT Answer FROM Answers WHERE AnswerNum = (FLOOR(RAND() * @NumAnswers) + 1))
-- Replace spaces from answer in display SET @Display = REPLICATE(''-'',LEN(@Answer)) SET @Index = CHARINDEX( '' '', @Answer) IF (@Index > 0) BEGIN WHILE @Index > 0 BEGIN SET @Display = (STUFF(@Display, @Index, 1, '' '')) SET @Index = (CHARINDEX('' '', @Answer, @Index + 1)) END END
INSERT INTO PuzzleStatus (Answer, Display, Misses) VALUES (@Answer, @Display, 0)
-- Reset letters DELETE FROM Letters INSERT INTO Letters(Letter, Display) VALUES ('''', ''Reset'')
DECLARE @NextChar AS NCHAR SET @NextChar = ''A'' WHILE UNICODE(@NextChar) <= UNICODE(''Z'') BEGIN INSERT INTO Letters (Letter, Display) VALUES (@NextChar, @NextChar) SET @NextChar = NCHAR(UNICODE(@NextChar) + 1) END END -- Otherwise check for hit and remove letter from available list ELSE BEGIN DELETE FROM Letters WHERE Letter = @Guess
DECLARE @Misses AS INT DECLARE @AnswerLen AS INT
SET @Answer = (SELECT Answer FROM PuzzleStatus) SET @Display = (SELECT Display FROM PuzzleStatus) SET @Misses = (SELECT Misses FROM PuzzleStatus) SET @AnswerLen = LEN(@Answer) SET @Index = CHARINDEX(@Guess, @Answer)
-- If guess is in answer, replace dashes in display IF (@Index > 0) BEGIN WHILE @Index > 0 BEGIN SET @Display = (STUFF(@Display, @Index, 1, @Guess)) SET @Index = (CHARINDEX(@Guess, @Answer, @Index + 1)) END END ELSE SET @Misses = (@Misses + 1)
UPDATE PuzzleStatus SET Display = @Display, Misses = @Misses END -- Return status SELECT Display, Misses FROM PuzzleStatusEND
When the procedure is initially called, it chooses a word at random from the Answers table, initializes the puzzle status, and resets the available letters. Subsequently, when you pass a letter in, it deletes it from the list of available letters and attempts to find it in the answer. If it is not there, it increments the number of misses. If it is, it replaces the dashes in the answer with the correct letter. Either way, it returns the current guess and number of misses.
Open up Hangman.rptproj to see the report itself. There are two queries - one that calls the stored procedure and one that lists the available letters. There is a little trick in that the UseTransaction flag is set on the datasource. This ensures that the calls to the stored proc happens before the letters query. Technically, the execution order of the queries in a report is not guaranteed, it ends up that they are always executed in the order in which they are defined.
On the layout side, the background image is set to a gallows. It has one list dataregion that displays all of the available letters. The textbox containing the letter has a drillthrough action on them that calls the same report and passes the guessed letter. There is a second list that displays the guess and contains the images for the display. Each one of the images has a conditional visibility expression that only displays it when the number of guesses indicates it should be. For example, the expression on the first picture is:
Preview the report in the Report Designer, it will initialize the puzzle and start selecting a letter from the list on the left. Enjoy!