Skip to Content

A lot of users have been confronted with an issue with the display of a column chart when label names are excessively long.  You have probably seen how chart labels will flip vertically if the labels are over a certain amount of charactors.  When this occurs, the data area of the chart becomes significantly smaller making the chart difficult to read.

long labels 1.png

There are several workarounds that can be used to make the data area of the chart more readable.  The simplest solution is to expand the chart.

However, this is not always an option due to the canvas size or by other components occupying space on the canvas.

expand long labels 1.png

Another quick option is to change the font size of the labels.  This too may present a problem since the labels may be difficult to read.

font  long labels 1.png

A better solution may be to word wrap the labels.

/wp-content/uploads/2013/06/wordwrap_237100.png

Most charts are generated dynamically from the data coming in from a connection.  This makes editing the label names less of a usable option.  Here, I will display how to word wrap labels by adding a line break between label names by using a formula.  There are a number of ways to do this and this is just one example.  In this example, we are assuming the labels are comprised of multiple words delimited by a space.  However, the same method can be used for other delimiters as well.

Let’s start with the basics of the dashboard.  In this example, we are populating the chart from data coming in from a connection and displaying the labels and data.

/wp-content/uploads/2013/06/data_237152.png

In this example,  we will split the labels words into two separate cells and then concatenate them back together along with a line break between each word.

We can add columns to flow the individual words,  or map the separated words to some unused cells within the spreadsheet.

Start by highlighting the labels column cells. and click on the DATA tab withing the excel spreadsheet.  We will be using the Text to Columns button to do this,  but this can also be done using an excel formula.  I find the Text to Columns method to be the easiest method.

/wp-content/uploads/2013/06/txttocolumns_237154.png

Click on the Delimited option if it isn’t automatically selected and click Next.

/wp-content/uploads/2013/06/delimited_237161.png

Since our labels are space delimited,  ensure the Space option is selected.  You can see from the preview how the labels will be split. Click Next to continue.

space 2.png

Choose a destination cell for the Label words to flow into.  This is a single cell that will be the first cell used for the Labels.  Click Finish to complete this Text to Columns step.

/wp-content/uploads/2013/06/destination_237170.png

Upon completing this step,  you will see the new cells populated with the Labels.

/wp-content/uploads/2013/06/split_237171.png

Now we will create a formula to concatenate the cells back with a line break between each cell.  To do this,  we will use the Excel CONCATENATE() function along with the CHAR() function.

Select a cell for the formula.  I suggest you use a cell next to the original cells.

/wp-content/uploads/2013/06/concat1_237172.png

Note:  Char(10) is the code for a line break on a Windows machine. 

Copy the formula down the entire range of Labels.

/wp-content/uploads/2013/06/concatall_237182.png

The final action in this stage is to format the cells to be word wrapped.  Highlight the column of concatenated cells right click with your mouse.  Select Format.

/wp-content/uploads/2013/06/format1_237183.png

On the format dialog box,  click on the Alignment tab and select Wrap Text.  Click finish to apply the formatting.

format 2.png

The final step is to remap your chart to use the new Labels. 

/wp-content/uploads/2013/06/final_237185.png

Here again is our final result.

/wp-content/uploads/2013/06/wordwrap_237100.png

These steps are provided as just one possible solution.  Hopefully, this can spawn other ideas that can be shared.

To report this post you need to login first.

17 Comments

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

  1. Alex Yale

    This doesn’t actually work in Xcelsius, unfortunately.  The CHAR() function is not supported, so CHAR(10) will break your dashboard.

     

    Instead of CHAR(10), you want to use ALT+ENTER

     

    So

    F2 = D2 & ” <ALT+ENTER> ” & E2

    where <ALT+ENTER> means actually pressing ALT+ENTER on your keyboard. 

     

    In the formula bar, this will show up as

    F2 = D2 & “
             ” & E2

    (0) 
    1. Tony Foster Post author

      Thank You Alex,

       

        Could you please specify the version of Xcelsius you are using?  The Char() function is working for me in both Dashboards4 and with my deployment of Xcelsius 2008 SP5.  The Supported Excel Functions area in the Help pages shows that function as being usable. I do not have a previous version of Xcelsius at the moment but would like to annotate any version where the CHAR() function cannot be used.

       

      Thanks again for your input.

      Tony Foster

      SAP AGS Support

      (0) 
      1. Alex Yale

        Hi Tony,

         

        I’m on Dashboard Design 6.0 – I guess the CHAR() function has been added recently?

         

        The CHAR() has been an oft-requested function precisely for the carriage-return character.  See http://crystaldashboardtips.com/xcelsius-tips-wrap-text-in-a-scorecard/ for example.

         

        Char() is also not mentioned in BO 4.0 Feature Pack 3 published last year:

        http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_dashD_user_en.pdf

         

        C

        •CEILING

        •CHOOSE

        •COMBIN

        •CONCATENATE

        •COS

        •COSH

        •COUNT

        •COUNTA

        •COUNTIF

        (0) 
        1. BO Developer S

          Hi Alex,

           

          This ia very nice workaround, but I am not able to see the refreshed data. Currently I am using Xcelsius 2008 SP5 and pulling the data from WebI through Live Office.

          (0) 
  2. Aashutosh Hore

    Hi Tony,

     

    It is a nice info and really helpful. I am able to implement the solution.

    Like this can we able to display the labels in 45 degrees orientation? Do you have any info related to it?

     

    Thanks,

    Aashutosh

    (0) 
  3. Runali Ghosh

    Hi Tony

    The CHAR function didnt work with Xcelsius 2008 SP5 – It throws the error Unsupported Excel function – CHAR.

    We usually use the ALT+Enter combination. Not sure if you have any properties enabled.

    However, splitting the long name using the Text To Column is a nice trick!

    Thanks

    Runali

    (0) 
  4. Nitin Patil

    Hi Tony,

     

    we are on BO sp6 , I tried the steps given by you, in preview mode its says “not supported function”. Can you tell us the version you have used and tested this function.

     

    Thanks,

    Nitin

    (0) 
    1. Javed Chowdhary

      Hi,

       

      You can also try Alt+Enter this also works fine for giving a Enter after the word you want

       

      eg- if you have “Label One” as label just put an Alt+Enter after “Label”.

       

      This will also make the labels looks like the final result mentioned above.

       

      Regards,

      Javed

      (0) 
  5. Nirupa Nalabotula

    Hi Tony,

     

    I have tried this, its very useful, thank you for sharing. But in Dashboards 4.0 SP5 CHAR function is not supporting in preview mode. The option posted by Alex Yale is working fine.

     

    Regards,

    Nirupa

    (0) 
  6. Arun Kumar

    Hi Tony,

     

    If we concatenate and put one word below the other, the size of the graph will shrink.  I had a pretty long name called ‘Quality Samples Program’ and i shortened it to ‘Quality’ so that the graph size do not shrink.

     

    Thanks,

    Arun

    (0) 
  7. Kailas Kurup

    This is an excellent work-around. I’ll definitely try this out.

     

    One question – This can be implemented when you know beforehand how many words you are expecting to be wrapped. What if the data comes directly from the database and you do not know how many words the column value would return.

    Has anyone tried this scenario?

    (0) 

Leave a Reply