Sum of months via Date

[jhhenkel]

Hallo,

I hope someone can halp me with the sum-function.

 

The situation:

I have a db with hours and timestamps. Looks like that:

hours    date                 xxx   xxx
5             12/07/2011    y       y
3             10/06/2011    y       y
2             07/07/2011    y       y
...

Now I want to sum in a variable all hours for the month 07/2011 and in another variable all ours for 06/2011 and so on.

How can I tell the variable to sum only the hours for THIS ONE month and not for all months?

Do I need the option "Print when expression" or can I handle it in another way?

 

Please help!

Thanks a lot!

[despec]

Just off the top of my head, create variables for each month and within the expression field, set up a condition that tests for that date and if true, add the number of hours...

 

David

[cbarlow3]

I thought I posted a reply to this several days ago, but it looks like it didn't get saved.  There are two solutions, depending on whether you still want your report to show the details (but ALSO show subtotals for each month) or whether you JUST want the subtotals.  First let's assume you want the details to show but also want a subtotal for each month and for each year:

Solution 1:  Show Details, but also subtotals by month and by year.

Make your query return the month, year, and month name as additional fields in the result set.  This isn't 100% necessary, but it makes the rest of the work easier:

SELECT
  x.date as date, x.dateYYYY as dateYYYY, x.dateMM as dateMM, x.hours as hours,  (and also other fields from your table)
  SUBSTR(‘JanFebMarAprMayJunJulAugSepOctNovDec’, 3*x.dateMM-2, 3*x.dateMM) as dateMonthName
FROM
(SELECT
  myTable.date as date,
  YEAR(myTable.date) as dateYYYY,
  MONTH(myTable.date) as dateMM,
  myTable.hours as hours  (and also other fields from your table)
FROM
  MyTable) x
ORDER BY
  date

I don't normally like to name columns the same thing as a datatype or SQL function name, but I hope that example isn't too confusing.  Because I'm returning the data in order by date, that automatically means I'm also getting it back in order by year, and within year in order by month.  So now before I start dragging fields into my detail band, I would create a group called yearGroup that is controlled by the $F{dateYYYY} field and have it create group headers and group trailers.  I would then create a second, subordinate group called monthGroup that is controlled by the $F{dateMM} field, and again, I would keep the default group headers and trailers.  Now drag the other fields you want (the actual date, the hours, etc.) into the detail band.  In the yearGroup header band, drag the $F{dateYYYY} field in (choose "The field value", not "The result of an aggregation function"), maybe make it bold, maybe create a hyperlink level 1 bookmark that will be handy for PDF export, etc.  In the monthGroup header band, drag the $F{dateMonthName} field, make it bold, etc.  I would make the yearGroup header band just high enough for the text field, but I would make the monthGroup header twice as high, and I would create a colored frame below that text box that extends the width of the report, and I would drag static text fields into that frame to build my own column header section (and I would delete the column header band).  I would set both column groups to repeat at the top of every page.  I actually usually do another trick as well, which is to make TWO text fields that overlap in my group headers:  one is bold and has a hyperlink and has a print-when condition like $V{yearGroup_COUNT==0}, while the other one I leave unbolded, I add " (continued)" to the end of the actual text expression, I don't build a hyperlink, and the print-when condition is mutually exclusive with the other  one: $V{yearGroup_COUNT>0}.  That way you only get hyperlinks and bold at the start of a new year, but you still get continuation headers at the top of every page to remind you what year you're in  (do same type of thing with the monthGroup header, by the way).  Those variables $V{yearGroup_COUNT} and $V{monthGroup_COUNT} are automatically defined by the system as soon as you create those groups.

Then drag a copy of $F{hours} into the monthGroup trailer, but this time say that you want the field to actually be a SUM, not "The field value".  This will automatically create a variable $V{hours_1}, which will display the sum of all the hours for that particular month within that particular year.  Drag another copy of $F{hours} from the field section of the Report Inspector tree into the yearGroup trailer ($V{hours_2}) and another one into the Summary band ($V{hours_3}).  You can add labels like a text field in the monthGroup trailer that says "Total hours for "+$F{dateMonthName}+": " and similarly you can create a label in the yearGroupTrailer and one that just says "Total hours: " in the Summary band.  I usually make that label in the summary band be a text field rather than a static text, just so I can add a final level 1 hyperlink with the label "Totals", which is handy if someone has a PDF export and wants to jump to the grand totals.

I'll put how to get JUST the subtotals in a separate post.

Carl

 

Post Edited by cbarlow3 at 08/26/2011 16:37

[cbarlow3]

If you ONLY care about the subtotals by month and don't need to see the details, you can do that in the SQL query without having to resort to groups and header/footer bands:

SELECT
  YEAR(myTable.Date) AS dateYYYY,
  MONTH(myTable.Date) AS dateMM,
  SUM(hours) AS hoursSubTot
FROM
  myTable
GROUP BY
  YEAR(myTable.Date),
  MONTH(myTable.Date)
ORDER BY
  dateYYYY, dateMM

Now just drag and format your three fields into the detail band.  The hours for each detail will be the sum of the hours for all rows in your database that have tha particular year and month value.

Carl