none
Wrong labeled x-axis of chart - Original dates aren't displayed RRS feed

  • Question

  • Hello,

    I use SQL Server Reporting Services 2005. There is a problem with the scatter chart (xy-chart) and the labeling of the x-axis.
    The x-axis contains values of the datetime format and the y-axis contains values of something numeric (e.g. currency).

    Here is an example:

    | X                   | Y        |
    | -----------------| --------|
    | 31.12.2007   | 1000 |
    | 31.01.2008   |  774   |
    | 31.01.2008   |  800   |
    | 28.02.2008   | 889   |
    | 28.02.2008   | 600   |
    | 31.03.2008   | 1021  |
    ....

    (a better example is in my second post)


    if I render the chart, the x-axis doesn't have the original values as label, it has everytime the first of the month! It starts correctly with drawing the chart, but the first label is at the 01.01.2008 and not at the root of the x-axis at 31.12.2007. This matters also the other months. There is everywhere the first of the month labeled and not the last of the month (like the data suppose).

    How can i get the chart to display the correct date (everytime the last of the month)?

    Thanks for help!

    Alex
    • Edited by Wiedsche Thursday, January 22, 2009 9:23 AM added some values to the example
    Wednesday, January 21, 2009 3:32 PM

Answers

  •  I spent some more time reviewing this today along with a few folks on the team.  The behavior in 2005 is actually correct, but there's a missing feature that isn't in the product until the 2008 version.  In order for this scenario to work, the x-axis has to a value axis, which means you can have an infinite number of points along the axis, and also allows you to stack two values at one point on the axis.  This part is working as designed in 2005.  The chart is actually rendering correctly there, it's just that the axis in 2005 controls where the labels are displayed.  You can set the interval to 1 and you will see multiple day tick marks and the chart will render at the right point on the axis.  

    The second piece that you need, which is available in 2008, is a feature that allows you to offset the axis labels by a day.  The axis in 2008 also has more logic in it to handle date-time intervals like 1 month, 1 day, 1 year, 1 hour, etc.  For this chart in 2008, you can simply set the label offset to -1 and the chart will display the last day of the previous month.  This, of course, assumes that all of your data comes is associated with the last day of the month.  Keep in mind that with this chart, the number of points along the x-axis is infinite if you had data that comes in on different days, this solution would not work for you.  However, if that is the case, you probably would not necessarily care if an axis label was associated with each data point, because if that were true you would be looking at a categorical axis.   Categorical axis does not support mulitple values at the same x-axis index.

    I'm going to attach the 2008 report to my Skydrive in this location at http://cid-209305deacf224d0.skydrive.live.com/browse.aspx/SQL%20Server%20Reporting%20Services%20Forum. It will be called StepFunctionChart.  You can download Report Builder 2.0 to for free and preview it to see that it works.  Also, if you were to purchase the Dundas SSRS components for 2005, this same functionality would be available to you.

    -Sean
    Program Manager, SQL Server Reporting Services
    • Marked as answer by Wiedsche Monday, February 23, 2009 10:05 AM
    Thursday, February 12, 2009 9:09 PM

All replies

  •  A couple of things...

    1.  What do you have on the category groups (X?).  What are you then using for the Data Values (X,Y)?  I only see two variables here so I'm assuming you are trying to use both X on category and X for values as well?
    2.  Take a look at the rdl file to see if the X value in the dataset is not set to System.String.  Make sure it's set to System.DateTime.



    -Sean
    Program Manager, SQL Server Reporting Services
    Wednesday, January 21, 2009 5:40 PM
  • hi sean,

    to 1.) Yes, I tried using X for the values and for the category groups. And i enabled "numeric or time-scale values". But it doesn't work. I specify the used data a little bit more. On some dates there are two values, so that there is a crack (or better a vertical line). (sorry, i forgot that in the previous post).

    X Y
    31.12.2007 100
    31.01.2008 200
    31.01.2008 150
    28.02.2008 180
    31.03.2008 300
    31.03.2008 350
    30.04.2008 200
    30.04.2008 150


    If I don't use the category groups field (leave it empty) and enable "numeric or time-scale values", then the chart looks correct, except the wrong label on the x-axis. If I use the category gorups field with Fields!x.Value, then the chart is wrong... the cracks (vertical lines) disappear ...

    how can i solve the problem?


    to 2.) The x value in the dataset is set to System.DateTime.


    Alex


    Wednesday, January 21, 2009 9:29 PM
  • isn't there a solution for this problem?

    i found this http://technet.microsoft.com/en-us/library/aa964128.aspx#moressrscharts_topic4 in an other thread ... figure 20 shows the correct chart ... but i don't know the data behind... 

    perhaps i have to write custom code?
    Thursday, January 22, 2009 10:48 PM
  • Hi,

     

    Try the following settings on the category group:

        Group on Expression: =Year(Fields!date.value)+Month(Fields!date.value)+Day(Fields!date.value)

        Label: Day(Fields!date.value)&"."& Month(Fields!date.value) &"."& Year(Fields!date.value)

    In addition, disable the “Numeric or time-scale values” option.

     

    If you have any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, January 28, 2009 10:25 AM
    Moderator
  • hi xiao-min tan,

     i have to disappoint you - it doesn't work. :(

     

    here are my settings:

    - Scatter-Chart

    - Values:

         X: =Fields!x.Value

         Y: =Fields!y.Value

    - Category Group:

         Expression: =Year(Fields!x.value)+Month(Fields!x.value)+Day(Fields!x.value)

         Label: =Day(Fields!x.value)&"."& Month(Fields!x.value) &"."& Year(Fields!x.value)

     - X-axis:

         DISABLED Numeric or time-scale values

         NO interval

     

    With this settings, the chart has NO vertical lines (as it should) and the x axis shows no label ... only many markers on it without text.

     What might be wrong?

     

    Here my testing data:

      31.12.2007 00:00:00 100
      31.01.2008 00:00:00 200
      31.01.2008 00:00:00 150
      28.02.2008 00:00:00 180
      31.03.2008 00:00:00 300
      31.03.2008 00:00:00 350
      30.04.2008 00:00:00 200
      30.04.2008 00:00:00 150

    Perhaps you (or someone else) get it working?

    thx alex

    • Edited by Wiedsche Saturday, January 31, 2009 11:32 AM duplicate content
    Friday, January 30, 2009 9:47 AM
  • Is this such an untypical problem?
    Couldn't you understand it, or is it such a real problem? Or am I too stupid and don't see the forest for the trees?

    please help me! thx!
    Tuesday, February 3, 2009 9:43 PM
  • Scatter charts, as described in Figure 20 of the referenced chart whitepaper MUST have a dynamic category grouping AND a dynamic series grouping.   Note that the whitepaper also has a report project attachment with all those sample reports contained.  Take a look at the scatter chart report in those samples - it should help you understand how it is done.

    In your case, for the series group expression, you have to pick a unique value or use an expression so that every row of your datasets results in a different value.

    HTH,
    Robert


    Robert Bruckner   http://blogs.msdn.com/robertbruckner

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, February 4, 2009 7:18 AM
  • Hi Robert,

    i already looked at the whitepaper and the report attachment. The "StepFunctionCharts" looks like what i want... BUT there is also the problem... Please try this yourself... change the Dataset1 to the following sql-string and look at the results.

    select 1 as MeasurementId, '07/31/2006' as TimeStamp, 10 as Value union
    select 2 as MeasurementId, '08/31/2006' as TimeStamp, 10 as Value union
    select 3 as MeasurementId, '08/31/2006' as TimeStamp, 8 as Value union
    select 4 as MeasurementId, '09/30/2006' as TimeStamp, 8 as Value union
    select 5 as MeasurementId, '10/31/2006' as TimeStamp, 10 as Value union
    select 6 as MeasurementId, '10/31/2006' as TimeStamp, 12 as Value union
    select 7 as MeasurementId, '11/30/2006' as TimeStamp, 12 as Value union
    select 8 as MeasurementId, '12/31/2006' as TimeStamp, 12 as Value union
    select 9 as MeasurementId, '12/31/2006' as TimeStamp, 9 as Value union
    select 10 as MeasurementId, '01/31/2007' as TimeStamp, 9 as Value

    As you can see, the chart starts with Aug 01 and not with Jul 31... and that is my problem.... How can I get it writing there Jul 31 or more better, just the values used in the datasource (and none automatically generated additional values).

    HFH,
    Alex
    Thursday, February 5, 2009 3:23 PM
  • Hi Alex,


    Based on the dataset above, I build a scatter chart using the following settings and it works.


        X: =Fields!TimeStamp.Value

        Y: =Sum(Fields!Value.Value)

        Category group expression: =Fields!TimeStamp.Value

        Category label expression: =Fields!TimeStamp.Value



    Hope this helps.

    Thanks.

     


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, February 6, 2009 3:58 AM
    Moderator
  • Hi Xiao Min Tan,

    this chart works, but it displays something different. it displays the sum of the values on a specific date.... this shouldn't be so... there should be a step in the chart (like the "StepFunctionChart" looks in the whitepaper).

    If i don't use
       Y: =Sum(Fields!Value.Value)
    but
      Y: =Fields!Value.Value
    there is only displayed the first value on that particular date (... and no step in the chart).

    HFH again,
    Alex
    Friday, February 6, 2009 10:18 AM
  • i see... this is a missing feature or a bug in sql server 2005 ... or there is nobody who has a solution..... :(
    Tuesday, February 10, 2009 9:39 PM
  •  I spent some more time reviewing this today along with a few folks on the team.  The behavior in 2005 is actually correct, but there's a missing feature that isn't in the product until the 2008 version.  In order for this scenario to work, the x-axis has to a value axis, which means you can have an infinite number of points along the axis, and also allows you to stack two values at one point on the axis.  This part is working as designed in 2005.  The chart is actually rendering correctly there, it's just that the axis in 2005 controls where the labels are displayed.  You can set the interval to 1 and you will see multiple day tick marks and the chart will render at the right point on the axis.  

    The second piece that you need, which is available in 2008, is a feature that allows you to offset the axis labels by a day.  The axis in 2008 also has more logic in it to handle date-time intervals like 1 month, 1 day, 1 year, 1 hour, etc.  For this chart in 2008, you can simply set the label offset to -1 and the chart will display the last day of the previous month.  This, of course, assumes that all of your data comes is associated with the last day of the month.  Keep in mind that with this chart, the number of points along the x-axis is infinite if you had data that comes in on different days, this solution would not work for you.  However, if that is the case, you probably would not necessarily care if an axis label was associated with each data point, because if that were true you would be looking at a categorical axis.   Categorical axis does not support mulitple values at the same x-axis index.

    I'm going to attach the 2008 report to my Skydrive in this location at http://cid-209305deacf224d0.skydrive.live.com/browse.aspx/SQL%20Server%20Reporting%20Services%20Forum. It will be called StepFunctionChart.  You can download Report Builder 2.0 to for free and preview it to see that it works.  Also, if you were to purchase the Dundas SSRS components for 2005, this same functionality would be available to you.

    -Sean
    Program Manager, SQL Server Reporting Services
    • Marked as answer by Wiedsche Monday, February 23, 2009 10:05 AM
    Thursday, February 12, 2009 9:09 PM
  • So i'll have to live with this problem - the autogenerated scale.... I'm not allowed to use SSRS2008 or dundas components at this time...

    Thank you all for supporting.
    Monday, February 23, 2009 10:07 AM