SharePoint and SQL Server Reporting Services - Authoring a simple report

Posted on 12/23/2007 @ 1:34 AM in #SharePoint by | Feedback | 18415 views


  1. SharePoint and Reporting Services - Introduction
  2. SharePoint and Reporting Services - Installation and Setup.
  3. SharePoint and Reporting Services - Setup your database.
  4. SharePoint and Reporting Services - Authoring a simple report.
  5. SharePoint and Reporting Services - Enabling end users - the report model.

Okay, now that you have setup SSRS + MOSS on your farm, and have setup a database. Time to write a report.

I am going to author an uber simple report - something that shows me all customers in the Northwind DB, (Select * from Customers), and they are grouped by Country.

Use the following steps:

#1: Fire up the Business Intelligence Studio, and create a new project based on the "Report Server Project" template.

#2: Once the project is setup, you should see two folders in your solution explorer - Shared Data Sources, and Reports. Begin by right clicking on the shared data sources folder and choosing "Add a new Data source". Setup a new Data Connection like as shown below:

For the smarter ones of you, you may have noticed that I am developing on a different machine (moss2007), and using windows auth. This is because I am not developing directly on the production server, I intended to demonstrate how you can also change the data connection afterwards.

Go ahead and save the new shared data source.

#3: Add a new report - Right click on the Reports tab, and add a new Report.

  • Choose the Shared Data Source "Northwind"
  • Specify "Select * from Customers" in the query. Usually you'd want to write a better query than that :).
  • Choose a tabular report
  • Add Country to Page, City to Group, and everything else to Details as shown below -
  • Format as necessary, Call the report "Customers".
  • Hit Finish, BINGO you have your report.

Now there is plentiful else you can do here to make your report even more compelling, I'm going to skip over that, and jump straight to "Deployment".

Deployment:

Right click --> properties on your BI project, and change the properties to as shown below -

Note again - I have specified my dev environment variables above. These can easily be moved/changed in a production environment later, and I will cover that shortly.

With the above values filled in, and assuming you're running and authoring these reports in the BI studio as a user who has rights to the above site, right click on your project, and choose Deploy. If you have followed all steps properly, the report should have deployed successfully.

You may verify that by peeking into the two document libraries you setup earlier, and you will note that the data source, and the report - both have been successfully deployed.

Viewing your report

Just go to the document library (http://moss2007/reports), and click on the report - and the report should run. :-)

You can also choose to drop the "SQL Server Reporting services report viewer" webpart, and configure it to show the report from the document library using the steps below.

a) Add the SQL Server Reporting services report viewer webpart -

b)  Point the webpart to the appropriate report using the dialogbox below -

 

c) Hit OK/Apply/Exit Edit Mode.

You should see the report running inside sharepoint as below -

 

AWESOME !!!! So I can author a report in BI studio, and run it inside a branded sharepoint site - complete with security, and bunch of other stuff? WWWOWWWWWW!!!! :-)

What bunch of other stuff ;-).

Here goes -

Deploying to Production:

You can now happily move the Customers.rdl file and Northwind.rsds file to production. In fact, you can also easily change either the rsds definition by simply clicking on Northwind.rsds and editing it using the web based form as shown below -

You can also choose to manage data sources on a per-report-level from the web UI as shown below.

But there are a number of other interesting options above -

Manage Subscriptions: You can choose to subscribe either a file system folder, a document library, an email address, or custom anything, to have copies of the report be run and stored as say a PDF at regular intervals.

Manage Parameters: If in case you were using a parameterized report, you could use this option to control parameter values.

Manage Processing Options: Use this option to control various settings such as, running the report on live or cached data, processing time, snapshotting options etc.

Edit in Report Builder: This is a REALLY REALLY cool click once application, that is best demonstrated using a Report Model - which is what I am going to talk about next.

Sound off but keep it civil:

Older comments..


On 12/24/2007 3:33:13 PM Raja said ..
Hi Sahil,

This is an awesome post...I am new to Sharepoint and I am trying to report against the data that is being keyed in using Sharepoint.


Is it possible to report against the data which we put in using the custom lists in sharepoint sites?


If yes then can you please show a sample by which we can report against the data since I happen to go through the content database created by sharepoint and it is pretty much confusing.

Thanks,


Raja


On 12/24/2007 8:46:20 PM Sahil Malik said ..
Raja -


You have to write a custom extension for that.

Sahil


On 4/11/2008 5:17:50 AM Mogens Nielsen said ..
Cheers,


thanks for sharing this...

/Mogens


On 4/24/2008 10:38:34 PM Richard Hain said ..
Cool! I thoughts lists and views and Excel exports are all I can do in SharePoint for reporting. Now I can try something for real reports. Nice job!!!


On 5/13/2008 7:05:36 AM Aastha said ..
Hi sahil

this is very helpful. can u tell a procedure to create dynamic reports as the report,a logged in user is getting is based on authorization.

Will be of great help!

thanks,


Aastha


On 5/30/2008 7:27:44 AM Saad Alam said ..
Hi,

Thnx so much Sahil for this is post.Sql server reporting in Sharepoint is absolutely awesome. Although it took me three days to make it work! :)


On 2/26/2009 5:36:25 PM Wes said ..
When I click on Manage Subscriptions I only see options for Windows File Share, Null Delivery Provider, and SharePoint Document Library. How do I create an email subscription?


On 3/13/2009 12:33:50 PM Bill said ..
Hi Wes,

I had the same problem with "E-Mail" not showing up. I resolved it by going to Reporting Services Configuration Manager and configuring the "Email Settings" section.


On 1/6/2010 10:02:46 AM HawkeyeDBA said ..
Thank you SO much for putting this together! You have made the deployment go smoothly for us! I hope Microsoft sees what you've done and encorporates it into the next BOL release!


On 1/8/2010 8:12:55 AM Damian Caley said ..
Can you possibly help i am trying to follow the above code but am receiving the following error when i go to view the report through sharepoint


(An error occurred during client rendering.


The remote server returned an error: (500) Internal Server Error.)


I am getting very frustrated as i cant seem to find out how to cure this

D


On 6/9/2010 3:07:44 AM richa said ..
Hi with windows authentication in wss4 its works well but when using forms authentieation its gives HTTP404 bad request error.


please help.


On 11/9/2010 1:43:37 AM Lasitha said ..
Hi Sahil,

Did you try to deploy reports to a SharePoint Site which uses Claim Based Authentication?


When I try to do it, there is error saying that permissions are insufficient for the user. But the user has the Full Control for the sharepoint site.

Thanks in advance


On 11/15/2010 7:04:21 AM WilliamB said ..
Where have all the pictures gone?


On 12/27/2010 7:33:53 PM Toan Nguyen said ..
Hello, I have question. I am making a report, that related to one day, week, month, and year. The user select the date in Sharepoint and it will produce a report. I don't know how to make that query connect to sharepoint so if the user select the day in sharepoint it will create a report.

Thank for your help a head of time