1. 

   gbrown100, Member

   Hi,

   I have a report that pulls a whole bunch of data for a trouble ticketing system I have. It basically lists all the work I have done on each ticket (inc obvious code to cleanup fields and formats). I have tried to use a dbr.sum on the two billing fields but absolutely nothing is added up. Any ideas what I am doing wrong? It reports fine, just no totals. Be aware I have only been using SQL for a few weeks so there may be a real stupid ommission on my part...

   Thanks

   Graham

   
   
   DROP PROCEDURE IF EXISTS sp_DBR_febburr
   
   $$
   
   CREATE PROCEDURE sp_DBR_febburr( vStartDate date, vEndDate date, inLogin varchar(30) )
   
   BEGIN
   
   select 'dbr.hdr', 5;    /* Sets the Year to be a header column */
   
   select 'dbr.sum', 3, 4; /* Calculate sum from Items and Weight column */
   
   select 'dbr.summarytxt', 1, 'Total';
   
   SELECT a.fieldvalue AS 'Work Location', FROM_UNIXTIME(b.dateline, '%d/%m/%Y') AS 'Date',sum(b.timespent) as 'Time Worked (Minutes)',sum(b.timebillable) AS 'Billable', c.TicketID AS 'Ticket Number',e.fullname AS 'AffinIT Staff',d.title AS 'Ticket Status', c.fullname AS 'Raised by',c.email AS 'Email Address',c.subject AS 'Ticket Subject', b.notes AS 'Entry Description'
   
   FROM kayako_suite.swtickettimetrack b, kayako_suite.swtickets c, kayako_suite.swcustomfieldvalues a, kayako_suite.swticketstatus d, kayako_suite.swstaff e, kayako_suite.aff_login f
   
   WHERE b.ticketid = c.ticketid
   
   and c.ownerstaffid = e.staffid
   
   and d.ticketstatusid = c.ticketstatusid
   
   and a.typeid = b.timetrackid
   
   and a.fieldvalue in('Remote','System Work')
   
   and b.dateline between UNIX_TIMESTAMP(vStartDate) and UNIX_TIMESTAMP(vEndDate)
   
   and f.myDBRLogin=inLogin
   
   and c.departmentid = f.departmentID;
   
   END
   
   $$ 
2. 

   gbrown100, Member

   okdokay, I have it working. It really was a stupid error also, I will still logged in as the user I was testing the report for not admin - although it showed I had updated the SQL it was not saving it...
3. 

   myDBR Team, Key Master

   Graham,
   when you say "absolutely nothing is added up" does it mean that the summary is 0 (zero) or that nothing is shown? What is the datatype of b.timespent and b.timebillable columns? They should be numeric (int/float/numeric etc).

   Btw, you are missing "group by" in your query. As a rule of thumb: if your select include both normal columns and aggregate functions (sum/min/max/count), you need to put "group by" at the end of query. Into the "group by" you put everything in the "select line" but the aggregate function references. In MySQL you can use numeric values to reference columns.

   Also, header levels are usually the leftmost columns.

   --
   myDBR Team

Reply

You must log in to post.