Jump to content

Custom Report For Billing


Megan LaFrance

Recommended Posts

Hello,

 

I am looking for a report out of eCapture for Discovery Jobs cross all clients. This would be for 2016.3.2; desired results would list each Discovery Job with the below columns of data:

 

Job Start Date as 'Date'

Enterprise Code Name

Matter #

# of Documents *OPTIONAL IF TOO DIFFICULT

Total size (same as was the discovery summary reports out)

Size in GB (conversion to GB value) *OPTIONAL IF TOO DIFFICULT

Job Duration *OPTIONAL IF TOO DIFFICULT

 

Nice to have for this is the ability to set a date to run this report from, so it can result in only jobs started from 12/1/2016 through 12/31/2016 for example.

Link to comment
Share on other sites

Hello,

 

I am looking for a report out of eCapture for Discovery Jobs cross all clients. This would be for 2016.3.2; desired results would list each Discovery Job with the below columns of data:

 

Job Start Date as 'Date'

Enterprise Code Name

Matter #

# of Documents *OPTIONAL IF TOO DIFFICULT

Total size (same as was the discovery summary reports out)

Size in GB (conversion to GB value) *OPTIONAL IF TOO DIFFICULT

Job Duration *OPTIONAL IF TOO DIFFICULT

 

Nice to have for this is the ability to set a date to run this report from, so it can result in only jobs started from 12/1/2016 through 12/31/2016 for example.

 

Megan,

 

I believe this script is something we could create. Let me see what we can come up with today and I will get back to you once we have a finished script.

 

Thank you,

Levi Roble

Technical Support Engineer 2

Link to comment
Share on other sites

Megan,

 

This sql script should return the report results:

 

/*******************************************************************
Name: eCaptureClientDiscoveryJobReport.sql
Author: Levi Roble Ipro Tech LLC
Created On: 12/16/2016
Use: Used to report on all completed or completed with errors
discovery jobs in a single eCapture environment
Notes: Run this on the Nucleus Config DB and this will not work
if there are multiple eCapture configurations on the nucleus
database. Merged jobs will not have a JobStartDate.
*******************************************************************/
SET NOCOUNT ON
IF OBJECT_ID ('tempdb..#Results') IS NOT NULL DROP TABLE #Results
IF OBJECT_ID ('tempdb..#Projects') IS NOT NULL DROP TABLE #Clients

CREATE TABLE #Results
(
   ClientName varchar(200),
   DiscoveryJobName varchar(200),
   JobStartDate datetime,
   EnterpriseCodeName varchar(200),
   MatterNumber varchar(100),
   NumberOfDocuments int,
   TotalSizeMB decimal(20,3),
   TotalSizeGB decimal(20,3),
   JobDuration varchar(200)
)

CREATE TABLE #Clients
(
   ClientId int,
   ClientName varchar(200),
   ClientDB varchar(200)
)

DECLARE @eCaptureConfigDB varchar(200),@SQL nvarchar(max),@ClientId int,@ClientName varchar(200)
DECLARE @ClientDB varchar(200),@NucleusDB varchar(200)

SET @NucleusDB = DB_NAME()

SET @eCaptureConfigDB = (SELECT ApplicationEnvironmentName FROM Enterprise.ApplicationEnvironment
wHERE ProductId = 2)

SET @SQL = 
'INSERT INTO #Clients
SELECT EC.ClientId,EC.ClientName,EC.ClientDatabase FROM Enterprise.Client NC
INNER JOIN ' + QUOTENAME(@eCaptureConfigDB) + '.dbo.Clients EC ON EC.NucleusClientId = NC.ClientId'

EXEC (@SQL)

WHILE EXISTS (SELECT * FROM #Clients)
BEGIN
   SET @ClientId = (SELECT TOP 1 ClientId FROM #Clients)
   SET @ClientName = (SELECT ClientName FROM #Clients WHERE ClientId = @ClientId)
   SET @ClientDB = (SELECT ClientDB FROM #Clients WHERE ClientId = @ClientId)

   SET @SQL = '
   USE ' + QUOTENAME(@CLientDB) + '
   ;WITH DiscoveryJobSizes
   AS
   (
       SELECT DiscoveryJobId,Sum(Cast(ItemFileSize AS DECIMAL(20,2))) + Sum(FileSizeRemainder) AS TotalSize FROM Items
       WHERE ItemStatus = 2
       GROUP BY DiscoveryJobId
   )
   INSERT INTO #Results (ClientName,DiscoveryJobName,JobStartDate,EnterpriseCodeName
   ,MatterNumber,NumberOfDocuments,TotalSizeMB,TotalSizeGB,JobDuration)
   SELECT ''' + @ClientName + ''',JobName,FirstTaskAssigned
   ,NC.CaseName,NC.MatterNumber,DJ.DocCount
   ,ISNULL((SELECT TotalSize FROM DiscoveryJobSizes DJS WHERE DJS.DiscoveryJobId = DJ.DiscoveryJobId),0)/1024
   ,ISNULL((SELECT TotalSize FROM DiscoveryJobSizes DJS WHERE DJS.DiscoveryJobId = DJ.DiscoveryJobId),0)/1024/1024
   ,ISNULL(RIGHT(''00'' + CONVERT(varchar(20),DATEDIFF(second,FirstTaskAssigned,CompletionDate)/86400),2) + '':'' +
   RIGHT(''00'' + CONVERT(varchar(20),DATEDIFF(second,FirstTaskAssigned,CompletionDate)%86400/3600),2) + '':'' +
   RIGHT(''00'' + CONVERT(varchar(20),DATEDIFF(second,FirstTaskAssigned,CompletionDate)%86400%3600/60),2) + '':'' +
   RIGHT(''00'' + CONVERT(varchar(20),DATEDIFF(second,FirstTaskAssigned,CompletionDate)%86400%3600%60),2),''00:00:00:00'')
   FROM DiscoveryJobs DJ
   INNER JOIN Projects P ON P.ProjectId = DJ.ProjectId
   INNER JOIN ' + QUOTENAME(@NucleusDB) + '.Enterprise.CaseProductEnvironment CPE ON CPE.CaseProductEnvironmentId = P.NucleusCaseID
   INNER JOIN ' + QUOTENAME(@NucleusDB) + '.Enterprise.Cases NC ON NC.CaseId = CPE.CaseId
   INNER JOIN vwConfig_JobStatuses JS ON JS.JobStatusId = DJ.JobStatusId
   WHERE JS.Status IN (5,6)'

   EXEC (@SQL)

   DELETE FROM #Clients WHERE ClientId = @ClientId
END

SELECT * FROM #Results
ORDER BY JobStartDate DESC

DROP TABLE #Clients
DROP TABLE #Results

 

Let me know if you have any further questions.

 

Thank you,

Levi Roble

Technical Support Engineer 2

Link to comment
Share on other sites

Megan,

 

This sql script should return the report results:

 

/*******************************************************************
Name: eCaptureClientDiscoveryJobReport.sql
Author: Levi Roble Ipro Tech LLC
Created On: 12/16/2016
Use: Used to report on all completed or completed with errors
discovery jobs in a single eCapture environment
Notes: Run this on the Nucleus Config DB and this will not work
if there are multiple eCapture configurations on the nucleus
database. Merged jobs will not have a JobStartDate.
*******************************************************************/
SET NOCOUNT ON
IF OBJECT_ID ('tempdb..#Results') IS NOT NULL DROP TABLE #Results
IF OBJECT_ID ('tempdb..#Projects') IS NOT NULL DROP TABLE #Clients

CREATE TABLE #Results
(
   ClientName varchar(200),
   DiscoveryJobName varchar(200),
   JobStartDate datetime,
   EnterpriseCodeName varchar(200),
   MatterNumber varchar(100),
   NumberOfDocuments int,
   TotalSizeMB decimal(20,3),
   TotalSizeGB decimal(20,3),
   JobDuration varchar(200)
)

CREATE TABLE #Clients
(
   ClientId int,
   ClientName varchar(200),
   ClientDB varchar(200)
)

DECLARE @eCaptureConfigDB varchar(200),@SQL nvarchar(max),@ClientId int,@ClientName varchar(200)
DECLARE @ClientDB varchar(200),@NucleusDB varchar(200)

SET @NucleusDB = DB_NAME()

SET @eCaptureConfigDB = (SELECT ApplicationEnvironmentName FROM Enterprise.ApplicationEnvironment
wHERE ProductId = 2)

SET @SQL = 
'INSERT INTO #Clients
SELECT EC.ClientId,EC.ClientName,EC.ClientDatabase FROM Enterprise.Client NC
INNER JOIN ' + QUOTENAME(@eCaptureConfigDB) + '.dbo.Clients EC ON EC.NucleusClientId = NC.ClientId'

EXEC (@SQL)

WHILE EXISTS (SELECT * FROM #Clients)
BEGIN
   SET @ClientId = (SELECT TOP 1 ClientId FROM #Clients)
   SET @ClientName = (SELECT ClientName FROM #Clients WHERE ClientId = @ClientId)
   SET @ClientDB = (SELECT ClientDB FROM #Clients WHERE ClientId = @ClientId)

   SET @SQL = '
   USE ' + QUOTENAME(@CLientDB) + '
   ;WITH DiscoveryJobSizes
   AS
   (
       SELECT DiscoveryJobId,Sum(Cast(ItemFileSize AS DECIMAL(20,2))) + Sum(FileSizeRemainder) AS TotalSize FROM Items
       WHERE ItemStatus = 2
       GROUP BY DiscoveryJobId
   )
   INSERT INTO #Results (ClientName,DiscoveryJobName,JobStartDate,EnterpriseCodeName
   ,MatterNumber,NumberOfDocuments,TotalSizeMB,TotalSizeGB,JobDuration)
   SELECT ''' + @ClientName + ''',JobName,FirstTaskAssigned
   ,NC.CaseName,NC.MatterNumber,DJ.DocCount
   ,ISNULL((SELECT TotalSize FROM DiscoveryJobSizes DJS WHERE DJS.DiscoveryJobId = DJ.DiscoveryJobId),0)/1024
   ,ISNULL((SELECT TotalSize FROM DiscoveryJobSizes DJS WHERE DJS.DiscoveryJobId = DJ.DiscoveryJobId),0)/1024/1024
   ,ISNULL(RIGHT(''00'' + CONVERT(varchar(20),DATEDIFF(second,FirstTaskAssigned,CompletionDate)/86400),2) + '':'' +
   RIGHT(''00'' + CONVERT(varchar(20),DATEDIFF(second,FirstTaskAssigned,CompletionDate)%86400/3600),2) + '':'' +
   RIGHT(''00'' + CONVERT(varchar(20),DATEDIFF(second,FirstTaskAssigned,CompletionDate)%86400%3600/60),2) + '':'' +
   RIGHT(''00'' + CONVERT(varchar(20),DATEDIFF(second,FirstTaskAssigned,CompletionDate)%86400%3600%60),2),''00:00:00:00'')
   FROM DiscoveryJobs DJ
   INNER JOIN Projects P ON P.ProjectId = DJ.ProjectId
   INNER JOIN ' + QUOTENAME(@NucleusDB) + '.Enterprise.CaseProductEnvironment CPE ON CPE.CaseProductEnvironmentId = P.NucleusCaseID
   INNER JOIN ' + QUOTENAME(@NucleusDB) + '.Enterprise.Cases NC ON NC.CaseId = CPE.CaseId
   INNER JOIN vwConfig_JobStatuses JS ON JS.JobStatusId = DJ.JobStatusId
   WHERE JS.Status IN (5,6)'

   EXEC (@SQL)

   DELETE FROM #Clients WHERE ClientId = @ClientId
END

SELECT * FROM #Results
ORDER BY JobStartDate DESC

DROP TABLE #Clients
DROP TABLE #Results

 

Let me know if you have any further questions.

 

Thank you,

Levi Roble

Technical Support Engineer 2

Thank you Levi. You are a complete Rock Star!

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...