CODEWITHSUNDEEP
sql-server-2008reporting-servicespivotpivot-table
Fabio
Fabio

Reputation: 49

Display Data on SSRS transpose

I want to display some data i have transposed, so that it is pivoted on a date at the top in SSRS like in the following picture:

Transposed report

I have tried already to do this via a dynamic sql query, but this just creates more problems in that the headers change names every day.

i expected it to be very easy just to spin the data around on SSRS but i cannot seem to work out how to do it.

This is SSRS 2008 / MSSQL 2012.

EDIT - When i try group on column "DATE" it comes out like this on ssrs, which is not what i want Click Here

EDIT

I have tried what was suggested below, but i dont know what you mean on how to do what you said? do you mean in SQL or in SSRS

THis was my query

 IF OBJECT_ID('tempdb..#Cass_SSRS_DailyMiTable') IS NOT NULL
    DROP TABLE #Cass_SSRS_DailyMiTable

CREATE TABLE #Cass_SSRS_DailyMiTable (
    [date] DATE   ,[Total Orders] INT   ,[Orders Done] INT   ,[Pieces picked] INT   ,[Items Picked] INT   ,[Average Items on Order] INT   ,[Picked Today] INT)

INSERT INTO #Cass_SSRS_DailyMiTable (
    date,
    [Total Orders],
    [Pieces picked],
    [Items Picked],
    [Average Items on Order],
    [Picked Today]) VALUES
    ('2017-03-24', 53, 352, 33, 22, 0),
    ('2017-03-25', 351, 23, 235, 52, 0),
    ('2017-03-26', 35, 55, 25, 95, 0)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT ',' + QUOTENAME(CONVERT(VARCHAR(100), c.date, 120)) 
            FROM #Cass_SSRS_DailyMiTable c
            ORDER BY c.date ASC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 

    ';WITH PreUnpivot AS
    (
        SELECT
            C.date, 
            C.[Total Orders],
            C.[Pieces picked],
            C.[Items Picked],
            C.[Average Items on Order],
            C.[Picked Today]
        FROM
            #Cass_SSRS_DailyMiTable AS C
    )
    SELECT
        P.Concept,
        ' + @cols + '
    FROM
        PreUnpivot AS C
        UNPIVOT (
            PivotedValues FOR Concept IN ([Total Orders], [Pieces picked], [Items Picked], [Average Items on Order], [Picked Today])
        ) AS T
        PIVOT (
            MAX(T.PivotedValues) FOR T.Date IN (' + @cols + ')
        ) AS P'


EXEC (@query)

Upvotes: 0

Views: 1414

Answers (2)

SuperSimmer 44
SuperSimmer 44

Reputation: 999

I have used your original sql and amended the script accordingly:

IF OBJECT_ID('tempdb..#Cass_SSRS_DailyMiTable') IS NOT NULL DROP TABLE #Cass_SSRS_DailyMiTable

CREATE TABLE #Cass_SSRS_DailyMiTable ( [date] DATE ,[Total Orders] INT ,[Orders Done] INT ,[Pieces picked] INT ,[Items Picked] INT ,[Average Items on Order] INT ,[Picked Today] INT, [id] INT)

INSERT INTO #Cass_SSRS_DailyMiTable ( date, [Total Orders], [Pieces picked], [Items Picked], [Average Items on Order], [Picked Today], [id] )VALUES ('2017-03-24', 53, 352, 33, 22, 0 , 1), ('2017-03-25', 351, 23, 235, 52, 0, 1), ('2017-03-26', 35, 55, 25, 95, 0, 1)

select * from #Cass_SSRS_DailyMiTable

The format of the report definition in my previous answer is still applicable.

If you require further please message me with your email address.

Upvotes: 0

SuperSimmer 44
SuperSimmer 44

Reputation: 999

Firstly you need to have a generic common identifier within your dataset, so that all rows have the same identifier which can be grouped upon. I inserted and used a field called "id" within the dataset.

Secondly insert a matrix with one column group(date field) and four row groups (category groups).

For each row group they must be grouped by the generic common identifier.(group properties, group on......)

Example below:

enter image description here

Upvotes: 1

Related Questions