Share via

Getting Started with SQL Server Reporting Services (SSRS)

  • Article

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.

image

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.

image 

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 (https://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.

image

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.

image

Step 7: Define the query. In the next dialog box (Design the Query), paste the following code that is the query for our report:

select p.ProductName,
c.CategoryName,
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.

image

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 https://localhost/ReportServer_SQLEXPRESS.  Click Next, then Finish.

image 

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:

image

Step 11: Deploy the report. On the designer menu, click Build>>Deploy NorthwindSalesByCategory.

image

Now when you browse to https://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.

Thanks.

-Brian

Share this on Twitter

Comments

  • Anonymous
    April 29, 2010
    Thanks Brian, great start. It is good to see Microsoft supporting PHP.You are appreciated,Larry Patelpate@ussugar.comManager-Agriculture ApplicationsUnited States Sugar Corporation
  • Anonymous
    April 30, 2010
    Thanks!
  • Anonymous
    February 01, 2011
    Thanks Brain,I am too are writing about Frist Report for SSRS. Please let me know if I can refer users here. ssrsdeveloper.blogspot.com
  • Anonymous
    February 13, 2011
    Thanks
  • Anonymous
    May 31, 2011
    great article!! thank you for your detail explaination!
  • Anonymous
    October 31, 2011
    Great article.Thanks a lot...
  • Anonymous
    January 25, 2012
    The comment has been removed
  • Anonymous
    August 02, 2012
    VIDEO IS REALLY HELP FULL AND A PERSON WHO DOSN'T KNOW ABOUT SSRS CAN LEARN ..BEST EVER I SAW
  • Anonymous
    February 14, 2013
    have a great opportunity for someone with it tech background, must have SSRS and dealt with finance... interested? let me know
  • Anonymous
    December 08, 2013
    How can I save it as pdf? I could not deploy it. http:/.... where do I geta correct site
  • Anonymous
    May 18, 2015
    The comment has been removed
  • Anonymous
    October 09, 2015
    vdfvdfvdfvdfvdfvdfvdvvdfvdfvdfvdfvdf