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.