SSRS – Collapsible Grouping

Introduction

Hello all. I am not generally writing SSRS related article. But to full fill the request of my close friend, i am trying to write something. This article is related to How to make a simple collapsible report by SSRS.

Base Table

-- Create Table

CREATE TABLE [dbo].[tbl_AccountType]

  (

     TypeID     INT             NOT NULL PRIMARY KEY,

     TypeName   VARCHAR(50)     NOT NULL

  );

-- Insert Records

INSERT INTO [dbo].[tbl_AccountType]

  (TypeID, TypeName)

VALUES(101, 'Type-A'),

      (102, 'Type-B'),

      (103, 'Type-C');

-- Create Table

CREATE TABLE [dbo].[tbl_AccountHolder]

  (

     AccountID  INT             NOT NULL PRIMARY KEY,

     HolderName VARCHAR(50)     NOT NULL,

     TypeID     INT             NOT NULL

  );

ALTER TABLE [dbo].[tbl_AccountHolder]

ADD CONSTRAINT FK_TypeID

FOREIGN KEY(TypeID) REFERENCES [dbo].[tbl_AccountType](TypeID);

INSERT INTO [dbo].[tbl_AccountHolder]

       (AccountID, HolderName, TypeID)

VALUES  (1, 'Joydeep Das', 101),

        (2, 'Deepasree Das', 101),

        (3, 'Shipra Das', 101),

        (4, 'Debleena Bhattacharya', 102),

        (5, 'Priya Bannerjee', 102),

        (6, 'Atanu Bannerjee', 102),

        (7, 'Shilpa Shethi', 103),

        (8, 'Soni Shethi', 103),

        (9, 'Sonal Mishra', 103);

SELECT  b.TypeName, a.AccountID, a.HolderName

FROM    [dbo].[tbl_AccountHolder] As a

        INNER JOIN [dbo].[tbl_AccountType] As b

           ON a.TypeID = b.TypeID;

Output:

TypeName	AccountID	HolderName
Type-A	1	Joydeep Das
Type-A	2	Deepasree Das
Type-A	3	Shipra Das
Type-B	4	Debleena Bhattacharya
Type-B	5	Priya Bannerjee
Type-B	6	Atanu Bannerjee
Type-C	7	Shilpa Shethi
Type-C	8	Soni Shethi
Type-C	9	Sonal Mishra

We want a Toggle Group (Collapsible one)

How We Do it

Step – 1 [ Create Data Source and Data Set ]

Step – 3 [ Add Table and Define the columns ]

Please look at the Group section. The Details group is already added

Step – 4 [ Add a Parent Group ]

Click on the Details Group section Right click à Add Group à Parent Group

Step – 5 [ Set Detail Group Visibility ]

Right Click on Detail Group à Group Properties

Observation

Hope it’s OK.

Posted by: MR. JOYDEEP DAS