You are here

Klotin SqlChart

About The Module

SqlChart is a module for DotNetNuke content management system that allows displaying results of custom Sql query as a chart, using Google Charts API.

Idea is simple - people with the Module Edit rights can define sql script, which produces output in a format acceptable by one of the Google Charts. Then the module reformats the data into Json representation, outputs it to the page and feeds to Google Chart API, which produces a nice looking chart.

There are no limits to the Sql query - everything that can be accessed with the DNN connection string can be output as a chart, so use it with care. Also, Sql query can take several predefined parameters as well as query string, form, session, cache, cookies and server variables.

The module supports several types of Charts.

Annotated Timeline

Dynamic diagram that allows to have events (annotations) on the chart, similar to Google Finance. It is rendered as Flash object, so is not shown on iOS (iPad, iPhone) that does not support Flash.

Klotin SqlChart

Bar Chart

colored horizontal bar series

Klotin SqlChart

Column Chart

Colored vertical column series

Klotin SqlChart

Line Chart

Klotin SqlChart

Area Chart

Klotin SqlChart

How To Use

1. Drop the module onto the page and make sure only authorized people have access to Edit the module. The module appears with the default text once added.

2. Use menu to Edit SqlChart Settings

3. Enter the Sql Script

For Sql script it is very important to have proper format of the output. It must match the one of the chart type.

For Annotated Timeline, the format of the output data should be the following:

Time | Line1Value | Line1EventTitle | Line1EventText | Line2Value | Line2EventTitle | Line2EventText | and so on ...

If there is no Title / Text - please select empty string to match the format. For all other chart types the format is simpler:

x-axis | y-axis-1 | y-axis-2 | and so on ...

However the data for the x-axis should be a string.

Sql script also accepts parameters - several predefined ones - $(usedId), $(tabId), $(tabModuleId) and $(moduleId), and URL query string / page form parameters, cache, session and server variables and cookies values in the same format of $(name). String parameters are enclosed into single quotes and have single quote escaped to fight the sql injections.

4. Click Run Sql & Get Column Info

At this stage Sql is validated and the column information retrieved.

Define caption for the columns and the format of the data. .Net string.Format() method is used to apply the format.

5. After that you need to specify the chart type, that would match the data, cache duration and style for the chart as on the example:

6. Once done, click save and navigate back to the page. You should see your chart now.

Notes

  • Chart uses Google so internet access from browser required in order to see the chart. This is true for most of the sites.
  • Chart data is rendered on the page as json, so it is easy to extract it.
  • You can use this module even if you don't have data in the DB but you still want render it as a chart. Just select the constant values using Sql, unioning the rows if necessary.

Sql Examples

Form and List

To use SqlChart with the Form and List module, you can use a query below. However, it requires some adjustments:

1. You need to set the proper module Id of the Form and List module. The module Id is visible in the Url of the Form and List settings page. Set it for the @moduleId variable.

2. Query pulls out all the columns from the List in the order of their creation and of string type, however SqlChart needs columns in the specific order and of specific type, so you need to adjust @outerSql variable to select proper columns and change type as needed. In my example I am selecting 2 columns - 5th and 6th (numbering starts with 0). Chart of ColumnChart type requires 1st column be as string and 2nd column - as integer, so I am changing the type for the 2nd column.

3. Replace the T_ prefix of Sql tables with the one matching your DNN installation


set nocount on;

declare @moduleId int;
declare @outerSql nvarchar(max)
 
set @moduleId = 446;
set @outerSql = 'select col4_Value as year, cast(col5_Value as int) as value'
 
declare fieldsCur scroll cursor for select UserDefinedFieldId, FieldTitle from T_UserDefinedFields where ModuleId=@moduleId
 
declare @fieldId int;
declare @fieldTitle nvarchar(max);
declare @sql nvarchar(max);
declare @select nvarchar(max);
declare @from nvarchar(max);
declare @rowId int;
declare @colName varchar(100);
 
set @rowId = 0;
 
open fieldsCur;
fetch first from fieldsCur into @fieldId, @fieldTitle;
 
while @@FETCH_STATUS=0
begin
  select @colName = 'col' + cast(@rowId as varchar(100));
 
  if @rowId = 0
  begin
    select @select = 'select cast(' + @colName + '.FieldValue as nvarchar(max)) as ' + @colName + '_Value';
    select @from = ' from (select * from T_UserDefinedData where UserDefinedFieldId = ' + cast(@fieldId as varchar(100)) + ') ' + @colName;
  end
  else
  begin
    select @select = @select + ', cast(' + @colName + '.FieldValue as nvarchar(max)) as ' + @colName + '_Value';
  select @from = @from + ' inner join T_UserDefinedData ' + @colName + ' on col0.UserDefinedRowId = ' + @colName + '.UserDefinedRowId and ' + @colName + '.UserDefinedFieldId = ' + cast(@fieldId as varchar(100));
  end
 
  select @rowId = @rowId + 1;
  fetch next from fieldsCur into @fieldId, @fieldTitle;
end
close fieldsCur;
deallocate fieldsCur;
select @sql = @outerSql + ' from (' + @select + @from + ') a';
exec(@sql);

Version History

Version Date DNN Notes
01.00.05 5/6/2012 5.x,6.x
  • Enhanced support of null values in chart
  • Increased sql script length to more than 2000 chars
  • TabId, moduleId, tabModuleId and userId are inserted as integers into Sql scripts, without quotes around them
  • Added an option to exclude columns from the chart
  • Removed unneeded css links
01.00.04 4/5/2012 5.x,6.x
  • Added support for Cache, Session, Server variables, and Cookies
01.00.03 4/2/2012 5.x,6.x
  • Initial public release

Licensing

The module is licensed per production DNN instance. Clustered DNN configuration is considered a single instance. Publicly accessible DNN is considered a production.

Download

You can purchase and download the module from the DotNetNuke store.

Related

The module is a part of the Klotin Sql DNN Productivity Kit. The other modules in the set are:

  • Klotin Sql Label - Allows inserting database driven tags into the text / html.
  • Klotin Sql Table - Allows presenting results of custom sql queries as a table.

If you have any comments or suggestions, you can either email me or use a form below.

Social Buttons: 

Comments

 #

Any idea how to implement SqlChart on FL data?

 
 #

I have not used the module, but if it stores data in proper Sql - it should be possible. Let me try and I will post a sample query.

 
 #

Thanks. The issue with FnL is that the data is not set is regular table. I hope You'll get an idea for this, since FnL is widely used for numerous tasks in DNN

 
 #

Alright, I was able to come up with Sql, I will add it to the page as it is too big for a comment field... Might also be not the most optimal, but you can try.

 
 #

This is very cool. I'll use it on one of my clients rain measuring tables and will post back the results.
Yehuda

 
 #

I used your script and it works.
Thank you very much,
Yehuda

 
 #

Hi,
I need to help one of my clients with viewing information as a graph or digram.
What I need to know is if your module kan be initiated vid values dynamically from a web page on DNN.
I will configure the module but the client will need to give some values each time he wants to show the information.
Is this possible?

If this is possible I need to see an example that I can test this function. Do you have any test page for this?

Or can i get access to a tiral version of the module to make the test by my self?

Bets Regards
Shervin Rahmani
_________________________________
3wPortal AB
Fashion House
Göteborgsvägen 89
431 30 Mölndal
Mobil: +46 (0)705 824445
Email: shervin@3wportal.se
http://www.3wportal.se
_________________________________

 
 #

You can use form elements in the sql for the chart, so if you have an input with id="txtValue" name="txtValue", you can refer to it in Sql as $(txtValue). Same goes for drop downsand etc.

However the module is so cheap (just $1) that there is no trial version. It is almost free so can be considered trial on its own.

 

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

Get Updates

RSS Twitter

Subscribe by Email:

Recent Comments

Who's online

There are currently 0 users online.