Hi,
IDEA can be applied to gain oversight into the top Travel & Entertainment (T&E) claim recipients in any given period.
So let us say you would like a report on Employees who are receiving more than 'x' % of T & E claims individually as a percentage of the total departmental claims for a period.
Following would be the procedure in IDEA to get the report on the Claim File -
a. Append a virtual numeric field for year using @year(ClaimDate)
b. Append a virtual numeric field for month using @month(ClaimDate)
c. Perform a Summarization with Fields to Summarize being Year, then Month, then Department, then Employee ID and Employee Name. Choose Fields to Total On as Claim Amount - this will give you a report of individual employee wise travel claim summary for each year month and department
d. Now perform a Summarization with Fields to Summarize being Year, then Month, then Department. Choose Fields to Total On as Claim Amount - this will give you a report of department wise travel claim summary for each year month.
e. Now Join the database from step c with the database from step d with matching key field being Year, Month and Department - the report will provide a comprehensive report of year wise month wise individual employee claim summary to departmental claim summary for the same period
f. Now append a virtual numeric field for percentage of individual claim summary to departmental claim summary using criteria ClaimAmountSum/ClaimAmountSum1*100
g. Finally apply a Direct Extraction to isolate all employees submitting total claims in any year month above 'x' % of the departmental claims.
Kind Regards
Jairam