/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Is there a way to exclude the created time from a report

07/12/2017 679 views
I am looking to create a report that shows how many tickets were created on a daily basis for the month. I set it up with the Wizard (I do not know SQL) and set it to break headers on Created thinking it would give me x # of rows per day, but it give a row for each ticket because it includes the time. 

Any help on this would be great. 

Thank in Advance. 
0 Comments   [ + ] Show comments

Comments


All Answers

0
Instead of having HD_TICKET.CREATED in your report query, replace it with DATE_FORMAT(HD_TICKET.CREATED, '%d-%m-%Y') AS C_DATE. This will return the date only without the time.
Answered 07/12/2017 by: Druis
Third Degree Green Belt

  • Thanks for the response. I am creating it with the Wizard, when I go to edit SQL this is what I have

    SELECT HD_TICKET.CREATED, O.FULL_NAME AS OWNER_NAME, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TITLE, HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.RESOLUTION FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_TICKET.CREATED < '2017-07-01 00:00:00') AND (HD_TICKET.CREATED > '2017-05-31 00:00:00')) ORDER BY CREATED, OWNER_NAME

    I have tried inserting or replacing what you suggested in multiple locations but it gives me an error everytime. Any suggestions would be appreciated. Like I said I am not good at SQL. Thanks.
    • Sorry, I had not fully read through your question. Try this:-

      SELECT COUNT(DATE_FORMAT(HD_TICKET.CREATED, '%d-%m-%Y')) AS T_COUNT,
      DATE_FORMAT(HD_TICKET.CREATED, '%d-%m-%Y') AS C_DATE,
      O.FULL_NAME AS OWNER_NAME,
      S.FULL_NAME AS SUBMITTER_NAME,
      HD_TICKET.TITLE,
      HD_CATEGORY.NAME AS CATEGORY,
      HD_TICKET.RESOLUTION FROM HD_TICKET
      LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
      LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
      JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
      WHERE (HD_TICKET.HD_QUEUE_ID = 1)
      AND ((HD_TICKET.CREATED < '2017-07-01 00:00:00') AND (HD_TICKET.CREATED > '2017-05-31 00:00:00'))
      GROUP BY C_DATE
      ORDER BY HD_TICKET.CREATED, OWNER_NAME
      • Thanks, it takes that SQL but when I try to run the report I get Error Running Report

        The query does not contain specified break field.

        Really appreciate the help with this.
    • I'm not sure where the error is. Try to create a 'New (SQL)' report from the action menu, rather than editing the one from the Wizard
    • Also I would highly recommend downloading a Query tool like MySQL workbench or Heidi SQL to test your queries.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ