Post reply

custom filter between x amount of work days before end of month

  • February 20, 2015 at 11:33 am

    #314701

    Hi guys,

    i am brand new to this forum and really hoping that someone could be of assistance.

    Essentially I want to be create a filter ideally within the tablix property because creating one from the query designer is the opposite of friendly.

    The filter I want to achieve will say that the dates being captured are within the 3 last working days of the previous calendar month through to dates up to 3 working days prior to the end of the current calendar month.

    Hopefully I have been clear enough on what I am seeking.

    Thank you in advance

  • February 20, 2015 at 3:49 pm

    #1779080

    Easiest way is to use a Calendar table and do the math in a stored procedure. Then base the report on that.

  • February 23, 2015 at 2:22 pm

    #1779416

    Hi David,

    There are a number of ways to achieve what you're after. You could add the filter to the query your data set is based upon, this will send a query to the database with the date range you're after. you could also filter the dataset itself by going to the Filter tab on the dataset, or you could filter the tablix by right-clicking the tablix and going to the filter section.

    Personally I think you're best off filtering the query that gets sent to the database as this will limit the amount of data sent back and allow your report to run faster. You can add something like the below to the query and then set the parameters to show up when running the report@

    AND (OrderDate BETWEEN (@StartDate) AND (@EndDate))

    Here is some information on how to set this up:

    https://technet.microsoft.com/en-us/library/aa337401(v=sql.105).aspx

    SQL SERVER Central Forum Etiquette[/url]

  • February 23, 2015 at 3:33 pm

    #1779435

    Thanks for the replies guys, unfortunately we don't have access rights to create stored procedures (we as the reporting team are subservient to the IT team/database administrators).

    I will also be the first to admit that I am quite new to this syntax, I was thinking it might look something like..

    IIF(Fields!Invoice_Due_Date.Value>(DateSerial(Year(Today()), Month(Today()), 0)-Working days of 3),"Yes"","No".

    Then do likewise to say if invoice due date <DateSerial(Year(Today()), Month(Today()), 1)-Working days of 3),"Yes"","No".

    Is that anywhere near what it should be?

    Obviously I have been sloppy with the syntax for last date of the current and present month, and will need to find out the syntax for 3 working days but am just trying to decipher.

  • February 23, 2015 at 3:41 pm

    #1779437

    I assume that if the first day of the month is a Sunday then the date you want to return is the previous Wednesday? Similarly, if the last day of the month is a Sunday you would also want to return the previous Wednesday, is that correct?

    Also if the first day of the month is a Friday, you'd want to return the Tuesday?

    Is that correct?

    SQL SERVER Central Forum Etiquette[/url]

  • February 23, 2015 at 3:47 pm

    #1779438

    So basically if the first day of the current month was a Sunday, I would want to capture everything from the Wednesday

    If the first day of the next month was a Saturday, I would want to capture everything up to Tuesday.

    So in the above example invoice due date wouid capture all between Wednesday previous month-Tuesday current month

  • February 23, 2015 at 3:53 pm

    #1779439

    For instance, the first day of this month (February 2015) was a Sunday. Do you want to return 28/Jan/2015 or 4/Feb/2015?

    Also the last day of this month (February 2015) is a Saturday. Do you want to return 25/Feb/2015 or 4/Mar/2015?

    SQL SERVER Central Forum Etiquette[/url]

  • February 23, 2015 at 4:10 pm

    #1779443

    Appreciate you helping me here so it would be 28/01/2015 - 24/02/2015

    The 24/2/2015 because the last 3 working days of the month are the cut off.

  • February 23, 2015 at 4:14 pm

    #1779444

    Ok, thought so. Unfortunately it's nearly half past 11 here so it's bed time. I'll shoot the formula over first thing tomorrow if you haven't solved it by then! Sorry I can't be any more help.

    I'd start by getting the first and last days of the month and checking if they were Saturday or Sunday and then taking off the appropriate number of days to get you to where you need to be.

    SQL SERVER Central Forum Etiquette[/url]

  • February 23, 2015 at 4:20 pm

    #1779445

    Appreciate it, thank you

  • February 24, 2015 at 4:25 am

    #1779530

    Hi Again!

    Open your report and go to Report > Report Properties > Code

    Then paste in the below:

    Function LastWorkingDay(StartDate As DateTime) As DateTime

    Dim StartDateDOW = DatePart(DateInterval.Weekday, StartDate)

    Dim DaysBack = 0

    If (StartDateDOW) = 7 Then

    DaysBack = -3

    Elseif StartDateDOW = 1 Then

    DaysBack = -4

    Else

    DaysBack = -2

    End If

    LastWorkingDay = DateAdd(DateInterval.Day,DaysBack,StartDate)

    End Function

    The above will get the working day 3 back from the day passed to the function. So we can use this function to get the last working day of last month by putting the below code into an expression in your report item:

    =Code.LastWorkingDay(DateSerial(Year(Today()),Month(Today()),0))

    You can pass in the last day of this month to the same function and it will give you the correct working day. Make sure you test it for November 2014 to make sure you're happy with the results.

    SQL SERVER Central Forum Etiquette[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

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