none
Format Date Time in SSRS Chart

    Question

  • Hi All,

    I need to create a chart with the some percentage values plotted in it. The graph should have the y-axis as the percentage of availability of an application and the x-axis should the in weeks. i.e. the data of the last 30 days should be displayed as weeks. eg: Aug 2nd week, Aug 3rd week, Aug 4th week, Aug 5th week etc. Is there any way to do this? Even if I can plot the starting and ending days of the week, then also it is fine. I mean if the x-axis values are like: Aug 2 2009 - Aug 8 2009, Aug 9 2009 - Aug 15 2009, etc. is also fine. Can anyone help me do this?

    Regards,
    Lavu.
    Tuesday, September 08, 2009 12:24 PM

Answers

  • Lavu

    You can define a calculated field for the datset outside of the query.  In the Data Fields or Report Data window, right-click the dataset and add a new calculated field.  Give it a name and then create an expression like the following.  There is no single Visual Basic function to take a date and return the week number of a month but you can use a combination of functions and some simple math to achieve this.  I don't know if this is exactly what you're looking for but it should get you pretty close.  Substitute the name of your date field for MyDate:
    =Format(Fields!MyDate.Value, "MMMM") & " Week " & (Int(DateDiff("d", DateSerial(Year(Fields!MyDate.Value),Month(Fields!MyDate.Value),1), Fields!FullDateAlternateKey.Value)/7)+1).ToString
    


    If you really want to return "1st", "2nd", "3rd", etc.; you can use a Choose or Switch function to add the postfixed text to the numbers.

    Hope this helps.


    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    • Marked as answer by kannadelavu Friday, September 11, 2009 9:57 AM
    Wednesday, September 09, 2009 5:52 AM

All replies

  • You can modify your dataset to return the week number and not the date time, using datepart(wk, column_name).
    • Proposed as answer by Grigorcea Alex Tuesday, September 08, 2009 12:31 PM
    Tuesday, September 08, 2009 12:27 PM
  • Tuesday, September 08, 2009 12:39 PM
  • Hi Grigorcea,

    I cannot modify my query. I have to do this in the Reporting end. My query is in DB2. And it is in the text format not in SP. The requirement is to keep the DB2 query exactly as it is and do all the modifications in the Reporting end.


    Regards,
    Lavu.
    Wednesday, September 09, 2009 3:39 AM
  • Lavu

    You can define a calculated field for the datset outside of the query.  In the Data Fields or Report Data window, right-click the dataset and add a new calculated field.  Give it a name and then create an expression like the following.  There is no single Visual Basic function to take a date and return the week number of a month but you can use a combination of functions and some simple math to achieve this.  I don't know if this is exactly what you're looking for but it should get you pretty close.  Substitute the name of your date field for MyDate:
    =Format(Fields!MyDate.Value, "MMMM") & " Week " & (Int(DateDiff("d", DateSerial(Year(Fields!MyDate.Value),Month(Fields!MyDate.Value),1), Fields!FullDateAlternateKey.Value)/7)+1).ToString
    


    If you really want to return "1st", "2nd", "3rd", etc.; you can use a Choose or Switch function to add the postfixed text to the numbers.

    Hope this helps.


    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    • Marked as answer by kannadelavu Friday, September 11, 2009 9:57 AM
    Wednesday, September 09, 2009 5:52 AM
  • Hi Lavu,

     

    Within my experience, there is no way to achieve this currently. Before, I took much time to do this but failed. I only work around a way, the x-axis values are the week numbers and add a table to show its date zone under the chart. You find this at http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/b140e868-7e53-4d53-abfa-f55085393a9c .

     

    I think you can use this way as workaround before someone work out it and kindly share here.

     

    Thanks,

    Jerry

    Thursday, September 10, 2009 2:35 AM
  • Paul,

    Thanks a million. It worked and it was exactly what I was looking for.

    Regards,
    Lavu.
    Friday, September 11, 2009 9:57 AM
  • the last link is not work , please try verify it cause i'm in need the same case
    thanks in advance
    Monday, September 28, 2009 12:56 PM
  • Dear Jerry,

    The above link(last one) which you posted is not working(page not found). please help me, I am also facing the same problem.

    Please check the below link then you can come to know the exact issue.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/3d52d65f-3c10-4d24-bcd3-fdb613215476

    please help me.

    Any help highly appreciated .

    Looking for quick reply.


    Thanks & Regards, REDDY

    Thursday, April 05, 2012 6:14 AM
  • please update link again
    Thursday, April 05, 2012 7:54 AM