Joyce

Hi @Victor, the field that I need are: h_itsm_requests.h_pk_reference h_itsm_requests.h_fk_user_name h_itsm_requests.h_dateresolved h_sys_accounts. h_email h_itsm_requests.h_custom_c h_itsm_requests.h_catalog h_cmdb_assets_computer.h_model h_itsm_requests.h_custom_c h_cmdb_assets_computer.h_name Filter by h_itsm_questions-> FormID = "Order" And h_itsm_questions-> Question Value = "Docking Station" AND h_itsm_questions-> QuestionID = "field_4" Thanks Joyce

the table join seems to eliminate some data. I have try to review, and I can't figure out why

Hi @Victor With the above settings, I seems to be missing some of the data. I have check the request, they all have similar field. The report definition for the two are: Test request for Victor- Missing rows.txt Test report for Victor- Correct.txt

@Victor help?

Hi, I need to link table 4 tables: h_itsm_requests, h_itsm_questions, h_cmdb_links, and h_cmdb_assets_computer. The field h_itsm_requests.h_pk_reference is equals to "h_itsm_questions.reference" , h_cmdb_links.h_fk_id_l is like "h_itsm_requests.h_pk_reference" and h_cmdb_links.h_fk_id_r is like "h_cmdb_assets_computer.h_pk_asset_id" How can I correctly join them in the report ? currently have them as per below, and I am getting an error, also the last join, seems to pick up the middle join

yes I think i have a problem. All the values i am after have been recorded in the same form, so has same form ID and are all as answers. so it is difficult to filter ( i am ending up with 4 rows on the same request) I guess, i was wondering if there is some kind of 'form' table where the field ID and Answers are recorded as different columns/fields So i get report as

Hi, I am trying to get a report, with details added through a progressive capture, using the form node. These details are in field_13, field_21, etc, in which table are these values store?

haha thanks Victor. will amend my query.

Hi Steve, I have try your query above, but I am getting an error. SELECT h_itsm_request_team_assignment.h_request_id As Reference, h_itsm_requests.h_summary AS Summary , COUNT(h_itsm_request_team_assignment.h_request_id) AS Assignments from h_itsm_request_team_assignment inner Join h_itsm_requests on h_request_id=h_pk_reference and h_itsm_request_team_assignment.h_previous_team_id !='No Previous Team' and h_itsm_request_team_assignment.h_previous_user_id !='No previous owner' Group by h_request_id HAVING COUNT(h_request_id) > 2 order by Assignments desc Limit 10 Not sure what I am missing. Regards, Joyce

Hi Steve, I created this report months ago, I think at a time the no previous owner was to eliminate the first count ( when ticket is logged, and assigned to a team, then another assignment is when an analyst within the same team takes the ticket). So the logic is, Its should have a Previous team, and have a previous Owner...so manly start counting the ticket when it move to the third assignment? does this make sense? I take it the above query can be used in Widgets and not possible on Report? Thanks. Regards, JOyce

Thanks Victor. I have a measure for it with query: h_requesttype ='Service Request' AND h_pk_reference IN(SELECT h_request_id FROM h_itsm_request_team_assignment WHERE h_previous_team_id != 'No previous team' AND h_previous_user_id !='No previous owner' GROUP BY h_request_id HAVING COUNT(h_request_id) > 2) I just want to have a data list of these tickets. Thanks. Regards, Joyce

Hi, How can I use Sql query to build a report, instead of selecting fields? Is there a place to put a queary? I am looking to create a report, to select tickets which have been re-assigned between team twice or more. I have done this in a measure to get a count per month, but I want a list so we can identify the tickets and review them. Is there any other way? Thanks. Regards, Joyce

Thanks Trevor

Yes, similar issue. I have send you a report definition:

Thanks @Steven Boardman. That is what i have set, but it seems to run randomly, also multiple time in a day. I will reset again and check next week.

Hi, How can I schedule a report to run weekly (eg every Monday). Thanks Regards, Joyce

Thanks everyone. its is working now.

Hi, How can I add multiple email addresses on the automatic node for sending email? This is what I have at the moment: It doesn't seems to work. Thanks. Regards, Joyce

Thanks James. This will be helpful

Hi, Is it possible to view/ access change calendar from the request ? Ideally, we want when someone is scheduling a change (by clicking the calendar button on the action buttons on the top of a request) , to be able to open the change calendar, and add the change on it, rather than selecting date. This will help for visibility of other changes which are already schedulled. Also, we would like to be able to edit the calendar by adding planned maintenance schedule, and change freeze dates. These are pre-agreed with the business 6 month in advance so they don't need a request, but need to be added to the change calendar. Thanks. Regards, Joyce

Just changed my resolved date interval to : DATE(h_fixby) BETWEEN DATE(NOW()) AND DATE(NOW() +INTERVAL 2 DAY) and it worked. Thanks. Joyce

Hi Victor, Managed to get the data I wanted finally. I had to put date interval, as noticed, it was only giving data upto end of the month. This query can be closed now. Thanks. Regards, Joyce

Hi, I had created widgets to display tickets which are about to breach in 2 days' time. I had both the list of tickets , and count of ticket. This was working perfectly until today, where no value is displayed, but we do have tickets which are about to breach. This is the sql I have for the list of SR: SELECT h_pk_reference as "Reference" , h_summary, h_ownername as "Owner", h_fixby as "Resolve By" FROM h_itsm_requests WHERE h_requesttype = 'service request' AND DATE(h_fixby) BETWEEN DATE (CURDATE()) AND (CURDATE() +2) AND h_fk_team_name ='Application Support' AND (h_status = "status.new" OR h_status = "status.open") And this is the query for count: this brings 0 data, while when looking at the application, there values due to breach in two days time

Thanks for letting me know