Saturday 25 February 2017

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

1 comment: