Issue Number | 3032 |
---|---|
Summary | [Licensee] Summary Vendor Report |
Created | 2009-11-25 17:08:58 |
Issue Type | Improvement |
Submitted By | Englisch, Volker (NIH/NCI) [C] |
Assigned To | Englisch, Volker (NIH/NCI) [C] |
Status | Closed |
Resolved | 2010-01-07 16:18:01 |
Resolution | Fixed |
Path | /home/bkline/backups/jira/ocecdr/issue.107360 |
BZISSUE::4708
BZDATETIME::2009-11-25 17:08:58
BZCREATOR::Volker Englisch
BZASSIGNEE::Volker Englisch
BZQACONTACT::Lakshmi Grama
I want to create a licensee summary report (probably in Excel) to list the data we're collected in the CDR.
BZDATETIME::2009-12-04 17:14:25
BZCOMMENTOR::Volker Englisch
BZCOMMENT::1
I've created an Ad-hoc report in the CdrQueries interface with the
name
Licensees List
and added the necessary query_terms to the query_term table.
This report lists the CDR-ID, licensee name, status, and activation dates.
Since Bob has just added the ability to create the reports of the interface in Excel format this may be all that's needed for this task at the moment.
Unless you, Lakshmi, have additional requirements for this report I'm going to mark this task as resolved.
BZDATETIME::2009-12-04 17:15:23
BZCOMMENTOR::Volker Englisch
BZCOMMENT::2
Query currently stored for the licensee list:
SELECT n.doc_id, n.value AS "Name",
t.value AS "Status",
a.value AS "Test Act", r.value AS "R-Date",
ti.value AS "TI-Date", p.value AS "P-Date",
pdi.value AS "Prod Inact"
FROM query_term n
JOIN query_term t
ON t.doc_id = n.doc_id
AND t.path = '/Licensee/LicenseeInformation' +
'/LicenseeStatus'
JOIN query_term a
ON a.doc_id = n.doc_id
AND a.path = '/Licensee/LicenseeInformation' +
'/LicenseeStatusDates' +
'/TestActivation'
LEFT OUTER JOIN query_term r
ON r.doc_id = n.doc_id
AND r.path = '/Licensee/LicenseeInformation' +
'/LicenseeStatusDates' +
'/TestExtension'
LEFT OUTER JOIN query_term ti
ON ti.doc_id = n.doc_id
AND ti.path = '/Licensee/LicenseeInformation' +
'/LicenseeStatusDates' +
'/TestInactivation'
LEFT OUTER JOIN query_term p
ON p.doc_id = n.doc_id
AND p.path = '/Licensee/LicenseeInformation' +
'/LicenseeStatusDates' +
'/ProductionActivation'
LEFT OUTER JOIN query_term pdi
ON pdi.doc_id = n.doc_id
AND pdi.path = '/Licensee/LicenseeInformation' +
'/LicenseeStatusDates' +
'/ProductionInactivation'
WHERE n.path = '/Licensee/LicenseeInformation' +
'/LicenseeNameInformation' +
'/OfficialName/Name'
ORDER BY t.value, n.value
BZDATETIME::2010-01-07 16:17:22
BZCOMMENTOR::Volker Englisch
BZCOMMENT::3
I've also added an ad-hoc report allowing to display the contact
person along with the licensees. The report is called:
Licensee Contacts
and lists the licensee name, contact name and contact type.
This is the SQL for it:
SELECT n.doc_id, n.value AS "Name",
t.value AS "Status",
b.value AS "Contact", ct.value AS "Type"
FROM query_term n
JOIN query_term t
ON t.doc_id = n.doc_id
AND t.path = '/Licensee/LicenseeInformation' +
'/LicenseeStatus'
JOIN query_term b
ON b.doc_id = n.doc_id
AND b.path = '/Licensee/ContactPersons' +
'/ContactPerson/ContactName'
JOIN query_term ct
ON ct.doc_id = n.doc_id
AND ct.path = '/Licensee/ContactPersons' +
'/ContactPerson/ContactType'
AND left(ct.node_loc, 8) = left(b.node_loc, 8)
WHERE n.path = '/Licensee/LicenseeInformation' +
'/LicenseeNameInformation' +
'/OfficialName/Name'
ORDER BY t.value, n.value, ct.value
BZDATETIME::2010-01-07 16:18:01
BZCOMMENTOR::Volker Englisch
BZCOMMENT::4
I'm closing this issue since no further licensee reports are needed at this time.
Elapsed: 0:00:00.001585