You have created a report, the report has month name as parameter, What is best way to provide values for month name parameter?

As we have fixed set of month names and they don't change. we can add the month names as static values in report for Month Name Parameter.

If the values of parameter does change often, it is good idea to insert them in a table and use Query to extract them for Parameter in report. By doing that we don't have to make changes in report every time if we have to add or remove value. We can simply insert or delete value/s in table those will be extracted by query.

You can use below script to create table for month Names.

Create Table dbo.rpt_MonthName ( MonthName VARCHAR(50))
go
insert into dbo.rpt_MonthName values
('January'),('February'),('March'),('April'),
('May'),('June'),('July'),('August'),
('September'),('October'),('November'),
('December')

Select * from dbo.rpt_MonthName

Add Month Name Parameter Values in SSRS Report - SQL Server Reporting Services(SSRS) Interview Questions and Answers

SSRS-What are Cascading parameters in SSRS report?

Cascading Parameters are set of parameters in which values of one Parameters depends on the value choose in another parameters.

It is always good idea to explain your answer with examples in interview. Let's consider below example.
We have a single value parameter call Region and then on selection of Region Parameter value, we would like to display Countries related to that region in Country Parameter.

The Region Parameter, you can provide static values or you can get the values from query. If more regions can be added or removed, it is good idea to get values for Parameter by using query. Let's say our table name is dbo.TotalSale which has Region, Country and other columns.


Main Query for Report

SELECT FName
    ,LName
    ,SaleAmt
    ,Region
    ,Country
FROM dbo.TotalSale
WHERE Region = @Param_Region
    AND Country IN (@Param_Country)

Query for @Param_Region Parameter ( Single Value Parameter)

SELECT DISTINCT Region
FROM dbo.TotalSale

Query for @Param_Country ( Multi Value parameter). Notice that we have used @Param_Region Parameter to get only countries related to that Region.

SELECT DISTINCT Country
FROM dbo.TotalSale
WHERE Region = @Param_Region

SSRS-What are range parameters in SSRS Report

Range parameters are used to choose a range of values. Let's say we would like to see data between two dates. We can create two parameters StartDate and EndDate.

The query to create date range parameters is shown below

SELECT FName
    ,LName
    ,SaleAmt
FROM dbo.TotalSale
WHERE SaleDate BETWEEN @StartDate
        AND @EndDate

We often use Range Parameters and configure them with default values to show records e.g

• Get records from start of the year to date
• Get records from start of month to date
• get all records for current quarter etc.

SSRS-What should be the data type for Radio Button Parameter Type in SSRS Report

To create Radio Button Parameter in SSRS Report, The data type should be set to boolean for Parameter.


After writing the query for your report by using bit type column, you have to go to Parameter properties and then set the Parameter data type to boolean to show Radio Buttons, Otherwise you will see a text box for parameter value.

Query for report with Single Value parameter, remember IsActive is Bit type Column in table.

SELECT *
FROM dbo.TotalSale
WHERE IsActive = @Param_IsActive

Text Box will show up as we have not set the Parameter Data Type to Boolean

Let's go ahead and change the Data type of Param_IsActive Parameter to Boolean

Run the report and you will see now you have Radio Button Parameter. 

Merge Statement in SQL 2008

Merge command is a new feature introduced in the SQL Server 2008. 

• It can perform Update, Insert and delete operations at a single statement which means all the data is processed and read only once instead of three times(In case of Insertion, deletion and update statements.)

•  It has a target table and Source table. These two tables are join based on a conditions and depending upon whether the Condition is matched or not, Update, Insertion and Deletion Operations are performed.

The Syntax of the Merge command is given below:-


MERGE  [AS TARGET]
USING  [AS SOURCE]
ON 
[WHEN MATCHED 
THEN  ] (UPDATE Target table from Source)
[WHEN NOT MATCHED [BY TARGET]
THEN  ](INSERT Target table from Source)
[WHEN NOT MATCHED BY SOURCE
THEN  ];(DELETE Target table)

; semi colon is important in end of merget statement.


select 's' source,* from Import_Employee


select 't' target,* from employee

MERGE employee AS t

USING Import_Employee as s

on t.EmployeeNumber=s.empno

When matched then 

update set T.Firstname=s.Firstname,T.Lastname=s.Lastname

When not matched by target then

insert(EmployeeNumber,firstname,lastname)

values(s.empno,s.firstname,s.lastname)

when not matched by source then

delete;

Writing Merge Command in SQL Server 2008

After Executed Merge command See the results

Can we able to create Clustered Index on Alpha Numeric Column or Not Primary Key Column in Table?

No You cant able to create Clustered Index Key on Varchar/AlphaNumberic/Non Primary Key column but u can able to create Non Clustered Index Key but it will not show u while u select column

Actually It can able to create it on table but table scan only happened while you select table bcos table cant physically order table using alpha numeric column

If you select table after created clustered index it will show you table scan only see below

If alpha Numeric column would be non duplicate value then we can able to create clustered index key

To find out what still needs to be replicated in Replication Monitor

To find out what still needs to be replicated, we could use both Replication Monitor as well as T-SQL commands to find out how what needs to be replicated to the subscriber database. Both options assume transactional replication is already configured in your environment.

Option 1: Using Replication Monitor

In SQL Server Management Studio (SSMS), navigate to Replication and right click and select 'Launch Replication Monitor'. Go to your listed server and expand it. Click on your publication and on the right side pane under the 'All Subscriptions' tab, go to your subscription and double click on it. Once done, you would see this window.

From this window, you could see the details of the activities that are occurring between the 'publisher-distributor' and 'distributor-subscriber' combinations. Click on the respective tabs to check the status of replication. If there are any replication commands that remain to be applied at the subscriber from the distribution database you would see the details in the 'Undistributed commands'. From this tab, you would get an idea of estimated time remaining to apply commands at the subscriber. Below is a sample screenshot.

Replication Monitor gives you this simple interface to identify any issues you may face with your replication configuration. For more details related to replication latency, we could use some replication commands as shown below.

Option 2: Using Replication commands

Run this on publisher database
To check if replication is fine, we could run sp_repltrans on the publisher database. This displays the undistributed commands present in the publisher database. If your log reader agent is scheduled to run continuously and if this command returns no rows, replication is fine on the publisher side. However, if your log reader agent is scheduled to run at intervals and there are changes that need to be sent to the distribution database, you would see rows returned when you execute (during the interval) this procedure which shows the LSNs of the transactions. See sample screenshot below.

On a busy server, if the log reader agent is running continuously, the rows that are returned after executing sp_repltrans would be constantly changing based on the load. By using this procedure, you wouldn't get the exact command that is pending but you would get an idea on the progress that is happening in your replication environment.
Run this on distribution database
The distribution database contains the system tables - MSrepl_commands and MSrepl_transactions which contain details of the replicated commands. Here is a sample output of a select query on these system tables.

select * from distribution.dbo.MSrepl_commands
select * from distribution.dbo.MSrepl_transactions

From the screenshot, we could see that the command column is of varbinary datatype and is non-readable. Hence, we could execute thesp_browsereplcmds system procedure in the distribution database with the relevant parameters and retrieve the data of the replicated commands in a user friendly manner.
In the above example, if we need to find out the actual command corresponding to xact_seqno = '0x00000085000002A10003' and command_id = 1, we could execute sp_browsereplcmds with these parameters. See screenshot and sample script below.

Use distribution
exec sp_browsereplcmds  @xact_seqno_start = '0x00000085000002A10003', 
                        @xact_seqno_end =   '0x00000085000002A10003', 
                        @publisher_database_id = '1', -- run sp_helppublication on publisher database                
                        @command_id = '1' -- command_id in MSrepl_commands table distribution database   

Here, we could see the actual command that was executed which was an insert statement. It is recommended to execute this procedure with the appropriate parameters otherwise it could result in generation of a huge number of rows as output.
In option 1, using replication monitor, we saw how to view the 'undistributed commands'. The same data could be obtained by executingsp_replmonitorsubscriptionpendingcmds in the distribution database. Use sample script below.

sp_replmonitorsubscriptionpendingcmds  
 @publisher ='Enter publisher server name',
 @publisher_db = 'Enter publisher database name',
 @publication ='Enter name of publication',
 @subscriber ='Enter subscriber server name',
 @subscriber_db ='Enter subscriber database name',
 @subscription_type ='0' --0 for push and 1 for pull 

Below is a sample output.

Testing

In your test replication environment, you could stop the distribution agent job and run a few insert commands in the publisher database. Once done, execute this procedure sp_replmonitorsubscriptionpendingcmds in the distribution database to find out the details of the pending commands to be applied on to the subscriber. Then proceed to query the MSrepl_commands and MSrepl_transactions system tables in the distribution database to determine the actual commands that are yet to be sent to the subscriber. Using MSrepl_transactions system table you could get the time you had executed the insert statements in your publisher database. Then make use of the sp_browsereplcmds as shown above to find out the full text of commands by providing the appropriate parameters. This would give an idea of the actual commands that are yet to be replicated to the subscriber.
You can also use this script to query the distribution system tables.

select rc.publisher_database_id, rc.xact_seqno, rc.command, rt.entry_time 
   from MSrepl_commands rc, MSrepl_transactions rt
    where rc.xact_seqno =  rt.xact_seqno 

It should be noted that the data available in MSrepl_commands, MSrepl_transactions, sp_browsereplcmds is purged periodically based on the schedule of the distribution clean up job and the distribution retention period.

Solve The transaction log for database Database is full

The transaction log for database BizTalkMsgBoxDb is full - Steps to fix this error
Introduction

In this article we will take a look at the steps which you need to follow when you receive “The transaction log for database ‘BizTalkMsgBoxDb’ is full” error.

Error Message:

The transaction log for database ‘BizTalkMsgBoxDb’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (.Net SqlClient Data Provider)

First thing would be to confirm the log space used by the BizTalkMsgBoxDb database. You can find this information by executing the below script.

DBCC SQLPERF(LOGSPACE)
GO

In the above snippet you could see that the log file of BizTalkMsgBoxDb database is 100% full.

How to fix Transaction Log for database BizTalkMsgBoxDB is full error message

To resolve this issue execute the below mentioned script which changes BizTalkMsgBoxDb database to Simple Recovery Model, Shrinks the Transactional Log file to 1 MB in Size and then Set the Recovery Model back to Full. Learn More about Different Recovery Models in SQL Server.

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY SIMPLE;
GO

Use BiztalkMsgBoxDb
GO
DBCC SHRINKFILE (BiztalkMsgBoxDb_log, 1);
GO

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL
GO

How to Verify Current Size and Space Used by the BizTalkMsgBoxDb Database Transaction Log File

To verify the current size and space used by the transactional log file of BizTalkMsgBoxDb database. One can either run SP_HELPDB command or DBCC SQLPERF(LOGSPACE).

/* Method One */

Use Master
GO
SP_HELPDB BiztalkMsgBoxDb
GO

/* Method Two */

DBCC SQLPERF(LOGSPACE)
GO

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Solution:

Starting with SQL Server 2008 you must always put a semicolon before WITH statement in CTE

But it is not need on below SQL version of SQL 2008 like SQL 2005,SQL 2000

Code:
create table #t
(
  pName Varchar(20),
  DateBegin DateTime,
  DateEnd DateTime
)

Insert Into #t(pName, DateBegin, DateEnd)
Values('Player1', '01/04/2012', '01/05/2012')

Insert Into #t(pName, DateBegin, DateEnd)
Values('Player2', '02/01/2012', '02/05/2012')


With DateRange(dt) As
(
    Select Convert(Datetime, '01/01/2012')
    UNion All
    Select DateAdd(dd, 1, Dat.dt) From DateRange Dat Where Dat.dt < CONVERT(Datetime, '01/31/2012')
)

Select T.pName, Dt.dt from #t T
Inner Join DateRange Dt on Dt.dt BETWEEN T.DateBegin and T.DateEnd

Drop Table #t

Correct Code:

Code:
create table #t
(
  pName Varchar(20),
  DateBegin DateTime,
  DateEnd DateTime
)

Insert Into #t(pName, DateBegin, DateEnd)
Values('Player1', '01/04/2012', '01/05/2012')

Insert Into #t(pName, DateBegin, DateEnd)
Values('Player2', '02/01/2012', '02/05/2012')


;With DateRange(dt) As----------------------------------To fix error added semicolon on SQL 2008
(
    Select Convert(Datetime, '01/01/2012')
    UNion All
    Select DateAdd(dd, 1, Dat.dt) From DateRange Dat Where Dat.dt < CONVERT(Datetime, '01/31/2012')
)

Select T.pName, Dt.dt from #t T
Inner Join DateRange Dt on Dt.dt BETWEEN T.DateBegin and T.DateEnd

Drop Table #t

Deploy SSRS Report Server Project

To deploy Report Server Project, please go through below steps:

1. Go to Report Service Configuration Manager then click on the Report Manager URL tab in order to get Report Server URL.


2. Now click on the Report Manager URL. We will get a empty screen as below:


3. Let us query catalog table of ReportServer Database.

SELECT * FROM CATALOG

It will show below output:

When we design a report, Report Definition Language File (.rdl) is generated whcih is basically a XML file which contains all the information about our report. So when we deploy the report, the .rdl file gets inserted into the our Report Server Database.

4. . Now come back to our SSRS Project. Right Click the Project and click on properties.

Alternatively we can also go from Project Menu.


5. In both case we will be directed to Project’s Properties Page where we are going to insert our Target Server URL Which is our Report Server Web Service URL.
In my case it is like

http://bhushan-pc:8080/ReportServer

6. After click on OK, right click the Project and select Deploy from the context menu.

7. If all fine we will receive Success Message.

8. If here we query our Catalog table of Report Server database we will get results like following.

SELECT * FROM CATALOG

Here we can see the entries of Our Project and Report.In above result Type 1 means Project and 2 means Report.
If you want to see the XML content, execute following query:
SELECT
[REPORT NAME] = NAME,
[RDL CONTENT] = CAST(CAST(CONTENT AS VARBINARY(MAX)) AS XML)
FROM CATALOG
WHERE TYPE = 2
You will get results like following:

9. Now let us see our report from the browser.Go to Report Manager URL and you will get a screen like below:

10. Now click on StartSSRS and it will show the list of report.In our case it will show only 1 report which is PersonAddresDetails.

11. Now Click on PersonAddresDetails Report and you will get following screen:

Congratulations! Our First SSRS Project is successfully deployed.

Moving the Report Server Databases to Another Computer

You can move the report server databases that are used in a current installation to a SQL Server Database Engine instance that is on a different computer. Both the reportserver and reportservertempdb databases must be moved or copied together. A Reporting Services installation requires both databases; the reportservertempdb database must be related by name to the primary reportserver database you are moving.

Moving a database does not effect scheduled operations that are currently defined for report server items.

Schedules will be recreated the first time that you restart the Report Server service.

SQL Server Agent jobs that are used to trigger a schedule will be recreated on the new database instance. You do not have to move the jobs to the new computer, but you might want to delete jobs on the computer that will no longer be used.

Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes.

Temporary report and user session data that is stored in reportservertempdb are persisted when you move that database.

SQL Server provides several approaches for moving databases, including backup and restore, attach and detach, and copy. Not all approaches are appropriate for relocating an existing database to a new server instance. The approach that you should use to move the report server database will vary depending on your system availability requirements. The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, but you must run Transact-SQL commands to perform the operations. Copying the database is not recommended (specifically, by using the Copy Database Wizard); it does not preserve permission settings in the database.

Important 
The steps provided in this topic are recommended when relocating the report server database is the only change you are making to the existing installation. Migrating an entire Reporting Services installation (that is, moving the database and changing the identity of the Report Server Windows service that uses the database) requires connection reconfiguration and an encryption key reset. For more information about migrating a Reporting Services installation, see Migration (Reporting Services).


Detaching and Attaching the Report Server Databases
--------------------------------------------------------------------------------

If you can take the report server offline, you can detach the databases to move them to the SQL Server instance you want to use. This approach preserves permissions in the databases. If you are using a SQL Server 2008 database, you must move it to another SQL Server 2008 instance. After you move the databases, you must reconfigure the report server connection to the report server database. If you are running a scale-out deployment, you must reconfigure the report server database connection for each report server in the deployment.

Use the following steps to move the databases:

1.Backup the encryption keys for the report server database you want to move. You can use the Reporting Services Configuration tool backup the keys.

2.Stop the Report Server service. You can use the Reporting Services Configuration tool to stop the service.

3.Start SQL Server Management Studio and open a connection to the SQL Server instance that hosts the report server databases.

4.Right-click the report server database, point to Tasks, and click Detach. Repeat this step for the report server temporary database.

5.Copy or move the .mdf and .ldf files to the Data folder of the SQL Server instance you want to use. Because you are moving two databases, make sure that you move or copy all four files.

6.In Management Studio, open a connection to the new SQL Server instance that will host the report server databases.

7.Right-click the Databases node, and then click Attach.

8.Click Add to select the report server database .mdf and .ldf files that you want to attach. Repeat this step for the report server temporary database.

9.After the databases are attached, verify that the RSExecRole is a database role in the report server database and temporary database. RSExecRole must have select, insert, update, delete, and reference permissions on the report server database tables, and execute permissions on the stored procedures. For more information, see How to: Create the RSExecRole.

10.Start the Reporting Services Configuration tool and open a connection to the report server.

11.On the Database page, select the new SQL Server instance, and then click Connect.

12.Select the report server database that you just moved, and then click Apply.

13.On the Encryption Keys page, click Restore. Specify the file that contains the backup copy of the keys and the password to unlock the file.

14.Restart the Report Server service.

Backing Up and Restoring the Report Server Databases
--------------------------------------------------------------------------------

If you cannot take the report server offline, you can use backup and restore to relocate the report server databases. You should use the ‘Copy_Only’ option. Beginning in SQL Server 2008, SQL Server Management Studio supports copy-only backups. After you restore the databases, you must configure the report server to use the database on the new server instance. For more information, see the instructions at the end of this topic.

Using BACKUP and COPY_Only to Backup the Report Server Databases
When backing up the databases, set the COPY_OnlyL option and backup type to Full.

Note 
Be sure to back up both the ReportServer and ReportServerTempDB databases as well as their associated log files.


For more information on using SQL Server Management Studio to backup a database, see How to: Back Up a Database (SQL Server Management Studio)

Using RESTORE and MOVE to Relocate the Report Server Databases
When restoring the databases, you can Use the RESTORE WITH NORECOVERY argument to perform the initial restore; this keeps the database in a restoring state, giving you time to review log backups to determine which one to restore. You would then repeat the RESTORE operation but use the RESTORE WITH RECOVERY argument.

Note 
Be sure to restore both the ReportServer and ReportServerTempDB databases as well as their associated log files.


For more information on using SQL Server Management Studio to restore a database, see How to: Restore a Database Backup (SQL Server Management Studio)

How to Configure the Report Server Database Connection
1.Start the Reporting Services Configuration tool and open a connection to the report server.

2.On the Database page, click Change Database. Click Next.

3.Click Choose an existing report server database. Click Next.

4.Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.

5.In Database Name, select the report server database that you want to use. Click Next.

6.In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.

7.Click Next and then Finish.

Note 
A Reporting Services installation requires that the SQL Server Database Engine instance include the RSExecRole role. Role creation, login registration, and role assignments occur when you set the report server database connection through the Reporting Services Configuration tool. If you use alternate approaches (specifically, if you use the rsconfig.exe command prompt utility) to configure the connection, the report server will not be in a working state. You might have to write WMI code to make the report server available. For more information, see Reporting Services WMI Provider.

MONDAY, DECEMBER 20, 2010

SQL Server Reporting Services: Quick way to get 10 digit year (all the zero’s) using String.Format

Dates are fun. See, by default most dates come out like 5/6/2008. But computers, and programs like them formatted as 05/06/2008. That way, all the dates, no matter what month or day, are all the same length, cool huh?

Well, in Reporting Services, if you have a date field coming back in a dataset, and you want to format it as a 10 digit string, there are about 50 different ways to do it. You can use old VBA Left and Mid etc, or you can use String.Format like..

=String.Format(“{0:MM}/{0:dd}/{0:yyyy}”,CDate(Fields!CalendarDate.Value))

MONDAY, SEPTEMBER 27, 2010

Working with Report Snapshots in SQL Server Reporting Services (SSRS)

Problem


We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources. We are always adding new reports and the number of users running these reports is also increasing rapidly. We would like to look at leveraging report snapshots as a way to reduce some of the load on our database servers. Can you provide us with an example of how to do this?

Solution

A report snapshot provides the ability to run a report at a scheduled time or on demand and save the report in the ReportServer database. When a user requests the report, they automatically get the snapshot version stored in the ReportServer database rather than executing the SQL queries to retrieve the data from the underlying data source. You can schedule report snapshots to be run during off-peak hours; this can reduce the load on your database servers. You can also create snapshots manually and select which snapshot version of a report that you would like to run. You can save as many snapshots as you like or you can have Reporting Services limit the number of snapshots that are saved. Besides reducing the load on your database servers, you can use report snapshots to archive reports at a particular point in time for later viewing; e.g. month-end reports, year-end reports, etc. Depending on how many report snapshots you keep, you could use a significant amount of space in the ReportServer database

Creating a Drill Down Report with SQL Server 2008

In SSRS we can generate drill down reports in very easy way. To create Drill down reports, please follow the following steps.
Step1: Create New Report Project with one Shared DataSource with data query for generating report.
Step1: Create report by taking Table type report as shown bellow

Step3: Now Add Group for product by right clicking on Product Column as shown bellow

Select Add Parent group

Selet Product from Group By DropDown and check Add Group Header, then click OK

The report table looks like this

Step 4: From Row groups > select Product group details (group Details and not group header) > go to Group Properties > select Visibility tab > select Hide > click on Display can be toggled by this group item and select the name of the group then click on OK

Then the  preview the report

Step 5:We can also change the drill down and make it reverse by setting ‘InitialToggleState’ to True and by selecting the group properties (header), visibility tab and click Show

Navigating in SQL Server Reporting Services 2008

We can make three types navigations in SQL Server Reporting Services

•  Navigating in same report(Book Marking)
• Navigating to another Report(Jump to Report)
• Navigation Website(Jum to URL)

 Any text box or image item can be used for intrareport or interreport navigation, for navigation to external resources like web pages and documents, and also to send e-mail. All of these features are enabled by using navigation properties that can be specified in the Textbox Properties or Image Properties dialog.

1.Navigating in same report(Book Marking)

This can be achieve with Book Marking in Reports.
Abookmark is a text box or image in a report that can be used as a navigational link. If you want to allow the user to click an item and navigate to another item, assign a bookmark value to each of the target items.

First, open the Textbox Properties dialog by right-clicking the text box and selecting Properties from the pop-out menu. In the Textbox Properties dialog, then switch to the Action tab in left panel.

To enable Hyperlink to a bookmark, set the Go to bookmark property to the target bookmark.

Using bookmarks to navigate within a report is very easy to do. Each report item has a BookMark property that may be assigned a unique value.

After adding bookmarks to any target items, use the Go to Bookmark Selection list to select the bookmark in the Properties for the Source item. This allows the user to navigate to items within the same report.

 2.Navigating to another Report(Jump to Report)

This powerful feature enables a text box or image to be used as a link to another report by passing parameter values to the target report. The target report can consist of a specific record or multiple records, depending on the parameters passed to the target report.

The following example uses a Products by Category report.That means the Products report shows the list of all Products and the Category Report shows the information about perticular Product. Name text box is used to link to a report that will display the details of a single product record. The Product Details report is very simple. This report accepts a ProductID parameter to filter the records and narrow down to the record requested.

First, open the Textbox Properties dialog by right-clicking the text box and selecting Properties from the pop-out menu. In the Textbox Properties dialog, then switch to the Action tab.

Then select the Go to report radio button, and select the target report from the drop-down list. And Add the perameters for the targetting report by clicking on Add button.

When a product name is clicked on the main report, the viewer redirects to the detailed report for the specific product by passing the ProductID parameter value.

3.Navigation Website(Jum to URL)

The Go to URL option can be used to navigate to practically any report or document content on your report server, and files, folders, and applications in your intranet environment or on the World Wide Web. It can also be set to an expression that uses links stored in a database, custom code, or any other values. It’s more accurate to say that any URI (Uniform Resource Identifier) can be used since a web request is not limited only to a web page or document. With some creative programming, queries, and expressions, your reports could be designed to navigate to a web page, document, e-mail address, Web service request, or a custom web application, directed by data or custom expressions.

Click on "Fx" to enter custom Web URL like http://www.google.co.in/ then click on ok.

Once you run the Report and click on the column which you have set "Go to URL", then you will be redirected to that web site.