In this post, I am going to explain about :
1.)How to Open SQL Server Business Intelligence Development Studio
2.)How to create new project for SSRS report
3.)How to add datasets in the form of query & stored procedure
4.)How to add parameter for report input & map it with parameter of dataset.
5.)How to design report & export it.
Step 1 :
In windows, goto “All programs”,under Microsoft SQL Server 2008 R2, select SQL Server Business Intelligence Development Studio
In windows, goto “All programs”,under Microsoft SQL Server 2008 R2, select SQL Server Business Intelligence Development Studio
Step 3:
Select Report server Project and mention [Name,Location..] for project as shown in below image
Select Report server Project and mention [Name,Location..] for project as shown in below image
Step 4:
Create report under the project we have created as shown in below image.
Create report under the project we have created as shown in below image.
Step 5:
Create Shared Data Sources as shown in below image.
In this example, Shared Data Sources is nothing but SQL Server database.
Enter server name,database name & mode of logon by clicking edit under connection string.
If you create Shared Data Sources/Shared Datasets under solution explorer, it means you can use that datasource / resultset for all reports under that project.
Step 6:
Under parameters, by right-clicking parameters we can create parameters for report.
Under parameters, by right-clicking parameters we can create parameters for report.
Step 7:
What is dataset?
Dataset is just a result set from database or other datasource
In this example, I have explained about query dataset & stored procedure dataset.
Example for Query dataset:
Create dataset using below query as shown in below image.
--query resultset SELECT TOP 5 name,type,type_desc FROM sys.objects WHERE type = @report_input
Step 8 :
Map the parameters of report with dataset parameters.
Map the parameters of report with dataset parameters.
So that when you enter value for report parameter, it will get the resultset based on parameter value from the dataset.
Step 9:
Example for Stored procedure dataset:
Create the Stored procedure in the database mentioned in Connection string.
--Stored procedure resultset CREATE PROCEDURE Procedure_dataset AS SELECT TOP 5 name,type,type_desc FROM sys.objects
Step 10 :
Create stored procedure dataset by calling stored procedure from where you have created as shown in below image.
Create stored procedure dataset by calling stored procedure from where you have created as shown in below image.
Step 11:
In the below image you can see Design & Preview tab.
In the below image you can see Design & Preview tab.
Design tab is for designing the report.
Step 12:
Select Toolbox from View menu, drag textbox for title of report, drag two Table from Toolbox (one for query dataset & one for procedure dataset) and put it in the body of report as shown in image:
Select Toolbox from View menu, drag textbox for title of report, drag two Table from Toolbox (one for query dataset & one for procedure dataset) and put it in the body of report as shown in image:
You can notice in above image, report is getting loaded.
Data from ReportServer Tempdb is rendered and displayed through report, this is called “Rendering in SSRS”.
No comments:
Post a Comment