Average room rate from Expense data

[melanieG]

Dear all,

hope someone can help me or had the same question in the past and was able to solve it.

Unfortunately I couldn´t find a standard report that would help me determine an average room rate based on the travel expenses submitted and paid.

Is it correct, that there´s only a report based on Expense data for this case, which would be EXPENSE ENTRY ANALYSIS DETAILS (GROUPED BY EXPENSE TYPE)?

Unfortunately this one doesn´t include the overall room nights from the main entry mask (Details) where you actually have to enter the check-in and check-out date.

If the transaction date in the itemziation is the date of the actual payment, it is impossible to find out from the standard report how many nights the employee spent in the hotel, as the report will then only show the total price.

When using the report "Hotel Summary", only the data from Travel is displayed. However, it would be useful to also include "send to payment", as employees might also book their hotels outside of Concur.

Thanks a lot for some helpful hints.

Best regards,

Melanie 

[KevinD]

@melanieG if you are looking for the number of nights and an average amount of those nights, you can fairly easily build this report yourself in Cognos. 

Since you are only concerned with Hotel expenses, you can build a report that has: Employee Name, Report Name, Check-in Date, Expense Type (filter this to only show hotel), Expense Amount (reimbursement currency) and then make sure you apply the Exclude Parent pre-built filter. Group the report by Employee Name, Report Name and Check-in Date. Once grouped, click the Summarize button (seen below) and choose Average. This will give you the average. See my second screenshot below. Don't mind the Vendor and Transaction Date columns, those aren't needed to show the average. If you don't need the lines highlighted in my screenshot, you can use the Headers and Footers option to hide them the Summarization for Employee, Report Name and Check-in Date. 

[melanieG]

Thanks a lot for your reply, Kevin, that´s what I was looking for.