Drupal 7 and SQL Server Reporting Services

Drupal is a popular content management system that until relatively recently has been associated more or less exclusively with the LAMP (Linux, Apache, MySQL, and PHP) Web development stack. However, Acquia Drupal is now available for Windows and can be installed with the Microsoft Web Platform Installer, and at DrupalCon in San Francisco, it was announced that a new SQL Server PHP driver for PDO and a SQL Server module for Drupal 7 would be released, enabling Drupal site data to be hosted in SQL Server. You can download a pre-release version of a Drupal and SQL Server package from here.

Drupal and Reporting Services Demo

Drupal is highly extensible through its PHP-based modular architecture, and SQL Server includes a wealth of features that could potentially enhance the value of a community-based content site, so given that you can now install Drupal with SQL Server, it seemed logical to do some investigation into how you can integrate some of the capabilities of SQL Server into a Drupal site.

Perhaps the most obvious starting point is to integrate Reporting Services into a Drupal site to create a sort of “Community Dashboard” that includes reports showing content and author related activity within the site. SQL Server Express Edition with Advanced Services includes Reporting Services for free, and the SQL Server Reporting Services SDK for PHP provides a framework for accessing Reporting Services from PHP-based applications. The video shows the Drupal solution we created using these technologies (you can also view this video on YouTube).

The demo actually shows “bi-directional” integration between Drupal and SQL Server Reporting Services in the sense that not only does the site include a custom PHP module to “pull” the reports from Reporting Services into the Drupal site user interface, the reports themselves query data in the underlying Drupal content database, which in this instance is hosted in SQL Server 2008 R2 Express Edition.

The diagram below shows the high-level architecture of the integration solution we created. Note that the Reporting Services instance contains two folders – one containing reports that show activity for all users, and another containing reports that are filtered based on the current Drupal user’s ID. The custom module in the Drupal site ascertains whether the current user is an administrator, and if not retrieves the current user’s ID via the Drupal PHP API and passes it as a hidden parameter when retrieving the reports.

The reports query the tables in the Drupal content database. For example, the following query is used to retrieve the data for the Content Activity report:

SELECT n.Title Page,
       cast(dateadd(ss,n.created,'1/1/1970') as Date) CreatedDate,
       u.name Author,
       c.[Subject] Comment,
       uc.name [Commenter],
       cast(dateadd(ss,c.created,'1/1/1970') as Date) CommentDate,
       cn.totalcount PageViews
FROM dbo.Node n
JOIN dbo.Users u ON n.[uid] = u.[uid]
LEFT JOIN dbo.Comment c ON n.nid = c.nid
LEFT JOIN dbo.Users uc ON c.[uid] = uc.[uid]
LEFT JOIN node_counter cn ON n.nid = cn.nid
WHERE MONTH(cast(dateadd(ss,n.created,'1/1/1970') as Date)) = @Month
AND YEAR(cast(dateadd(ss,n.created,'1/1/1970') as Date)) = CONVERT(char(4),@Year)

Note that the date values in the Drupal database are stored in the standard UNIX format (the number of seconds since 1/1/1970), and so need to be cast to an appropriate SQL Server data type (we’ve used the Date datatype).

Our custom module code uses the SQL Server Reporting Services SDK for PHP to connect to and retrieve information from Reporting Services. For example, the following code is used to retrieve a list of available reports in the appropriate folder in Reporting Services (depending on whether or not the user is an administrator):

/*
  connect to SQL Server Reporting Services and retrieve a list of available reports
  */
function getReports(){
module_load_include('php', 'ssreport', 'bin/SSRSReport');
    
    $reports = array();
try
{
    $ssrs_report =  new SSRSReport(new Credentials(variable_get('ssreport_user'), variable_get('ssreport_password')), variable_get('ssreport_url'));
    $childPath = "";
 
// if user is admin then load from community dashboard
if (user_access('Administer site configuration')){
   $childPath = variable_get('ssreport_primary');
}
else{
// load from personal dashboard
   $childPath =  variable_get('ssreport_secondary');
}

$catalogItems = $ssrs_report->ListChildren($childPath, true);

foreach ($catalogItems as $catalogItem) {

if ($catalogItem->Type == ItemTypeEnum::$Report) {
$reports[$catalogItem->Name] = $catalogItem->Name;

}
}

}catch(SSRSReportException $serviceException)
{
    drupal_set_message($serviceException);
}
return $reports;

}

Note that the code uses a number of variable_get calls to retrieve global variables that are set in the Drupal administrative (such as the credentials to access the Reporting Services instance, the URL of the Reporting Services Web service, and the path to the CommunityDashboard and PersonalDashboard folders). Finally, the code uses the SSRSReport.ListChildren method provided by the SQL Server Reporting Services SDK for PHP API to retrieve the list of reports.

When a user selects a report, the following PHP function is used to retrieve the list of parameters that the report expects:

$rep=NULL;
if (user_access('Administer site configuration')){

$rep = t(variable_get('ssreport_primary').'/@report',array('@report' => $report));
 
}
else{
$rep = t(variable_get('ssreport_secondary').'/@report',array('@report' => $report));

}

// connect to SQL Server Reporting Services using the credentials and URL supplied in the administrative dashboard
$rs = new SSRSReport(new Credentials(variable_get('ssreport_user'), variable_get('ssreport_password')),variable_get('ssreport_url'));
$params = array();
  try
{
// get the parameters for the specified report
$reportParameters = $rs ->GetReportParameters($rep, null, true, null,     null);
    
$params = array();
        
foreach($reportParameters as $reportParameter)
            
{
    array_push($params,$reportParameter->Name);            
   
}

Finally, the following code is used to render the selected report in the Drupal user interface:

$rs = new SSRSReport(new Credentials(variable_get('ssreport_user'), variable_get('ssreport_password')),variable_get('ssreport_url'));
//get the report params array
  $path = drupal_get_path('module','report_services');

$reportParameters = $rs ->GetReportParameters($rep, null, true, null,     null);
    
$params = array();
        
foreach($reportParameters as $reportParameter)
{
  //code to set the parameter values

}

       $rs->SetExecutionParameters2($parameters);
   

    // delete existing image files
    $dirname = './images/';
   $dir_handle = opendir($dirname);
   if (!$dir_handle)
      return false;
   while($file = readdir($dir_handle)) {
      if ($file != "." && $file != "..") {
         if (!is_dir($dirname."/".$file))
            unlink($dirname."/".$file);
         else
            delete_directory($dirname.'/'.$file);    
      }
   }
   closedir($dir_handle);

    $htmlFormat = new RenderAsHTML();

  $htmlFormat->StreamRoot = './images/';
 
  $result_html = $rs->Render2($htmlFormat,PageCountModeEnum::$Estimate,$Extension,$MimeType,$Encoding,$Warnings,$StreamIds);

      $i = 0;

      // handle the images by writing them to the temp images folder
      foreach ($StreamIds as $StreamId){

$result_image = $rs->RenderStream($htmlFormat,$StreamIds[$i],$Encoding,$MimeType);

if (!$handle = fopen("./images/" . $StreamIds[$i] , 'wb'))
{
  echo "Cannot open file for writing output";
exit;
}
if (fwrite($handle, $result_image) === FALSE)
{
echo "Cannot write to file";
exit;
}
fclose($handle);
$i++;
}
// construct the html of the report
$content="<div align='center'>";

$content .="<style type='text/css'> td{border-top-width: 1px;border-top-style: solid;border-top-color: transparent;}</style>";
$content .="<div>";

$content .='<div>';
/* add the report content */
$content .=$result_html;
$content .='</div>';
$content .='</div>';

$content .='</div>';

Note that the report is rendered as HTML by the Render2 method of the SSRSReport class provided by the SQL Server Reporting Services SDK for PHP . If the report contains any images or charts, the method generates image files for these and stores them in a specified folder – hence the requirements to clear out the image folder before rendering the report to remove any images left over from previously viewed reports. One gotcha that we found when implementing the solution was that these images are generated with no file extension, so we had to configure IIS to map the .* file name pattern to the Image/png MIME type.

The solution we built was based on pre-beta releases of Drupal 7 and the related SQL Server driver for PDO and SQL Server module. Given the complex dependencies on multiple pre-release software components, it seems foolhardy to publish the full source code for this solution until all of the dependencies are stabilized in a final release.  At that point, the plan is to update the code for our solution and publish it on CodePlex so that anyone can examine and extend it, so watch this space!