[Link multiple tables (4 tables) in a report]

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

[Link multiple tables (4 tables) in a report]

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

[Link multiple tables (4 tables) in a report]

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

[Link multiple tables (4 tables) in a report]

@Victor help?

[Link multiple tables (4 tables) in a report]

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

[What table stores PCF form fields]

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 

 

 

 

[What table stores PCF form fields]

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?

 

[Using SQL queries on report platform]

haha thanks Victor. will amend my query.

[Using SQL queries on report platform]

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

[Using SQL queries on report platform]

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

[Using SQL queries on report platform]

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

 

 

[Using SQL queries on report platform]

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

 

 

 

 

 

[Scheduling report to run weekly]

Thanks Trevor

 

[Scheduling report to run weekly]

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

[Scheduling report to run weekly]

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.

 

[Scheduling report to run weekly]

Hi,

 

How can I schedule a report to run weekly (eg every Monday).

 

Thanks

Regards,

Joyce

[Automated node on BPM to send email]

Thanks everyone. its is working now.

[Automated node on BPM to send email]

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

 

[FAQ Published by]

Thanks James. This will be helpful

[Change Calendar edit and viewing from request]

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

[Widget not providing data]

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

 

Thanks.

 

Joyce

[Widget not providing data]

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

[Widget not providing data]

4 hours ago, Joyce said:

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

 

[Widget not providing data]

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

[FAQ Published by]

Thanks for letting me know