The Interoperability team here at Microsoft recently released the SSRS SDK for PHP (SSRS = SQL Server Reporting Services). At the time of the announcement, I was very excited to check out this SDK, but I was so busy that I’ve just recently had a chance to dig in. Since I had only a high-level understanding of SSRS, I thought I’d better check out SSRS on its own before figuring out how to integrate PHP. I WILL get to examining what the SDK has to offer in a later post, but in this post I’ll provide a quick look at how to get started with SSRS without getting into any PHP code. (That later post is now available here.)
First things first – what is SQL Server Reporting Services? The “official” answer is that it is “full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality.” Personally, that didn’t help me much since I’ve never used SSRS before. So, to better answer this question, I set out to build a simple report based on data in the Northwind sample database. I also set out to do this with only free offerings from Microsoft, which I wasn’t sure was possible (but I found that it is :-)).
Edit: When I first published this post, I used Report Builder 2.0 to generate the report. I later found out that Report Builder 2.0 is only available for use if you have a paid-for version of SQL Server. It says so right in the license agreement…I just didn’t read it carefully. However, you can still generate reports with all free software. I’ve modified this post to show you how to use SQL Server Business Intelligence Studio, which comes with SQL Server 2008 Express with Advanced Services.
Edit: If you have trouble setting up permissions for SSRS users, this post may be helpful: Configuring SQL Server Reporting Services (SSRS) Users.
The report I generated shows the dollar sales for product categories and allows you to drill into the sales for for each product within a category.
The rest of this post will walk you through what I did.
Step 1: Download the SSRS SDK for PHP. As I said in the introduction, I won’t get into the SDK itself in this post. However, it does contain some excellent detailed instructions for some of the steps that follow. For that reason, I suggest you download it now.
Step 2: Download and install SQL Server 2008 Express with Advanced Services. You have to have the “with Advanced Services” edition of SQL Express here – other editions do not offer SSRS. I would suggest you follow the instructions for setting up SQL Express that are in the SSRS SDK download (see the Installing SQL Server 2008 Express with Advanced Services section in the \Samples\HelloWorld\setup\Client_Setup.htm file). I should also note that you will have to install SQL Server 2008 Service Pack 1 afterwards.
Step 3: Download and install the Northwind sample database. (I know the download says its for SQL Server 2000, but I find the Northwind database better than most for tutorials like this one.) After you have downloaded the package, move the northwnd.mdf and northwnd.ldf to the DATA directory of your SQL Express installation (C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA on my machine). To attach the database to your server, start SQL Server Management Studio, right-click Databases, and select Attach. In the Attach Databases dialogue, click Add. Finally, in the Locate Database Files dialogue, select northwnd.mdf and click OK.
Step 4: Set up the report server. Once again, there are very good instructions for doing this in the SSRS SDK. Follow the directions in the \Samples\HelloWorld\setup\Server_Setup.htm file. (You only need to follow steps 1-11 of Task 1.) Make note of the Web Service URL (http://localhost/ReportServer_SQLEXPRESS is suggested) – you’ll need it in the next step.
Step 5: Create a new BI project. Open SQL Server Business Intelligence Development Studio (which come with your SQL Express installation). When the development studio opens, click File>>New Project, and create a Report Server Project Wizard project.
Step 6: Define the data source. When you create the project, the Report Wizard will start. When the Select the Data Source dialog box opens, provide a name and click Edit to build the connection string.
Step 7: Define the query. In the next dialog box (Design the Query), paste the following code that is the query for our report:
ROUND(SUM((od.UnitPrice * (1-od.Discount)) * od.Quantity),2) as Sales
from Products p join [Order Details] od
on p.ProductID = od.ProductID
join Categories c
on c.CategoryID = p.CategoryID
group by od.ProductID, p.ProductName, c.CategoryName
Step 8: Build the report. In the Select the Report Type dialog box, choose Tabular and click Next. In the following dialog box (Design the Table), move CategoryName and ProductName (in that order) into Group, and move Sales into Details. In the Choose the Table Layout dialog box, select Stepped, Include subtotals, and Enable drilldown.
Step 9: Set the deployment location. Click Next to get to the Choose the Table Style dialog box (and select what you want). Click Next again to get to the Choose Deployment Location dialog box. Make sure the Report Server field is set to http://localhost/ReportServer_SQLEXPRESS. Click Next, then Finish.
Step 10: Customize the report. You can do what you want here, but I provided a title, widened the columns, and deleted the bottom row, so it looks like this in the designer:
Step 11: Deploy the report. On the designer menu, click Build>>Deploy NorthwindSalesByCategory.
Now when you browse to http://localhost/Reports_SQLEXPRESS, click on Sales, and your report will be generated.
I hope that helps in getting started with SQL Server Reporting Services. As I mentioned earlier, I’ll follow up soon with a post about consuming and presenting these reports with PHP. Please let me know if you have questions in the meantime.