Generate SSRS report purely from user input

Question

I have a simple requirement that turns suprisingly difficult to achieve in SQL Reporting Services.

My report, should do the following:

• User inputs an integer number, let's call it A
• A label is generated for each number from 1 to A
• all labels are printed on A4 paper (four labels fit into one row, number of rows will be calculated accordingly)

so if user enter number 100, then 100 labels are printed in 25 rows.

For good reasons I need to use SSRS - label contains barcode for which I have a third party component working only in SSRS. I also want to integrate this report nicely with existing system and use all SSRS goodies like paging etc.

How can this be done? Clearly, this report doesn't need any SQL connection or data, but SSRS seems to not let me use Tablix without actual datasource. I have tried to create a dummy dataset in SQL, but I don't know how to create query returning dummy table of given number of rows.

I would be great if there was solution using only VB script embedded in report. At this moment I can only think of creating temporary table and filling it with my data, is there more elegant solution?

Answer 1

If the problem boils down to having a query return A rows then you should go research sql techniques for generating numbers tables. Here's one simple way to do it up to 25 rows.

select d1.n * 5 + d0.n
from    
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4) as d0(n),
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4) as d1(n)
where d1.n * 5 + d0.n < A

Answer 2

You will need to make a connection to a database. Make it to any database does not matter and you can use the following statement to get the numbers

DECLARE @A INT = 100;

SELECT DISTINCT  number 
FROM master..spt_values
WHERE number >= 1
AND   number <= @A

Answer 3

Since you don't need to actually generate any data, you can create something very simply like this to generate (A / 4) rows. This will be the basis for the rows in your tablix.

DECLARE @A INT = 100;

SELECT  TOP (CAST(CEILING(@A / 4.0) AS INT)) AnyVal = 1
FROM    sys.all_objects a, sys.all_objects b;

There are a number of other ways to generate an arbitrary set, and with the execption of loops/recursive CTEs there is not a lot to chose between them. You will still need a connection to a database though.

To go one step further you can also detail which of the 4 columns need to be displayed:

DECLARE @A INT = 5;

SELECT  DisplayCol1 = 1,
        DisplayCol2 = CASE WHEN RowNumber * 4 + 1 >= @A THEN 0 ELSE 1 END,
        DisplayCol3 = CASE WHEN RowNumber * 4 + 2 >= @A THEN 0 ELSE 1 END,
        DisplayCol4 = CASE WHEN RowNumber * 4 + 3 >= @A THEN 0 ELSE 1 END
FROM    (   SELECT  TOP (CAST(CEILING(@A / 4.0) AS INT)) 
                    RowNumber = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
            FROM    sys.all_objects a, sys.all_objects b
        ) t;

This gives:

DisplayCol1 DisplayCol2 DisplayCol3 DisplayCol4
1           1           1           1
1           0           0           0

Indicating that to display a total of 5 labels, you only need to show the first column of row 2.