• Article Type: General
• Product: Rosetta
• Product Version: 3.1

Description:
Desired outcome / Goal
To configure the date for the "Delivery Stats Report"?
For example: most viewed items from a date range.

Environment
Rosetta 3

Resolution:
Procedure:
It's not possible to apply parameters to the Data Management > Advanced Tools > Report > Delivery Stats Report
It is possible to create a new report by querying the events views.
Below is the sample SQL statement for this report.

1. Open a ssh session on the Rosetta server
2. Enter: s+ rep00 to get the SQL prompt
3. In a notepad file, create a mock-up that can be pasted into the prompt as follows:
Replace the "V2EA" with the customer's installation prefix and add the suffix "_rpt00".
For example, If the schema name is V2EA then the user/pass are "V2EA_shr00".

SELECT rownum AS POSITION ,
a.number_of_deliveries,
a.pid ,
a.LABEL,
a.OWNER
FROM
(SELECT b.number_of_deliveries,
b.pid ,
a.LABEL
a.OWNER
FROM V2EA_rep00.hdecontrol a ,
(SELECT COUNT(b.VALUE) AS number_of_deliveries,
b.VALUE AS pid
FROM V2EA_shr00.hfrevent a,
V2EA_shr00.hfreventkeys b
WHERE a.event_type = 160
AND a.event_id_number = b.event_id
GROUP BY b.VALUE
) b
WHERE b.pid = a.pid
AND a.objecttype = 'INTELLECTUAL_ENTITY'
ORDER BY b.number_of_deliveries DESC
) a
WHERE rownum < 101;

Additional Information
The complete list of reports and views can be found in chapter 2 in the staff users guide