Jump to content

Convert a sql script to report

Michael Taylor

Recommended Posts


Is it possible to convert the following sql script into a report, so that the dates in the where clause are selectable?

SELECT h1.h_pk_reference AS ticket_number,

       h1.h_datelogged AS date_logged,

       h1.h_dateclosed AS date_closed,

                   DATEDIFF(h1.h_dateclosed,h1.h_datelogged) as days_to_close,

       h1.h_ownername AS owner,

       h1.h_status AS status,

                   h2.h_fk_childrequestid AS category_number,

                   CONCAT(h3.h_category," > ",h3.h_summary) AS category_desc

FROM h_itsm_requests h1,

     h_itsm_rel_requests h2,

                h_itsm_requests h3

WHERE h1.h_pk_reference LIKE 'IN%'

AND   h1.h_fk_team_name = 'Applications York'

AND   h1.h_pk_reference = h2.h_fk_parentrequestid

AND   h2.h_fk_childrequestid = h3.h_pk_reference

AND   h1.h_datelogged BETWEEN '2016-05-30' AND '2016-06-03'

Link to comment
Share on other sites

  • 2 weeks later...

HI Michael

There is no ability to import a report from an SQL query, you would need to build your own report in Administration -> Service Manager -> Reports -> Create 

You will need to use SQL Schema Designer 

Screen Shot 2016-06-13 at 07.22.07.png

Add the tables and configure your joins,

In Select Filter you can pick User Prompted Value so at run time the user can put in the dates. 

Screen Shot 2016-06-13 at 07.25.55.png

Kind Regards

Trevor Killick

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...