SSRS 101: Creating Basic Report in BIDS–Part I
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 …
- Removing Favorite, HomeGroup and Libraries from Explorer in Windows 7
- FTP connection using BATCH file
- Sorting in GridView
- Setting up Domain Controller in Virtual System … Part I
- MAPS in SSRS
|This entry was posted by Gaurang Patel on August 28, 2011 at 11:22 am, and is filed under How To, SSRS. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site.|
about 1 month ago - No comments
There are many after market tools available to monitor health of SQL Server. But with help of built-in tools you can actually create some pretty neat solution by yourself too. And one of the most handy tool is “Alerts” in SQL Server. With help of Alerts you can monitor SQL Server Events, Various Performance Conditions
about 4 months ago - No comments
This week I ran into strange issue. Out of no where my Who Is report stopped working. To be exact, InfoDB API stopped working ! With little digging I found that it needs to have format=xml not XML !! For some reasons InfoDB guys just decided that it has to be in lower case, but
about 9 months ago - No comments
Back in Pre SQL Server 2005 editions of SQL Server there was nice feature called SQL Mail which basically allowed users to send email from within database server. But that system has many dependencies and major one was requirement of Microsoft Exchange… thus usage was severely limited to enterprise customers who has resources to setup
about 10 months ago - No comments
So after setting up Domain Controller (DC) next step is to configure workstations and add them into newly created Domain. It is actually not that difficult part but during my early trials I spent quite a bit time to make these workstations connect to DC. Because even though they were part of same network they
about 10 months ago - 1 comment
If you are like me, who like to “experiment” with random software downloaded from Source Forge, CodePlex or anything alike … on top of working on bunch of side projects in different work environments … and you have a “bug” in your mind which make you learn anything and everything .. then I am sure
about 11 months ago - No comments
If you have worked with MSI then you probably know that it uses an internal database to store all kind of information and you can actually directly modify this information by using a great tool OCRA. It is tiny utility released by MSFT. But unfortunately it is not “that” simple to get OCRA because it
about 11 months ago - No comments
As usual, I was searching something over internet and going through random crap to look for something (I don’t remember now what was that) and I somehow found that there is really great utility available in good ol’ Sysinternals Suite which literally allows user to remotely execute any application on another machine !! That utility
about 1 year ago - 1 comment
From past some time I am trying to create windows domain in home computer with bunch of VMs using Windows Server 2008 R2 for “educational” purposes … my ultimate goal was to setup clustered windows system and have SQL Server in that environment. I want to lean about how SQL Server behaves in clustered systems
about 1 year ago - No comments
It has been while since I posted something. Actually I was on very long vacation for almost 2 months and during that time all I did was eating, traveling, sleeping and eating again … and now it is ended and I am back .. I think I have gained few pounds… so (just like every
about 1 year ago - No comments
Ok, few weeks back I posted how to create a fixed width file using SSIS for test purposes. Then after that I posted TSQL way to import it into database, and now today I am going to show how to import it using SSIS package… I have used same database (which I used to generate