You need to also make the Excel file available on the webserver so that the dashboard can connect to this Excel file and display information. You want to update the information in the Excel file on regular basis and upload or copy the file to the webserver directory.

Note: The dashboard uses ODBC connection to connect to Excel files. ODBC works only on windows machine. So technically you cannot connect to any Excel files over the internet or using http URL.

InfoCaptor internally has a mechanism to connect to files that are stored on the webserver and can access the excel files through URLs

When you launch the dashboard, it transparently connects to the Excel file by creating a local cache on the user’s machine.

To achieve this, you need to pick the following Excel Web File driver

and then provide the direct URL to the Excel file as shown above.

Click connect and it will create a local cache and connect.

You can also control how often the Excel cache is updated locally.

Click on the options button and then the Excel Cache settings tab

Set the number of days you want the Cache to expire.

ShareThis

So what if your data is not classified by a column. If you had different columns representing each stack, you can still create a stack bar.

Here is the Excel screenshot of some sample data. Each column represents the amount spend by a Person on different things such as measl,travel,gas,car and hotel. You want to see a stack bar for each person where each stack represents each column value.

You connect to this Excel file through InfoCaptor

Create a Dashboard and add the stacked bar chart.

Click Apply and you get the below stack bar

so there is no need for any special logic. You can do it with three columns or multiple columns

ShareThis

Let say you have two databases. Both databases have similar tables and you need to display the difference in the number of rows from these two tables.

InfoCaptor allows you to connect to multiple databases and display information on the same dashboard page. But in this situation we are required to create two distinct queries from two seperate databases and do calculation on the returned values.

This can be accomplished as illustrated below

Create first parameter with the total row count from 1st database table

Create second parameter with row count from second database table

Now create a Qlet that will do a difference between the two parameters

Right click on the text editor, will show the parameter list. Select the parameter Display value, remove the single quotes and the comments and then select the second parameter display value.

Once you select the p32 Get Display the Qlet text editor shows as below

Remove the single quotes and the comment section as shown below

Add a minus sign after the above string and then select the second parameter display value

Thats it - you get a Qlet that represents the difference of row counts between two seperate databases.

Now how about applying some conditional formatting to the Qlet

For the Qlet, enable the Javascript option by setting ‘Run Dynamic Java Script = ‘Y’

and then enter the following script

if (numericValue < 0 )
{
renderer.setForeground(Color.red);

renderer.setBackground(new Color(153,153,250));

}
else if (numericValue >0 && numericValue <100)
{
renderer.setForeground(Color.yellow);

renderer.setBackground(new Color(153,153,250));

}
else if (numericValue >100 )
{
renderer.setForeground(Color.orange);

renderer.setBackground(new Color(153,153,250));

}

Alternatively you could create a meter chart with the same query

http://www.infocaptor.com/user_help/dashboard_meter_chart.htm

ShareThis

Here are the steps to clear it. Also it is advised to clear the browser cache.

http://www.java.com/en/download/help/5000020300.xml

ShareThis

In case of a database action you can call a database stored procedure

Goto Menu Create -> Parameter

Leave the Parameter Name blank (Optionally- If you need have a title name for the button then you may enter it)

The Display Column property text is displayed on the button.

Select the Datatype as ‘BLET’ from the drop down list.

Finally add the database stored procedure call in the text editor. In the below example we are making a call to an Oracle Package procedure. In the call to the stored procedure you can pass references to other parameters.

How to make non-database calls or actions

In order to call external programs or launch specific URLs you need to follow the above steps except for the Text Area just enter NULL

Select DataType =’BLET’

Select ‘Run Dynamic Java Script’ = ‘Y’

and then click on the Dynamic Java Code

Enter the following command to launch a URL

[Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + http://www.infocaptor.com/how_to_build_dashboard_using_excel.pdf);]

ShareThis

• Database monitoring
  • Lot of database activity is stored real time in logging and statistics table. It is possible to create dashboard purely using SQL queries against these performance tables. Once the dashboard is created, you could set it on automatic refresh say every 5, 10 or 15 minutes to highlight the latest charts and server information
• Metrics on TV or huge display
  • Lot of companies now a days have huge flat panel TVs installed in corridors that display real time metrics. These TV displays are fed through a live refresh of Dashboard data

InfoCaptor dashboard can be set to refresh automatically. Here are the steps below

Goto Edit -> Dashboard

Scroll to the “Refresh Rate(seconds)” property and set the desired frequency in seconds for e.g. 300 seconds = 5 minutes refresh interval

Once set Goto Run -> Start Auto Refresh

This will start the refresh every 5 minutes

How do you refresh and rotate Dashboard views

If you have multiple dashboard views and would like to rotate the views at a certain interval then follow the steps below

Click on the options button on the toolbar. Then click on the Auto-Refresh tab.

Select the check box for “Auto Refresh & Cycle Each Dashboard”

Enter the number of seconds you wish to stay on each dashboard.

Click OK and Goto Run -> Start Auto Refresh

ShareThis

Goto Create –> Parameter, select ‘STATIC’ for the DataType. Enter the static text in the text area.

Data Entry Fields

To create Data Entry Fields select DataType = ‘FIELD’

Users can enter any loose text into these fields. These Data Entry fields can be used as regular parameters inside your SQL queries.

Add Image or Logos

You could add images as backdrop to any of the charts. If you need to add images or logos as seperate objects

Create a Parameter and select DataType as ‘IMAGE’. In the text editor enter the URL of the Image or the file location of the image on your PC

Type the direct URL to the image in the text editor. It is recommended to host the image on a webserver and use the http url to reference the image location. This is ideal because when you deploy the dashboard on the server it is still universally locatable.

You can also just type the file path on your PC e.g. “c:\my_images\xyz.gif”

ShareThis

In order to get the Item labels display a percent value, Edit the chart property “Item Label Number Format” to “##0%” and in order to display percent on the y-axis or the number axis, change the javascript code and the add the lines that are highlighted below in bold

//import the necessary classes
import org.jfree.*;
import org.jfree.chart.axis.CategoryAxis;
//import org.jfree.chart.axis.CategoryLabelPositions;
import org.jfree.chart.axis.NumberAxis;
//import org.jfree.chart.labels.CategoryItemLabelGenerator;
//import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PiePlot3D;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.chart.renderer.category.BarRenderer3D;
//import org.jfree.chart.renderer.category.CategoryItemRenderer;
//import org.jfree.chart.renderer.category.LineAndShapeRenderer;
import org.jfree.ui.GradientPaintTransformType;
import org.jfree.ui.StandardGradientPaintTransformer;
import org.jfree.chart.plot.IntervalMarker;
import org.jfree.ui.Layer;
import org.jfree.ui.RectangleAnchor;
import org.jfree.ui.RefineryUtilities;
import org.jfree.ui.TextAnchor;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import org.jfree.chart.axis.NumberTickUnit;

BarRenderer barRenderer = (BarRenderer)plot.getRenderer();
barRenderer.setDrawBarOutline(false);

CategoryAxis domainAxis = plot.getDomainAxis();
domainAxis.setTickLabelFont(new Font(”Arial”,Font.BOLD,12));

NumberAxis rangeAxis = (NumberAxis) plot.getRangeAxis();
rangeAxis.setTickLabelFont(new Font(”Arial”,Font.BOLD,12));
rangeAxis.setTickUnit(new NumberTickUnit(.1, new DecimalFormat(”##0%”)));

 

IntervalMarker target = new IntervalMarker(7000,7500);
target.setLabelFont(new Font(”SansSerif”, Font.ITALIC, 11));
target.setLabelAnchor(RectangleAnchor.LEFT);
target.setLabelTextAnchor(TextAnchor.CENTER_LEFT);
plot.addRangeMarker(target, Layer.BACKGROUND);

Similarly you can add different formats, such as Dollar $ signs etc

//import the necessary classes
import org.jfree.*;
import org.jfree.chart.axis.CategoryAxis;
//import org.jfree.chart.axis.CategoryLabelPositions;
import org.jfree.chart.axis.NumberAxis;
//import org.jfree.chart.labels.CategoryItemLabelGenerator;
//import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PiePlot3D;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.chart.renderer.category.BarRenderer3D;
//import org.jfree.chart.renderer.category.CategoryItemRenderer;
//import org.jfree.chart.renderer.category.LineAndShapeRenderer;
import org.jfree.ui.GradientPaintTransformType;
import org.jfree.ui.StandardGradientPaintTransformer;
import org.jfree.chart.plot.IntervalMarker;
import org.jfree.ui.Layer;
import org.jfree.ui.RectangleAnchor;
import org.jfree.ui.RefineryUtilities;
import org.jfree.ui.TextAnchor;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import org.jfree.chart.axis.NumberTickUnit;

BarRenderer barRenderer = (BarRenderer)plot.getRenderer();
barRenderer.setDrawBarOutline(false);

CategoryAxis domainAxis = plot.getDomainAxis();
domainAxis.setTickLabelFont(new Font(”Arial”,Font.BOLD,12));

NumberAxis rangeAxis = (NumberAxis) plot.getRangeAxis();
rangeAxis.setTickLabelFont(new Font(”Arial”,Font.BOLD,12));
rangeAxis.setTickUnit(new NumberTickUnit(1000, new DecimalFormat(”$##,##,##0″)));

 

IntervalMarker target = new IntervalMarker(7000,7500);
target.setLabelFont(new Font(”SansSerif”, Font.ITALIC, 11));
target.setLabelAnchor(RectangleAnchor.LEFT);
target.setLabelTextAnchor(TextAnchor.CENTER_LEFT);
plot.addRangeMarker(target, Layer.BACKGROUND);

ShareThis

In this example we placed this image as background for the thermometer chart http://www.foreststreams.com/snowcreekwater3web.JPG

import org.jfree.chart.plot.ThermometerPlot;
import org.jfree.chart.JFreeChart;
import java.awt.Toolkit;
import java.awt.Color;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.awt.event.KeyEvent;
import java.io.File;
import java.io.IOException;
import java.net.URL;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;

 

ThermometerPlot plot = (ThermometerPlot)chart.getPlot();
// plot.setInsets(new RectangleInsets(5.0, 5.0, 5.0, 5.0));
plot.setThermometerStroke(new BasicStroke(2.0f));
plot.setThermometerPaint(Color.lightGray);
plot.setUnits(ThermometerPlot.UNITS_NONE);
plot.setRange(50000.0,200000.0);
float h = displayFrame.getHeight();
float w = displayFrame.getWidth();
// GradientPaint gradientPaint = new GradientPaint(0.0F, 10.0F, Color.WHITE, h, w, Color.green.darker());
//plot.setBackgroundPaint(gradientPaint);

chart.setBackgroundPaint(new GradientPaint(0,0,Color.blue,w,h, new Color(102,0,102)));

plot.setMercuryPaint(new GradientPaint(0,0,Color.blue,w,h, new Color(102,0,102)));
plot.setValuePaint(Color.black);
plot.setThermometerPaint(Color.ORANGE);

plot.setSubrange(0, 0.0, 80000.0);
plot.setSubrange(1, 80000.1, 120000.0);
plot.setSubrange(2, 120000.1, 200000.0);

plot.setSubrangePaint(2, Color.BLUE);
plot.setSubrangePaint(1, Color.ORANGE);
plot.setSubrangePaint(0, Color.RED);

URL url = new URL(”http://www.foreststreams.com/snowcreekwater3web.JPG“);
BufferedImage image = ImageIO.read(url);
plot.setBackgroundImage(image);

Here is another background image

http://www.foreststreams.com/fallcreek1.JPG

Here is the world map behind the bar chart. We get this world map dynamically created from Google Chart APIs

To add background image to any chart, please include the following piece of code in Java Script

Add these import statements on the top

import java.net.URL;
import java.awt.image.BufferedImage;
import javax.imageio.ImageIO;

Add the following lines at the bottom

//URL url = new URL(”your own image url”);
URL url = new URL(”http://www.foreststreams.com/snowcreekwater3web.JPG“);
BufferedImage image = ImageIO.read(url);
chart.setBackgroundImage(image);

ShareThis

Current version is at 3.3.10 and includes the following enhancements

• PDF and HTML reporting of charts (powerful reporting engine - combine Text and graphs during your PDF export)
• Logos on dashboard (image objects or logos )
• SYS_PROP_ICLOGIN variable made available to identify the login person
• Duplicate or clone objects (right click any dashboard object and select duplicate)
• Plugin scripts during launch in desktop and plugin_script variable during viewer launch from server.

3.3.9 includes the following enhancements

• New Parameter types -
  • Button - Add buttons on the dashboard to call external programs or stored procedures (More.. )
  • Date - Add a date/calendar parameter
  • Field - Just an input text parameter
  • Static Text - Useful for sticky notes on the dashboard
• Bug fix for DateTime format
  • Custom date format for datetime is now fixed
• Drill from Bar chart or Pie Charts
  • This is experimental and includes drill through from Bar chart and Pie charts
• PDF reporting of Charts
  • Earlier the export from Tree browser included only the table objects but with this release charts are also included as part of the PDF export
• Excel button on Connection wizard to connect directly to excel files
  • Simplify the connection to Excel files. You can now browse the file directory and pick the Excel file to connect.

Version 3.3.8

• bubble chart
• scatter chart
• polar chart
• XY Series
• Time Series

ShareThis