Automate

Trying to create an ROI Evolve Report aggerated by solution, month and user. 

Below is what I came up with but its not working.

SELECT 
    DateName(month, EOMonth(RPT_TransactionRoiView.ActivityDateTime)) + ' ' + 
    CAST(Year(EOMonth(RPT_TransactionRoiView.ActivityDateTime)) AS nvarchar) AS 'Month',
    RPT_ProcessReportingDetailsView.SolutionName AS 'Solution Name',
    SUM(RPT_TransactionRoiView.RecordsUploaded) AS 'Uploaded',
    SUM(RPT_TransactionRoiView.RunErrors) AS 'Errors',
    RPT_TransactionRoiView.SAPSystem AS 'SAP',
    SUM(RPT_TransactionRoiView.TimeSaved) AS 'Time',
    RPT_TransactionRoiView.Username AS 'User',
    RPT_ProcessReportingDetailsView.AppName AS 'App Name'
FROM 
    RPT_TransactionRoiView
RIGHT JOIN 
    RPT_ProcessReportingDetailsView
    ON RPT_TransactionRoiView.SolutionId = RPT_ProcessReportingDetailsView.SolutionId
GROUP BY 
    RPT_TransactionRoiView.Username, 
    DateName(month, EOMonth(RPT_TransactionRoiView.ActivityDateTime)),
    Year(EOMonth(RPT_TransactionRoiView.ActivityDateTime)),
    RPT_ProcessReportingDetailsView.SolutionName,
    RPT_TransactionRoiView.SAPSystem,
    RPT_ProcessReportingDetailsView.AppName;

Hi Joseph

Time columns are unfortunately text fields

You'll want to convert to minutes or something like that - example 

will give you this

You can swap to Minute instead of seconds instead and roll up to hours/days - your choice. . And please consider using TotalTimeSaved to calculate time savings.

FYI

Sigrid

------------------------------
Sigrid Kok
Precisely Software Inc.
------------------------------