I created a report using Query Studio in IBM Cognos and I noticed that the report is ignoring unassigned transactions.
Do you know if it is possible to include unassigned transactions to a report using query studio?
Unassigned transactions should be included, however keep in mind that the other data items on your report are probably preventing them from being displayed. We call this an inner join. I would need to see a copy of your report to better guide you. Feel free to take a screenshot of all the data items on your report and send it to me in a private message. I'm fairly confident that you have a data item or two that is keeping the unassigned transactions from displaying.
Thanks Kevin, I will review the fields from the report again. If I still have a problem I will send you a message. But it's good to know that it is possible. Thanks.
Hi Kevin, I wasn't able to run the report with the unassigned transactions using query studio.
I left in the report only the fields 'Transaction Date' and 'Employee ID' and no unassigned transactions are showing.
If you can open your report and take a screenshot of all the data items, that would help me greatly to know what might be causing the issue.
By unassigned transactions, do you mean credit card transactions not yet put into expense report or e-receipts and other items under 'Available Expense'?
If you mean unassigned credit card transactions, you should use the join function in reporting studio. Query studio does not have this function. Use 'Left Join' for columns in credit card transactions and columns in expense report.
If you mean all other items under 'Available Expense', I don't have a solution yet
Where are your Transaction Date and Employee ID fields coming from? If you're pulling them from certain folders, it would cause unassigned transactions to not show up. So I'm assuming you're pulling info from here for the unassigned transactions:
Expense > Expense Reports > Credit Card > Credit Card Transactions > Transaction Status Value
For the Transaction Date and Employee ID fields, you'll want to make sure you pull from here:
Expense > Expense Reports > Credit Card > Credit Card Transactions > Transaction Date
Expense > Expense Reports > Credit Card > Credit Card Accounts > Employee ID
If you're pulling those two fields from somewhere else, it will hide unassigned transactions. For instance, if you're pulling them from here:
Expense > Expense Reports > Entry Information > Transaction Date
Expense > Expense Reports > Report Header Information > Employee ID
Then that will not work. The reason is because those two fields are looking for an assembled report, and are pulling data off of those. Since the unassigned transaction does not exist on a report, those two fields are null, and are therefore hidden from output. To find information about the transactions outside of a report, you'll need to pull fields that exist outside of a report. Any piece of information you need about the transaction will need to come from folders that are not dependent upon a report (or entry on a report) existing.
I know that with the filter options there are the selections to include missing values, but in my experience it doesn't work this way. Likely it's the 'inner join' issue that others have mentioned.
Hope that helps!
Thanks for your reply @Josh and @Felix!
You are both correct. I was trying to pull credit card unassigned transactions from the entry information. It looks like that it's not possible to get all transactions (assigned and unassigned) in the same report using Query Studio.
I believe that the only option is using the join function in Reporting Studio. I am new to Report Studio, but I will try to figure it out.
Thanks for your help.
@fbromeiro, yes, that's correct. When I need a report for transactions, I have to run two separate reports for assigned and unassigned. I only have access to Analysis, so I'm not sure how Intelligence / Reporting Studio functions, but good luck building your reports!
No problem @fbromeiro. Except for Join function, there is an easier way to do this call "Master-Detail" relationships for nested lists.
What follows is an example I got from Concur Training. It's not the exact steps to join expense report and credit card transactions but the logic is the same. Hope it helps.
If you include comments or attendees or allocations in a query against expense reports entry rows in the database, you run the risk of repeating the expense amount for every occurrence that joins to the expense line. The report creates a new line for every combination.
To eliminate this situation, you’ll need to create a second query that is nested in the first: