How to combine Assigned and Unassigned Card Transactions

[akumaryu]

How do I go about combining assigned and unassigned credit card transactions on one report in "Report Studio?" Should I be using the "Join" function, and if so what "Cardinality" should I be using; and what are the best "Keys" to use in this case? Thank you!

[GrantC]

This is a great question. You should use a Union, instead of a Join. Both are different ways to merge two data sets, but they go about it differently. One great way to differentiate between them is a Union adds rows, while a Join adds columns. Since assigned and unassigned transactions have many of the same data elements (transaction date, vendor, amount, etc) using a join would cause the data in the report to be out of alignment.

Unions are great for taking two data sets that have the same columns and combining them. The trick is both sides of the Union must have the same number of fields in the same order. If there is a data column that exists in one query but not the other (Report Name will exist on the assigned charges, but not the unassigned charges) you can simply add a blank to the query that does not have that data value.

Also, no keys or cardinality are required for a Union.

I'd recommend looking at the details of the Expense Accruals report to see how it works.

[akumaryu]

Thanks Grant! I took your recommendation and utilized one of the Expense Accrual reports - working backwards, I dismantled and reassemble the report to suit; however, I am having trouble with adding and configuring two data elements "Current Date" and "Employee Country Code."

1. Upon adding the Current Date to both unassigned and unsbmitted expenses query and then create the union, then run - the report came back with an error.

2. Upon adding Employee Country Code to both unassigned and unsubmitted expenses (added a filter and prompt) to the query then create the union, selected one region then run - the report came back with other all regions and not the one that I have selected.

What am I doing wrong in both instances? Thanks!

[GrantC]

Without seeing it, I would guess the issue is related to where you are sourcing the fields for the two sides of the union. All the data elements for the unassigned query must be sourced from the Credit Card folder under Expense\Expense Reports\. If you add data elements from out side of that folder to the unassigned query it will restrict the items getting returned on that side of the join and cause issues in the over all report.

The data elements on the unsubmitted side can come from anywhere.

Employee Country Code can be found under Expense\Expense Reports\Credit Card\Credit Card Accounts\Additional Employee Details\

Since Current Date does not exist in the Credit Card folder you will need to create it on the unassigned query. To do so, add a Data Item from the Toolbox and name it Current Date. In the Expression Definition type “current_date” but without the quotation marks.

 That should hopefully do the trick. If it doesn’t, please send me a private message with your company name and the location of the report and I’ll take a look.

[akumaryu]

Hey Grant - Thanks!

Keep in mind, I am doing a "Union" not a "Join." I am adding the data from the respective sources - For the unsubmitted expenses, I am pulling the Employee Country Code from the Expense Folder and for the Unassigned, I am pulling the Employee Country Code from the Expense/Credit Card folder. The issue is when I add a filter and prompt on the "Employee Country Code" and run the report to the unsubmitted query, the report comes back with the selected country code on the unsubmitted query and all other country codes included the selected on the unassigned - remember the report is ran after the union is done.

As for the "Transaction Date" - Thanks a lot! I was able to figure that out per your instructions.

[DeniseNATS]

I’m trying to do exactly the same report, show all transactions and the report names for those that are in a report, blank spaces for those unassigned to a report. I am using a report with a Join and no matter what cardinality I use I only get results for transactions that are in a claim, not those unassigned. Your reply does not seem to do what I want, as it is columns (report name) that I need to add and not rows. Are there instructions for using Union, Intersect and Except joins? The instruction document I have does not cover that.

[GrantC]

Remember that Joins add columns and Unions add rows. So, a Union is the way to go to solve this problem. The trick to a Union is all the queries involved must have the same columns in the same order for it to work. You can use the analogy of two spreadsheets: if I have two spreadsheets that I need to combine into one, as long as both spreadsheets have the same columns in the same order I can simply copy and paste the data from one into the other. That is essentially what the Union is doing: takes two separate data sets (one for assigned transactions and one for unassigned transactions in this example) and merge them into a single data set.

One thing to keep in mind is placeholders are allowed if one of the data sets does not have data that the other one does. For example, the data in assigned transactions query will have Report Names and Business Purposes, but the unassigned charges won't; they're not on an expense report and that data only exists in the context of an expense report. In this case you can put a blank space or insert some generic text like 'unassigned' in the unassigned transactions query.

We do not have specific instructions for Unions, Intersects, and Excepts (Intersects and Excepts are both very rarely used, I don't think any of the Concur provided reports use them and I can say I have never used them in my entire career) but we do have some resources on Unions that might be useful and they both involve the Expense Accruals report in the Accruals folder under the Standard Reports. This report is already doing exactly what you are asking for, except it also includes cash expenses. So, for a lot of customers simply modifying that report is easier than starting from scratch.

First, I'd recommend opening that report and drilling down into the queries it is using to see how they are setup and what the different filters are doing. Take a look at how the two queries in the Union are “balanced” and have the same fields in the same order.

Next, there are two videos that might be useful:

This one guides you through the process of adding columns to the Accruals report. http://assets.concur.com/concurtraining/cte/en-us/cte_en-us_rpt_accruals-modification.mp4?width=80%2...

This one is more general instruction on how to modify standard reports in general. At the 19:30 mark is where the instructor goes into the Accruals report. It covers the same ideas on adding fields as the previously mentioned recording, but also shows how to add filters and prompts.

http://assets.concur.com/concurtraining/learning-services/customer_learning_series_standard/Customer...

I hope this helps.