Skip to Content

If you have a chart in Crystal Reports with a date value for the X-Axis, you may have noticed that it is difficult to format / customize the X-Axis date values. For example, you may want the format to be January 2012 or Dec ’11, etc. If you change the X-Axis value to be a string conversion of the date, then you may not have the desired order in the chart depending on how you format the label.

Here’s a tip to help you customize the X-Axis label and also keep the correct date order in the chart…create a cross-tab based on a label formula, the summary that you want to show on the chart, and a date. You can then create a chart by right clicking on the cross-tab and choosing Insert Chart.

Steps:

1) Click here to open the sample report…note that you can change the format of the X-Axis label by using the parmeter / prompt of the report. Also note that the order of the chart does not change.

2) Go to the Details section of the report. In Report Header C there is a cross-tab and the description of the cross-tab used to create the chart labels.

3) The cross-tab contains 3 values…the Row is the formula that is used for the date label formatting. Right now it uses the parameter / prompt to get a dynamic format from the end user. This will be used by the chart for the On Change of value. Note that this value is actually a string / text output.

4) The second value (the first summary) in the cross-tab is used for the Show Value…in this case it’s a sum of sales.

5) The third value (the second summary) is an important one as it is a max on the date field that is used to create the correct order of the chart. The cross-tab group expert uses the max of the date to create the sort on the cross-tab, which is later passed to the chart.

6) Note that a cross-tab chart can be created by right clicking on any cross-tab and choosing Insert Chart. There are a few drawbacks to using this method such as a cross-tab chart has limited options such as only 1 Show Value.

To report this post you need to login first.

20 Comments

You must be Logged on to comment or reply to a post.

  1. Nisar Ahm

    Hi Jamie,

    I was trying to create a Chart based on Cross Tab which has variable Columns meaning I have a column which is of date type in crosstab. For this I need to make it in Mon-YY format.

    Any pointers

    Thx

    (0) 
    1. Jamie Wiseman Post author

      hi Nisar Ahm,

      there is one formula used on the sample report that affects the display and order on the chart. it is the @DateDisplay formula. this formula is used in the Rows dialogue of the cross-tab. if you open it up it has syntax of

      //totext({Orders.Order Date},”MMM yyyy”)

      totext({Orders.Order Date}, {?DateFormat})

      currently it takes a parameter to format the chart. so if you enter MM-yy into the parameters panel you’ll get the format that you need. you can also change the DateDisplay formula to have syntax of

      totext({Orders.Order Date},”MM-yy”)

      if you no longer wish to use the parameter.

      jamie

      (0) 
  2. Stuart Isaac

    Is there a way to create a chart of type Date Axis Line Chart that will respect this? I’ve tried the approach suggested here (with the sample report), but once I set it to Date Axis Line Chart, the dates that I get seem to be offsets from 12/31/1899, not  the actual dates from 2000 – 2002. The regular line chart works okay, but then I don’t get the proportional spacing that the date axis line chart provides (i.e. if there are missing days in the date range).

    (0) 
    1. Jamie Wiseman Post author

      hi Stuart, usually when you see a ‘1899’ date then there’s NULL data coming in from your database for those particular dates. to check for this go to the Database menu then Show SQL Query and copy this and run it through your database sql query editor. you should see a bunch of NULL dates coming through. if this is the case you can use a check in a crystal formula for an ISNULL on the date…if there is a null, then you’ll need to decide what you want to convert the date to. you will use a formula in place of putting a date in your chart or crosstab.

      i.e. IF ISNULL({yourdatefield}) then DATE(yyyy,MM,dd)…

      let us know what’s coming back in your data.

      jw

      (0) 
      1. Stuart Isaac

        Null dates are not the issue, although I imagine that’s probably what is happening in the internals of the report. I replicated this with the supplied sample report in your initial post. If you get different behavior when switching the bar chart to numeric axis line chart in the sample report, I would be quite surprised. 🙂

        Stuart

        (0) 
        1. Jamie Wiseman Post author

          hey Stuart…you are indeed correct…i got the exact same behaviour as you did.

          i’ll let product group know about this issue. however, i am not sure how this will get treated as i believe that a numeric axis chart is supposed to take either a number or a date as the x axis / on change of value. in this case, we’re using a string / text which is why it is choking. changing the on change of to a true date works correctly.

          jw

          (0) 
          1. Stuart Isaac

            Figuring that was the case, one other thing that I tried was to make the crosstab row be a true date, and to use the Customize Group Name field option to return the formatted string. I.e. I used {Orders.Order Date} for the crosstab row, and {@DateDisplay} in “Use a formula as group name”. That results in the same behavior, though.

            The suggestion that I would like to make is to take the crosstab out of it altogether: that the chart expert allow separating the sorting element from the display element, just as crosstabs do. That would solve more than one problem for me. 🙂

            (0) 
  3. Karthikeyan Vellaisamy

    Hi Jamie,


    I am trying to draw line chart having the X-Axis with date ( month and year ) and Y-Axis with Price.


    I will have the Price for almost all the days ( except holidays ) which I need to show the values. But, in the X-Axis (Date) I just need to show the values on monthly basis like, Jan 13, Fed 13, etc..

    The values on Y-axis (Price) shouldn’t have any aggregate functions applied to it. It should show all the values. How can I achieve this functionality ?


    Ex. of my data from :


    Date                                    Price

    01/10/2013                       12.3

    01/11/2013                       13.3

    01/13/2013                       10.3

    .

    .

    .

    02/09/2013                       16.3

    02/10/2013                       19.3

    .

    .

    03/01/2013                       12.3

    .

    .


    I would like the chart like,



    Y-Axis

    12.3, 13.3, …16.3,19.3,….12.3


    X-Axis

    Jan 13

    Feb 13

    Mar 13


    Thanks!

    Karthik

    (0) 
    1. Jamie Wiseman Post author

      hi Karthik, you can do this without the cross-tab workaround actually.

      1) create a new chart and have that chart type as the Numeric Axis > Date Axis Line Chart.

      2) after you choose the On Change Of (your date field) ensure that you’ve got the Order / granularity set to For Each Day.

      3) once you’ve finsihed in the Chart Expert, preview the chart and right click on one of the values in the X axis and choose Group (X) Axis Options.

      4) go to the Scales tab and set the Base Unit to Month(s), the Major Unit to 1 Month and the Minor Unit to 1 Day.

      5) go to the Numbers tab and then set the Type to Feb-02.

      6) press OK to preview the chart again. left click on the report somewhere outside of the chart, and then right click on the chart and ensure that you choose Apply Changes to All Charts.

      you should now have a chart where the line points are by day but the labels are by month and there is only one label per month.

      cheers,

      jamie

      (0) 
  4. Karthikeyan Vellaisamy

    Thanks for your response !

    I can only complete the point 1.

    2) I can set “On Change Of” with my Date field and set it to “Ascending Order”

    What do I set for the “Show values” ? Since I have selected “On Change Of” the price field have to added along with an aggregate function like, Sum, Maximum, etc..but I need to show the values as it is and not apply any functions.

    I am not sure about the points 3 to 6. Please note I am using Visual Studio 2010 to design the CR 13. I am designing the report and be using the Export function to export it to PDF. I won’t be showing this report on screen. How do I change the settings while I am in the preview ?

    I think I am missing something here. Please help me out.

    Thanks!

    Karthik

    (0) 
    1. Jamie Wiseman Post author

      hi Karthik,

      i didn’t know you were using visual studio. you should post this question to the crystal reports version for visual studio area as the steps will probably vary a lot from what i’ve listed and this may not even be possible in that designer.

      before you do so a couple of suggestions…if you don’t see any options to change the granularity of your date field, it’s probably because that field isn’t coming into crystal as a date…if that’s the case create a formula that changes the field to a date and use that as the On Change Of.

      if isdate({yourfield})

      then date({yourfield})

      if you’ve only got one record per day like you’ve indicated in your sample data then the SUM will be okay for the Show Value.

      again, if you don’t have the same options etc. available, please post these questions to the visual studio area.

      -jamie

      (0) 
        1. Jamie Wiseman Post author

          you can use crystal reports 2008 standalone designer and above. i.e. get out of any of the development environments and just use the report designer desktop application.

          i’m not 100% sure when the Numeric Axis style charts were added but it’s in cr 2008 and above for sure.

          you can get a 30 day trial version of cr 2013 here if you want to check that this type of chart will work for your data.

          (0) 
          1. Karthikeyan Vellaisamy

            Thanks Jamie,

            It worked !

            However the point 4) go to the Scales tab and set the Base Unit to Month(s), the Major Unit to 1 Month and the Minor Unit to 1 Day.

            I couldn’t set the Major Unit to 1 Month – It’s not changing anything other than 1 Day.  If I choose Months then it goes back to Day automatically. Not able to change the number 1 too.

            The report displayed with once in two months for X axis and all the data ( daily ) for Y axis.

            Not sure why the X axis printed for every two months – but I believe this is OK.

            Thanks!

            Karthik

            (0) 
            1. Jamie Wiseman Post author

              that’s good that it works but it’s odd that this chart dialogue will not let you change some of the important options to complete the overall task.

              a) perhaps try changing them when the report is in Preview mode vs. having the Design mode open.  it works fine for me in cr2011 sp4. please click the picture below if you can’t see the options at the right.

              or

              b) create a brand new chart and do the process from start to finish on the new chart…once in a while i’ve found that when you edit a chart too many times that strange things start to happen in the chart Options or Editor…and at that point it’s best to just create a new chart.

              Untitled.jpg

              if that doesn’t work i would suggest opening up a new Discussion in the crystal reports forum to see if anyone else has had this issue and can help you solve it.

              (0) 

Leave a Reply