CODEWITHSUNDEEP
reporting-servicesssrs-2012ssrs-tablix
Jason
Jason

Reputation: 5

SSRS: Grouping a List report with a chart

I am trying to create a list type report and can't seem to get the groupings correct. I am using VS 2017.

Here is the data set:

student_id  full_name   dob email   note    session_desc    Percentage
6   Test Student    2008-07-24  Sample email    Sample Note Main Session 1  59.5
6   Test Student    2008-07-24  Sample email    Sample Note Main Session 2  61.5
6   Test Student    2008-07-24  Sample email    Sample Note Main Session 3  66.9
6   Test Student    2008-07-24  Sample email    Sample Note Main Session 4  64.9

I am trying to get the report to look like this (I cannot embed images):

At the top I have a rectangle with the parent group:

                Test Student1
                DOB: 1/1/2000
                Email: [email protected]

And then inside another list or rectangle below, I want the detail records:

Main Session1   59.5%        Line chart here from 0-100
Main Session2   61.5%        Line chart here from 0-100
Main session3   66.9%        Line chart here from 0-100
Main Session4   64.9%        Line chart here from 0-100

This format would repeat for each student in the dataset. I am trying to use lists, but everything I have tried repeats the Student,dob,email info for the detail records.

Upvotes: 0

Views: 142

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21683

I started with a larger version of your sample data so it included two students. Here's the dataset query I used to generate the data

note when I recorded the GIF I had the percentage column with the wrong number of decimals (decimal(5,2) instead of decimal(5,3)) hence why there are no decimals in the final output.

DECLARE @t TABLE(student_id int, full_name varchar(50),  dob date, email varchar(100),   note varchar(100),    session_desc varchar(100), Percentage decimal(5,3))
INSERT INTO @t VALUES
(6, 'Test Student', '2008-07-24', 'Sample email', 'Sample Note', 'Main Session 1', 0.595), 
(6, 'Test Student', '2008-07-24', 'Sample email', 'Sample Note', 'Main Session 2', 0.615), 
(6, 'Test Student', '2008-07-24', 'Sample email', 'Sample Note', 'Main Session 3', 0.669), 
(6, 'Test Student', '2008-07-24', 'Sample email', 'Sample Note', 'Main Session 4', 0.649),
(7, 'Test Student 2', '2008-07-01', 'Another email', 'Another Note', 'Main Session 1', 0.495), 
(7, 'Test Student 2', '2008-07-01', 'Another email', 'Another Note', 'Main Session 2', 0.515), 
(7, 'Test Student 2', '2008-07-01', 'Another email', 'Another Note', 'Main Session 3', 0.569), 
(7, 'Test Student 2', '2008-07-01', 'Another email', 'Another Note', 'Main Session 4', 0.549)

SELECT * FROM @t

other than that I changed the percentages to be the correct decimal version (e.g. 0.5 = 50%), it's easier to work with and format in the report.

I didn't know what you meant by line chart so I added a databar to represent the percentage, I set the max value to 1 as our percentages are now all between 0 and 1

This clip starts after I added the dataset with the sample data and added a simple table. When you watch, the yellow highlights are left-clicks and the red highlights are right-clicks.

As you can seem even with a bit of clean up at the end the whole process take about 3 minutes to get the basic report running.

If you want to download the gif to watch it later or fullscreen, it's here

https://i.sstatic.net/22Fl2.gif

Hope this helps you in the future.

enter image description here

Upvotes: 1

Jason
Jason

Reputation: 5

I figured it out. Instead of creating a parent row group from the detail group, I have to add a group expression to the detail group itself!

SSRS takes some getting used to - not the easiest to follow at first

Upvotes: 0

Related Questions