Extract all card transactions from Cognos11

[Auditinterne]

Hello everybody

I am trying to extract from Concur a list of all credit card transactions for all cardholders in my organisation over the prior 12 months. This is for internal audit purposes and I expect is a common request.

I have the Cognos11 BI module and have read through the catalog of reports but I can't find anything that addresses this request.

Can the community help? 🤞

[cjmarimo]

Hello @Auditinterne 

We use that standard report: Analisys -> Finances -> Credit Card Transactions

My user has rol: Cognos Business Author

If you have the BI manager role, you will only be able to see the data of the employees assigned to you as a supervisor.

BR,

cj

[KevinD]

@Auditinterne I think @cjmarimo gave you the best option. 🙂 Let us know if this report isn't working for you.

[Auditinterne]

Thank you @cjmarimo & @KevinD 

I seem to have the correct BI profile to see the whole organisation’s data and was able to run that standard report.

It gives a base level of transactional data but I was hoping for a richer array of fields in the report. It seems to only provide:

Payment Type / Currency / Employee / Transacton Value / Merchant / Transaction Date / Posted Date/ Posted Amount.

Is it possible to modify the fields in this standard report?

We have recently migrated from another EMS and could extract the fields below:

EMPLOYEE ID / NAME / EMAIL

TRANSACTION REFERENCE

TRANSACTION LINE

POSTING DATE

TRANSACTION DATE

TRANSACTION TYPE

BILLING CURRENCY

AMOUNT

TAX AMOUNT

SUPPLIER INFORMATION

TRANSACTION STATUS

APPROVAL STATUS

SUPPLIER ID

SUPPLIER NAME

LINE AMOUNT

LINE TAX

SOURCE CURRENCY

SOURCE AMOUNT

Expense Description (Mandatory)

RECEIPT STATUS

GL Code

CC Code

Project

SUPPLIER CITY

SUPPLIER STATE

SUPPLIER COUNTRY CODE

SUPPLIER TAX REFERENCE

MERCHANT GROUP

MERCHANT CATEGORY

MERCHANT CATEGORY CODE

TAX CODE

WORKFLOW STATUS

LAST APPROVER

DATE

APPROVER COMMENT

ACCOUNT HOLDER COMMENT

[KevinD · Accepted solution]

@Auditinterne you can modify the standard reports, but it can be tricky because many of them contain Unions or Joins so you would need to know how to modify the different queries used in the report itself. There is a recording that shows how to modify a standard report that can give you an idea of how to do it. You can find the recording here: https://www.concurtraining.com/cnqr/cognos-11-registration 

You may consider just building your own report. The list of items is quite long and there isn't a standard report that would include all those items, so building your report might be the best option. 

[Auditinterne]

Thank you @KevinD It looks like I'm off to register for that training.

[Auditinterne]

For anyone looking for help on this in the future, I was able to get most of what I need by:

• Requesting the Cognos Professional Author role
• Building a custom report in Cognos 11 using Report Studio (accessible through the Home/Reporting/Intelligence drop-down in Concur.

The training here was helpful to start me off Building Custom Reports in Report Studio

As was the Data Model User Guide for expenses Analysis / Intelligence: Data Model - Expense Folder User Guide (concurtraining.com)

There is still some work to do to locate the table in which specific fields are saved – but I’m making good progress.