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

Add date parameters and how to use them in Excel Dashboards

A new Date Type parameter is introduced to simplify Dashboard creation and query.

When you define the Date Parameter you can specify the desired format

Select ‘DATE’ from the drop down list for DataType and specify your own Date Format.

When you create the date parameter with the above defaults it appears as below

When you click on the indicated button it expands to show the complete calendar

Date with Excel Dashboards

If you are querying an Excel Date Field then we need to use the following format

[ and date between #11/1/2008# and #11/17/2008# ]

The date format needs to be as “Month/Day/Year”

In order to meet the desired date condition we will need to change the date format in the Date Parameter

Click on “Full Apply”

and you should get the below new Date Format

Similarly create anothe date Field and call it To Date

Now we can use this date for any Excel Query

Below is our sample SQL with hard coded date values

Now we will replace it to use our date Parameters

Remove the text after between

select * from
[qb_data$]
where “Account Type” = ‘Expense’
and date between

When you select the submenu you will see the SQL query change as below

select * from
[qb_data$]
where “Account Type” = ‘Expense’
and date between ‘G_PARAM<p37:Display>’ /*value*/

Format the SQL by removing the /*value*/ strings and replace the single quotes with #

select * from
[qb_data$]
where “Account Type” = ‘Expense’
and date between #G_PARAM<p37:Display>#

Now repeat the process and add the To date field

select * from
[qb_data$]
where “Account Type” = ‘Expense’
and date between #G_PARAM<p37:Display># and #G_PARAM<p38:Display>#

Click on apply and your query will respond to any date selections followed by dashboard refresh.

ShareThis

Read More

Subscribe to the comments for this post

Webhost Change for InfoCaptor

There has been a lot of changes here at InfoCaptor website. Recently we have had lot of problems with our web host provider (lonex). We repeatedly got over CPU utilization notifications. We kept buying more and more of CPU limits but that didn’t seem to solve any issues. For no good increase in traffic our CPU limits were already going above 12%. For e.g When our limit was 4.5%, we got notifications saying our CPU utilization was 4.75%, so we increased it to 7%. Then we got notifcations for 7.75%.

It kept going that way, so we finally increased it to 12% and the same thing kept repeating. Then we realized the Lonex guys had sneakingly changed our htaccess so lot of our webpages were returning 404 error. It was high time and we had to scramble over the weekend to migrate our website to a new host at mediatemple.

We have successfully migrated all the critical components. There are few items that still needs to be completed. If you find any issues accessing any page or file, please let us know. (infocaptor”AT”gmail.com)

ShareThis

Read More

Subscribe to the comments for this post

JDBC with SQLserver - Connection refused: connect

If you are trying to use SQLserver with InfoCaptor and are using the SQL Studio Express 2005 then you might encounter connection refused error

Please try the following setup before attempting to connect

open the Configuration Manager
Start -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager

2. Then at the left hand tree. Select SQL Server 2005 Network Configuration-> Protocol for SQLEXPRESS-> TCP/IP

3. Right click and enable it.

4. A window box appear on double click the TCP/IP. Click on the “IP Addresses” Tab

5. Set the TCP Port value to 1433 then click apply

6. Then at the left hand tree. Select SQL Server 2005 Services -> SQL Server (SQLEXPRESS) -> right click and restart.

Now go back to InfoCaptor connection wizard and enter the connection details

Where “rudrasoft” is the PC name where SQL server is installed. So use the appropriate name for your installation

If you go to your SQL Server Studio, you will see the name before the backslash

The port is 1433 which we entered in the TCP/IP port settings

and the database is “Adventureworks”

ShareThis

Read More

Subscribe to the comments for this post