sqlReports for 6.x

I have been trying to do a query using a custom field and searching for text in that field ( upper case NI).  I finally have the sql query working in sql Developer but when I cut it and paste it into the sql reports, I receive "No records found."  My custom field is

xai_2_tier_1_inter

The query that I am using is:

Select s.last_name, s.first_name,s.grade_level, s.home_room, ps_customfields.getstudentscf(id, 'xai_2_tier_1_inter') as Tier_I_Intervention
from students s
where s.grade_level=2 AND ps_customfields.getstudentscf(id, 'xai_2_tier_1_inter')like '%NI%'
order by s.home_room

Do you have any idea where I might be going wrong?

This is the query with the tags in it in case I have an error in there.

Select '<td>'|| s.last_name||'</td><td>'|| s.first_name||'</td><td>'||s.grade_level||'</td><td>'|| s.home_room||'</td><td>'|| ps_customfields.getstudentscf(id, 'xai_2_tier_1_inter') as Tier_I_Intervention||'</td>'
from students s
where s.grade_level=2 AND ps_customfields.getstudentscf(id, 'xai_2_tier_1_inter')like '%NI%'
order by s.home_room

I am afraid that I can't use custom fields with this.  Is that so?  Thanks so much. 

I just put this on my server - very cool. Your sample has the 'Create Student Selection' item active.
But when I test it, it doesn't work.
Anything I could have done to cause, or can do to fix?

I am writing a fee report that is customized to our needs. This is the code that works:
SELECT FEE.DATE_VALUE, FEE.FEE_PAID, FEE.YEARID, FEE.SCHOOLID, FEE.COURSE_NAME, FEE.COURSE_NUMBER, FEE.DEPARTMENT_NAME, FEE_TYPE.NAME, FEE.DESCRIPTION, FEE_TYPE.DESCRIPTION
FROM PS_MGMT.FEE FEE, PSNAVIGATOR.FEE_TYPE FEE_TYPE
WHERE FEE_TYPE.ID = FEE.FEE_TYPE_ID AND ((FEE.FEE_CATEGORY_NAME='Activity'))
ORDER BY FEE.DATE_VALUE

I would like to sum the FEE.FEE_PAID column, but the Excel ODBC program will not let me use the sum function in the application. I get the following error:
FROM keyword not found where expected

Could anyone help out with this? Thank you so much!!

JoAnne Smith
Batavia Public Schools
Batavia, IL

Dean -

Firstly, I would like to thank you for putting this together... since installing it late last year, I've made extensive use of this add-on for various reporting needs - it has in fact become my primary reporting tool thus far. I'm a HUGE fan!!! :-)

Secondly, are you planning on any further development? If so, I wonder if you'd consider a request/suggestion? When a report is configured to open in a new window, it would be great if there was a way to suppress the PowerSchool Header and navigation links down the left side of the page so that the report can be printed without that extra clutter.

Thanks again!

Our Jr.High counselors need a list of 7th grade students identifying their current math class and the Q1, Q2 and Q3 grades. They use the list to determine who should take Algebra in grade 8. I was able in SASI to export the data to Excel and am sure there must be a way in SQL to get the data, but I'm at a loss as to how to begin. I am just beginnng to learn my way through SQL and would appreciate any help. I am so grateful to have this site!!

Sue Todd
Technology Coord
Hopewell Area School District

I love this tool. I am also very new to it (started playing yesterday). Anyway, I am having trouble making the current selection of students work. This is what I have for my SQL Query (and it works for me):

SELECT ' '|| STUDENTS.LASTFIRST||' '|| STUDENTS.GRADE_LEVEL||' '|| COURSES.COURSE_NAME||' '|| PGFINALGRADES.GRADE||' '|| PGFINALGRADES.FINALGRADENAME||' '|| TEACHERS.LASTFIRST
||'

'
FROM PSNAVIGATOR.CC CC, PS.COURSES COURSES, PS_MGMT.PGFINALGRADES PGFINALGRADES, PS_MGMT.STUDENTS STUDENTS, PS.TEACHERS TEACHERS
WHERE STUDENTS.ID = CC.STUDENTID AND STUDENTS.ID = PGFINALGRADES.STUDENTID AND CC.COURSE_NUMBER = COURSES.COURSE_NUMBER AND TEACHERS.ID = CC.TEACHERID AND ((STUDENTS.SCHOOLID=20001) AND (CC.TERMID=1902) AND (PGFINALGRADES.GRADE='D') AND (PGFINALGRADES.PERCENT<70) AND (PGFINALGRADES.FINALGRADENAME='S2') OR (CC.TERMID=1905) AND (PGFINALGRADES.GRADE='F') AND (PGFINALGRADES.FINALGRADENAME='T3'))
ORDER BY STUDENTS.LASTFIRST

I would like to make the students the current selection and am not having much luck. This is what I tried:

SELECT DISTINCT students.lastfirst, cc.sectionid, pgfinalgrades.grade,
pgfinalgrades.finalgradename
FROM CC, COURSES, PGFINALGRADES, STUDENTS, TEACHERS
WHERE
STUDENTS.ID = CC.STUDENTID
AND STUDENTS.ID = PGFINALGRADES.STUDENTID
AND CC.COURSE_NUMBER = COURSES.COURSE_NUMBER
AND TEACHERS.ID = CC.TEACHERID
AND ((STUDENTS.SCHOOLID=20001) AND (CC.TERMID=1902) AND
(PGFINALGRADES.GRADE='D') AND (PGFINALGRADES.PERCENT<70) AND
(PGFINALGRADES.FINALGRADENAME='S2') OR (CC.TERMID=1905) AND
(PGFINALGRADES.GRADE='F') AND (PGFINALGRADES.FINALGRADENAME='T3'))

Thank you for all of your hard work!!!!!

JoAnne Smith
Batavia Public Schools
Batavia, IL

Fairly new to PowerSchool and very new to sql reports. I've added this customization and a few other reports from colleagues. Everything runs but the column headings are all shifted to the right by a tab. I've re-applied the customization but I'm not sure where else to look. Suggestions?

Thanks,
Andrea

Andrea Stetson
Botetourt County Schools

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