This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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 Name | Employee name | L1 Head Name | Employee ID | Employee Location | Step | Step Entry Date/Time | Org Unit 2 - Code | Org Unit 4 - Code | Policy | Reimbursement Currency | Total Approved Amount (In local currency) | Approved Amount in USD | Entitlement in USD | Exception amount in USD | Country (of Employee) | TAT W/O Weekends | First Submitted Date | Approval Status | Employee Assigned to Step | Report Exception | Policy |
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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:
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:
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
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
@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.
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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)
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
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
@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. 🙂
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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:
Cognos creates multiple rows because one Report ID has multiple exceptions.
To display one row per 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
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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.
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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:
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
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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?
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
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:
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
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
@Rahul4 I'm asking one of my reporting contacts about this. I'll let you know what I hear back.
This content from the SAP Concur Community was machine translated for your convenience. SAP does not provide any guarantee regarding the correctness or completeness of this machine translated text. View original text custom.banner_survey_translated_text
@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.