Subscribe to RSSArchive for the ‘Dashboard Vendor Blogs’ Category:
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.
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)
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”
Polar chart in Dashboard
This is a simple chart to generate. It needs only X and Y values
select film_id x
,count(inventory_id) y1
from inventory
group by film_id
Scatter chart in Dashboard
This is similar to bubble chart
Here is the SQL to generate Scatter plot
select film_id x
,count(inventory_id) y1 ,count(store_id)/10 z1
, film_id y2,count(inventory_id)/5 z2
from inventory
group by film_id
Dashboard Bubble Chart
Bubble charts are very interesting as it provides a depth to the normal 2 dimensional values.
The depth is percieved by the size of the bubble. You can create a bubble chart by providing a third column that represents the radius of the bubble. This is known as the Z axis.
select film_id x
,count(inventory_id) y1 ,count(store_id)/10 z1
, film_id y2,count(inventory_id)/5 z2
from inventory
group by film_id
In the above SQL, the X-axis is common for all series of bubbles (the red bubbles represents 1 type of series and the blue bubbles represent second type of series).
The columns following X axis are the Y axis which indicates the position of bubble on X-Y and the third column represents the Z- Axis which is the radius of the bubble. The columns Y1, Z1 represents 1 series and columns Y2, Z2 represent second series
Dashboard Time Series Chart
Time Series is similar to XY Series chart. The only difference is the first X axis represents a Date or Time value. The Second column represents the Y value. Similarly you can add multiple series by adding more columns to the query
select date(payment_date),sum(amount) from payment
group by date(payment_date)
order by payment_date asc
XY Chart in Dashboard
To create this chart all that is needes is just the X and Y information.
The first column in your SQL should be the X value and the second column should be the Y value. If you are going to have multiple series then the X value is shared axis. The third column represents the second series.
select film_id,count(inventory_id),count(store_id)+3 from inventory
group by film_id
Dashboard Performance Fix
The autorefresh feature of InfoCaptor suffered some memory leak issues. This has been fixed now.
Please download the corresponding beta files for testing.
- copy this infocaptor_33.exe file and replace it in the infocaptor home directory.
- For the server option, copy and replace this file infocaptor_server.jar
