Extracting Mileage Rates to Cognos

[brandtp09]

We have a Car Configuration where we set up the Distance Limit to 5000km. We have two mileage rates to be considered: 1-5000km (0.68 CAD) and 5001 and up (0.62 CAD). In Car Configuration, you have the tab where this is defined and we're wondering if this is extractable in Cognos because we have a request where they want to determine which mileage expenses used the 0.68 CAD rate and which ones used the 0.62 CAD rate. I have a feeling that this might not be possible, especially in cases where you incurred a mileage expense that contains a distance that will start from <5000km and then exceed it (as this would mean two rates are going to be considered).

To give more context, this is what we're trying to extract:

Have you experienced something like this? Were you able to accommodate such a request?

[KevinD]

@brandtp09 I did a search and did not find anything in the Cognos Data Warehouse about mileage rates.

[brandtp09]

Hi @KevinD. Thanks for checking! I thought I was going insane to think that this could be possible, but thanks for confirming my hunch.

[KevinD]

@brandtp09 what do they say, "always go with your gut". 🙂

Now, it might be possible for you to create a report that shows the total for the mileage expense entry and the total distance entered. Then, on your Cognos report, build a calculation on these two data columns that will divide the amount by the distance entered, it will give you a decimal that should closely match the reimbursement rate. Just a thought. I like to offer alternatives when I can.

[brandtp09]

Hi @KevinD, we thought of doing this, but we got stumped in scenarios where we have variable rates for certain distances. In the event where we have a mix (distance covering two rates), the calculation may not work as we have no way to determine which portion of the distance should fall for rate A and which one for rate B. We'll get back to the request we received on this and present them some workarounds.

[KevinD]

@brandtp09 I always wondered what would happen if a user has a trip that spans two different rates. So, If a user is at 4990km and they drive 20km, the system multiplies 10km by the under 5000km rate and the other 10 by the over 5000km rate?

I mean that makes sense, but I didn't know it was smart enough to do that. 🙂

[brandtp09]

@KevinD, that's right. Anything excess of the initial distance will be multiplied to the "overdistance" rate and in cases of combinations, it's smart enough to know which ones would fall under the normal rate and which ones for the excess. The only thing I find interesting here is the way to track it in Cognos, but I guess that's why we don't have the rate Data Items there for mileage because it's quite complex to determine the splits for mileages with a mix of the variable rates. What we decided to check now is to utilize the Accumulated Distance, instead. That way, we still have a way of knowing whether a mileage expense has exceeded the initial distance or not.

Who knows, maybe Concur will include such a data item in future updates? Fingers crossed 😁