Drill Through Report in SSRS

Drill Through Report means..
A Drillthrough Report is a report that user opens by clicking a link with in another report.Drillthrough reports commonly contain details about an item that is contained in an original summary report.
or
In Simple words,Based on some action if we are migrating from one report to another report is called Drill Through Report..
Here am showing an example of this...

Take 2 Reports....
Here I Designed Drillthrough and Drillthrough1 Reports...

and

Now ,My Requirement is .., if i click on the Dept name in my Drillthrough Report,then it migrates to the Corresponding Deptno and Comments in Drillthrough Report1..,
for that we follow the below process..,
first you have to create a dummy parameter in DrillThrough1 Report like

then Pass this parameter to query like this,

After this,come to the Main report i.e,DrillThrough Report.., and select the field ,on which you have to impose the action .., and right click on that field,you will get the Textbox Properties option.Click on this..,

after click on the textbox properties you will get the window like this..,

Here.., you go to Action tab and click the radio button Go to report and select the report and click the Add button to match the parameter values,
Here u must match the Parameter value with the actual value from the main report..
i.e,in this report am matching the deptno from Drillthrough Report with deptno from Drillthrough1 Report..
after that click ok..
Now u will find the Drillthrough Report...., actually how it will works

 Now,If u click on the Marketing ,then it migrates to Marketing Details in the Drillthrough1 Report..
like this

How to Display the Foreignkey Relationships and Name of the Constraint for each table in the Database

By using the below Query,we can display the foreign key relationships and name constraint for each table in the database


SELECT
ForeignkeyTable = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PrimarykeyTable = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME

DrillDown Reports in SSRS

The Main Purpose of This article is ,How to use Drill Down Reports in SSRS..,
Before that, we have to know what is meant by Drill down Reports..

     Drilldown Reports are nothing but,With in the same report implementing the functionality of expansion and collapse is called Drill down Report.with this feature we can move from one content to another content with in the report
and also this drill down Reports Provides Visibility and hiding with in the same report ,Generally to analyze the data, bottom to top,top to bottom  in hierarchical fashion .., these reports are helpful

Create a table like this
Create table employee(eid int,ename varchar(30),eaddress varchar(200),sal int,deptname varchar(30))

Eid	Ename	Eaddress	Sal	Deptname
1001	Sharathkumar	Hyderabad	50000	IT
1002	ranivenkat	St.louis	50000	IT
1003	Prasad	Warangal	50000	IT
1004	Satish	Hyderabad	50000	IT

Open BIDS.., Take a New Report and Create a Data Source,assign a Dataset to the Created Datasource.
after that.., take a table on to the Report Design Surface and assign the data to the table,which is to show in the Report,like this

after this See The Preview...

Now.., Which Columns have to be keep in hide.., Select Those Columns like this

Now Press F4.., and change the properties of Hidden to True and Toggle Item to ename(The column name ,Which we are Implementing the Drill Down action) like this

Now,See the Preview...

The Preview will be Like This

Click The Expand Option Provided on the ename Column to view the data inside...

Parameterized Report in SSRS

The Main Purpose of writing this article is to filter the data based on parameter values……

For this.., need to create a table like this

create table employee(eid int,ename vachar(30),designation varchar(20), Salary int,eaddress varchar(200),phonenumber int)

eid	Ename	Designation	Salary	eaddress	Phonenumber
1	Rajesh	Developer	10000	Hyderabad	123456
2	Rani	SSE	100000	USA	012344556
3	Venkat	Manager	100000	USA	090909090

Now My Requirement is to Filter the data by Employee Name…

For that Open BIDS

And Select Report Server Project and add new report..

In Report ..Specify your Data Source

Most of the cases we use Shared Data Source..,

After Specify the data source..,

Create a Dataset…

In Dataset u write your Query like…

Select * from employee

After that drag and drop a table from toolbox..

And specify the columns like this

After assign the columns

Press preview to see the report..

Now…we have to filter the data by employee name..

For that we have a create a parameter and ,specify that parameter in dataset also like this…

Create a new dataset and write the specify the query as

Select ename from employee  ..,because we are filtering based on ename

In Report Parameter Properties…

Specify Report Parameter name.I specified as ename

Specify the Prompt as Select Employee Name

In Available Values Section.., Select Get Values from Query..and assign our new dataset to it.., then specify the value field as column name ename and lable field as column name ename for it..thats it.

Now assign this parameter “ename” in our dataset

Then our query will be like this

Select * from employee where ename =@ename

We assign the parameter to a variable or column by using @ symbol.

Now u see the preview…

Select the employee name and click view report.., the data will filter and the selected employee’s details will be display

SSRS Fundamentals

 SSRS Report Types

We can create different kind of reports using Reporting Services. This article describes the terminology used for the various types of reports and the ways reports can be created and then used. A single report can have multiple characteristics e.g. snapshot reports can be parameterized, ad hoc reports incorporate clickthrough report, and subreports can be linked reports.

With Reporting Services, we can create following types of reports:

• Parameterized reports
• Linked reports
• Snapshot reports
• Cached reports
• Ad hoc reports
• Drilldown reports
• Drillthrough reports
• Subreports

Parameterized reports: A parameterized report uses input parameter to complete report processing. With a parameterized report, you can filter report data based on the values that are set at run time. Parameterized reports are frequently used for filtering reports data.

Linked reports: A linked report is a report that provides an access to an existing report. It is similar to a hiperlink that we use to open a page. A linked report is derived from an existing report and retains the original report definition. We can create a linked report on the report server when we want to create additional versions of an existing report. We can create linked reports whenever we want to deploy an existing report with different settings.

Snapshot reports: A snapshot report contains query results that were retrieved at a specific time. Unlike on-demand reports, which get up-to-date query results when we run the report, snapshots reports are processed on a schedule and then saved to Report Server. When we select a report snapshot to view, Report Server retrieves the stored report from the report server database and shows the data and layout that were captured for the report at the time the snapshot was created.

Report snapshots serve three purposes:

1. Report history - by creating a series of report snapshots, we can build a history of a report that shows data changes over time.
2. Consistency - use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next.
3. Performance - by scheduling large reports to run during off-peak hours, we can reduce processing impact on the Report Server during core business hours.

Cached reports: A cached report is a saved copy of a processed report. These reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports.

Ad hoc reports: An ad hoc report can be created from an existing Report Model using Report Builder. Ad hoc reports refer specifically to Report Builder reports. Ad hoc reports leverage report models and pre-defined templates to enable information workers to quickly and easily explore business data using familiar terminology and data structures that have been implemented in the report model. Ad hoc reports can be saved and run locally or published to a report server, just like other Reporting Services reports.

Drilldown reports: Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Best example of Drilldown report is Sale information for the Year, then drill down for Quarters followed by Months and week. Other example is Sale by Region then drilldown by Countries followed by State and Cities.

Drillthrough reports: Drillthrough reports are standard reports that are accessed through a hyperlink on a report item in the original report. Drillthrough reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart. The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display greater details based on the aggregate in the main report. Drillthrough reports can be filtered by parameters.

Subreports: A subreport displays another report inside the body of a main report. A subreport is used to embed a report within another report. Any report can be used as a subreport. The subreport can use different data sources than the main report. We can set up the parent report to pass parameters to the subreport. Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports