Intelligence: issues building a report to show approver

[cchajkowski]

I am in the process of building a report to show expense reports that were approved and by who.

I've been mostly successful, but seem to have hit a wall on a couple of items:

1. I am getting duplicate results (ie. the same expense report is showing up twice in the report output). Not quite sure why
2. The approver filter on the prompt screen I built isn't just limited to showing actual approvers, it has everyone in Concur listed.

On the first point - I have no idea what's going on here. I'm using the following fields in the Query:

1. Step (filtered to Cost Object Approver; we use COA workflow); this data point is not used on the report page and is only used for filtering
2. Employee Assigned to Step
3. Step Action Date/Time (formatted to only show the date; we don't care about the time for this report)
4. Employee
5. Report Name
6. Total Approved Amount (rpt)
7. Approval Status (filtered to only show 'Approved'); this data point is not used on the report page and is only used for filtering

On the second point, I built a filter on the prompt screen to search specific approvers. However, I think the data point I'm using (Employee Assigned to Step) lists all users in Concur. I'd like to either filter it to only show people that have approved reports OR see if there's another data point I don't know about that could be used instead.

Hopefully I've provided enough detail and someone can help!

[KevinD]

@cchajkowski for the first point, you might be seeing duplicates because there is more than one approval step in the workflow. So, for each step, you will see the name of the report. That is why we use the Group function. This will show the report name only once. This is my guess based on what you described. 

On your second point, are you saying that when the prompt opens and you see the list of people to search, it is showing all employees? 

What is the name of the report you are working on? Is it saved in your My Content folder?

[cchajkowski]

Good Afternoon @KevinD!

So I have "Employee Assigned to Step" filtered to only show the approval step "Cost Object Approval". Each duplicate shows the same information (same approver, same report, etc).

Yes, it is listing every employee who has a Concur account, but I would like it to only list those who have actively approved a report (if possible).

Sort of, it is in the My Content>Unfinished Reports folder. It is labelled "Approved Expense Reports by Approver".

Thank you,

Caitlin

[KevinD]

@cchajkowski I'm asking one of my reporting contacts about the duplicate lines. It is being caused by the Step Action/Date Time data item, but I don't know why. 

You could add the data item Step to the report and then filter for that data item to only show Cost Object Approval. The data item doesn't need to be visible, if you don't want. You would need to just cut it from the report page after creating the filter. 

[cchajkowski]

@KevinD- I think I mentioned in my first post that I am actively filtering for this data point. See third paragraph point one.

I see Pooja's posts and replied to her DM!

Thank you,

Caitlin

[KevinD]

@cchajkowski there might be a way to tell the report to not include something that is "null" or not showing anything. I'm not quite sure how to do this, but I think I've seen our Reporting team do it. 

[PoojaKumatkar]

Hi @cchajkowski ,

Please find my response below:

1. I am getting duplicate results (ie. the same expense report is showing up twice in the report output). Not quite sure why

Pooja - I tried building similar report in our sandbox and I observed the same issue. However, upon further investigating and checking expense report audit trails we observed that those reports have been recalled multiple times and resubmitted due to which it went through cost object approval step multiple times. Hence, in Cognos report it is giving you multiple records for the same report as it is approved by the COA that many times.

Example, 

for below report I am getting 2 records, meaning this report is initially submitted and approved by COA, recalled and resubmitted and approved by same COA again. 

Note - You can also validate this from your end. You can take such records and check audit trail of these report where you will find reports are recalled and resubmitted again. I have validated this for the couple of reports and this what we observed.

Also, find out few reports where you are getting single record in Cognos report and check audit trail of that report. Those reports never been recalled that's the reason you are not getting duplicate record for those reports. 

2. The approver filter on the prompt screen I built isn't just limited to showing actual approvers, it has everyone in Concur listed.

Pooja - Yes, I observed the same issue. All employees are getting listed. However, you can search for the specific COA and get report. For now, I don't have solution on this.

Regarding report, I have tried building report with required filters and prompt. 

Role Required - Cognos Professional Author

Follow below navigations to create report. I will send report code on your email. Please check your personal message.

• 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 (top left 5th icon after edit option)>> Search for the COA in search box >> click finish on bottom
• Done

Result:

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

[cchajkowski]

So I've figured out what's causing the duplicates and I don't know that there's a way to resolve it.

We have an extra workflow step outside of the ones that come standard with the COA workflow that we added due to a different approval process for a specific few users. Because of this, it's listing both steps, even if it's skipped for a user.

Is there a way to filter by Step to resolve this?

Thank you,

Caitlin

[PoojaKumatkar]

Hi @cchajkowski ,

I think I know what you are talking about. I did observe this issue when I tried building this report in our sandbox. For duplicate records, system shows some other default workflow name which is not even used in our custom workflow.  

However, if you've used the custom report code I shared with you, that report includes all the necessary filters to exclude such records and ensures that only the COA step is included. The few duplicate records you're seeing could be due to the expense report being recalled and re-submitted multiple times, causing the system to display multiple records for the same report. 

Have you tried using that code? Did it work for you?

[cchajkowski]

I'm sorry for my late reply.

I tried the file you sent me, and I'm still getting duplicates. The only changes I've made to your settings were to make the approver filter optional and added the option to select multiple approvers.

Looks like the duplicates are as I said - due to our extra cost object approver workflow step outside of the standard workflow.

Is there a way to filter this so it only shows the step that was actually acted on by an approver?

I've left this report saved in my unfinished reports folder with your initials after it.

Thank you,

Caitlin

[PoojaKumatkar]

Hi @cchajkowski ,

In addition to existing filters, try adding below. Let's see if it works:

There is a field call Process which gives workflow name, try adding that and see if you can apply any filter on that.

I suggest taking a few sample records (noting them down separately) that have duplicate entries and examining the workflow name. If the same workflow name appears for all the duplicate records, then there's nothing more you can do to remove the duplicates.

However, if the workflow names differ, you can exclude the ones that are unnecessary and check if you're left with unique records. It's important to note that you should only consider applying a filter if the workflow name is consistent across all the duplicate records. If the workflow names are not consistent, applying a filter wouldn't be effective.

When I was trying this a few days ago, I observed that I was getting different workflow names, which weren’t consistent across all the duplicate records, so I didn't proceed with the filtering. Our conclusion was that these duplicates occurred because the reports were recalled multiple times and resubmitted, which caused the duplication.