Cognos Report - Add number of reports processed, approved, submitted and not submitted

[mamehta]

Hello, I am trying to create a report in Concur where I want following details,

For each country count of Number of reports submitted and not submitted , and the average time required to submit the report.

For each country count of Number of reports Approved and pending approval, and the average time required to Approve the report.

For each country count of Number of reports processed and pending processing , and the average time required to process the report.

Is there any way I can do this, I am new to cognos and I am still learning how it works, I would really appreciate if anybody can walkme through the process.

Thank You in Advance.

[KevinD · Accepted solution]

@mamehta let me start by saying that yes, you can achieve what you are looking for. However, due to your newness to Cognos, I would recommend doing this as three separate reports. Why? Because what you are asking for is technically three different queries and being a beginner, it would be very difficult, time consuming and frustrating for you to build this report as one report. 

Now, I'm about transparency...so, I can tell you that to walk you through this would be a bit too much to type here in this forum. I can, however point you in the right direction to get you started. Luckily, each of these three reports are basically the same with a couple small variances. 

For the first report, you will need to add the following data items to the report:

1. Employee Country Code
2. Employee
3. Report Name
4. Approval Status
5. Created Date
6. Last Submitted Date

After you have added these data items, you will need to group the report by Employee Country Code. This will section out the report by country. Next, create a filter on Approval Status that will Show Only the following and select Not Submitted and Submitted and Pending Approval. Now, select the Last Submitted Date column, hold the CTRL key and select Created Date. With these two columns selected, click the Calculate button (green icon) on the toolbar. In the Operation field, use the dropdown arrow to select (difference in days). In the new item name field, I would type Number of Days to Submit, then click OK. 

Now, click on the new column named Number of Days to Submit. Go to the left hand menu in Cognos and select Edit. From the list that appears, select Summarize. In the Summarize section (appears below your report) on the right side look for a link labeled Advanced. Click this link. In the options that appear, you'll see an option for Footers. Click the dropdown arrow and then select Average. Then click OK. You will have to page down the report to see the summarized average per country. It will be in gray and bolded. 

So, for the second report you want, which sounds easy, is actually quite complicated because a report can be approved by a manager more than once. If a processor sends a report back to a user, then it starts the approval process over so the manager could technically approve the same report again. There isn't a data item in Cognos that shows Last manager approval. So, trying to build this into a report will be very difficult for someone with only a little Cognos experience. 

You may want to reach out to your SAP Concur account manager and ask about getting assistance with these reports from someone at Concur. 

Kevin

[mamehta]

Hi @KevinD , First let me thank you for a detailed response and guide to my question. I appreciate all youe efforts. I understand what you are saying, and I tried with your first solution on Submitted and Not submitted and it works perfectly fine. However I would still like to go ahead and try creating the other two reports that you mentioned will be very complex. I am open to learn, and I would really appreciate if you can guide me or point me to any resources which I can use that can help me develop the other two reports I am looking for.

Thank You

[ania_p85]

Hi @mamehta  where you able to create the report for number of processed reports?

BR

Anna

[mamehta]

Hello Anna, I was able to create the report. Its a bit complicated report if you are new to Cognos. You need to filter your data to show only the number of expense reports that were processed. The way you can do this is

1. Select all the expense report in your system

2. In the filter prompt, from the workflow trail folder add the following filters to filter your data to only show processer approved expense reports

a. [Step Action Date/Time] is not null - this will filter out all the expense report that werent approved

b.[Current Workflow Flag (Current = 1)] = 1 - this will only consider expense report that has been worked upon

c. [Role Legacy Key] IN ('ACCT_CLERK') - Selects only the expense reports assigned to the processor

d. [Employee Assigned to Step] <> 'System, Concur' - Will not select those expense reports that were processed by the system.

Once you have your view of the data, you can simply organize it to the way you want to. Hope this will help.