Create a report using Report wizard in SSRS

Aim – We will create a simple report with the help of Report wizard using Microsoft SQL Server Reporting Services (SSRS) 2008 version. With the step by step implementation of reporting services, we will also try to understand the working environment for the report making. Let’s create a report without wasting time on theoretical part as doing practical will make you understand better.

STEPS TO FOLLOW:-

Step 1. Open Business Intelligence Development Studio (BIDS) — Go to Start – All Programs – MS SQL Server 2008 R2 – Business Intelligence Development Studio.

Step 2. This will open a Start page. Go to Create project and choose Report Server Project Wizard. We need to provide Name, Location and Solution name for the project.

Step 3. Clicking OK button will pop up Welcome Report wizard screen. You can tick the check-box if you do not prefer to see this screen next time.Step 4. Now we need to select a data source from which we will obtain data for creating this report.Either we can choose Shared Data Source (If data source is already been present) or can create a new Data source by providing data base credentials.

I will be creating a new data source so that we can understand this step clearly. Give a desired nameand type for the data source. I am using MS SQL Server as my database, so will choose it from theType drop down box. Click on Edit button.

It will lead to the Connection properties screen containing the fields as mentioned below:-

• Server Name – Choose the Server name from the drop down box.
• Log on to the Server – Select the preferred Authentication method and provide valid credentials for that method (As i am using client MS SQL Server, so i will use SQL Server Authentication method to get connection with my server).
• Connect to a Database – As the connection established, all the tables or views will be populated in the drop down box and we can choose our desired table for creating report.

NOTE :- If everything goes well then clicking on Test Connection button will result into Success else you have to configure your connection correctly.

Step 5. Now we need to concentrate on this step as we are going to design our query to fetch the desired data from the chosen table. Click on Query Builder to generate automatic query for our task. You can also write it by your own but i prefer shortcuts.

Click on Add Table button present at the extreme right on top and choose the tables whose report you want to generate and click OK. I will be selecting a single table to perform this report creation by Report wizard method.

Step 6. Clicking Next button will lead to Report type screen. Select the desired Report type and click Next.Step 7. Choose how you want to see your data in the report format according to your need. This is the screen where you can design the layout for your data. Click Next button.

Step 8. Choose the type of Layout for the table. I will be making the report using Stepped layout but you can also try Blocked layout and can analyze the difference between these 2 layouts.

NOTE:- Include subtotals and Enable drill down features will be covered later as we move forward towards the complex report generation but if you are anxious to know then you can tick the check boxand can learn it from the output you get. You can select Enable drill down check box if you want to make a drill down report by Report wizard.

Step 9. Choose a style for the table and click Next. Choose the Deployment location for the report and click Next button.Step 10. We are about to see the output for our report. Provide the name for the report and clickFinish button to create a new report using Report wizard.NOTE :- If we select Preview report check box, then we directly can see our report in the Preview Paneelse we will land into the Design Pane.

Step 11. Clicking Finish button leads to the Design pane which is known as Workspace. Let’s understand the basics of our working environment.In our working Environment we will have features like:-

1. Toolbox containing tools such as table, rectangle, line, matrix, sub-report, etc.
2. Dataset containing columns which are generated by our SQL query which we passed in Query builder.
3. Two types of modes named as Design and Preview mode.
4. Solution Explorer and Properties window pane for the report.
5. Work space, Built in fields, parameters and many other features.

Step 12. Now, it’s the time to raise the curtains and see our output for the report which we have created by spending so much of time.That’s Great !!! We get the success for our first report creation using MS SQL Server Reporting Services (SSRS) by Report Services Wizard. You can also deploy your report if you have theadministrative rights.Deploying your report to the server will make you to see it via web interface.I guess it was very simple for you to implement this task. We will be creating custom reports in our next part using MS SQL Server Reporting Services – SSRS 2008. Your comments and views are welcome.