SSRS Stepped reported based on number

Question

Using SSRS with SQL Server 2008 R2 (Visual Studio environment).

I am trying to produce a stepped down report based on a level/value in a table on sql server. The level act as a indent position with sort_value been the recursive parent in the report.

Sample of table in SQL Server:

Sample of output required

Your problem is that Taps and fittings are both a product. This makes it so you can't split it up this way. — Snowlockk, Apr 13 '17 at 08:27
Is this actual format of the data in the table or is this the result of another query. If it's the result of another query, can you post a sample of the raw data. What you are trying to do is possible I think, it just might be a lot easier if we had better structured data. — Alan Schofield, Apr 13 '17 at 10:05

score 0 · Accepted Answer · answered Apr 13 '17 at 10:43

OK, I've come up with a solution but please note the following before you proceed. 1. The process relies on the data being in the correct order, as per your sample data. 2. If this is your real data structure, I strongly recommend you review it.

OK, So the first things I did was recreate your table exactly as per example. I called the table Stepped as I couldn't think of anything else!

The following code can then be used as your dataset in SSRS but you can obviously just run the T-SQL directly to see the output.

-- Create a copy of the data with a row number. This means the input data MUST be in the correct order.
DECLARE @t TABLE(RowN int IDENTITY(1,1), Sort_Order int, [Level] int, Qty int, Currency varchar(20), Product varchar(20))

INSERT INTO @t (Sort_Order, [Level], Qty, Currency, Product)
    SELECT * FROM Stepped

-- Update the table so each row where the sort_order is NULL will take the sort order from the row above
UPDATE a SET Sort_Order = b.Sort_Order
 FROM @t a
    JOIN @t b on a.RowN = b.rowN+1
 WHERE a.Sort_Order is null and b.Sort_Order is not null

 -- repeat this until we're done.
WHILE @@ROWCOUNT >0
    BEGIN
        UPDATE a SET Sort_Order = b.Sort_Order
            FROM @t a
                JOIN @t b on a.RowN = b.rowN+1
            WHERE a.Sort_Order is null and b.Sort_Order is not null
    END

-- Now we can select from our new table sorted by both sort oder and level.
-- We also separate out the products based on their level.
SELECT 
        CASE Level WHEN 1 THEN Product ELSE NULL END as ProdLvl_1
        , CASE Level WHEN 2 THEN Product ELSE NULL END as ProdLvl_2
        , CASE Level WHEN 3 THEN Product ELSE NULL END as ProdLvl_3
        , QTY
        , Currency
    FROM @t s
    ORDER BY Sort_Order, Level

The output looks like this...

You may also want to consider swapping out the final statement for this.

-- Alternatively use this style and use a single column in the report.
-- This is better if the number of levels can change.
SELECT 
        REPLICATE('--', Level-1) + Product  as Product
        , QTY
        , Currency
    FROM @t s
    ORDER BY Sort_Order, Level

As this will give you a single column for 'product' indented like this.

SSRS Stepped reported based on number

1 Answers1