Solved

SSRS to Mail Merge all details(students) on one page

Posted on 2016-09-13
6
85 Views
Last Modified: 2016-10-05
Looking for some help on a mail merge process.  I would like to group all students into one page however when I run the mail merge all the students have their own page instead of being grouped by school.  

I have the report grouped by school then by group by child on exam description then group by child on the exam date.  


When I go to mail merge in word it will list out the students as individuals.  Where do I add the group by school so all students who fit that exam description & exam date  are under the school.  Where is this option at?  Is this a word mail merge,  SSRS report builder, or done in the sql query itself?


select CONVERT(varchar,ME.members_exams_examdate, 101) as exam_date,
		mem.members_firstname, mem.members_lastname, 
		me.members_exams_id, me.members_exams_score,  me.members_exams_examdate,
		ex.exams_description, 
		sc.schools_name, sc.schools_city, sc.schools_state	
from members_exams as me
join exams as ex
on me.exams_id = ex.exams_id
join schools as sc
on me.schools_id = sc.schools_id
join members as mem
on me.members_id = mem.members_id

Open in new window



This is what the report look like:  
Report.JPG
This is what I am able to get from the mail merge:
Single.JPG
This is what I am looking for :
group.JPG

Thanks for your help,
0
Comment
Question by:lovettjay
  • 3
  • 2
6 Comments
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points (awarded by participants)
ID: 41797367
I think you should do all the operations in the SQL itself. that should help,
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41797369
If you can share the table details , I can write the complete query for you !
0
 

Author Comment

by:lovettjay
ID: 41797944
Hello Pawan,

select CONVERT(varchar,ME.members_exams_examdate, 101) as exam_date,
            mem.members_firstname, mem.members_lastname,
            me.members_exams_id, me.members_exams_score,  
            ex.exams_description,
            sc.schools_name, sc.schools_city, sc.schools_state      
from members_exams as me
left join exams as ex
on me.exams_id = ex.exams_id
join schools as sc
on me.schools_id = sc.schools_id
join members as mem
on me.members_id = mem.members_id

Thanks,

Jay
0
 

Author Comment

by:lovettjay
ID: 41801635
Here is the SSRS table:

SSRS table
The Row Groups:
Row-Groups.JPG
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41829523
Closing this.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question