How to create an expense accrual report with many custom fields in Query Studio

[DMicu]

I have access just to Reports Analysis, therefore I cannot modify the standard expense accrual report.

I built my own query,  but cannot figure out how to add the unassigned expenses charged to the employees' corporate card.

Also, how can I exclude expenses that were already processed and posted to the G/L in the month that I'm checking the accruals for?

Thank you,

Diana

[TammyK]

I built our Accrual but had to request assistance from Concur by submitting a case ticket to have them write a query that will include "Un-assigned" credit card transactions.

I also had to request that it eliminated "Payment Processing and Sent for Payment" status.

2nd screen shot shows Approval Status column which includes expenses that are (Unassigned to a report, Assigned to a report but not submitted, Submitted pending manager approval and Approved by Manager and pending Accounting Review (aka Concur Processor)).

[DMicu]

Thank you, Tammy

I appreciate your prompt answer. it will not work in my case as I don't have the possibility of changing a report or combining two queries at the same time.

[KevinD]

@DMicu The example that @TammyK posted is showing an example of a multi-query report, which requires you to have our Business Intelligence offering. Query studio can only build single-query reports. 

You might be able to get a report that is close to what you are looking for, but it depends on what data you want included in the report. There are some data items, that when added to your query, might cause other data to disappear. This is due to the data warehouse being a relational database. I'll give an example. If you have a data item on your report that is Approved Amount and then you add the transaction status of Unassigned, your report might show as No Data Available. Why? Because Unassigned transactions cannot be approved, so there wouldn't be a relationship between Unassigned transactions and an approved amount. 

You may need to build two separate reports, run them in Excel, then manually combine them. If you take a screenshot of the data items you currently have on your query, I can tell you what might need to be removed. You can simply screenshot the column headers of your query. 

[DMicu]

Hello Kevin,

I learned the hard way about the data disappearing 🙂 so in a way I've got that figured out more or less. So I decided to run

two separate queries, export to excel and add some formulas to get the results that I'm looking for. I didn't succeed 100%, but I didn't have enough time today to dedicate to this.

I built a query for Unassigned expenses, and it looks like it the totals are ok; these are the columns in that query: 

And I'm trying to build another query that will give me the rest of the information, so the total of them will agree to the Expense Accrual Report by date that is in our Standard Reports.

The second query:

The accrual expense report by dates columns are:

The report has subtotals by employee name and ID, and the name is shown across the entire row at the beginning and end of the subtotal.

Thank you so much for trying to help!

Regards,

Diana