Date attributes for credit card data

[oliroe]

Hi

I'm trying to create a pivot report based on credit card transactions but need the month number and year number. Several of the dates provide the attributes in order to do this but none of the credit card dates do (as shown below)

Is there anyway to retrieve the month and year number from the credit card date types?

[Munari_alEXPert]

dear @oliroe 

you are right as I neither can see it. What I used to do, but maybe is not the most rapid way, is to manually inputted myself, ordering by date, adding a column, and for JAN --> 1; FEB-->2... DEC-->12.

It shouldn't take to long with the drop down functionality and your mouse, OR I am sure there is some formula that would read the date and input the number. 

[oliroe]

Thanks but I'm trying to create a self-serve report and not require manipulation in Excel. It's really frustrating that Concur reporting can't do such basic things.

[GrantC]

Hi oliroe, 

It is possible and fairly easy to create these attributes within a report in Concur Reporting.

If you are using Query Studio, follow these steps:

1 – Add the field in question to the report, say Posted Date.

2 – Click the field in the report and then click the Calculate icon on the toolbar.

3 – Set the Operation to Month and then give you field a name in the New item name field.

4 – Click the Insert button.

5 – Repeat steps 2 – 4, but use the Year option as the Operation.

6 – Remove the Posted Date field from the report.

7 – Within that report you can treat your two added fields just like any other field in a report. You can group, sort, filter, etc by them. You can even use them as the starting point of a new calculation.

This can be done in Report Studio as well, but is a bit more challenging because you have to build an expression:

1 – From the toolbox, add a new Query Calculation to your report.

2 – Give your new field a name in Name field.

3 – In the Expression definition, type extract(month,

4 – Browse through the data model and double-click the field you want to add, say Posted Date.

5 – Put your cursor at the end of the expression and type ). The finished product will look something like this: extract(month,[Expense].[Credit Card Transactions].[Posted Date])

6 – Click the OK button.

7 – Repeat steps 1 – 6, but type extract(year, as the first step. The finished product will look something like this: extract(year,[Expense].[Credit Card Transactions].[Posted Date])

8 – Within that report you can treat your two added fields just like any other field in a report. You can group, sort, filter, etc by them. You can even use them as the starting point of a new calculation.

I strongly recommend you check out our training materials at the following two links. There is a wealth of information on how to use the tools to create customer reports.

http://www.concurtraining.com/toolkit/en/data-insights/analysis

http://www.concurtraining.com/toolkit/en/data-insights/intelligence

Thanks,

[oliroe]

Apologies for being hasty, this is exactly what I was after.

Please could I ask one more question? (I can accept the solution and post separately if that's the correct thing to do)

I'm looking at assigned and unassigned credit card transactions and trying to pull in the report status of the transactions that have been assigned. As the unassigned are not part of a report yet they disappear from the analysis.

Is there any way to bring in the report data but also include the unassigned in the same report (i.e. with report status blank)?

Thanks

[KevinD]

@oliroe hello there. One little tip before I answer your question, when you get a reply to one of your posts that you want to then reply to, be sure to tag the person so they will get notified of your response or you can just click the Reply button on that person's post. That will tag them as well. I mention this so you can get faster replies from someone who had previously answered one of your questions. 🙂

To answer your question, I would recommend using one of the Standard Reports found in Public Folders. When you click on the Intelligence - Standard Reports folder, you will see a list of sub-folders. The first sub-folder is named: Accrual. Go into this folder. You will see a report named Expense Accrual. This report shows all outstanding liabilities including assigned and unassigned transactions. However, it does include out of pocket expenses that are on expense reports. But, when you run the report it does prompt you for payment type, you can then just select your corporate card payment type, which will then return results for only card transactions. This report gathers data up until yesterday's date and goes back as far as there are outstanding charges. You could also use Expense Accrual by Date Range report that will let you choose a specific range of dates. 

Now, if you want to see unassigned and assigned transactions on the report Grant mentioned, you would need to use a Union to be able to include them both. You may be able to just add the date attributes from Grant's example to the existing Expense Accrual or Expense Accrual by Date Range Report. Here is a resource for editing a standard report: https://assets.concur.com/concurtraining/cte/en-us/cte_en-us_rpt_accruals-modification.mp4. In fact it shows how to edit the Expense Accrual report. 

I hope this helps. 

[oliroe]

@KevinD 

Very helpful. I'm afraid I only have Analysis so I don't have Report Studio but a Union seems to be exactly what I was after. Looks like I'll be dumping the base data in Excel and doing it from there

[KevinD]

@oliroe Yes, Analysis only will limit you, so building two different reports, running them, downloading them in Excel then merging together will be your best bet. Good luck and let us know if you have any questions while giving that a try.