Partager via

The Pace is Glacial (Hangman Report)

  • Article

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 = ''''
AS
BEGIN
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 PuzzleStatus
END

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:

=IIF(Fields!Misses.Value<1,True,False)

Preview the report in the Report Designer, it will initialize the puzzle and start selecting a letter from the list on the left. Enjoy!

Comments