locked
Prices – From the certain period to certain month RRS feed

  • Question

  • User923858955 posted

    Hi All,
    First off all I am new to the forum and I am new to SSRS and I need some guidance. I have a table with prices for certain periods:

    ItemID Price FromDate ToDate
    1 £20 01/07/2018 30/09/2018
    1 £22 01/10/2018 31/12/2018
    2 £10 01/07/2018 31/12/2018
    3 £31 01/07/2018 31/07/2018
    3 £35 01/08/2018 31/10/2018
    3 £37 01/11/2018 31/12/2018

    This is just an example, I have over 200 records in my table. I would like to convert the above table to;

    ItemID Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18
    1 £20 £20 £20 £22 £22 £22
    2 £10 £10 £10 £10 £10 £10
    3 £31 £35 £35 £35 £37 £37

    Is it possible to that with Matrix? Any help would be appreciated.

    Piotr

    Tuesday, May 15, 2018 9:23 AM

All replies

  • User347430248 posted

    Hi Pete,

    I suggest you to develop a query that can produce the desired result.

    Then you can directly use this query in SSRS to generate the result.

    Below is one example to get an idea.

    I use query below as a dataset in SSRS.

    SELECT *
    FROM (
        SELECT  
              t.Code
            , [Range] = 
                CASE 
                    WHEN DaysSinceStart BETWEEN 0 AND 9   THEN '0-9'
                    WHEN DaysSinceStart BETWEEN 10 AND 19 THEN '10-19'
                    WHEN DaysSinceStart BETWEEN 20 AND 29 THEN '20-29'
                    WHEN DaysSinceStart BETWEEN 30 AND 39 THEN '30-39'
                    WHEN DaysSinceStart BETWEEN 40 AND 49 THEN '40-49'
                    WHEN DaysSinceStart BETWEEN 50 AND 59 THEN '50-59'
                    WHEN DaysSinceStart BETWEEN 60 AND 69 THEN '60-69'
                    WHEN DaysSinceStart BETWEEN 70 AND 79 THEN '70-79'
                    WHEN DaysSinceStart BETWEEN 80 AND 89 THEN '80-89'
                    WHEN DaysSinceStart BETWEEN 90 AND 99 THEN '90-99' 
                    ELSE 'Over 100' 
                END
        FROM tblJobs t
    ) o
    PIVOT
    (
        COUNT(o.[Range])
        FOR [Range] IN (
            [0-9], [10-19], [20-29],
            [30-39], [40-49], [50-59],
            [60-69], [70-79], [80-89], 
            [90-99], [Over 100]
        )
    ) pt

    In SSRS:

    Output:

    So you can try to create a query as per your requirement and you can also try to use PIVOT in your T-SQL Query to get result like Metrix.

    FROM - Using PIVOT and UNPIVOT

    Regards

    Deepak

    Wednesday, May 16, 2018 5:05 AM