Post reply

SSRS need filter expression: datepart(dateinterval.month,activity_date) < datepart(dateinterval.month,now)

  • September 9, 2014 at 5:55 pm

    #311478

    I have a simple table having one column of data, which I group on Year.

    I want two filters on the parent Year group.

    (1) this first filter works. It returns data only for year(now)-1 ie. last year

    (2) I also need a filter that returns only data for months where the MonthNumber is less than the MonthNumber of the current month/year ie. less than Sep. Next month the current month will be Oct, so I want all months of the prior year that are less than Oct. I have a MonNo column that is INT, so I should allow for comparing with datepart(dateinterval.Month, now). My report still returns a sum of all data for the whole of the past year.

    How to write expression for the (2) filter?

  • September 10, 2014 at 7:34 am

    #1744425

    [SomeDateColumn]<=TODAY

  • September 10, 2014 at 12:10 pm

    #1744549

    no, the answer is as follows:

    instead of trying to add a second filter to the Year parent group, build the constraining expression into in the column:

    =sum(iif(Fields!MonNo.Value < datepart(dateinterval.Month, today), Fields!SentLeads.Value, 0))

    this returns only those rows having month numbers less than the month number of the current year. So simple, yet elusive because of where it needs to be placed.

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

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