Home > Home, MSDN, SSRS > SSRS: Custom Start of week (Friday to Thursday )

SSRS: Custom Start of week (Friday to Thursday )

Generally we have start of week Sunday to Saturday or Monday to Sunday depends upon the system settings.

So before setting our expression to custom week as Friday to Thursday , we should know what is the default start of the week.

Can get more information on MSDN

As I am checking weekday Starting from Sunday or Monday.

CheckingWeekStart

So In my SSRS report week start is Sunday and end at Saturday.

Now when we want to modify week as Friday to Thursday then we need to identify the pattern.

Below  column F and H are the pattern :-

week PatternAnalysis

Now write expressions based on the pattern:-

Start of week Expression:-

=Format(iif(WeekDay(Fields!Date.Value)>=6,dateadd(DateInterval.Day, 6-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, -1-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

End Of week Expression:-

=Format(iif(WeekDay(Fields!Date.Value)>=6,dateadd(DateInterval.Day, 12-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, 5-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

Run the report , output as given below:-

SSRS Ouput

Now we will check how to get the previous week using again pattern analysis

Previous week  Pattern Analysis

Now write expressions based on the pattern:-

Start of Previous Week :-

=Format(iif(WeekDay(Fields!Date.Value)<=5,dateadd(DateInterval.Day, -8-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, -1-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

End Of Previous Week:-

=Format(iif(WeekDay(Fields!Date.Value)<=5,dateadd(DateInterval.Day, -2-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, 5-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

 

Run the report, output looks like below:-

ReportPreviousWeekOutput

So we can set previous week or current week based on custom requirement, only we need to identify the pattern. Pattern help to write case or IF statements. Without pattern we need to minimum 7 cases for each week day.

Hope you people like this post.

Please provide your valuable feedback through liking the post or adding comments. It will help to encourage to do lot of similar stuff.

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

Advertisements
Categories: Home, MSDN, SSRS Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: