none
Obtaining week commencing from date

    Question

  • I have a list of dates within one table of SSRS

    What i need to do is create the same table but instead of running daily running from the date the week commeces: EG:

    This is what i have

    Date Count
    11/06/2012 1
    12/06/2012 1
    13/06/2012 1
    14/06/2012 1
    15/06/2012 1
    16/06/2012 1
    17/06/2012 1

    This is what i want

    Week Commecning Count
    11/06/2012 7

    Any help would be much appreciated


    Thanks

    Friday, June 22, 2012 11:26 AM

Answers

  • Hi There

    thanks for your posting again. If you are using Matrix control then please do the following

    create a row group inside you matrix and put this expression please  for your group

    =formatdatetime(DateAdd("d", 2 - DatePart("w",Fields!WeekComencing.Value) , Fields!WeekComencing.Value),dateformat.ShortDate)

    I have put a screenshot for your help

    If you have any question please do ask

    Many thanks

    Syed Qazafi Anjum

    Saturday, June 23, 2012 2:58 AM
    Moderator

All replies

  • Hi There

    Thanks for your posting. please add this expression to your group if you have a group otherwise you can put by right click on your details row and add total

     

    =formatdatetime(DateAdd("d", 2 - DatePart("w",Fields!date1.Value) , Fields!date1.Value),dateformat.ShortDate)

    A screenshot for your help

    Many Thanks

    Syed

    Friday, June 22, 2012 12:15 PM
    Moderator
  • Thank You Syed, this worked in giving me the week commencing

    This leaves with another problem my data is now:

    11/06/2012 1
    11/06/2012 1
    11/06/2012 1
    11/06/2012 1
    11/06/2012 1
    11/06/2012 1
    11/06/2012 1

    Instead of

    11/06/2012                     7

    Friday, June 22, 2012 1:34 PM
  • Hi There

    Please put this expression as fellow

    Right click on your tablix details group and  add total after or before  and it will create a new row under your tablix details row

    now put the expression

    =formatdatetime(DateAdd("d", 2 - DatePart("w",Fields!date1.Value) , Fields!date1.Value),dateformat.ShortDate)

    in that row instead of tablix details row

    I hope this will help

    many thanks

    Syed


    Friday, June 22, 2012 1:39 PM
    Moderator
  • SYed...sorry i think there may have been a bit of confusion and i may not have explained myself very well

    I am not looking to have a total for the week at the bottom of my tablix, this is not required

    what i require was a whole new table showing a summary of each week,

    i am trying to do this via a matrix

    This is the end result for what i am hoping to achieve

    Week Comencing    Count

    04/06/2012                6

    11/06/2012                7

    18/06/2012                8

    Friday, June 22, 2012 1:49 PM
  • Hi There

    thanks for your posting again. If you are using Matrix control then please do the following

    create a row group inside you matrix and put this expression please  for your group

    =formatdatetime(DateAdd("d", 2 - DatePart("w",Fields!WeekComencing.Value) , Fields!WeekComencing.Value),dateformat.ShortDate)

    I have put a screenshot for your help

    If you have any question please do ask

    Many thanks

    Syed Qazafi Anjum

    Saturday, June 23, 2012 2:58 AM
    Moderator