Reporting - How to add unassigned transactions using Query Studio?

[fbromeiro]

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.

[KevinD]

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

[fbromeiro]

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.

[fbromeiro]

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.

[KevinD]

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,

[Felix]

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

Cheers,

Felix

[Josh]

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!

[fbromeiro]

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

[Josh]

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

[Felix]

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:

• Remove the Comment from the main query (if the field is still there).
• Go to the Page Explorer tab and open Page 1. From the toolbox to the left, add a List Item to the page output - this will create a new imbedded query. Just drag the list item over and drop it as you would a column on the layout. Should look like the picture below at this point.

• Go to the Query Explorer tab and open this new Query. It should have no data items at this point. Add the field “Comment” from the Expense Entry Comments section. Also, add the field “Associated Report Entry Key” from the Comments > Keys section. The query should now have these two data items.
• In the first Query, from the Entry Information > Keys section, add the field “Entry Key”
• In Page Explorer, on Page 1, right-click inside the List and create a Master Detail Relationship

• Link the Keys when the Master Detail box comes up

• Drag the Comment field from the 2nd query into the Page’s List item

For aesthetics

1. In List property, change the report’s Master Column Heading from ‘List’ to “Comment” (Text Source > Text)
2. In List property, change the list’s General > Column Titles to “Hide”
3. In List property, change the list’s Conditional > No Data Contents to Specified Text of null
4. In 8 and 9, make sure only your list is highlighted and not the entire report