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.
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!
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.
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.