Reporting: If statement and Sum statement

Report · Sep 23, 2013

I have an instructors schedule that shows each time a course is taught for example:

Course Credits Day

AAA123 3 Mon.

Wed.

The Credit only shows the first time (Print When Condition: calc.counter IS 1). I need to total the credits for each course when I use the Sum(credits) function it totals for each line (the above ezample would show a total of 6), I only want to sum if calc.counter EQ 1

Report · Sep 23, 2013

Please show your SQL query and also how you are calculating "calc.counter".

Report · Sep 23, 2013

My query looks like this:

SELECT crssect."sch_yr", crssect."session", crssect."section", crssect."sp_taken", crssect."cancel", crssect."lead_fac",

crssect."sid", name."soc_sec", name."last_name", name."first_name", name."mi", name."suffix", course."course",

course."crs_txt", course."credits", course."crs_id", practicl."room_cod", practicl."start_hr", practicl."start_min",

practicl."start_ampm", practicl."end_hr", practicl."end_min", practicl."end_ampm", semester."sm_desc", sysvar."title",

dayweek."day_rid", dayweek."day_cod"

FROM

{ oj (((((((((("crssect" crssect INNER JOIN "course" course ON

crssect."crs_id" = course."crs_id")

INNER JOIN "rpt_sem" rpt_sem ON

crssect."semester" = rpt_sem."semester")

INNER JOIN "rpt_schy" rpt_schy ON

crssect."sch_yr" = rpt_schy."sch_yr")

INNER JOIN "name" name ON

crssect."lead_fac" = name."soc_sec")

INNER JOIN "semester" semester ON

crssect."semester" = semester."semester")

LEFT OUTER JOIN "practicl" practicl ON

crssect."sid" = practicl."sid")

INNER JOIN "sysvar" sysvar ON

course."crs_id" <> sysvar."title")

INNER JOIN "rpt_dept" rpt_dept ON

name."dept_cod" = rpt_dept."dept_cod")

INNER JOIN "rpt_div" rpt_div ON

name."div_cod" = rpt_div."div_cod")

INNER JOIN "rpt_camp" rpt_camp ON

name."camp_cod" = rpt_camp."camp_cod")

LEFT OUTER JOIN "dayweek" dayweek ON

practicl."day_cod" = dayweek."day_cod"}

WHERE

crssect."cancel" = 0 AND

rpt_sem."token" = '#session.token#' AND

rpt_schy."token" = '#session.token#' AND

crssect."lead_fac" LIKE '#client.idnum#' AND

rpt_div."token" = '#session.token#' AND

rpt_dept."token" = '#session.token#' AND

rpt_camp."token" = '#session.token#'

UNION

SELECT crssect."sch_yr", crssect."session", crssect."section", crssect."sp_taken", crssect."cancel", crssect."lead_fac",

crssect."sid", name."soc_sec", name."last_name", name."first_name", name."mi", name."suffix", course."course",

course."crs_txt", course."credits", course."crs_id", practicl."room_cod", practicl."start_hr", practicl."start_min",

practicl."start_ampm", practicl."end_hr", practicl."end_min", practicl."end_ampm", semester."sm_desc",

sysvar."title", dayweek."day_rid", dayweek."day_cod"

FROM

{ oj (((((((((((("crssect" crssect INNER JOIN "course" course ON

crssect."crs_id" = course."crs_id")

INNER JOIN "rpt_sem" rpt_sem ON

crssect."semester" = rpt_sem."semester")

INNER JOIN "rpt_session" rpt_session ON

crssect."session" = rpt_session."session_code")

INNER JOIN "rpt_schy" rpt_schy ON

crssect."sch_yr" = rpt_schy."sch_yr")

INNER JOIN."practssn" practssn ON

crssect."sid" = practssn."sid")

INNER JOIN "name" name ON

practssn."ssn" = name."soc_sec")

INNER JOIN "semester" semester ON

crssect."semester" = semester."semester")

LEFT OUTER JOIN "practicl" practicl ON

crssect."sid" = practicl."sid")

INNER JOIN "sysvar" sysvar ON

course."crs_id" <> sysvar."title")

INNER JOIN "rpt_dept" rpt_dept ON

name."dept_cod" = rpt_dept."dept_cod")

INNER JOIN "rpt_div" rpt_div ON

name."div_cod" = rpt_div."div_cod")

INNER JOIN "rpt_camp" rpt_camp ON

name."camp_cod" = rpt_camp."camp_cod")

LEFT OUTER JOIN "dayweek" dayweek ON

practicl."day_cod" = dayweek."day_cod"}

WHERE

crssect."cancel" = 0 AND

rpt_sem."token" = '#session.token#' AND

rpt_session."token" = '#session.token#' AND

rpt_schy."token" = '#session.token#' AND

practssn."ssn" LIKE '#client.idnum#' AND

rpt_div."token" = '#session.token#' AND

rpt_dept."token" = '#session.token#' AND

rpt_camp."token" = '#session.token#'

ORDER BY name."last_name", name."first_name", name."soc_sec", crssect."session",course."course", crssect."section", dayweek."day_rid", practicl."start_ampm", practicl."start_hr"

</cfquery>

calc.counter - I count the number of course found and zero the counter when the soc_sec changes

Report · Sep 23, 2013

The quickest solution I can think of, off the top of my head, would be to have another calc field that stores the value of course.credits when course.course changes.

When course.course changes, you can use the new calc field to print the total and then reset the new calc field.

Report · Sep 24, 2013

Thanks for the suggestion. I ended up writing a report function.