I have a question about using things like per.att in this tool. I read somewhere that it would work if it is put inside single quotes. I have tried that but I don't get a valid count. I am using the example you posted that lists the students courses and am trying to list absences instead. The currentabsences in the cc record does not seem to be accurate.
any ideas???
thanks!
Max B

I would like to be able to use the %param% to let the end user specify a date range. Specifically so they can pull discipline reports by an incident date range. Is there a way to do this using the %param% feature?

Thanks, Dean...this has already been a huge help in writing reports.

Does anyone happen to have any examples that might help to export a "student schedule" one line that contains student demographic information and Period 1-8 schedule information. Have tried using an ODBC connection and pivot table, but takes forever to process. This seems like it would work great, but need some samples to get me going. Any other ideas would be helpful.

Thanks!

This is a fantastic tool for adding custom reports easily into PowerSchool. Using the basic SQL skills I learned at the Michigan System Administrator's Conference, I have used this add on to create several custom reports including data validation reports for Michigan Pupil Accounting (state reports).

The video link was a perfect intro to get me started. Thanks for posting it!

Laurie Kinney

Is there a character limit to what can be entered into the SQL Query Text Box?

Also is there a way I can create an exported report file for import?

Robert Wadsworth

Can this be used to issue update SQL statements?

SELECT ' '|| T.LastName||' '||
T.FirstName||' '||
SC.Description||' '||
S.SectionIdentifier||' '||
FGS.FinalGradeSetupType||' '||
P.Q1||' '||
P.Q2||' '||
P.Q3||' '||
P.Q4||' '||
P.E1||' '||
P.E2||' '||
P.R1||' '||
P.R2
||'

'
FROM PSM_FinalGradeSetup FGS
INNER JOIN
psm_Section S ON S.ID = FGS.SectionID
INNER JOIN
psm_ReportingTerm RT on RT.ID = FGS.reportingTermID
INNER JOIN
PSM_SectionTeacher ST ON ST.SectionID = S.ID
INNER JOIN
PSM_Teacher T ON T.ID = ST.TeacherID
INNER JOIN
psm_SchoolCourse SC on SC.ID = S.schoolcourseid
INNER JOIN
psm_School SCH on SCH.ID = S.SchoolID
LEFT JOIN

(SELECT FGS.GradingFormulaID,
MAX(CASE RTRIM(RT.name) WHEN 'Q1' THEN GFW.weighting ELSE NULL END) AS Q1,
MAX(CASE RTRIM(RT.name) WHEN 'Q2' THEN GFW.weighting ELSE NULL END) AS Q2,
MAX(CASE RTRIM(RT.name) WHEN 'Q3' THEN GFW.weighting ELSE NULL END) AS Q3,
MAX(CASE RTRIM(RT.name) WHEN 'Q4' THEN GFW.weighting ELSE NULL END) AS Q4,
MAX(CASE RTRIM(RT.name) WHEN 'E1' THEN GFW.weighting ELSE NULL END) AS E1,
MAX(CASE RTRIM(RT.name) WHEN 'E2' THEN GFW.weighting ELSE NULL END) AS E2,
MAX(CASE RTRIM(RT.name) WHEN 'R1' THEN GFW.weighting ELSE NULL END) AS R1,
MAX(CASE RTRIM(RT.name) WHEN 'R2' THEN GFW.weighting ELSE NULL END) AS R2
FROM psm_GradingFormulaWeighting GFW
INNER JOIN
psm_ReportingTerm RT on RT.ID = GFW.reportingTermID
INNER JOIN
psm_GradingFormula GF on GF.ID = GFW.ParentGradingFormulaID
INNER JOIN
PSM_FinalGradeSetup FGS on FGS.GradingFormulaID = GF.ID
WHERE RT.startDate >= TO_DATE('%param2%','MM/DD/YYYY')
GROUP BY FGS.GradingFormulaID) P
ON P.GradingFormulaID = FGS.GradingFormulaID
WHERE SCH.abbreviation = '%param1%'
AND RT.startDate >= TO_DATE('%param2%','MM/DD/YYYY')
AND RT.name = 'F1'
ORDER By T.LastName , t.FirstName, SC.Description, S.SectionIdentifier