cognos report querry open expenses till previous day

[Ilonaglowacka]

Hello,

In our daily Cognos report for the previous day, expense reports if thay have  Approved Amount of 0 EUR, they are not appearing.

For example, a report with a Total Report Amount of 410.35 EUR and an Approved Amount of 0 EUR is missing from the Cognos report. However, the  similar report is available in the daily SAP Concur Processing List.

SAP Concur previously advised us that:

“A report with a 0 EUR approved amount will only appear if it matches all selected filters, including Approval Status and Company Code. If it doesn't match those filters, it will be excluded.”

Following this guidance, we completely removed the Approved Amount column from the Cognos report. The Total Report Amount column remained in the report. However, expense reports , if they have  an Approved Amount of 0 EUR are still not appearing.. 

 

What could be causing these reports to be excluded from the Cognos report, even though their  Report  Total Amount is higher than 0 EUR and they are visible in the Processing List?

Could this be related to the query structure, Approval Status, Company Code, joins, or another filter?

Thank you in advance for your help.

[PoojaKumatkar]

Hi @Ilonaglowacka ,

If my understanding is correct, you are expecting report details for expense entries where the Approved Amount is 0 EUR. Please confirm if my understanding is correct.

If that is the case, you will need to include expense entry-level details in the report and apply a filter on Approved Amount between -0.01 and 0.01. This will return all expense entries with an approved amount of 0.00 EUR. All of these details needs to be picked up from Entry Information folder instead of Report Header Information folder.

Add below columns and apply suggested filter. It should work.

Note - This report is primarily intended to identify expense reports that contain expense line items with an approved amount of 0.00.

Result: 

Note - I recently worked on similar requirement where I created report with same logic and it is giving expected result.

If this answers your query, then please mark solution as accepted!

[Ilonaglowacka]

Hi PoojaKumatkar,

Thank you for your response.

We are not trying to create a report that returns only expense entries with an Approved Amount of 0.00 EUR.

Our report is designed to return all open expense reports from the previous day, regardless of the Approved Amount.

The problem is that we have identified several expense reports that are visible in the SAP Concur Processing List and have a Total Report Amount greater than 0 EUR, but they are not returned by the Cognos report. So far, the common factor seems to be that these reports have an Approved Amount of 0 EUR.

We already removed the Approved Amount column from the report following previous guidance from SAP Concur, while keeping the Total Report Amount column. However, these reports are still missing.

Therefore, we are trying to understand why otherwise valid expense reports are being excluded from the Cognos query, rather than specifically reporting on entries with a 0 EUR approved amount.

[PoojaKumatkar]

Hi @Ilonaglowacka ,

Could you please share the list of columns included in your current expense report, along with any filters applied? If possible, please also share a screenshot for reference.

Based on Concur's response in the screenshot above, it appears that filters on the Approval Status and Company Code columns may be excluding certain reports from the results.

Could you please try removing the filters applied to these two columns and check whether the expected results are displayed?

If this answers your query, then please mark solution as accepted!

[Ilonaglowacka]

hello here are the column names

 Employee | Employee ID | Employee Assigned to Step | Role | Step | Step Entry Date/Time | Step Action Date/Time | Report ID | Report Name | Approval Status | Custom 1 - Name | Cost Center - Code | Project - Code | Custom 1 - Name | Custom 1 - Code | Custom 2 - Name | Custom 2 - Code | Custom 3 - Name | Custom 3 - Code | Total Report Amount | Reimbursement Currency | Step Entry Date/Time | Step Action Date/Time | Ever Sent Back | Last Submitted Date

[PoojaKumatkar]

Hi @Ilonaglowacka ,

Can you please check your private message and respond me there.

Now follow below navigations to create report. I will send report code on your email. For that please check my personal msg.

Required Role - Cognos Professional Author

• Under Team Content >> Click New + (top right) >> Report
• Select and double click Blank template >> select Concur Data Warehouse Source >> Open
• Click 3 dots (top right, beside Properties) >> Click 1st Option (Open report from clipboard) >> Popup window (click No) >> Copy paste Code given in my text file >> Click OK.
• Save report by clicking Save as (top left corner save icon)
• Run the report using Excel or Excel data option (top left 5th icon after edit option)

Note - This is just a basic draft of the report, and I have not applied any filters. Please validate the results first and check whether the previously missing expense reports are now appearing. If everything looks good, you can then apply the necessary filters to the same report based on your requirements.

If this answers your query, then please mark solution as accepted!

[KevinD]

@Ilonaglowacka do you have any filter set on the Approval Status column? 

Also, for future reference, by adding Total Approved Amount, you will automatically exclude any reports that are not fully approved. Approved Amount does not include reports approved by the default manager, it looks at Approved Amount as the amount after final approval. Just a little tip for future report building.

I'll await your reply about the approval status column. Oh, do you happen to have a filter set on Ever Sent Back? This filter would either be "N" or "Y". Let me know.