(Example I have used not required subreport but goal of this article to explain subreport in an easy way)
Before starting Subreport I create three table tblorder, tblProducts and tblOrderDetails
tblProducts:- Master table contain product information like
ProductID, ProductDescription
tblorder:- Table contain order information order by client.
tblOrderDetails:- Transaction table contain order and product information like order belong which product
Now table created Data is inserted ,we will create two store procedure one for parent report and another for subreport This store procedure will accept OrderID as parameter and return the details of particular OrderID (ProductID,Qy )
Second store procedure will take ProductID as parameter and return Product description which will be shown on sub report
So we have created store procedure which will be used in reports. Let is create reports which will show order information base on order id.
1) Open BIDS (Business Intelligence Development Studio)
2) BIDS will be open go to open New Project window by using File-->open -->new project
3)New project window will be open, left panel will show project type right panel will show project templates select “Report Server Project” to continue in Name textbox specify Name of Project Location specify location path click ok button
4)In Solution Explorer will be shown at right side of window if you are not able Solution Explorer then go to View--> Solution Explorer. two folder will be shown
a)Shared Data Source:-- This folder contain Data Source which
share by multiple reports
b)Reports :- Reports contain report file
4)Right click on “Shared Data Source” folder then select “Add new data source” Data Source window will be open right panel contain two option “General” and “Credentials”
a)Name:- Specify Name of Data Source
b)Type :- Type of Data source currently we will select “Microsoft Sql
Server”
c)Connection String:- mentioned Connection string
5)Provide Credentials for connoting to database
6)Data Source will be listed on solution explorer
7)Right like on “Reports” folder select “Add” then select “New item ” from sub menu option
8)Add “New Item” box will be open, select “Report” option from templates after selection click on ok button created report will be listed into “Reports” folder.
9) Data source and Test Report are created now we will create Dataset. Dataset is a metadata which represent data in a specific DataSource we will create two dataset one parent report and another for subreport Open “Report Data” Go to ->Report Data option Report Data window will be open click on “New” option then select “Dataset “
10) “DataSet” properties window will be open.
Option shown left side list box allow u to set dataset property
Query option:
Name: - Specify the name Dataset
DataSource: - Dropdown allow u to select Data source already created (dsTest)
QueryType:-QueryType radio button option specify what type of Query we will execute
a)Text:- When specify select command
b)Table:- When specify only table name
c)Store Procedure:-Specify store procedure name
Import:-Using this button we import file which contain query
TimeOut:-We can specify timeout time of Query if query will not execute in specify time then Timeout will occurred.
11)After specified required value in Query option select Parameter right we will get the list of parameter which is required in store procedure we have selected in Query option we can set default value (Below screen).
Dataset will be listed on ReportData explorer
12)We will create another report which will contain only one column product description All procedure will be same mentioned above except store procedure name will be “SP_GetProductdetail”
Properties which will set for new reports
Two reports will be shown solution explorer.
13)Open main report design view add table which contain three columns.After Adding table Drag ProductID and Qty column from ReportData explorer Into table column change Header name as per requirement
14)Drag newly created report from Solution explorer drop into third column
15)Right click on subreport select “Subreport properties” subreport properties will be shown
16)Select parameter option click on “Add” button. select column
17) Go to Preview panel enter order number details will be shown below mentioned product description is subreport
