Reporting – How to create Report in SSRS?
Introduction: SSRS – SQL Server Reporting Services, its an report generating system which is made by Microsoft.
Description: To create a Report in SSRS, we have to use Business Intelligence Development Studio of SQL Server 2005/2008. In this article we will see how to Toggling, Adding Dataset, and Share your Data source with your Dataset. First we will create a project into SSRS.
First Step: – Go on Start button > Click SQL Server 2008 > Click SQL Server Business Intelligence Development Studio.
Second Step :- Once the Business intelligence development studio open go on
File > New > Project
Third Step : – Select Report Services Project and Give the name of the project Click Ok and Location where to save the Report on Hard Drive.
4. Right click on your Project in Solution Explorer and Add new item and Select Report and click ok it will create one .rdl file under Reports in Solution Explorer. If you are unable to see solution explorer
View > Solution Explorer
Go on Chapter 2 how to create Report?
Know here we will learn how to add data source, how to share it with Dataset, and how to do grouping, how to do Toggling?
- Create a Datasource Right click on Shared Data Source from Solution Explorer, one window will Pop Up > Click on Edit another window open (Connection Properties) and Give your Server Name, Log on to the Server, select the Database, and Test Connection
- Once a Data source is created go on Report Data on Left pane > Click New > Datasource >
- One window will Pop Up Data Source Property > User Shared Data Sources Reference
- From Report Data, you will see your Shared Data Source will appear right click on that and Add Dataset.
- Data Set Property window will open, give the name to your Dataset and in Query Type select as per your need, Text or Stored procedure, for this tutorial we will use Stored Procedure.
- Insert Table and add columns in Design Mode, as per your Need. And also add more functions, like rectangle to add text, it will show you everything in systematic mode.
- Add all columns from your dataset to Design mode table in report. By only dragging column from dataset to Report design mode tables, and then right click to below column where data is going to show and add parent
- After clicking on Add parent one window will PoP window open called Tablix group > Select a name a group with Add group header checkbox check (Group is nothing but a column name where you want to group your data to show), you can select as per business requirement > click ok
- After adding group you will find one two groups added below the Design Mode,
- A group which you had created above by selecting “Add Parent”, Change the group property by right click on
- In Group property window set General property > Group on as Column Name (which you had created above by selecting “Add Parent”) Here you have to add column name to group On the rest of the columns
Here you have to add column name which will sort the result set. (As per Business needs)
- Detail Group :-
Change the group property by right click on above Detail group and toggled the column by selecting Visibility Property. When the report is running you have to hide the display open.
“Hide” Option – Here you have to select the column for Toggling.
If you want to add some Expression values you can right click on Expression for any blank text box, Average, Subtraction, addition, Multiplication, Division, or else you can show who is currently login by using Built in field Category (User ID), or Execution time, Total Pages, Page Number. You can format your Current Datetime, Numbers, Percent, by using Common Functions Category. You can do lots of stuff in Expression window to show more things in your Report.
Press F5 to run and enjoy the Report.