Jul 22 2007

Alerters on Charts: Part I

Categories: Full Client,Report Techniques,Variables! Dave Rathbun @ 12:28 pm

The Challenge

Officially there is no way to put an alerter on a chart. Yet some time back I was challenged by a client to create a line chart that was green if the overall trend was up, and red if the overall trend was down. This chart appeared in a sectioned report so there was a different chart for each section value. Possible? It turns out that there was an answer that works. With minor changes it works in both the desktop and the web products as well.

The eFashion database and universe are used in this demonstration.

Solution using Business Objects 6.5

The first step is to get some data that will support this requirement. I selected the Lines objects from the Product class, the Year from the Time class, and the Sales revenue from the Measures class. Depending on your version of eFashion data you might have slightly different values but the 6.5 version of the database looks like this:

Raw data

As you can see from reviewing the data the Accessories product line is in an overall downward trend if you take the first and last year as the only data points. City Skirts is in an upward trent, and City Trousers is again in a downward trend. I don’t intend to do a true trend analysis with moving averages; I am going to make a very simple definition of “up” or “down” and use the first year and last year as my only two data points for this tip.

The next step is to create a variable that determines the trend direction. I am going to need a variable because that’s how I can determine which chart to show later on. The variable is hard-coded in this sample since I know what the end points are. The formula for the variable looks like this:

=Sign(<Sales revenue> Where (<Year>="2003") - <Sales revenue> Where (<Year>="2001"))

There are two components to this formula. The first is the “Where” clause on the measure value. This applies a filter to the revenue so that the value for the measure is filtered to a specified year rather than showing a total. The second component is the Sign() function. The Sign() function is fairly simple: it returns the numeric sign of the argument. If the argument is any positive value, no matter how big or small, the sign is +1. If the numeric argument is negative, the sign is -1. If the number is zero the sign is zero, and finally if the number is undefined the sign is also undefined.

The results of this formula are shown here:

Trend data

Notice that each product line has an appropriate trend, based on the difference in the 2003 revenues and those for 2001? I will be using this again shortly.

The next step is to set up the chart. For this example I am going to use a simple line chart as it shows the trend in the most obvious fashion. First I created a section on Lines, and then a line chart with Sales revenue and the Year data. Here’s what the first version of the chart looked like, and I’m not entirely happy with the results:

First chart

What don’t I like? Because of the difference in scale I can barely see the second chart as it is using the same scale as the first chart. Here is what I would rather see:

First chart

What I have done here is activate a setting that tells the report to use the scale within each section rather than for the overall report. I think it’s going to be much easier to read. Here’s where that setting appears:

Adjust chart scale

I now have a chart that I like, but it doesn’t fit the requirement yet. All of the charts are green, even those that are in a downward trend. The next few steps will complete this tip and solve the problem. First, I will duplicate the chart so that I have two identical charts. I will then change the color on the second chart so that it is red. (At the same time I increased the weight of the line just to make the charts more visible.) Here’s where these steps leave me:

Dual charts

The final step is to ensure that only one of these two charts appears for each section. This is done by right-clicking on the chart, selecting Format Chart, and going to the Appearance tab of this dialog box. I started on the red chart and entered a “Hide” formula that looks like this:

=<Sales Trend> = 1

For the green chart I do something similar but use this formula instead:

=<Sales Trend> <> 1

Here’s the screen where this is done:

Hide Chart Feature

What does this do? If the trend is positive then the red chart is hidden. If the sales trend is negative or zero then the green chart is hidden. So I get something like this:

Hidden Charts

I’m almost to the end of the technique… the last step is to carefully size and position the two charts so that they occupy the same space. Since I know only one or the other will ever be displayed I want them to be in exactly the same space. This provides the illusion that there is a chart alerter that is changing the color of the chart based on the trend of the sales data. The reality is there are two charts that switch places. Here is the final output:

Final Charts

Part II of this article will continue this idea and show how to accomplish the same illusion using techniques available in Web Intelligence.

Summary

The purpose of this article was to show how to simulate the appearance of a chart alerter. Since the tools do not provide this feature directly I used a variable and the “hide block” feature instead.

Functions Used

  • Sign()

Associated Downloads

  • BusinessObjects 6.5 Sample
    This download was built using the eFashion sample database provided by Business Objects. It should be accessable to anyone using version 6.5 or higher. The source file is zipped so you will need an unzipping utility to extract the file.

17 Responses to “Alerters on Charts: Part I”

  1. Comment by Marek Chladny

    Very nice, I like this workaround.

  2. Comment by Dave Rathbun

    Hi, Marek, thank you for your comment. 🙂 This is one of my favorites, and I have used it in several different presentations over the years. Part of my plans for this new blog are to go back and review some of my older tips and redo them in this format, just so I can preserve everything in one place.

  3. Comment by Kris

    I have a requirement and was looking for something exactly similar. Your post have helped me a lot and thank you. Really appreciate your post.

  4. Comment by Dave Rathbun

    You are welcome. 8) Thank you for taking the time to leave a comment to let me know that you found the tip useful.

  5. Comment by Krishna

    This is very good and helpful. I am able to do this in Deski by following your steps. Where as in WEBI XI R2 I am not able to see certain Chart Formats.

  6. Comment by Dave Rathbun

    Hi, Krishna, did you see Part II of this series? It shows how to accomplish the same trick in Web Intelligence. I only did it with a line graph as that’s what made sense to me for this technique.

  7. Comment by Nidhi

    Dave, I find the post very useful. Although,i never had a requirement till now,now i know it is possible in BO 🙂

  8. Comment by Anantha

    Hi Dave,

    This site is excellent and especially “Alerters on charts” really awesome. Great job!!!. Thanks a lot.

    Anantha

  9. Comment by Kishore

    Hi Dave,
    How to solve this in XI 3.0 , there is no Hide Block option…!!

    Thanks

  10. Comment by Dave Rathbun

    Hi, Kishore, thank you for your comment and welcome to my blog. Did you see the follow-up post to this one? It showed how to accomplish the same functionality in Webi.

    http://www.dagira.com/2007/07/31/alerters-on-charts-part-ii/

  11. Comment by Shiva

    This i an AMAZING trick……

    Thanks Dave for sharing this. 😀

  12. Comment by Preeti

    Hi,

    Great solution. Is there any way in the bar chart we can use different colors if the charts value is in downward direction i.e. less then ‘0’? So e.g. I have months on x axis and I have volumes on y axis. The measure is difference of this year;s this month volume and volume for same month last year. so if the difference is +ve it should be green if -ve then red. I know straightaway this is not possible. Is there a workaround for this?

    Thanks a ton,
    Preeti

  13. Comment by Dave Rathbun

    I am not aware of any way to do this. I was only able to come up with the trending alerter because the entire graph is set to green or red, using the technique from the post. There does not appear to be a way to customized individual chart components.

  14. Comment by Mohammed

    Hi Dave,

    In this report, I selected 2 Years by applying filter on Year. I was wondering if its possible to put a prompt on Year, like Year Inlist @Prompt and select 2 Years every time I refresh the report and the Alerter feature work every time. I tried to do it but couldn’t spend time to complete it. I created variables for Selected Year 1 as substr(userresponse();1;4) and Selected Year 2 as substr(userresponse();5;4). I’m lazy to complete the next part. Will let you know if done.

    Thanks,
    Mohammed

  15. Comment by Bryan

    @ Preeti:

    There are two ways to accomplish this. If you want all the bars to be facing in one direct (IE Up or towards the right) then use the Stacked Bar graph and create two varibles.

    Var 1:
    =If(Sign(CurrentMonth-LastYearMonth)=1 Or =If(Sign(CurrentMonth-LastYearMonth)=0) Then(CurrentMonth-LastYearMonth) Else (0)

    Var 2:
    =If(Sign(CurrentMonth-LastYearMonth)=-1) Then(CurrentMonth-LastYearMonth) Else (0)

    Then select your color pallet. The first Var should show yoor posative measures and the 2nd should show your negative. This would cause you to have two different color bars but in the same direction.

    The 2nd way is if you want the posative and negative var to be in opposet directions (IE posative up/right and negative down/left) You would set up the same two var except leave the “Else (0)” off and choose a normal bar chart. Then select your color pallet

  16. Comment by Edwin

    You should update this page. You can apply alerters to charts in BO 3.1. You just apply the alerter on the variable on each axis.

  17. Comment by RC REDDY

    Hi Dave,
    Thank u very much for valid information