Create "Bingo Card" in Report Builder

Question

I have an SSMS query that list orders numbers. In Report Builder we want these order numbers to expand right in the columns and down in the rows to resemble a bingo board. When an order has been shipped that order (cell) would turn green. How can I fill up the report page with one cell across the columns and down the rows?

I have not been able to figure this one out.

Answer 1

I'm assuming here that you have a simple list of order numbers and some kind of status to indicate complete/shipped.

I've created a simple table variable to use as the sample data with the following...

DECLARE @Orders TABLE(OrderID int IDENTITY(1000,1), OrderStatus INT)
INSERT INTO @Orders(OrderStatus) VALUES (1), (0), (0), (1), (1), (1), (0), (1), (0), (0), (0), (0), (1), (1), (1), (0), (0), (1), (1), (0), (0), (1)

SELECT * FROM @Orders

This gives us the following (truncated)

From here you can use your own order table.

Next we need to calculate the row and column numbers. To do this I've set a variable to give the number of required columns, you can set this as you please or even pass it in from, the report as a report parameter, for now it'll be static.

So the full query looks like this

DECLARE @Orders TABLE(OrderID int IDENTITY(1000,1), OrderStatus INT)
INSERT INTO @Orders(OrderStatus) VALUES (1), (0), (0), (1), (1), (1), (0), (1), (0), (0), (0), (0), (1), (1), (1), (0), (0), (1), (1), (0), (0), (1)

DECLARE @MaxCols int = 5

SELECT 
    OrderID,
    OrderStatus,
    ColNum = (ROW_NUMBER() OVER(ORDER BY OrderID)-1)%@MaxCols, 
    RowNum = (ROW_NUMBER() OVER(ORDER BY OrderID)-1)/@MaxCols
    FROM @Orders

This gives us this (truncated)

Now in our report, all we need to do is add a dataset with the above as the dataset query, then add a matrix control.

Drag the ColNum to the column placeholder, RowNum to the row placeholder, OrderID to the data placeholder.

Next we can set the background to green if the OrderStatus = 1 .

Finally we can delete the header rows and column if required.

Here's a 1 minute GIF showing the process of building the report. (right-click and open in new window to make it bigger if needed)