In my previous post I talked about how to use Google Visualization API with SharePoint to create a new dashboard/reporting platform. Then I talked about a system to pulls any data and makes it available to the Google Visualization API using the Wire Protocol in this post.
This system I call "DTS" for Data Transformation System. As I wrote in the previous post, this system is nothing more than an ASP.NET page that loads DLL's (based on the DSNAME parameter in the URL). Then the DLL is responsible for providing a data-table with the content the user requested. The ASP.NET page will then convert the data-table into JSON which the Google Visualization API can use.
Currently I only have 1 plugin (DLL) provided which allows you to connect to a Microsoft SQL Server.
The zip file provided below contains instructions on how to install this system on Windows 2008 using SQL Express 2008. You will also find ALL the source included. Remember this is only a proof of concept and there is probably a great deal of work that can be done to make this system better.
In my previous post I explained how to use the Google Visualization API with SharePoint to create a reporting dashboard/portal.
As a .NET Developer I tend to use Microsoft SQL Server a lot to store and manage my data. After reading through the Google Visualization documentation I realized there was no way to connect to a MS SQL Server out-of-the-box.
Then I discovered that I could implement my own data source using the "Wire Protocol v0.6". After reading the document it turns out that the Google Visualizations API can connect to a web server; pass commands, and then take the results and load them into the Visualization API.
For example, I can send the following request to a web server:
Then based on the input values (parametername/parametername2), the server can make a decision on what data to display and then output the data as the API requires.
Using ASP.NET I wrote a proof-of-concept program for testing purposes. This proof-of-concept program simply connected to a SQL server, loaded the data, converted it to JSON, and returned it to the requester (Visualization API). There we had it, the Google API rendered the charts with no problem.
Now I wanted to ensure that I can load data from any data source. For example, most of my data is stored in a Microsoft SQL Server, but I also have some data that I need from other systems such as PLC's, ERP Systems, SharePoint Lists, Text Files, Web Services, etc.
To accomplish this I had to figure out a way to specify which data source the user was expecting data from, then allow the user to pass parameters to this system so it can get the proper dataset. To do this in the URL request, I required the user to pass a parameter called DSNAME (data source name). This DSNAME would tell the system which DLL to load and execute to get the proper data back.
For this system to be able to understand the DLL's I created a very simple Interface. This interface contains 1 method called "Execute" that will accept a Dictionary Object as a parameter and returned 1 DataTable. The Dictionary object will contain all the parameters that were passed by the requestor's URL parameters (see above's http URL). Once the DLL executes, it would have to return a DataTable. From this point the system would then convert the DataTable to JSON then return that back to the requestor of the URL.
The DSNAME parameter tells the system that it wants to load the DLL associated with "MSSQL" (this is configured in a Database - will go into more details later). Then the QUERY parameter is sent which contains the actual query statement to be executed. From this point the system knows which DLL to execute and the DLL knows what data to read from where (based on the parameters we passed).
I know you might wonder where the connection string is to the DB. Since you don't want to pass sensitive data in the URL, I made this system be capable of loading default parameters (configured in a db) based on the DSNAME. For example "MSSQL" knows to automatically load a default Connection String.
Now to test all this follow the steps mentioned in my previous post; but add the following HTML code to the Content Form Web Part (sorry cant paste HTML code in here):
This sample HTML code basically loads Google's Annotated Time Line (my favorite), then connects to my data source (that we specified above), loads the MSSQL DLL, passes the Query parameter, then the system returns the JSON code which the Google API's can use to render the charts.
Here is a quick showing what I mean.
I intend to release the source code to all the code behind this as I am sure there are many great enhancements that can be made to it.
I've been looking at using Google Visualization API + SharePoint (WSS\MOSS) to create a presentation dashboard that can be an alternative to MS Reporting Services.
After digging around I found it relativity easy to embed Google Charts/etc into SharePoint using the "Form Web Part". Simply add one to the site, then modify it using the Source Editor and copy and paste the HTML code for the Google Visualization API.
You might be wondering how to generate the HTML code so that we can use the Google Visualization API. Google simplified this by creating the Google Code Playground (http://code.google.com/apis/ajax/playground/) Here you can see demos from all the charts they provide. Be sure to enable the HTML view, then copy and paste the HTML code into the Form Web Part and you'll see the Google Visualization API load.
Here is a step by step instructions on how I did it:
Hit Site Actions | Create
Then on the left side, you want to create a "Web Part Page"
Give the Web Part Page a name (I call it "Test 1"). Now select a Layout, for this example I use the "Full Page, Vertical" layout, but anything will work. Assign to a Document Library and hit Create.
Now we should automatically see the Test 1 page open in Edit Mode. Click the "Add a Web Part". Now a new window should pop up; select the "Form Web Part".
Once added, hit the "Edit" button on the web part we just added and hit "Modify Shared Web Part", then hit "Source Editor".
Now a new Source Editor Window should appear. Copy and Paste the HTML code that you generated at the Google Playground and paste it in. Then hit save.
Now you should be back on the new site you created and the Google chart that you used should have loaded.
Here is a YouTube video I posted explaining the steps above:
For those that dont know. Windows SharePoint Services (WSS) can be downloaded and used for Free. WSS runs a Micrsoft SQL Server for its data storage. WSS works fine on MS SQL 2008 Express (also free). This is what I am running for the demonstration video.
Next post I will explain a system I wrote that will expose data from any source (MS SQL, Txt File, SharePoint List, etc) and convert it into a format that the Google Visualization API can use.
In my previous post I talked about how to use Google Visualization API with SharePoint to create a new dashboard/reporting platform. Then I talked about a system to pulls any data and makes it available to the Google Visualization API using the Wire Protocol in this post.
This system I call "DTS" for Data Transformation System. As I wrote in the previous post, this system is nothing more than an ASP.NET page that loads DLL's (based on the DSNAME parameter in the URL). Then the DLL is responsible for providing a data-table with the content the user requested. The ASP.NET page will then convert the data-table into JSON which the Google Visualization API can use.
Currently I only have 1 plugin (DLL) provided which allows you to connect to a Microsoft SQL Server.
The zip file provided below contains instructions on how to install this system on Windows 2008 using SQL Express 2008. You will also find ALL the source included. Remember this is only a proof of concept and there is probably a great deal of work that can be done to make this system better.
In my previous post I explained how to use the Google Visualization API with SharePoint to create a reporting dashboard/portal.
As a .NET Developer I tend to use Microsoft SQL Server a lot to store and manage my data. After reading through the Google Visualization documentation I realized there was no way to connect to a MS SQL Server out-of-the-box.
Then I discovered that I could implement my own data source using the "Wire Protocol v0.6". After reading the document it turns out that the Google Visualizations API can connect to a web server; pass commands, and then take the results and load them into the Visualization API.
For example, I can send the following request to a web server:
Then based on the input values (parametername/parametername2), the server can make a decision on what data to display and then output the data as the API requires.
Using ASP.NET I wrote a proof-of-concept program for testing purposes. This proof-of-concept program simply connected to a SQL server, loaded the data, converted it to JSON, and returned it to the requester (Visualization API). There we had it, the Google API rendered the charts with no problem.
Now I wanted to ensure that I can load data from any data source. For example, most of my data is stored in a Microsoft SQL Server, but I also have some data that I need from other systems such as PLC's, ERP Systems, SharePoint Lists, Text Files, Web Services, etc.
To accomplish this I had to figure out a way to specify which data source the user was expecting data from, then allow the user to pass parameters to this system so it can get the proper dataset. To do this in the URL request, I required the user to pass a parameter called DSNAME (data source name). This DSNAME would tell the system which DLL to load and execute to get the proper data back.
For this system to be able to understand the DLL's I created a very simple Interface. This interface contains 1 method called "Execute" that will accept a Dictionary Object as a parameter and returned 1 DataTable. The Dictionary object will contain all the parameters that were passed by the requestor's URL parameters (see above's http URL). Once the DLL executes, it would have to return a DataTable. From this point the system would then convert the DataTable to JSON then return that back to the requestor of the URL.
The DSNAME parameter tells the system that it wants to load the DLL associated with "MSSQL" (this is configured in a Database - will go into more details later). Then the QUERY parameter is sent which contains the actual query statement to be executed. From this point the system knows which DLL to execute and the DLL knows what data to read from where (based on the parameters we passed).
I know you might wonder where the connection string is to the DB. Since you don't want to pass sensitive data in the URL, I made this system be capable of loading default parameters (configured in a db) based on the DSNAME. For example "MSSQL" knows to automatically load a default Connection String.
Now to test all this follow the steps mentioned in my previous post; but add the following HTML code to the Content Form Web Part (sorry cant paste HTML code in here):
This sample HTML code basically loads Google's Annotated Time Line (my favorite), then connects to my data source (that we specified above), loads the MSSQL DLL, passes the Query parameter, then the system returns the JSON code which the Google API's can use to render the charts.
Here is a quick showing what I mean.
I intend to release the source code to all the code behind this as I am sure there are many great enhancements that can be made to it.
I've been looking at using Google Visualization API + SharePoint (WSS\MOSS) to create a presentation dashboard that can be an alternative to MS Reporting Services.
After digging around I found it relativity easy to embed Google Charts/etc into SharePoint using the "Form Web Part". Simply add one to the site, then modify it using the Source Editor and copy and paste the HTML code for the Google Visualization API.
You might be wondering how to generate the HTML code so that we can use the Google Visualization API. Google simplified this by creating the Google Code Playground (http://code.google.com/apis/ajax/playground/) Here you can see demos from all the charts they provide. Be sure to enable the HTML view, then copy and paste the HTML code into the Form Web Part and you'll see the Google Visualization API load.
Here is a step by step instructions on how I did it:
Hit Site Actions | Create
Then on the left side, you want to create a "Web Part Page"
Give the Web Part Page a name (I call it "Test 1"). Now select a Layout, for this example I use the "Full Page, Vertical" layout, but anything will work. Assign to a Document Library and hit Create.
Now we should automatically see the Test 1 page open in Edit Mode. Click the "Add a Web Part". Now a new window should pop up; select the "Form Web Part".
Once added, hit the "Edit" button on the web part we just added and hit "Modify Shared Web Part", then hit "Source Editor".
Now a new Source Editor Window should appear. Copy and Paste the HTML code that you generated at the Google Playground and paste it in. Then hit save.
Now you should be back on the new site you created and the Google chart that you used should have loaded.
Here is a YouTube video I posted explaining the steps above:
For those that dont know. Windows SharePoint Services (WSS) can be downloaded and used for Free. WSS runs a Micrsoft SQL Server for its data storage. WSS works fine on MS SQL 2008 Express (also free). This is what I am running for the demonstration video.
Next post I will explain a system I wrote that will expose data from any source (MS SQL, Txt File, SharePoint List, etc) and convert it into a format that the Google Visualization API can use.
