Create 1 calendar for each student
-
Hello!
I'm attempting to replace a clunky mail merge process with SSRS 2016.
The data is the attendance code for a list of students on each school day of a month.
(picture Columns: Student_id, School_Date, Attendance_Code for a given month)
I need to depict their attendance on a calendar, simply placing the contents of Attendance_Code on every School_Date, with one calendar for each student.
Using the info found here: https://sqlserverbi.blog/04-creating-a-calendar-report/, the calendar table is created, the calendar matrix is designed, and it does correctly display the attendance codes, but only for one student.
What do I need to do next in order to generate ONE calendar for each of 500+ students? I SUSPECT I need another grouping somehow but I'm stuck.
Thanks in advance, please let me know if I can provide anything further.
- This topic was modified 2 years, 9 months ago by bravocharlie.
-
Why do you need a separate calendar for each student?
Couldn't you just make Student_id a parameter for the report & query?
-
The finished product will be a rendering of a Florida Department of Education form that will be sent home with the student to get a parent signature, which then must be returned to the school and retained for a few years. Basic student information, the calendar depicting an 'X' for each day they were marked "present", and a couple fields for the signature. The output would be PDF, sent to the respective schools and printed.
While the student ID is part of the dataset, wouldn't making it a parameter mean having to generate each one individually? I like this idea for further testing (Thanks!) but this is roughly 550 students each month.
Here's the letter I wish to generate...the PDF wouldn't attach so it's a screen snip.
Many thanks,
BC
-
Does the attendance table that you're deriving the data from actually have 1 row for every day of the school year for every student or is it missing days, such as Saturdays and Sundays? And to holidays and other non-school days also have an entry for each student?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The attendance table contains records for each school day (roughly 18 per month). So weekends, holidays, etc are not represented. When joined with the calendar table there are nulls represented for the non-school days.
-
Seems like you could do a main/subreport.
Main is based on Student, and then the subreport is based on attendance in a span of time (it has the StudentID as a parameter to join to the parent report).
-
Ok... I don't even know how to spell SSRS. The following creates a test table for others to play with and I use it to create an output that SSRS could use for one student. That output could easily be converted to a stored procedure.
Anything else I could comment on is in <insert drum roll here> the comments in the code. They explain everything. You will need the fnTally function from the article at the similarly named link in my signature line below all this.
Code to build the test table:
--=====================================================================================================================
-- Create and populate a temporary test table.
-- This is NOT a part of the solution. We''re just creating a test table to work with.
--=====================================================================================================================
--===== If the test table already exists, drop it make reruns in SSMS easier.
DROP TABLE IF EXISTS ##Attendance;
GO
--===== Create the test table. It won''t matter much if your datatypes are different but NULLs should be avoided.
CREATE TABLE ##Attendance
(
Student_ID INT NOT NULL
,School_Date DATE NOT NULL
,Attendance_Code CHAR(1) NOT NULL
,PRIMARY KEY CLUSTERED (Student_ID,School_Date) WITH (IGNORE_DUP_KEY = ON)
-- You should add a similar index to your real table. INCLUDE the Attendance_Code for non-clustered indexes.
)
;
--===== Populate the test table with one row per date per student for every day in 2021 except for holidays
-- and weekend. I didn''t exclude the summer months because it doesn''t matter for this testing.
WITH cteGenRows AS
(--==== This generates more than 130 thousand rows in less than 2 seconds for a "full" test of 1 year.
-- Note that the Attendence_Code is randomly generate and no two runs will be the same.
SELECT Student_ID = st.N
,School_Date = DATEADD(dd,dt.N,'2021')
,Attendance_Code = IIF(ABS(CHECKSUM(NEWID())%30) > 0,'P','A') --"P" = Present, "A" = Absemt
FROM dbo.fnTally(1,500) st --See my signature line below for where to get the
CROSS APPLY dbo.fnTally(0,DATEDIFF(dd,'2021','2022')) dt --fnTally function needed for this. (It replaces loops)
)--==== This eliminates weekends and federal holidays and does the actual INSERT.
INSERT INTO ##Attendance WITH (TABLOCK) --TABLOCK is necessary for "Minimal Logging"
(Student_ID,School_Date,Attendance_Code)
SELECT Student_ID,School_Date,Attendance_Code
FROM cteGenRows
WHERE DATEDIFF(dd,0,School_Date)%7 < 5 --Only populate week days and...
AND School_Date NOT IN (--===== exclude Federal Holidays for 2021
'1-Jan-2022'
,'18-Jan-2022'
,'15-Feb-2022'
,'31-May-2022'
,'18-Jun-2022'
,'5-Jul-2022'
,'6-Sep-2022'
,'11-Oct-2022'
,'11-Nov-2022'
,'25-Nov-2022'
,'26-Nov-2022'
,'24-Dec-2022'
)
ORDER BY Student_ID,School_Date --This helps with "Minimal Logging"
OPTION (RECOMPILE) --This helps with "Minimal Logging"
;
-- SELECT * FROM ##Attendance --Run this if you want to see what''s in the table
;
GOCode to solve the problem using T-SQL. Seriously, read the comments.
--=====================================================================================================================
-- Demonstrate a solution that can easily be converted to a stored procedure.
-- It uses a nasty fast process of pivoting data called a CROSSTAB.
-- There is a thing called a "Matrix" in SSRS that does similar as the final SELECT but I don''t use SSRS and so I
-- can''t tell you how to do that. I can, however change the output of the final SELECT to make it so a Matrix
-- can use the data. Let me know if you need to do that.
--=====================================================================================================================
--===== Parameters for a stored procedure
DECLARE @pStudent_ID INT = 250
,@pYear INT = 2021
,@pMonth INT = 10
;
--===== Local Variables
DECLARE @MonthStart DATETIME = DATEFROMPARTS(@pYear,@pMonth,1)
,@NextMonthStart DATETIME = DATEADD(mm,1,DATEFROMPARTS(@pYear,@pMonth,1))
;
--===== More local variables that build on the assigned values from the ones above.
DECLARE @Week1Start DATETIME = DATEADD(dd,DATEDIFF(dd,-1,@MonthStart )/7*7 ,-1) --Always Sunday <= 1st of Month
,@FinalDay DATETIME = DATEADD(dd,DATEDIFF(dd,-8,@NextMonthStart)/7*7-1,-1) --Always Saturday >= Last of Month
;
-- SELECT @MonthStart,@NextMonthStart,@Week1Start,@FinalDay; --This was just for sanity checking during development.
--===== This produces two columns for each day of the week. One for the day of the month and one for the Attendance_Code.
-- They are grouped by and should be sorted by the week number for the month, which is also created and would be used
-- if you end up doing the pivot (CROSSTAB) using a Matrix in SSRS.
WITH
cteCalendar AS
(--===== This takes the place of a Calendar table for the given month.
-- For purposes of this report, we only deal with whole weeks starting on Sunday.
SELECT c.CalendarDate
,WeekOfMonth = DATEDIFF(dd,@Week1Start,c.CalendarDate)/7+1 --Always starts on Sunday
,DoW = LEFT(DATENAME(dw,c.CalendarDate),3) --3 Letter abbreviation of the day name
,DoM = DATENAME(dd,c.CalendarDate)
,IsCurMonth = IIF(c.CalendarDate >= @MonthStart AND c.CalendarDate < @NextMonthStart,1,0)
FROM dbo.fnTally(0,DATEDIFF(dd,@Week1Start,@FinalDay))t
CROSS APPLY (VALUES(DATEADD(dd,t.N,@Week1Start)))c(CalendarDate)
)
,ctePreCrossTab AS
(--==== This "merges" the attendance data with the calendar data.
SELECT *
FROM ##Attendance a
RIGHT JOIN cteCalendar c ON a.School_Date = c.CalendarDate
AND a.Student_ID = @pStudent_ID
)--==== This takes the place of a "Matrix" in SSRS, which I don''t know how to do.
-- If you do, I can greatly simplify this for use with the Matrix.
SELECT ct.WeekOfMonth
,SunDoM = MAX(IIF(ct.DoW = 'Sun' AND IsCurMonth = 1,ct.DoM,''))
,SunAtt = MAX(IIF(ct.DoW = 'Sun' AND IsCurMonth = 1,ct.Attendance_Code,''))
,MonDoM = MAX(IIF(ct.DoW = 'Mon' AND IsCurMonth = 1,ct.DoM,''))
,MonAtt = MAX(IIF(ct.DoW = 'Mon' AND IsCurMonth = 1,ct.Attendance_Code,''))
,TueDoM = MAX(IIF(ct.DoW = 'Tue' AND IsCurMonth = 1,ct.DoM,''))
,TueAtt = MAX(IIF(ct.DoW = 'Tue' AND IsCurMonth = 1,ct.Attendance_Code,''))
,WedDoM = MAX(IIF(ct.DoW = 'Wed' AND IsCurMonth = 1,ct.DoM,''))
,WedAtt = MAX(IIF(ct.DoW = 'Wed' AND IsCurMonth = 1,ct.Attendance_Code,''))
,ThuDoM = MAX(IIF(ct.DoW = 'Thu' AND IsCurMonth = 1,ct.DoM,''))
,ThuAtt = MAX(IIF(ct.DoW = 'Thu' AND IsCurMonth = 1,ct.Attendance_Code,''))
,FriDoM = MAX(IIF(ct.DoW = 'Fri' AND IsCurMonth = 1,ct.DoM,''))
,FriAtt = MAX(IIF(ct.DoW = 'Fri' AND IsCurMonth = 1,ct.Attendance_Code,''))
,SatDoM = MAX(IIF(ct.DoW = 'Sat' AND IsCurMonth = 1,ct.DoM,''))
,SatAtt = MAX(IIF(ct.DoW = 'Sat' AND IsCurMonth = 1,ct.Attendance_Code,''))
FROM ctePreCrossTab ct
GROUP BY WeekOfMonth
ORDER BY WeekOfMonth
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. That looks like a shedload of code. It actually isn't. The forum software nearly double-spaces code. 🙁 🙁 🙁
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good Morning!
1) This is really cool stuff you're showing me, THANK YOU!
2) I created the temp table per the instructions, created fnTally, and enclosed the calendar code into a stored procedure.
3) I created a quick table in SSRS and sure enough, it does depict a calendar style matrix with the month dates and the value in ##Attendance (screen snip attached). I don't like how the days and attendance codes appear in different cells, and merging cells didn't help. What changes would need made to make it work in a matrix?
4) Our attendance data records activity only for "school days", which are found in a "attendance_calendar" table, so holidays and weekends are already omitted.
I am guessing this alteration to include OUR data would look something like this:
WITH cteGenRows AS
(Select
student_id, school_date, attendance_code from attendance_day
where school_date in (select school_date from attendance_calendar where month = 01 and year = 2022)
)
BUT, do I still want to "CROSS APPLY" the fnTally? I'm guessing yes since that includes all dates for the date range.
Many thanks,
BC
Do you see any potential problems with that? Does this gunk up the join?
-
Here's an approach using SSIS that looks promising: https://social.msdn.microsoft.com/Forums/SECURITY/en-US/8c114455-19c3-405f-8234-c83690dc3450/ssrs-export-separate-pdfs-from-ssrs-generated-report-subscriptions?forum=sqlreportingservices
And an approach using Powershell: https://www.sqlservercentral.com/forums/topic/ssrs-export-to-separate-pdfs
Because, as far as I know, SSRS can't export pages as separate files, if you want to use SSRS, I believe you're going to going to need an approach that iterates through the students to produce a separate export for each StudentID.
- bravocharlie wrote:
3) I created a quick table in SSRS and sure enough, it does depict a calendar style matrix with the month dates and the value in ##Attendance (screen snip attached). I don't like how the days and attendance codes appear in different cells, and merging cells didn't help. What changes would need made to make it work in a matrix?
I left the day of the month and the attendance code in separate "cells" so that you could do the formatting in SSRS where each "date cell" can be formatted using more than one source column. What I produced was NOT meant to be the final product. It was meant to drive you formatting 1 report row for each week returned. That much I do know about SSRS.
Change the final SELECT in my code to the following. Again, details are in the comments.
--===== I believe this is what you might need to drive a "Matrix" in SSRS but don't know for sure.
-- The "Matrix" might be able to pivot to a month format without the use of WeekOfMonth and DoW.
-- I just don't know because I don't use SSRS.
SELECT CalendarDate,WeekOfMonth,DoW,Attendance_Code,IsCurMonth
FROM ctePreCrossTab ct
ORDER BY CalendarDate
;bravocharlie wrote:4) Our attendance data records activity only for "school days", which are found in a "attendance_calendar" table, so holidays and weekends are already omitted.
So change the cteCalendar CTE to use your Calendar table instead of generating one on the fly. So the FROM would be your Calendar table instead of fnTally and get rid of the CROSSAPPLY. You'll also need to add a WHERE clause to filter the date from your Calendar table using the @Week1Start and @FinalDay variables
Again, I've never made and SSRS "Matrix" before and so someone else will need to help you there. I only know that they work very much like the CROSSTAB code. Again, it may be that the Matrix capability in SQL Server might have some sort of a "Month Calendar Template" somewhere or you certainly "borrow" one from someone else if you do a search for such a thing.
As the others have said, I don't believe that SSRS will render a PDF for each page and so you're a bit stuck with having to loop through the students and generating the one page per student but, IIRC, that's a fairly easy task.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
One PDF is acceptable to me if I have it sorted correctly (a copy of acrobat pro should allow me to break it up manually),
BUT
I will likely try the SSRS method you linked to break it up on the School field (the schools will do the printing and distributing to students). This will be a big help, thank you.
-
It's looking great right now, sample attached with redactions.
I kept cteCalendar as you designed it, and changed ctePreCrossTab to use my attendance table.
It correctly merges the data correctly, one student at a time.
My next task will be to get it to do that automagically for multiple students.
Thank you for help, I'll post results later.
- bravocharlie wrote:
It's looking great right now, sample attached with redactions.
I kept cteCalendar as you designed it, and changed ctePreCrossTab to use my attendance table.
It correctly merges the data correctly, one student at a time.
My next task will be to get it to do that automagically for multiple students.
Thank you for help, I'll post results later.
That's awesome! Well done.
Unfortunately, I don't know enough about SSRS to continue in your effort (I don't even remember how to fire it up anymore). We need someone that actually knows SSRS well enough to "loop" through each student, run the stored procedure, and get the output you're looking for or show you how to do it with a Matrix for all students and I'm not that guy.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
pietlinden was on a decent track with the main report/sub report idea. I've used it once before, the basic process is:
- Create the SSRS report that will generate the form you need for a single student, taking the student ID (or another identifying field) as a parameter. This is your sub report.
- Create a second report to use as the main report.
- Add a dataset to the main report that will pull the list of all student IDs (or whatever parameter you need for the sub report).
- Add a Tablix control to the main report and tie it to the dataset with all the IDs. Make the tablix a single cell and add a subreport control to it.
- In the subreport properties, select the single student report you created in step one. Then on the parameters tab, setup the parameters needed to call the sub report. I can't remember if SSRS will auto-populate the parameter names or if you need to manually add them. Depending on what parameters you need, you can tie the parameters of the subreport to the value in the ID dataset or pass through parameters from the main report to the subreport.
When I was building it out, I had a second column in the tablix to help make sure I was pulling the information I expected. I found it to be a little tricky to get the sub report to take up only a single page in the main report. That part was a fair amount of trial and error and I ended up putting a border around different components to figure what ended where and how to adjust it to get the layout I wanted.
The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply