Add Buttons on Dashboard

Buttons on Dashboards can be used to take certain actions. InfoCaptor dashboard buttons can be set to take a database action or a non-database action.

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

Read More

Subscribe to the comments for this post

Auto-refresh and Rotate Display of Dashboards

Automatic refresh of dashboard view is useful in following scenarios

• 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

Read More

Subscribe to the comments for this post

Sticky Notes, Data Entry Fields and Logos or Images on Dashboard

Sticky Notes: Sometimes it is useful to pass some information in the means of plain text on the dashboard. Sticky notes can be created by selecting ‘STATIC’ type in the Datatype field of the parameters

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

Read More

Subscribe to the comments for this post

Dashboard Updates

Get the latest Test version at http://www.infocaptor.com/test/infocaptor_setup.exe

Current test version is at 3.3.9 and includes the following enhancements

• New Parameter types -
  • Button - Add buttons on the dashboard to call external programs or stored procedures
  • 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

Read More

Subscribe to the comments for this post

Add Custom background to Charts | Spicing up your Dashboards | jfreecharts

How to add custom background images to your dashboard charts.

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

Read More

Subscribe to the comments for this post

Interval Marker | Set Goal or Target line in Bar Chart

Interval Marker is a special feature that allows you set targets or reference point for your bar or line charts.

Here is a basic bar chart

To add a reference line or interval marker as below

Use the following code for the javascript

//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;

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

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

ShareThis

Read More

Subscribe to the comments for this post

Add threshold line in a line chart | Line Shapes | Line Customizations | jfreechart

How to add a constant horizontal line or a control line.

Following is a simple line chart

The query for the above chart is as follows

select calendar_month_name, sum(quantity_sold)-3500 as qty_sold
from [detail_data$]
where fiscal_year = 2001
and country_region like ‘Americas’
group by calendar_month_name

Now if we need to add a Target line which would be a constant horizontal line then we can use the following technique

We will simply change the above query and add a new column with the target value representing the target sales

select calendar_month_name, sum(quantity_sold)-3500 as qty_sold, 11500 as Target
from [detail_data$]
where fiscal_year = 2001
and country_region like ‘Americas’
group by calendar_month_name

We get the below result

To customize the lines, for example to make them thicker follow this tutorial or simply add the following code in the Dynamic java script section

//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.renderer.category.LineAndShapeRenderer;

// CategoryPlot plot= chart.getCategoryPlot();< /FONT>

LineAndShapeRenderer lrenderer = (LineAndShapeRenderer) plot.getRenderer();< /FONT >

lrenderer.setStroke(

new BasicStroke(4f, BasicStroke.JOIN_ROUND, BasicStroke.JOIN_BEVEL)

);

In order to change the line style please use the below script

//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.renderer.category.LineAndShapeRenderer;

// CategoryPlot plot= chart.getCategoryPlot();< /FONT>

LineAndShapeRenderer renderer = (LineAndShapeRenderer) plot.getRenderer();< /FONT >

/*renderer.setStroke(

new BasicStroke(4f, BasicStroke.JOIN_ROUND, BasicStroke.JOIN_BEVEL)

);
*/

renderer.setSeriesStroke(
0, new BasicStroke(
2.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,
1.0f, new float[] {10.0f, 6.0f}, 0.0f
)
);
renderer.setSeriesStroke(
1, new BasicStroke(
2.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,
1.0f, new float[] {6.0f, 6.0f}, 0.0f
)
);
renderer.setSeriesStroke(
2, new BasicStroke(
2.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,
1.0f, new float[] {2.0f, 6.0f}, 0.0f
));

Usually the target will not be a straight line, for example if you have forecasted the sales for each month, then the seasonal activity will differ from month to month and hence the target values. You could join to a budget or forecast table and get the target values for each month or period and display them along with the sales quantity. This gives more flexibility rather than hard coding the target value.

Custom Shapes on the Line Chart

To create the shapes on the line chart use the following javascript

//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 java.awt.BasicStroke;
import java.awt.Color;
import java.awt.Polygon;
import java.awt.Shape;
import java.awt.geom.Rectangle2D;
import org.jfree.chart.renderer.category.LineAndShapeRenderer;
import org.jfree.chart.plot.DefaultDrawingSupplier;
import org.jfree.chart.plot.DrawingSupplier;

// CategoryPlot plot= chart.getCategoryPlot();

LineAndShapeRenderer renderer = (LineAndShapeRenderer) plot.getRenderer();

//Enable shapes on the line chart
renderer.setShapesVisible(true);
/* renderer.setDrawOutlines(true);
renderer.setUseFillPaint(true);
renderer.setFillPaint(Color.white);

*/
/*
renderer.setStroke(

new BasicStroke(4f, BasicStroke.JOIN_ROUND, BasicStroke.JOIN_BEVEL)

);
*/

//Create Dotted or Dashed lines
renderer.setSeriesStroke(
0, new BasicStroke(
2.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,
1.0f, new float[] {10.0f, 6.0f}, 0.0f
)
);
renderer.setSeriesStroke(
1, new BasicStroke(
2.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,
1.0f, new float[] {6.0f, 6.0f}, 0.0f
)
);
renderer.setSeriesStroke(
2, new BasicStroke(
2.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,
1.0f, new float[] {2.0f, 6.0f}, 0.0f
)

Add custom colors

//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 java.awt.BasicStroke;
import java.awt.Color;
import java.awt.Polygon;
import java.awt.Shape;
import java.awt.geom.Rectangle2D;
import org.jfree.chart.renderer.category.LineAndShapeRenderer;
import org.jfree.chart.plot.DefaultDrawingSupplier;
import org.jfree.chart.plot.DrawingSupplier;

// CategoryPlot plot= chart.getCategoryPlot();

LineAndShapeRenderer renderer = (LineAndShapeRenderer) plot.getRenderer();

//Enable shapes on the line chart
renderer.setShapesVisible(true);
renderer.setDrawOutlines(true);
renderer.setUseFillPaint(true);
renderer.setFillPaint(Color.yellow);

/*
renderer.setStroke(

new BasicStroke(4f, BasicStroke.JOIN_ROUND, BasicStroke.JOIN_BEVEL)

);
*/

//Create Dotted or Dashed lines
renderer.setSeriesStroke(
0, new BasicStroke(
2.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,
1.0f, new float[] {10.0f, 6.0f}, 0.0f
)
);
renderer.setSeriesStroke(
1, new BasicStroke(
2.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,
1.0f, new float[] {6.0f, 6.0f}, 0.0f
)
);
renderer.setSeriesStroke(
2, new BasicStroke(
2.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,
1.0f, new float[] {2.0f, 6.0f}, 0.0f
)

ShareThis

Read More

Subscribe to the comments for this post

Excel Cell Ranges using SQL - Named - Unnamed - ODBC - JDBC

How to query Excel files using ODBC - JDBC?

Summary :

• Why use square brackets for SQL against Excel?
• How to access range of cells from Excel?
• How to use un-named range of cells?

This topic is covered in various tutorial for InfoCaptor. These following snippets are taken directly from Microsoft website as easy reference

Select Excel Data with Code
Your Excel data may be contained in your workbook in one of the following:

* An entire worksheet.
* A named range of cells on a worksheet.
* An unnamed range of cells on a worksheet.

Specify a Worksheet

To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example:

strQuery = “SELECT * FROM [Sheet1$]“

You can also delimit the worksheet name with the slanted single quote character (`) found on the keyboard under the tilde (~). For example:

strQuery = “SELECT * FROM `Sheet1$`“

Microsoft prefers the square brackets, which are the standing convention for problematic database object names.

If you omit both the dollar sign and the brackets, or just the dollar sign, you receive the following error message:

… the Jet database engine could not find the specified object

If you use the dollar sign but omit the brackets, you will see the following error message:

Syntax error in FROM clause.
If you try to use ordinary single quotes, you receive the following error message:
Syntax error in query. Incomplete query clause.

Specify a Named Range

To specify a named range of cells as your recordsource, simply use the defined name. For example:

strQuery = “SELECT * FROM MyRange“

Specify an Unnamed Range

To specify an unnamed range of cells as your recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:

strQuery = “SELECT * FROM [Sheet1$A1:B10]“

A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheeet title above and to the left of the data in cell A1.

A caution about specifying ranges: When you specify a worksheet as your recordsource, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range.

ShareThis

Read More

Subscribe to the comments for this post

Dashboard Screenshots

ShareThis

Read More

Subscribe to the comments for this post

Number and Percent format for Charts (bar, line etc)

This tip demonstrates how to create custom number format and percent format for the number axis (also known as the range axis in jfreechart)

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

Read More

Subscribe to the comments for this post