How to integrate FlexMonster Pivot Table & Charts Component with PHP/MySQL

FlexMonster Pivot Table Component can be easily interated with your PHP site

We will guide you through simple PHP integration sample.

To integrate Pivot Table into your page you should accomplish the following steps:

1. Copy content of Pivot folder (Pivot component and supported files) to root of your site or sandbox folder.
2. Insert Pivot component into your .php file – paste code below:
   
   

   <script type="text/javascript" src="swfobject.js"></script>
   
   <div id="flashContent">No Flash player or Javascript disabeled message</div>
   
   <script type="text/javascript">
   
     var so = new SWFObject("PivotTable.swf", "flash", "1000", "720", "9", "#FFFFFF");
   
   
       so.addParam("allowScriptAccess", "sameDomain");
   
       so.addParam("menu", "false");
   
       so.addParam("allowFullScreen", "true");
   
       so.addVariable("filename", "your_data_script.php");
   
       so.addVariable("styleSheetName", "original.css");
   
       so.addVariable("configuratorEnabled", "true");
   
       so.addVariable("chartsEnabled", "true");
   
       so.write("flashContent");
   
       flash.focus();
   
   </script>

3. Set path to your data file. To do this change value of filename FlashVar.
   

   so.addVariable("filename", "your_data_script.php");

4. Now we are ready to write data source script.

Wrinting data source script

The best data format for Pivot Table Component is CSV. It most compact and can be easily convert to desired data structure. So now we will write simple CSV generator.

Your script should retreive data from database and output CSV file.

• Firstly we have to get list of columns:
  
  
  • connect to database
  • retreive set of columns
  • split resulted dataset
  • write names of columns to csv

  function getColumns() {
    mysql_connect(localhost,$username,$password);
  
    mysql_select_db($database) or die( "Unable to select database");
  
    $csv_output = "";
  
    $sql = "SHOW COLUMNS FROM ".$table;
  
    $result = mysql_query($sql);
  
    while ($row = mysql_fetch_assoc($result)) {
  
      $csv_output .= $row["Field"].",";
  
    }
  
    $csv_output = substr($csv_output, 0, -1);
  
    $csv_output .= "\r\n";
  
  }
  
  

• Retreve data from database:
  
  
  • select data from database
  • parse result of query
  • convert dataset to csv rows
  • write to csv

  function getDataRows() {
    $query="SELECT * FROM ".$table;
  
    $result = mysql_query($query);
  
    while($row = mysql_fetch_row($result)){
  
      for ($i = 0; $i < count($row); $i++) {
  
        $csv_output .= $row[$i].",";
  
      }
  
      $csv_output = substr($csv_output, 0, -1);
  
      $csv_output .= "\r\n";
  
    }
  
  }
  
  

• Send csv response
  

  header('Content-type: text/plain');
  
  print $csv_output;
  

You can download full source code of this sample.

How to talk to MS Analysis Services from Flash to reach OLAP Cube? It was the first question.

Firstly, you need to know that Microsoft Analysis Services uses SOAP-based XML for Analysis protocol to exchange of data between clients and server. (You may also read more about XMLA on Wikipedia)

So in Flash application

1. generate XMLA request,
2. forward it to SOAP wrapper and
3. send SOAP request to the HTTP pump (http://your_host_name/msmdpump.dll),
4. HTTP pump forwards the request to the server.

Microsoft Analysis Services

1. sends the SOAP response back to the HTTP pump,
2. SOAP parser retrieve data from HTTP pump and
3. prepare data for our application.

There two types of XMLA request. The Discover method retrieve information about data structure on a server and the Execute method sends requests for retrieving or updating data. We discover data structure and choose desired dimensions for view, retrieve data from server and show in grid.

You can try demo in action - it is connected to the sample OLAP cube in MS Analysis Services. You can configure the dimensions structure and visualize the result.

Today we have prepared a new demo of Pivot Table. We want to show you new feature of Pivot visualization. You can organize your own structures of Pivot dimensions and see data from different points of view by selecting.

There are five areas in options view: Filters, Columns, Rows, Values and All Fields. To change data structure drag field from one area and drop to another. When desired dimensions structure is ready - click "Apply Changes" button and see your new visualization of the same data. In this demo you can change only columns and rows. Filter area is still unused and Values show only sum of one dimension - work hours.

Video Demo (40 sec)

Try PIVOT TABLE in Action