Ok, so after some initial struggling of setting up whole environment for SSRS to work smoothly and after getting some sample data I am finally ready to begin my epic journey to SSRS (it is epic for me because most of the stuff that I am going to learn will be myself and I will not have any on-site help, but of course Google – his brethren .. i.e. Bing, Yahoo etc will be always helping me in my quest).
Anyways, this first post will show how to create a basic report for SSRS using Business Intelligence Development Studio (a.k.a BIDS). Since BIDS is component of VS 2008 and it is not available for VS 2010, we are left with only one choice of IDE that is VS 2008 (if you have Pro or higher version or you are stuck with BIDS which gets installed with SQL Server 2008r2).
But before doing anything I will create a sample table for us to use in report. Of course most of the “tutorials” out there are using standard Adventureworks database but for this report I am using my usual table that I practice with. Following is SQL Query to create that table, and of course you will need to have AdventureWorks 2008 r2 sample database installed (or you can modify this query to match your setup).
SELECT p.BusinessEntityID,p.FirstName,p.LastName,a.AddressLine1,a.City,sp.Name [State/Province],cr.Name [Country],a.PostalCode into TableFoo FROM AdventureWorks2008R2.Person.Person p JOIN AdventureWorks2008R2.Person.Address a ON p.BusinessEntityID=a.AddressID JOIN AdventureWorks2008R2.Person.StateProvince sp ON a.StateProvinceID=sp.StateProvinceID JOIN AdventureWorks2008R2.Person.CountryRegion cr ON sp.CountryRegionCode=cr.CountryRegionCode
Now, after having something to select from database fire up VS 2008 instance and create New Project and select Business Intelligence Project –> Report Server Project. At first you should see empty environment and on your right hand side, you should see 3 options,
1) Shared Data Sources: which can be used to share same database connection across all report in that project, we will see later on how we can use this.
2) Share Datasets: which are nothing but select query or a SP which will be used to select data from table.
3) Reports: where you will add reports.
At first, right click on Shared Data Sources and select “Add New Data Source” , and it will present you that common interface that you will find in any MSFT product that lets you connect to DB using wizard. Select your database where you have created tableFoo using above query and click on “test Connection” to make sure that it works !!
Once having data source, right click on Shared Datasets and select “Add new Dataset”. Here you will have option to select query type as simple sql string or SP, for now we will go with text option. Then click on Query Designer button it will open up query designer window which is basically window to create SQL Query visually.
Initially it will not show anything in screen,but then right click on screen and select “Add Table”. If you have connection with correct database then you should see the tableFoo along with list of other tables in database when you click on “Add Table”, select tableFoo.
Then either select all fields or write a sql query in third section of window. And then click on “run” to make sure that query return desired results. If everything looks OK then click on OK to exit out of the query editor. And you will be back on Shared Dataset window.
But this time you should see your select query in Query window. Click on OK since we don’t need to worry about other thing in this tutorial. Now the fun part, right click on Reports and select ADD (not add reports) and then select Add Report from available options.
After this you should see empty report screen. But now you need to add connection information and dataset information. Which will be our next steps. On left hand side you should now see options to add new datasource and dataset for your report.
 |
 |
select Data Source from “New”, and select shared datasource that we have previously created. And same is for Dataset. and select Shared dataset that we have previously created.
If everything is done properly you should see your dataset with data fields which we will use for our report.
Rest of part is easy. Right click of report body part and select insert, which will give you options about controls which you can insert like Header, Footer, Table, Textbox, Charts etc. For our report we will add a table first. And when you select a data column of table you will see all those data fields from our data set. You just need to select which field you want to select for your table column. And final report may look something like what I have below.
Once done designing you can actually preview the report by clicking on “Preview” tab located next to Design tab. And you will see how your report will look when it will be deployed on server.
And that concludes our 101 guide for creating repot is SSRS. Next step will be to deploy report on report server. But since this tutorial is already too much stretched, I will create another tutorial for step to deploy report on report server which is kind of very easy 
…
I’ve uploaded this project code here
Remember, It’s Just A Thought … 
