Rahul4
Occasional Member - Level 3

Cognos reporting

Hello team,

 

I need below fields in the report, there are few employees only on L1 and I can use employee assigned to step filter but I am not getting report exceptions and other details in the same report, please help me to build such report

Report NameEmployee nameL1 Head NameEmployee IDEmployee LocationStepStep Entry Date/TimeOrg Unit 2 - CodeOrg Unit 4 - CodePolicyReimbursement CurrencyTotal Approved Amount (In local currency)Approved Amount in USDEntitlement in USDException amount in USDCountry (of Employee)TAT W/O WeekendsFirst Submitted DateApproval StatusEmployee Assigned to StepReport Exception Policy 
17 REPLIES 17
samasuraj
Occasional Member - Level 3

Hi @Rahul4 

 

Topic: Cognos reporting

 

Please check the below. Hope this helps.

-----

SAP Concur Cognos Reporting – L1 Approval Workflow, Exceptions & TAT

Yes, this reporting requirement can be approached in SAP Concur Intelligence (Cognos).

However, the challenge is combining Expense Report details, Approval Workflow history, Employee information

and Report Exceptions into a single report without duplicating records.

The key issue is that these fields may belong to different Cognos query subjects and operate at different reporting levels.

 

The main challenge is combining Expense Report details, Approval Workflow information and Report Exceptions,

as these come from different query subjects.

 

Create the below Three Queries:

  1. Expense Report Query – Employee details, Report Name, Policy, Approved Amount, Currency, and Submission Date.
  2. Approval Workflow Query – L1 Step, Assigned Approver, Step Entry Date/Time, and Approval Status.
  3. Report Exceptions Query – Exception details associated with each Expense Report.

Apply the L1 filter within the Approval Workflow query, then join the queries using the common Expense Report identifier.

 

Use an appropriate Outer Join to retain reports without exceptions.

Please also ensure that multiple exceptions do not duplicate the report-level approved amounts.

 

For TAT excluding weekends, you may need a calculated field or Business-Day Calendar.

The exact query subjects and relationships will depend on your Cognos Reporting Package.

 

Cognos Build Sequence:

  1. Query 1 – Expense Reports: Retrieve report identifiers, employee details, policy, approved amounts, currency, and submission date.
  2. Query 2 – L1 Approval: Retrieve workflow step, assigned approver, entry date/time, and approval status. Apply the L1 step filter here.
  3. Query 3 – Report Exceptions: Retrieve report-level exception details and any available exception codes or descriptions.
  4. Join the queries: Use the common Expense Report identifier, with appropriate outer joins to preserve reports that have no exceptions.
  5. Handle multiple exceptions: Aggregate exception descriptions or display them in a separate detail section to avoid multiplying report amounts.
  6. Calculate TAT: Use workflow timestamps and a business-day calendar that excludes Saturdays and Sundays. Account for holidays separately if required.
  7. Validate: Compare the results against a few known Expense Reports in SAP Concur, especially reports with multiple exceptions.

Do not sum the approved amount after joining workflow and exception detail rows unless the data has first been brought to the correct reporting grain. Otherwise, amounts may be overstated.

 

 

Thanks & Regards

--

Samantha

SF_EC & ECP / SAP Concur Consultant

KevinD
Community Manager
Community Manager

@Rahul4 my answer is based solely on your screenshot. I believe since you have L1 Head Name, this will filter out any other employees that do not have an L1 Head. Think of each data item as a condition that must be met. 

 

Here is an example. Lets say we add the data item of Employee to a Cognos report. If we run this simple query, the results would be any employee that has a Concur profile. Now lets add the data item Report Name to the query and run it. You might see some employee names disappear from the report. By adding Report Name any employee who has never created a report would be left off. While they might have a profile, they don't meet the "condition" of having a report associated to their name. 

 

I'm telling you this in hopes it will help you better see how the Cognos data warehouse works and that you need to sometimes create joins or unions to ensure all the data you are looking for appears in the report you are building. 

 

Providing the steps for creating the report you are requesting would take me probably about half my day to write out. Also, it depends on what your level of report building expertise is. On a scale of 1-10, would you rate yourself above or below a 5? This also helps me know how to better assist you. 


Thank you,
Kevin
SAP Concur Community Manager
Did this response answer your question? Be sure to select “Accept as Solution” so your fellow community members can be helped by it as well.
Rahul4
Occasional Member - Level 3

Hello @KevinD @samasuraj 

I will rate myself above 5

samasuraj
Occasional Member - Level 3

Hi @Rahul4 

 

Topic: Cognos reporting

 

In response to my response above, please check the below. Hope this helps.

I am providing the brief steps for Cognos Report Build.

@KevinD will provide you more Advanced Steps in detail.

-----

SAP Concur Cognos – Report Creation (Brief Steps)

  1. Open Cognos: SAP Concur → Reporting → Intelligence → New Report → List.
  2. Query 1 – Expense Report: Add Report ID, Employee, Policy, Amount, Currency, and Status.
  3. Query 2 – L1 Workflow: Add L1 Head, Approval Step, Step Entry Date/Time, and Assigned Approver.
  4. Query 3 – Exceptions: Add Report ID, Exception Code, and Description.
  5. Apply L1 Filter: Filter for reports currently pending at L1, if that is the requirement.
  6. Join Queries: Use Report ID. Join Report + L1, then left-join Exceptions.
  7. Calculate TAT: Calculate approval duration excluding weekends.
  8. Validate Amounts: Prevent duplicate amounts from multiple workflow steps or exceptions.
  9. Run & Export: Validate results, then export to Excel.

 Build Three Separate Queries—Expense Report + L1 Workflow + Report Exceptions and combine them

using Report ID.

 

Exact field names and query subjects depend on your SAP Concur Cognos Reporting Package.

 

 

Thanks & Regards

--

Samantha Prathikantam

SF_EC & ECP / SAP Concur Consultant

KevinD
Community Manager
Community Manager

@Rahul4 did you test out to see if the L1 Data item you have added is causing the issue of data not showing? Cognos is a lot of trial and error, so give this a try. Remove the item and see if it changes what data is displayed. 

 

For full transparency, when it comes to Cognos reports I always ask customers to give things a try first and when they have exhausted all possibilities they can think of, then come here and ask me for some guidance. Cognos is different because providing steps for Cognos reports in relation to something like, clearing Concur Expense exceptions, is much more detailed and takes me a lot more time to write out. 

 

Try my suggestion and let me know what happens. 🙂


Thank you,
Kevin
SAP Concur Community Manager
Did this response answer your question? Be sure to select “Accept as Solution” so your fellow community members can be helped by it as well.
Rahul4
Occasional Member - Level 3

Hello @KevinD 

I have created one query for report details and TAT and second query for exceptions and join with 1.n and 1.n cardinality and it is showing data, Its just some exceptions are blank in the rows, rest everything is fine

Rahul4
Occasional Member - Level 3

Hello @KevinD @samasuraj 

If there are 3 exceptions in the report then 3 rows are getting created in the excel, i need one row for one report id and all 3 exceptions should come in the same row

samasuraj
Occasional Member - Level 3

Hi @Rahul4 

 

Topic: Cognos reporting

 

Please check the below. Hope this helps.

-----

Blank Exception fields may occur when no matching exceptions exist.

If Exceptions exist but are missing, review the Join Keys, Filters and Query Relationships.

 

Check:

  1. Report ID: Verify the join key in both queries.
  2. Exception Data: Confirm whether exceptions exist for the affected reports.
  3. Join Type: Use a Left Outer Join to retain reports without exceptions.
  4. Cardinality: Validate the relationship - 1 (one-to-many) when Query 1 has one row per report and Query 2 has multiple exceptions.
  5. Duplicates: Check for multiple matching records that may over calculate report totals.

Cognos creates multiple rows because one Report ID has multiple exceptions.

To display one row per Report ID:

  1. Group Query 2 by Report ID.
  2. Concatenate all exceptions using LISTAGG or STRING_AGG, if supported by your data source.
  3. Left Outer Join Query 1 and Query 2 using Report ID.

This will display all three exceptions in a single Excel row.

Changing cardinality alone will not combine multiple exceptions into one row.

 

 

Thanks & Regards

--

Samantha Prathikantam

SF_EC & ECP / SAP Concur Consultant

Rahul4
Occasional Member - Level 3

Hi @samasuraj @KevinD 

Grouping is done on the page level and not on the query level is what I believe. I don't find any option to group the report id on query level. I want all 3 exceptions of the report in a single row.

Rahul4
Occasional Member - Level 3

Hello @samasuraj  @KevinD when grouping on the page level, one row is getting created for report id and other fields are getting captured in the 3 rows including exceptions

samasuraj
Occasional Member - Level 3

Hi @Rahul4 

 

Topic: Cognos reporting

 

Please check the below. Hope this helps.

-----  

You are correct!

Grouping the Report ID on the report page alone will not achieve the required single-row data structure.

 

Page-level grouping groups the report display but does not combine multiple exception records into a single data row. That is why the Report ID appears once while the three exceptions remain on separate rows.

 

To display all three exceptions in one Excel row per Report ID, the exception values must be consolidated into one field, or placed into separate columns, before the final report output.

 

To achieve one Excel row per Report ID with all Three Exceptions:

  1. Keep Query 1 at one row per Report ID.
  2. In Query 2, use a supported string aggregation function such as LISTAGG to combine exceptions by Report ID.
  3. Left Outer Join the consolidated Query 2 with Query 1.

If string aggregation is unavailable in your Cognos reporting package, check whether a Repeater with a Master-Detail relationship can meet your layout requirement.

 

 

Thanks & Regards

--

Samantha Prathikantam

SF_EC & ECP / SAP Concur Consultant

Rahul4
Occasional Member - Level 3

Hello @samasuraj  @KevinD 

LISTAGG and string aggregation are not supported, is there a separate licence to use these functions? Is using Rank function the only way to do this?

samasuraj
Occasional Member - Level 3

Hi @Rahul4 

 

Topic:  Cognos reporting

 

Please check the below. Hope this helps.

-----  

SAP Concur Cognos Reporting – LISTAGG / String Aggregation

There is NO separate SAP Concur Cognos License that enables LISTAGG or String Aggregation.

These Functions are not available as Standard Supported Functions in the SAP Concur Reporting Environment.

Their availability depends on the Cognos Version, Underlying Database.

 

RANK() is not a replacement for String Aggregation, as it ranks records rather than combining multiple text values into one field.

 

If your requirement is to concatenate values from multiple rows, check with SAP Concur Support for an approved workaround.

 

 

Thanks & Regards

--

Samantha Prathikantam

SF_EC & ECP / SAP Concur Consultant

Rahul4
Occasional Member - Level 3

Hi @samasuraj  @KevinD 

I cannot raise a concur support ticket for reporting as we have not purchased reporting services from concur. I have seen in my previous project concur created a report using rank function to capture all comments in a single row, could you please check again

samasuraj
Occasional Member - Level 3

Hi @Rahul4 

 

Topic: Cognos reporting

 

Please check the below. Hope this helps.

-----  

Since your organization has not purchased SAP Concur Reporting Services, I understand that raising a reporting support ticket is not an option for you.

 

You may still be able to achieve this using your existing Cognos report-authoring access.

An SAP Community discussion describes an alternative approach for displaying multiple expense-entry comments together in one report row without LISTAGG.

Actions:

  1. Create a Main Query containing Expense Report details.
  2. Create a Second Query containing Expense Entry Comments.
  3. Use a Repeater with a Master-Detail Relationship to display the related comments.
  4. Add a Comma-and-Space Separator between comments, if supported by the layout.
  5. Group the Report appropriately to avoid repeating the main report details.

This is a report-layout approach rather than true string aggregation. It does not require RANK(), but it does require suitable Cognos report-authoring permissions.

Please check whether these Features are available in your existing Cognos environment.

 

Reference: SAP Community – Combining Multiple Entry Comments into One Row

 

 

Thanks & Regards

--

Samantha Prathikantam

SF_EC & ECP / SAP Concur Consultant

KevinD
Community Manager
Community Manager

@Rahul4 I'm asking one of my reporting contacts about this. I'll let you know what I hear back.


Thank you,
Kevin
SAP Concur Community Manager
Did this response answer your question? Be sure to select “Accept as Solution” so your fellow community members can be helped by it as well.
KevinD
Community Manager
Community Manager

@Rahul4 the only way to accomplish what you need is through a Repeater Table. My contact told me that running this type of report can be slow, so just be aware. 

 

Also, my suggestion if you don't know how to build a repeater table would be to ask Copilot or ChatGPT. Either should be able to provide the steps in much less time than it would take me to write them out.


Thank you,
Kevin
SAP Concur Community Manager
Did this response answer your question? Be sure to select “Accept as Solution” so your fellow community members can be helped by it as well.