CDR Tickets

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
Description

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.

Comment entered 2009-12-04 17:14:25 by Englisch, Volker (NIH/NCI) [C]

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.

Comment entered 2009-12-04 17:15:23 by Englisch, Volker (NIH/NCI) [C]

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

Comment entered 2010-01-07 16:17:22 by Englisch, Volker (NIH/NCI) [C]

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

Comment entered 2010-01-07 16:18:01 by Englisch, Volker (NIH/NCI) [C]

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