fbromeiro
Occasional Member - Level 1

Reporting - How to add unassigned transactions using Query Studio?

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?

Thanks.

0 Kudos
8 Replies
SAP Concur Employee
SAP Concur Employee

Re: Reporting - How to add unassigned transactions 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. 

Kevin

0 Kudos
fbromeiro
Occasional Member - Level 1

Re: Reporting - How to add unassigned transactions using Query Studio?

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.

0 Kudos
fbromeiro
Occasional Member - Level 1

Re: Reporting - How to add unassigned transactions using Query Studio?

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.

0 Kudos
SAP Concur Employee
SAP Concur Employee

Re: Reporting - How to add unassigned transactions using Query Studio?

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.

Thank you,

0 Kudos
Felix
Occasional Member - Level 2

Re: Reporting - How to add unassigned transactions using Query Studio?

Hello,

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 Cat Happy

Cheers,

Felix

Josh
Routine Member - Level 2

Re: Reporting - How to add unassigned transactions using Query Studio?

Hey @fbromeiro,

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!


Josh
fbromeiro
Occasional Member - Level 1

Re: Reporting - How to add unassigned transactions using Query Studio?

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.

Fernando

0 Kudos
Josh
Routine Member - Level 2

Re: Reporting - How to add unassigned transactions using Query Studio?

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


Josh
0 Kudos