Wednesday, 1 October 2008

SSRS Basics


This is the foremost blog on internet aimed at helping all the developers who are struggling their way out by creating reports using SQL Server Reporting Services [SSRS 2000 or SSRS 2005].

Are you Lost?
Are you a developer who has loads of work experience in Vb.Net or ASP.Net and has now to also serve as the Report Developer since his application has now a Reporting Angle ?
Are you the developer who was quite happy copy-pasting ADO.Net Code from MSDN and now has to deal with the perils of SSRS and integrating it with your application?

Resolution .......For the Escapists
For the developers who really dont think they could not do much about their skills in SSRS since this primarily a thing which the lazy database developer in the team should do. Why do demote our high payable .Net skills for something petty as Reports...think and talk to your PM, ask him to have mercy on your career and then ask that lazy tea drinking database developer to leave the vending machine and start looking at Reports....

Resolution....For the people who want to take the Bull HEADS ON!!!!
If you give some time maybe search a couple of articles on Internet [ like this one...] you will realize that SSRS is not the Raging Bull determined to pin you down. It is a beautifully simple and extremely intuitive application which would help you work on creating , designing and publishing reports just like that.
Trust me, I have seen it most of what SSRS can offer. I cannot commit I know it all but I am still very much eager to learn on SSRS. Also it would not be justifiable to say you have conquered it all in any tool. It would surely bite you back [in the wrong place and at the wrong time].

Enough Boost .........Finally.....SSRS Basics
SSRS is Microsoft SQL Server Reporting Services 2005 /2000. Initially distrubted as free installer with Service Pack 3A with SQL Server 2000, where it robbed Crystal Reports because of Crystals high prices license constraints and its eventual take over by Business Objects making it even more difficult to make it a tool of choice. Small and Mid size who had entire Reporting Architectures managed by Crystal Reports had to move SSRS since there was a of cost incentive attached to implementing a free Reporting tool which was only carrying 20% of Crystal Reports features but could be matched by extensive SQL Coding.

This is the reason why most developers who were so happy with Crystal Reports became wary of SSRS 2000 since it required them to have a new outlook on Reporting.

Reporting Services
SSRS has a very basic architecture.
The components being Reporting Services, Reporting Designer now available as the Business Intelligence Studio , Reporting Manager and the Sparkling new Drag Drop wonder of Report Builder.

Report Designer is where you see all the tools to create a reporting right starting from
  1. Creating a Reporting Services Solution
  2. Adding a Data Source [ Connecting to your database]
  3. Adding your Report.rdl [ Choosing either the wizard should help or you know you way]
  4. Click on the Data Tab on the Report Designer 
  5. Create a Dataset , Choose the DataSource you have created in Step 2. 
  6. Check you query in Dataset by running and seeing the result you are getting
  7. Tip in creating query in Dataset is that ask you lazy database developer to help you get the result you want to show in reports. Use parameter by using "?" in the where clause something like "SELECT Name, Address FROM Customers where Name = ?"
  8. Click on the Layout tab
  9. Drag a Table on the layout
  10. Right-Click on the Table and then select the dataset so that the Table is bound by the Dataset and hence would show all the fields in the dataset result.
  11. Add the Fields in the dataset in DETAIL field by dragging and dropping them from the dataset.
  12. The heading for the field would be added automatically if you want them to be default. Defualt being the name of the field in the Result Set returned by running the Query.
  13. Click on the preview 
  14. Put the correct parameter values if applicable and then click View Report.
  15. Select the solution and Right Click on it , the report deployment dialog box appears.
  16. Fill in the correct path to the Report Server [ Report Server is installed in your wwwroot folder in InetPub , basically it is added to the Default WebSites in your Web folder]
  17. Right Click and Deploy.
  18. Done!!!!
Check the report at http://localhost/ReportServer

This is real just the scratch of the surface of SSRS.
There are many nitty gritty on Expression , Parameters , Sub Reports , Charts , Matrix etc.

Await further blogs in the same series.

Till then....stop being afraid of SSRS , your saviour blog is here...........