XMPie uProduce Production Units Report for uProduce with “Volume Pack” license

Summary: This article explains how to run a query to receive information about how many production units were consumed per account/campaign/job.

Audience: XMPie customers who want to know the amount of production units consumed and are using uProduce with a “Volume Pack” license.

Prerequisites: This article assumes the user understands how to run SQL queries using the Microsoft SQL Server Management Studio.

Overview

From PersonalEffect version 7.1.1, production units history usage is stored in the XMPie database. This data allows the XMPie customer to report how many production units were consumed per account / campaign / job.

Getting this data from the XMPie database is currently done by running an SQL Query on the XMPie database. This document explains how to run such query.

SQL Query

Open the Microsoft SQL Server Management Studio for the SQL server instance in which the XMPie databases reside.

  1. Use the following SQL query:

    WITHJobsAS

    (

    SELECTj.*,c.AccountIDasaccountIDFROM[XMPDB2].[XMPie].[TBL_JOB]jLEFTJOIN[XMPDB2].[XMPie].[TBL_CAMPAIGN]cONj.campaignID=c.campaignIDWHEREISNULL(parentJobID,0)= 0

    UNION

    SELECTj.*,c.AccountIDasaccountIDFROM[XMPDB2].[XMPie].[TBL_JOB_DELETED]jLEFTJOIN[XMPDB2].[XMPie].[TBL_CAMPAIGN]cONj.campaignID=c.campaignIDWHEREISNULL(parentJobID,0)= 0

    )

    --SELECT SUM(jobClickCharge) as clickCharge FROM Jobs where accountID = 1

    --SELECT SUM(jobClickCharge) as clickCharge FROM Jobs where campaignID = 1

    --SELECT SUM(jobClickCharge) as clickCharge FROM Jobs where documentID = 1

    --SELECT SUM(jobClickCharge) as clickCharge FROM Jobs where jobID = 1

    --SELECT SUM(jobClickCharge) as clickCharge FROM Jobs where accountID = 1 AND jobSubmitTime BETWEEN '2015-01-26 08:00:00' AND '2015-01-29 08:00:00'

  2. Remove the comments from the line you want to use.

    For example, to query the production units consumed by job ID 88663, run the query as follows:

    WITHJobsAS

    (

    SELECTj.*,c.AccountIDasaccountIDFROM[XMPDB2].[XMPie].[TBL_JOB]jLEFTJOIN[XMPDB2].[XMPie].[TBL_CAMPAIGN]cONj.campaignID=c.campaignIDWHEREISNULL(parentJobID,0)= 0

    UNION

    SELECTj.*,c.AccountIDasaccountIDFROM[XMPDB2].[XMPie].[TBL_JOB_DELETED]jLEFTJOIN[XMPDB2].[XMPie].[TBL_CAMPAIGN]cONj.campaignID=c.campaignIDWHEREISNULL(parentJobID,0)= 0

    )

    --SELECT SUM(jobClickCharge) as clickCharge FROM Jobs where accountID = 1

    --SELECT SUM(jobClickCharge) as clickCharge FROM Jobs where campaignID = 1

    --SELECT SUM(jobClickCharge) as clickCharge FROM Jobs where documentID = 1

    SELECTSUM(jobClickCharge)asclickChargeFROMJobswherejobID= 88663

    --SELECT SUM(jobClickCharge) as clickCharge FROM Jobs where accountID = 1 AND jobSubmitTime BETWEEN '2015-01-26 08:00:00' AND '2015-01-29 08:00:00'