CODEWITHSUNDEEP
reporting-services
Koen VC
Koen VC

Reputation: 47

Reporting Services 2005: Group into columns

From a query, I get these results:

Machine 1, Product A, 1 pal  
Machine 1, Product B, 2 pal  
Machine 1, Product C, 3 pal  
Machine 2, Product D, 2 pal  
Machine 2, Product E, 2 pal  
Machine 3, Product F, 1 pal

I would like to create a report that looks like this:

Machine 1  |  Machine 2  |  Machine 3 
Product A  |  Product D  |  Product F  
1 pal      |    2 pal    | 1 pal  
Product B  |  Product E  |   
2 pal      |  2 pal  
Product C
3 pal

I know above formatting sucks but it gives an idea. I guess this is the same problem as in New group in New column but with another technology. Is anyone able to help? Would be very much appreciated, thanks!

Upvotes: 1

Views: 4736

Answers (2)

user756519
user756519

Reputation:

Here is one possible way of achieving this using SSRS Matrix report item in conjunction with SQL Server ROW_NUMBER function. Following example shows how this can be done. The example uses SSRS 2005 with SQL Server 2008 R2 database.

Step-by-step process: SSRS 2005

  1. Created a table named dbo.GroupColumns representing the data that you provided as shown in screenshot #1. Create scripts of the table is provided under SQL Scripts section.

  2. Created a query shown in screenshot #2, which will be used to create the SSRS dataset. Query is provided under SSRS dataset query section. Screenshot #2 shows the query output in Management Studio. The query uses ROW_NUMBER() function to assign sequential numbers within a given Machine sorted by Machine and Product.

  3. On the SSRS Report, place a Matrix report item. Refer screenshot #3.

  4. Drag and drop RowRank, Machine and Product values from the data set onto the Matrix as shown in screenshot #4.

  5. Right-click on the cell containing Product value and select Add Row as shown in screenshot #5. A new second column will be added along with a new row.

  6. Drag and drop the Units column from data set onto the Matrix as shown in screenshot #6.

  7. Set the Width property on the second matrix column to 0 (zero) because we don't want to show that column. Even though it is set to zero, the value will change to 0.03125 in. Screenshot #7 shows the Matrix report item after applying some formatting and coloring.

Hope that helps.

SQL Scripts:

CREATE TABLE [dbo].[GroupColumns](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Machine] [varchar](10) NULL,
    [Product] [varchar](10) NULL,
    [Units] [varchar](10) NULL,
 CONSTRAINT [PK_GroupColumns] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

SSRS dataset query:

SELECT  ROW_NUMBER() OVER(
            PARTITION BY    Machine 
            ORDER BY        Machine
                        ,   Product
        ) AS RowRank
    ,   Id
    ,   Machine
    ,   Product
    ,   Units 
FROM    dbo.GroupColumns

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6

Screenshot #7:

7

Screenshot #8:

8

Upvotes: 1

zzawaideh
zzawaideh

Reputation: 2011

If I understood your question correctly, you can achieve this using the Matrix control. Just place the Machine Field in the column group and then just place the product and pal fields in the details group. You can just stub the row group with "=1" in your expression.

Upvotes: 0

Related Questions