Visual alerts – Exception highlighting in reports and dashboards

Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on email

In this article we will expand on the “Bells and Whistles” section of our Dashboards.

There are multiple ways to alert the user when your data crosses a certain threshold.

The most used technique is to change the color of the data depending on whether the data is on the target, above the target or below the target.

In this article you see a basic example of the column data is mainpulated to change the colors.

Each and every widget has access to this global array of its data.

This is accessible using the “glb” variable. It is an associative array and the actual pointer to the data cells is glb[“_odata”]

So let us understand how we built the color highlights for the status column on this “Status Dashboard

Using the same techinque in the documentation we inspect each cell and add the html css styling to the desired cell.

var cols=glb["_odata"].length;
var rows=glb["_odata"][0].length;
 
for (var i=0; i < rows; i++)
{
   if (glb["_odata"][3][i]=="Completed")
   {
      glb["_odata"][3][i] = "<span style='color:white;background-color:green;padding:5px;'>"+glb["_odata"][3][i] +"</span>";
   }
   else if (glb["_odata"][3][i]=="In Progress")
   {
      glb["_odata"][3][i] = "<span style='color:white;background-color:blue;padding:5px;'>"+glb["_odata"][3][i] +"</span>";
   }
   else if (glb["_odata"][3][i]=="Cancelled")
   {
      glb["_odata"][3][i] = "<span style='color:white;background-color:red;padding:5px;'>"+glb["_odata"][3][i] +"</span>";
   }
   else if (glb["_odata"][3][i]=="Scheduled")
   {
      glb["_odata"][3][i] = "<span style='color:white;background-color:orange;padding:5px;'>"+glb["_odata"][3][i] +"</span>";
   }

}

 

Using the above code, gives us the result as shown below

Image

 

In the javascript code, we determine how many rows and columns does our data have

var cols=glb[“_odata”].length;
var rows=glb[“_odata”][0].length;

glb[“_odata”].length; => This tells us how many columns we have

glb[“_odata”][0].length; => Next we inspect the first column and find out its length. This tells us how many rows it has.

Then we process each row for and inspect the cell data in the “Status” column. Looking at the table, we know that the status is the 4th column and since the index of the column starts at zero (0) the index of “Status” is = 3

so each cell is accessible by this glb[“_odata”][3][i] in the loop

Now we can do whatever we wish to the data. You can replace the contents of the data but in our case we simple prepend and append the required CSS tags for color highlighting.

 

How to change the colors of Symbols and Icons

For single cell values, you can access the data in the cells [0][0] through the glb[“_new”] variable.

This is true for all icons and widgets where you expect single cell value.

For grids and other charts, it will only give you the first cell value

Let say you have an icon showing some number. You want to change the color of this icon when the number goes below or above a certain threshold.

We will create a new icon and drag it to the canvas

Image

Next we will feed this icon with data from our database

Right click on the icon and select “Data Source”

In our case we will work with SQL Server 2005 database

We select the connection and then add the following SQL in the sql window

select count(*) as user_count
from “ic”.”dbo”.”xxic_user”

 

Image

 

We click ok and in few seconds we get the count displayed on the icon

Image

Now we want to add some exceptions, if the count goes below 3 then make the background color red, if it goes above 5 then make it green

Here are the list of properties that are available for us to manipulate.

https://www.infocaptor.com/help/visual_effetcs___alerts.htm

  1. “dWidth”:200, [change the width of the object]
  2. “dHeight”:200, [change the height]
  3. “dx”:0, [change the x location]
  4. “dy”:186, [change the y location]
  5. “fillColor”:”#533813″, [change the fill color]
  6. “fontName”:”default”, [change the font name – this is disabled]
  7. “fontSize”:57, [change the font size]
  8. “fontStyle”:” “, [change the font style – not needed, manipulate through showBold, showItalic etc]
  9. “horizCellPadding”:10, [change cell padding]
  10. “horizObjectPadding”:15, [change overall padding]
  11. “lineColor”:”default”, [change line color]
  12. “lineWidth”:1, [change line width]
  13. “margin”:5, [change margin]
  14. “opactiy”:0.4, [change transparency]
  15. “showBackground”:true,
  16. “showBold”:false,
  17. “showBorder”:false,
  18. “showItalic”:false,
  19. “showStrike”:false,
  20. “showUnderline”:false,
  21. “textBackgroundColor”:”none”,
  22. “textColor”:”red”,
  23. “textHorizAlign”:”center”,
  24. “textX”:-10, [change the relative position of text X location in icons]
  25. “textY”:0,
  26. “vertObjectPadding”:10,
  27. “zIndex”:-1,
  28. “showTextShadow”:false,
  29. “textShadowOffsetX”:0,
  30. “textShadowOffsetY”:0,
  31. “textShadowBlur”:0,
  32. “textShadowColor”:”white”,
  33. “showBoxShadow”:true,
  34. “boxShadowOffsetX”:0,
  35. “boxShadowOffsetY”:0,
  36. “boxShadowBlur”:0,
  37. “boxShadowColor”:”white”

With all the above properties we can alter the entire icon look and feel and manipulate it to its core. But we will restrain our selves to just a few basic properties.

The most important is the #5 “fillColor”

We add the following Javascript code

if (glb[“_new”]<3)
{
this.fillColor=’red’;

}
else if (glb[“_new”]>=3 && glb[“_new”]<5 )
{
this.fillColor=’blue’;
}
else if (glb[“_new”]>5)
{
this.fillColor=’green’;
}

Image

And when the number is 3 we get this

Image

When the number goes below 3 we get this

(in our case we decrease the count by 2

select count(*)-2 as user_count
from “ic”.”dbo”.”xxic_user”

)

Image

and our icon changes to the red background

Image

Next we try to go above 5

Image

and the result is

Image

So our icon exception is tested for three conditions and it works. In this way if the data changes in real time then the icon whenever refreshed will pick its background color accordingly.

In the same javascript you can manipulate other properties. The icon is accessible using the “this” variable and the property.

so this.textColor=”blue” will change its text to blue.

The color can be specified as standard string values like red, green, blue,orange, cyan,black,white etc or using the HEX codes like ‘#FFFEEE’, ‘#FDEFED’ and so on.

If you manipulate the dx and dy then the icon will move around the screen.

Other properties are there for reference but don’t have any practical use.

The most interesting effects are by adding the animation effects.

So in the same condtion, I can ask the icon to blink or dance

this.div.effect(“bounce”, { direction:’left’, times:5 }, 300);

will bounce the icon towards left 5 times before it stops

Here is our final javascript code

if (glb[“_new”]<3)
{
this.fillColor=’red’;
this.div.effect(“bounce”, { direction:’left’, times:5 }, 300);

}
else if (glb[“_new”]>=3 && glb[“_new”]<5 )
{
this.fillColor=’blue’;
}
else if (glb[“_new”]>5)
{
this.fillColor=’green’;
this.div.effect(“pulsate”, { times:3 }, 2000);
}

When the number goes below, it turns the icon red and bounces

When the number goes above 5 then it turns green and blinks on and off.

This is extremely useful if you are monitoring some real time application.

There are two other alerting mechanism, sound and email. Both of them can be added in the if and else condtion in similar way.

Hope this article gave some additional insights into how you can design effective visual dashboards and reports.

The Core Tools

Create dashboard for any Database

Data Visualizer and Dashboard Application
SALE
This is the best dashboard software for its price. One good thing we did was to hire their consulting services to build few dashboard prototypes and provide some quick dashboard training.
- Terry Seal, IL
We evaluated Xcelsius and Qlikview and the cost for organization to implement dashboards was quoted over 10,000 USD. For fraction of the above quoted price, we were able to buy the licenses for the web based dashboard software and get some free training. This is truly a dashboard software for small businesses like us.
IT Manager of a Trucking company, OH