Tabbed Report in SSRS using Bookmark

Here we will see how to create tabbed report in SSRS.

Step 1: Create following SQL table to work on

IF OBJECT_ID(N’dbo.EMPDetails’,N’U’) IS NOT NULL
DROP TABLE dbo.EMPDetails
CREATE TABLE dbo.EMPDetails (
EmpId int,
EmpName nvarchar(50),
Location nvarchar(500),
EmpSalary int
)

INSERT INTO dbo.EMPDetails
VALUES
(11,’Emp1′,’Chennai’,1000),(12,’EMP2′,’Chennai’,1000),
(13,’Emp3′,’Bangalore’,1000),(14,’Emp4′,’Mumbai’,1000),
(15,’Emp5′,’Hyderabad’,1000),(16,’Emp6′,’Hyderabad’,1000),
(17,’Emp7′,’Chennai’,1000),(18,’Emp8′,’Chennai’,1000),
(19,’Emp9′,’Bangalore’,1000),(101,’Emp1′,’Mumbai’,1000),
(111,’Emp11′,’Hyderabad’,1000),(112,’Emp12′,’Chennai’,1000),
(113,’Emp13′,’Chennai’,1000),(114,’Emp14′,’Hyderabad’,1000),
(115,’Emp15′,’Bangalore’,1000),(116,’Emp16′,’Chennai’,1000),
(117,’Emp17′,’Hyderabad’,1000),(118,’Emp18′,’Chennai’,1000),
(119,’Emp19′,’Mumbai’,1000),(120,’Emp2′,’Hyderabad’,1000),
(121,’Emp21′,’Bangalore’,1000),(122,’Emp22′,’Hyderabad’,1000),
(123,’Emp23′,’Mumbai’,1000),(124,’Emp24′,’Bangalore’,1000),
(125,’Emp25′,’Bangalore’,1000),(126,’Emp26′,’Hyderabad’,1000),
(127,’Emp27′,’Bangalore’,1000),(128,’Emp28′,’Mumbai’,1000),
(129,’Emp29′,’Hyderabad’,1000),(130,’Emp3′,’Hyderabad’,1000),
(131,’Emp31′,’Mumbai’,1000),(132,’Emp32′,’Bangalore’,1000),
(133,’Emp33′,’Bangalore’,1000),(134,’Emp34′,’Bangalore’,1000),
(135,’Emp35′,’Hyderabad’,1000),(136,’Emp36′,’Chennai’,1000)

Step 2:

Create Shared Data source and a Data set for each location as follows:


Step 3:

Add images (you can also use background color instead if it) as below:

Here are the images I have added above. (You can create these from excel via Insert -> Shapes and save)

Step 4:

  • Create textbox for each location as shown.
  • Also, create Page Header by right click -> Insert -> Page Header. Place a rectangle and textbox and name as shown below


Step 5:

Select textbox and press F4 for properties. Under Fill -> BackgroundImage, give Source as Embedded and Value as Selected (Image Name). Also give BackgroundColor to it. (You can also customize font used)


Repeat above step for all locations and it will now look as:


Step 6:

Place a rectangle over it as below and give Bookmark value as Bkm_

Step 7:

Add table from toolbox with values of Chennai as follows:


With all values filled and some customization your data will be as:

Repeat step 4 to step 7 for all locations.

Step 8:

Now the report will be as below:

Step 9:

Select the rectangle except the first one and select “Add a page break before” option.

Repeat this for all rectangles on each location.

Step 10:

For each text box select action and give action as “Go to bookmark” and Select bookmark value as Bkm_. Repeat this for all location textboxes.

Step 11:

Select each rectangle and give PageName as its Location Name highlighted. Repeat it for each location.


Step 12:

Run the report.

On click of each location name it will take to desired detailed page as below:

Export it to excel


Excel will be generated with location name in each tab. Also, tab functionality will work here. J


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s