Post reply

SSRS Monthly Data Comparisons

  • May 1, 2019 at 3:51 pm

    #3602497

    I have a SQL table formatted similarly to the sample table below.

    create table GrowthSample
    (
    DateArchived date
    ,Inventory int
    )

    insert into GrowthSample
    values ('11/1/17', 98)
    ,('12/1/17', 45)
    ,('1/1/18', 645)
    ,('2/1/18', 78)
    ,('3/1/18', 42)
    ,('4/1/18', 65)
    ,('5/1/18', 78)
    ,('6/1/18', 94)
    ,('7/1/18', 125)
    ,('8/1/18', 37)
    ,('9/1/18', 27)
    ,('10/1/18', 89)
    ,('11/1/18', 67)
    ,('12/1/18', 39)

    I need to build a SSRS report that shows the count of inventory units for each month in that year and also the number of units increase/decrease and percent increase/decrease compared to the previous month for the time frame. I've been trying to create calculated columns on my dataset but I'm not having any luck. Is there another way I can go about this so the data displays like this Capture

    • This topic was modified 5 years, 6 months ago by  RonMexico. Reason: Fixed typo and calculation error in table

  • May 1, 2019 at 4:53 pm

    #3604128

    If you have only one value per month, then you can use LAG([Inventory],1) OVER (ORDER BY DateArchived) AS PrevValue

    and then compare. Maybe easier to do it in T-SQL.

  • May 1, 2019 at 6:12 pm

    #3604648

    That gets me closer. However, this report also needs to do yearly comparisons where it will look at the same month a year ago so the T-SQL  might get cumbersome. Are there expressions that can be generated on a field in SSRS to handle the calculations there?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply