Drill-through or Linked Reports

1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18

    It provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.

Example:

• Open BIDS and Create New solution or Open existing solution.
• Create "ParameterizedReport" report.
• After that Add a new report with the name "DrillThroughOrLinkedReport".

• Create "Data Source" and "Dataset". While dataset creation use Dept table instead of Employee table.
• Query: 
• SELECT        DEPT_ID, Name, Location
  FROM            DEPT

• Click on "OK" button.
• In designer window insert a table and map the columns.

• Select "DEPT_ID", right click and select 'Text Box Properties.." (highlighted in the below screen).

• Then the "Text Box Properties" window will open.

• Go to "Action" section.
• Select the "Go to report" check box.
• Specify a report: select the required report(ParameterizedReport) from the drop-down list.
• Click on "Add" button then add the parameter.
• After that click on "OK" button.
•  Click on "Preview" then report will generate.

• If we click on Dept_ID (10,20,30 or 40) then that particular department related employee information will open.
• Now i am going to click on "20" then the 20th dept related employee information should display.

 

•  Linked report is generated as expected.

Sub Reports

1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17

    A main report that is a container for multiple related reports is called sub report.

Example:

• Report1: MultivaluedParameters.
• Report2: DeptReport

The Above two report i will call into a new report.

• In the Same solution add a new report with the name "SubReport".

• Right click on Parameters.

•  Select "Add Parameters.." then "Report Parameter Properties" will open.

• Give the required names (Highlighted in the above screen). Then click on "OK" button.

• In "Design" Window: Right click and select 'Insert => Subreport.

•  Then Sub report will insert.

• Right click on the "Subreport" and select "Subreport Properties".

• Then "Subreport Properties" window will open.

• Name: Give the required name (DepartmentReport).
• Use this report as a subreport: Select the required report (DeptReport) from the drop-down list.
• Go to 'Parameters' section.

• Click on 'Add' button, then 'Name and Value' will enable. Then give the parameter details and then click on "OK" button.

• One sub report (DeptReport) is added. Same way Add second report (MultivaluedParameters).

• Go preview section and give the parameter value, and then click on "View Report" button. then report will generate.

Parametrized report using Department Table

• In the Existing solution add a new report with the name "DeptReport".

• Right click on Parameters.

•  Select "Add Parameters.." then "Report Parameter Properties" will open.

• Give the required names (Highlighted in the above screen). Then click on "OK" button.

• Create "Data Source" and "Dataset". While dataset creation use Dept table instead of Employee table.
• Query: 
• SELECT        DEPT_ID, Name, Location
  FROM            DEPT
  WHERE DEPT_ID IN (@DEPT_ID) 

• Go to "Parameters" selection and click on expressions (Highlighted in the below screen).

• Then "Expression" window will open. Write the below expression
• =IIF(Parameters!DEPT_ID.Value="",-1,Split(Parameters!DEPT_ID.Value,","))

• Then after click on "OK" button.
• In designer window insert a table and map the columns.

• Click on "Preview" and pass the variable values and then click on "View Report" button. Then report will generate.

Sample Employee and Department tables creation

Dept Table:

CREATE TABLE [dbo].[DEPT](

      [DEPT_ID] [int] NOT NULL,

      [Name] [varchar](30) NULL,

      [Location] [varchar](30) NULL,

 CONSTRAINT [PK_DEPT] PRIMARY KEY CLUSTERED

(

      [DEPT_ID] ASC

))

INSERT INTO DEPT VALUES

(10,'Design','Hyd'),

(20,'Sales','Bang'),

(30,'Accounts','Che'),

(40,'Maintenance','Pune'),

(50,'Engineering','Delhi');

Employee Table:

CREATE TABLE [dbo].[EMPLOYEE](

      [ID] [int] NOT NULL,

      [FirstName] [varchar](30) NULL,

      [LastName] [varchar](30) NULL,

      [Gender] [char](1) NULL,

      [Designation] [varchar](10) NULL,

      [ManagerID] [int] NULL,

      [Dept_ID] [int] NULL,

      [Salary] [decimal](18, 4) NULL,

      [Commission] [decimal](18, 4) NULL,

      [HireDate] [date] NULL,

 CONSTRAINT [PK_EMPLOYEE] PRIMARY KEY CLUSTERED

(

      [ID] ASC

));

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE FOREIGN KEY (Dept_ID) REFERENCES DEPT(Dept_ID);

INSERT INTO EMPLOYEE VALUES (101,'Charan','Yagenti','M','Developer',105,10,4000,NULL,'2001-05-21')

INSERT INTO EMPLOYEE VALUES (102,'Roja','Mare','F','Developer',105,10,3500,NULL,'2005-03-18')

INSERT INTO EMPLOYEE VALUES (103,'Mahi','Jagarla','M','Sales man',104,20,3000,500,'2011-05-23')

INSERT INTO EMPLOYEE VALUES (104,'Puja','Dama','F','Manager',110,20,8000,NULL,'2009-05-19')

INSERT INTO EMPLOYEE VALUES (105,'Vijay','Kanti','M','Manager',110,10,10000,NULL,'2001-10-30')

INSERT INTO EMPLOYEE VALUES (106,'Krishna','Konda','M','Sales man',104,20,2500,600,'2010-09-05')

INSERT INTO EMPLOYEE VALUES (107,'Prabhaker','Venna','M','Account',109,30,8000,NULL,'2006-03-11')

INSERT INTO EMPLOYEE VALUES (108,'Nagini','Nagavara','F','Account',109,30,8500,NULL,'2000-11-25')

INSERT INTO EMPLOYEE VALUES (109,'Giri','Pinniboina','M','Manager',110,30,15000,NULL,'2008-04-04')

INSERT INTO EMPLOYEE VALUES (110,'Raja','Pedaraju','M','Manager',Null,40,20000,NULL,'2000-01-10')

INSERT INTO EMPLOYEE VALUES (111,'Charan','Jammi','M','Developer',105,10,6000,NULL,'2007-10-25')