How do I get total expense report counts by submitted/approved?

[a03090]

Is there a report we can run to find out total how many expenses been submitted and how many got approved?

[KevinD]

@a03090 to better answer this question, I'll need a little more information from you.

1. Are you on our professional or standard configuration? If you are on Professional, then skip my second question.

2. Is your company U.S. based or based in another country? 

3. If your answer to question 1 is Professional, then do you have any experience using Cognos reporting? 

I ask these questions because those in the U.S. on our Standard configuration use a different reporting tool than those on Professional. Cognos is the reporting tool for our Professional customers. The tool is not very intuitive, so if you do not have any experience with the tool, it will be difficult to guide you. 

I'll await your reply.

P.S. Just so you know, what you are asking for is possible and quite easy to build if you know how to use Cognos. 🙂

[a03090]

Hi Kevin,

thanks for replying.

We are on Professional, our company in US and am familiar with Cognos Analysis - Standard Reports

[KevinD]

@a03090 Okay, one more clarification question, when you say you want to see reports that are approved, are you talking about approved by the manager or reports that have been fully approved? Or, maybe you want one report that shows reports that are submitted, approved by the manager and also fully approved. You can build your report to show any combination of these statuses, but combining them into one report is less work, but you can always create a prompt on the Approval

status and each time you run the report, choose one of these three statuses. Anyway, here is how you would build the report. 

Add the following data items:

1. Employee

2. Report Name

3. Approval Status

Next, select Employee and Report Name columns so they are both highlighted, then use the Group option from the toolbar. 

To get a count of the reports, select just the Report Name column, then go to the Menu on the left and click Edit Data. From the Edit options, select Summarize. You'll see a little dropdown field appear at the bottom of the screen labeled Summary for Footers. From the list, select Count. 

You will see the report total up reports by employee, but if you go to the bottom of the report, you'll see a total count of all reports. 

Now, click on Approval Status. Select the Funnel icon (Filter) from the toolbar. You will see your filter options appear. Look for a little checkbox that says Prompt every time report runs. Click this checkbox. When you run the report, you will be presented with a list of statuses you want to include in the data results. You can choose one or multiple statuses to be included. Always go to the bottom of the report to see the total count. You can see a count by just Submitted and Pending approval status, or Approved & In Accounting Review and even Approved. You could also choose all three statuses to be included in the results. Depends on what you want to see. 

This should do it for you. Let me know how it works out.

[a03090]

Wait...

when you mention Add the following data items:

1. Employee

2. Report Name

3. Approval Status

Where exactly am doing this? Am I going to Cognos Standard Reports? Or Is it some kind of a custom report you want me to build?

Where exactly am following the steps you have given?

Also I am taking about reports which are fully approved. Reason is we use a middleware to push "fully approved" expenses to our ERP system

To reimbuse cash expenses through ADP

Generale a flat file and post card expense payments to our Credit card company

In this process, we doing the data reconciliation so like to make sure not missing any "fully approved" expenses transfrred to our ERP system

[KevinD]

@a03090 this is a report you are building yourself. 

If you just want fully approved reports, then when you click Approval Status then the Funnel (filter icon) you will see a list of all statuses, select Approved from the list. No need to use the Prompt checkbox now since you are only looking for one status. This will then show only reports that are in an approved status. 

I'm not sure if you are looking for a date range or not. The report as I told you to build it will return all reports that have ever been Approved. So, you may want to add Sent for Payment Date as a data item and then add a filter or prompt so you can either return a specific date range or use the Prompt to always be able to choose what range you want for the returned results.