Excel Dashboards - Connect Excel file over the web

February 26, 2009 – 3:00 pm

Scenario: You have created a dashboard using Excel File as the data source. Now you need to put the dashboard on the webserver using this web dashboard setup

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

Image

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

Image

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

Tags: , ,

Posted in Excel, How-to | No Comments »

Stack Bar with multiple Columns on Dashboard

January 22, 2009 – 8:15 am

In this article we demonstrated how to create stack bar using three columns. The second column is the category that splits the stack.

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.

Image

You connect to this Excel file through InfoCaptor

Image

Create a Dashboard and add the stacked bar chart.

Image

 

Click Apply and you get the below stack bar

Image

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

Posted in Excel, How-to | No Comments »

Dashboard Parameter trick : Count from two seperate Databases

January 13, 2009 – 12:11 pm

In this article we will discuss a scenario and how to accomplish it within the dashboard.

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

Image

 

Create second parameter with row count from second database table

Image

 

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.

Image

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

Image

 

Remove the single quotes and the comment section as shown below

Image

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

Image

 

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

Image

 

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

Image

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

Posted in Excel, General, How-to | No Comments »

Clear Java Cache : JNLP Exceptions

January 6, 2009 – 12:25 pm

If you hit any jnlp Exceptions during the launch of InfoCaptor it is recommended to try cleaning the Java Cache

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

Posted in How-to | 1 Comment »

Add Buttons on Dashboard

December 21, 2008 – 8:51 pm

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.

Image

 

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

Image

 

Select DataType =’BLET’

Select ‘Run Dynamic Java Script’ = ‘Y’

and then click on the Dynamic Java Code

Image

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);]

Posted in How-to | No Comments »

Auto-refresh and Rotate Display of Dashboards

December 21, 2008 – 8:21 pm

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

Image

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

Image

 

 

 

 

 

 

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

Image

Tags: , ,

Posted in How-to | No Comments »

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

December 21, 2008 – 7:17 pm

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.

Image

 

Data Entry Fields

To create Data Entry Fields select DataType = ‘FIELD’

Image

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

Image

Image

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”

Tags: ,

Posted in How-to | 3 Comments »

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

December 1, 2008 – 9:39 am

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

Image

Image

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

Image

Image

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

Posted in Charts, How-to | No Comments »

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

November 25, 2008 – 4:25 pm

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

Image

Image

 

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

Image

Image

 

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

 

Image

 

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

Tags: , , ,

Posted in Charts, How-to | 1 Comment »

Dashboard Updates

November 25, 2008 – 3:26 pm

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

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 -
  • 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

Posted in Announcement | 1 Comment »

Previous Entries