4 Replies Latest reply: Sep 24, 2013 7:23 AM by Cozmo2

Reporting: If statement and Sum statement

Cozmo2 Sep 23, 2013 7:48 AM

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

1. Re: Reporting: If statement and Sum statement

Eddie Lotter Sep 23, 2013 10:41 AM (in response to Cozmo2)

Please show your SQL query and also how you are calculating "calc.counter".
Like Show 0 Likes (0)

Actions
2. Re: Reporting: If statement and Sum statement

Cozmo2 Sep 23, 2013 11:54 AM (in response to Eddie Lotter)

My query looks like this:
<cfquery name="CFReportDataQuery" datasource="#session.ds#">
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
Like Show 0 Likes (0)

Actions
3. Re: Reporting: If statement and Sum statement

Eddie Lotter Sep 23, 2013 12:22 PM (in response to Cozmo2)

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.
Like Show 0 Likes (0)

Actions
4. Re: Reporting: If statement and Sum statement

Cozmo2 Sep 24, 2013 7:23 AM (in response to Eddie Lotter)

Thanks for the suggestion. I ended up writing a report function.
Like Show 0 Likes (0)

Actions

Go to original post

Reporting: If statement and Sum statement

1. Re: Reporting: If statement and Sum statement

2. Re: Reporting: If statement and Sum statement

3. Re: Reporting: If statement and Sum statement

4. Re: Reporting: If statement and Sum statement

Actions

More Like This

Legend