Getting Started with SQL Server Reporting Services (SSRS) and PHP

In a recent post I took a look at how to get started with SQL Server Reporting Services (SSRS). In this post I’ll dive into the SSRS SDK for PHP that was recently released by the Interoperability team here at Microsoft. The SSRS SDK for PHP allows you to render render reports from within your PHP code. The documentation in the SDK is complete with explanations for the classes that make up the SDK, code examples, and a “hello world” example. However, in this post, I’ll build a simple web page (code attached to this post) that renders the Sales report that I created last week. My example uses some reporting features that aren’t shown in the “hello world” example, and this way you’ve got multiple examples to work from when you want to use the SDK.

[Edit: If you have trouble setting up user permissions with SSRS, this video may be helpful: http://www.youtube.com/watch?v=PSYHqkhhdOo.)

Overview

With the Business Intelligence Development Studio you can design reports in SQL Server 2008 Reporting Services. You can access these reports directly via the Report Server (as I showed in this post) or you can use the SSRS SDK for PHP, which uses the SOAP API to communicate with the SQL Server Reporting Services Web Service.

Prerequisites

Follow the steps in this post for creating the Sales report (based on the Northwind database): http://blogs.msdn.com/brian_swan/archive/2010/04/29/getting-started-with-sql-server-reporting-services-ssrs.aspx.

Create a User with the Right Permissions

I struggled a bit to create a new User that has the permissions necessary to access reports. Perseverance paid off, however. Here’s what I did:

1. Create a local Windows user (PHPDemoUser) on your machine. You do not need to make PHPDemoUser an Administrator...a Standard user is fine.
2. Create a login on your SQL Server Express installation for PHPDemoUser. When you create this login you'll want to qualify it with your machine name: MACHINE_NAME\PHPDemoUser.
3. Add PHPDemoUser as a user for the Northwind and ReportServer$SQLEXPRESS databases. Give the user dbdatareader and dbdatawriter priviliges.
4. Modify the rsreportserver.config configuration file located under the ReportServer installation folder (C:\Program Files\Microsoft SQL Server \MSRS10.SQLEXPRESS\Reporting Services\ReportServer in most common scenarios). You might need to open the text editor with Administrator privileges. Add the <RSWindowsBasic/> element to the <AuthenticationTypes> node (if other nodes are already included, don't delete them):

<Authentication>
    <AuthenticationTypes>
        <RSWindowsNegotiate/>
        <RSWindowsNTLM/>
        <RSWindowsBasic/>
    </AuthenticationTypes>
    <EnableAuthPersistence>true</EnableAuthPersistence>
</Authentication>

Connect to the Report Server Web Service

To connect to the Report Server Web Service, we simply need to create a new SSRSReport object (passing in the credentials of the PHPDemoUser and the service URL). If we are toggling an item in the report (as we would do when drilling into the products that make up a category in the Sales report), we call the ToggleItem method. Otherwise, we simply load the report:

require_once 'SSRSReport.php';
define("UID", 'MACHINE_NAME\PHPDemoUser');
define("PWD", "PHPDemoUser_PWD");
define("SERVICE_URL", "http://localhost/ReportServer_SQLEXPRESS/");
define("REPORT", "/Sales");

$ssrs_report = new SSRSReport(new Credentials(UID, PWD), SERVICE_URL);
if (isset($_REQUEST['rs:ShowHideToggle']))
{
    $ssrs_report->ToggleItem($_REQUEST['rs:ShowHideToggle']);
}
else
{
    $ssrs_report->LoadReport2(REPORT, NULL);
}

Depending on how a report is designed, methods other than ToggleItem exist for rendering a report. For example, if your report allows a user to sort items, the Sort2 method is available. (For a complete list of methods on the SSRSReport class, see the SSRS SDK for PHP User Guide.htm file in the SDK.)

Note: In Reporting Services 2008, new functionality was introduced that required altering some methods. To maintain backwards compatibility with older Reporting Services releases, new methods were created (with the same name as their “old” counterparts) and post-fixed with “2”.

Render the Report as HTML

Next (and last), we render the report as HTML by creating a new RenderAsHTML object. (Other methods allow you to render a report as Excel, Word, CSV or Text, XML, Image, and PDF.)

$renderAsHTML = new RenderAsHTML();
$renderAsHTML->ReplacementRoot = getPageURL();
$result_html = $ssrs_report->Render2($renderAsHTML,
                                     PageCountModeEnum::$Estimate,
                                     $Extension,
                                     $MimeType,
                                     $Encoding,
                                     $Warnings,
                                     $StreamIds);

echo '<div style='overflow:auto; width:1000px; height:700px'>';
echo $result_html;
echo '</div>';

Note the use of the ReplacementRoot property on the RenderAsHTML type: its value is used to replace all links in the generated report with relative links to the PHP page you are using. Here is the getPageURL function that returns the current page URL:

function getPageURL()
{
    $PageUrl = $_SERVER["HTTPS"] == "on"? 'https://' : 'http://';
    $uri = $_SERVER["REQUEST_URI"];
    $index = strpos($uri, '?');
    if($index !== false)
    {
         $uri = substr($uri, 0, $index);
    }
    $PageUrl .= $_SERVER["SERVER_NAME"] . ":" . $_SERVER["SERVER_PORT"] . $uri;
    return $PageUrl;
}

Note: See this post for an example of rendering a report as an Excel document: http://blogs.msdn.com/b/brian_swan/archive/2010/09/23/rendering-sql-server-reports-as-excel-documents-with-php.aspx.

Really, that’s it…pretty simple. (Attached to this post is a file with the complete source code.) Of course, depending on the nature of your report, you might have to write a bit more code to handle postback events, but the basics are here. I think the Interoperability team did a great job in designing the SDK…it really makes consuming and displaying reports straightforward from PHP. To dig deeper into the capabilities of the SDK, read the docs that come with the download. Or, if you have questions, ask in a comment here and I’ll do some homework.

Thanks!

-Brian

Share this on Twitter