Expense Accrual Date Prompt

[jenrusniak]

I would like to be able to schedule the standard expense accrual report to go out to our accounting teams at month end but I am getting stuck at creating a dynamic date prompt.  How do you create a dynamic date prompt for this report?

[rohanpatil]

Hi @jenrusniak 

I would setup a prompt called last month as part of the prompt page to support the automated schedule. I would use the expression below in the corresponding query.

(?pOpt? = 'Lastmonth' AND _month_of_year (current_date) - 1 = [Expense].[Entry Information].[Transaction Month] AND year(current_date) = [Expense].[Entry Information].[Transaction Year]

Hope this helps.

[KevinD]

@jenrusniak if you are looking to schedule this report to run automatically, you shouldn't use a dynamic date prompt. Prompt means someone needs to input something, but scheduled reports run in the background, which means they do not appear to any user. That's why you can't schedule reports to run that have prompts built into them.

I think you mean you want a dynamic date range for the report. What kind of date range are you looking this report to show? Previous month only, last 60 days, last 90 days, last seven days? Something like that?

[jenrusniak]

I am wanting to schedule the expense accrual report to run as of the last day of the prior month.  There are multiple queries in the standard report (Unpaid Entries and Unsubmitted CC Trans) that look like they are pulling dates from different places and that's where I am getting stuck on where to put the dynamic date range. 

[kelseymast]

Following this thread. @KevinD any suggestions? For my team, I am attempting to schedule accrual reports to pull in any transactions which took place during the previous fiscal month. I am stumped on how to add this logic with transaction dates instead of sent for payment dates. 

[KevinD]

@kelseymast a couple of questions for you:

1. Are these reports you or someone at your company built?

2. Have you configured your Fiscal Calendar months in Expense Admin>Reporting Configuration?

I'll await your reply.

[kelseymast]

Hi @KevinD. To answer your questions above: 

1. I started with the Expense Accrual by Date Range Report, but have added some additional columns using the training videos online to capture all the details needed by our team. I have it labeled as Expense Accrual by Date Range_Updated with Ledger & GL Code in My content. 

2. I have configured our Fiscal Calendar month within Concur. 

Thanks in advance!

[KevinD]

@kelseymast alright, so as the Beatles once sang, "I get by with a little help from my friends". My reporting contacts provided the following expression to return data from the previous fiscal month. You would need to add this expression to the correct data item one of the Queries used in that report.

    ([Expense].[Report Dates and Statuses].[Current Date Fiscal Month Number]<>1 AND

     [Expense].[Report Dates and Statuses].[Fiscal Month Number] = [Expense].[Report Dates and Statuses].[Current Date Fiscal Month Number]-1)            

[kelseymast]

Hi @KevinD. Thanks for the advice. I think this makes sense to add this to the query where expenses have already been added to an expense report. I think there may still be a gap after adding this in and running the report because it does not include logic for any expenses which have not already been added to a report. Any suggestions on how to only include transactions that have a transaction date which took place in the previous fiscal month, regardless of if they are assigned to a report or not?