CODEWITHSUNDEEP
reporting-services
Meraj
Meraj

Reputation: 426

SSRS Grouping where details start in next row

I would like a Table in SSRS where the Grouped column value is in one Row and Details will start in next Row, But Details will start just below the group Column like in the image below, I have tried a lot but haven't found a solution.

enter image description here

Upvotes: 0

Views: 212

Answers (1)

Sam
Sam

Reputation: 7313

An ideal way to do this would be to add a Row Number into your query that is grouped by your Name and first value.

SELECT ROW_NUMBER() OVER(PARTITION BY Name, val1 ORDER BY Name DESC) RowNumber
, * FROM 
(
SELECT 'Ram' Name, 'Math' val1, 100 val2 UNION
SELECT 'Ram' Name, 'Math' val1, 80 val2 UNION
SELECT 'Ram' Name, 'Hindi' val1, 100 val2 UNION
SELECT 'Ram' Name, 'Hindi' val1, 71 val2 UNION
SELECT 'Shyam' Name, 'Math' val1, 100 val2 UNION
SELECT 'Shyam' Name, 'Math' val1, 85 val2 UNION
SELECT 'Shyam' Name, 'Hindi' val1, 100 val2 UNION
SELECT 'Shyam' Name, 'Hindi' val1, 76 val2
)data

This give the below results:

╔═══════════╦═══════╦═══════╦══════╗
║ RowNumber ║ Name  ║ val1  ║ val2 ║
╠═══════════╬═══════╬═══════╬══════╣
║         1 ║ Ram   ║ Hindi ║   71 ║
║         2 ║ Ram   ║ Hindi ║  100 ║
║         1 ║ Ram   ║ Math  ║   80 ║
║         2 ║ Ram   ║ Math  ║  100 ║
║         1 ║ Shyam ║ Hindi ║   76 ║
║         2 ║ Shyam ║ Hindi ║  100 ║
║         1 ║ Shyam ║ Math  ║   85 ║
║         2 ║ Shyam ║ Math  ║  100 ║
╚═══════════╩═══════╩═══════╩══════╝
  • Then if you set up your column group to group by RowNumber field.
  • Name as the parent group, val1 as the child group.
  • Add a total to the child group, select before.
  • Delete any unwanted columns but leave the grouping in tact.

Table example1

Then you can get the below result:

Table example2

Upvotes: 1

Related Questions