Reporting Functions

[Karthikhemadri]

Team,

is there a way to use IF and VLOOKUP (Similar to Excel) function in cognos report studio. If yes, how can i do it.

actually i have 2 queries as below:

Query1: Employee ID, Report ID, Report Key, Status (AP Review), Last submitted date, Processor start Date

Query2: Employee ID, Report ID, Report Key, Status (AP Review), Last submitted date, Processor start Date, Change Date/time (from Audit Trail), Field changed (RECEIPTS_RECEIVED), Old Value, New VALUE (Yes).

My Requirement is

Employee ID, Report ID, Report Key, Status (AP Review), Last submitted date, Processor start Date, Aging Starts (New Column)

below logic to be applied to get the "Aging Starts" column in requirement.

Logic: If Report Key of Query1 is appearing in Query2 then value should be change date/time in Query2 else processor start date in Query1

Ex: if report #123 from Query has processor start date as 01/01/02021 and has data in Query2 with filed changed as 15/01/2021 then aging starts column should return to 15/01/2021. If report 456 from Query1 has processor start date as 02/01/2021 and no data in Query2 then Aging stats column should return value as 02/01/2021.

[KevinD]

@Karthikhemadri this is a pretty complicated question. Let me first ask you, what are you trying to accomplish with the requirements you posted? What is it you want to see in your reports? I think it will be a little easier for people to assist if they know what the goal of this report you want to build is. 🙂

[Karthikhemadri]

@KevinD Yes that's right.

My goal is to calculate aging days for a report in AP Review. But the logic to get the aging days little complex.

We have multiple groups with receipt requirement as Image Only, Both and original pager.

For Both and original Paper requirement, aging should be calculated based on receipts received date. lets says for report #123 processor start date is 01/01/02021 and receipt marked as received on 05/01/2021 then aging should calculate from date when receipts are marked to Yes. in this example days between today to the date receipts are received.

for Image only, it is straight forward, aging is days between today and processor start date.

as the receipt received date is at audit trail level, i'm trying to create column to get these details to compare for aging analysis.

[KevinD]

@Karthikhemadri I was looking through the Cognos data model guide we have and I did not see anything regarding the date receipts were received. I only saw data items that would indicate a Y or N meaning the receipts were received or they weren't. So, I don't think you will be able to get your aging report since the date of the receipts received isn't something that is found in Cognos. 

[Karthikhemadri]

@KevinD  if the hard copy of receipts received, i guess receipt processor marks receipts received from No to Yes. This can be tracked in Audit Trail (Change Log) under Expense reports using field changed as 'Receipts_Received'

[KevinD]

@Karthikhemadri yes, that is true, but you don't get the date in Cognos that the processor marked the receipts received. So, you won't have any way in your Cognos report to subtract the Receipts Received date from the processor start date.

[Karthikhemadri]

@KevinD No, the change date/time is the the date of receipts received when you filter down the Filed changed column as 'Receipt_Received' and New Value as 'Yes"

and i'm not subtracting the processor date from date of receipts received but comparing whether processor start date is occurring after the receipts received or not. 

[KevinD]

@Karthikhemadri I understand that, but what I'm saying is that the date the receipts are received does not feed to Cognos, so you won't be able to see if the date the receipts were received was before or after the processor start date in a Cognos report. 

But just to make sure I'm clear, when you say the change date/time of receipts received when you filter down the field changed column, where are you seeing this? In the report Audit Trail or somewhere else?

[Karthikhemadri]

@KevinD The fields can be located in Report Audit list from Audit Trail (Change Log) folder under Expense.

I know that cognos wont compare however i can create a query calculation to check if teh processor starts date is greater than receipts received date.

[KevinD]

@Karthikhemadri Okay, now I see what you are talking about. Yes, if you have the Change Date/Time from the Audit Trail, you could subtract that from the Processor Start Date. Do you know how to create that calculation in Cognos?

[Karthikhemadri]

@KevinD I have the report and had a lookup with another query using Cross product allowed option.

i have new problem now as, i'm getting the duplicate report Keys (genuine) as receipts are marked from No to Yes multiple times

so now i want to eliminate the duplicate records and keep only the latest record for those repeating.

logic is to give ranking to repeated report and eliminate all that beyond rank 1.

any idea or formula to do this.

also i'm using the below formula to look up:

lookup ( Report Key ) in ( query.report key --> query1.change date/time ) default ( processor start date )

this formula is resulting the duplicate rows one with query1.change/date/time and another with processor start date for same report key.

how to it corrected.

[KevinD]

@Karthikhemadri I would really need to look at the report to have any idea of how to assist you with this. If you save a copy of the report in your My Folders, I can go in and take a look at it and see if I can provide some help. 

I will need your company's name so I can go into your profile and access Cognos from your SAP Concur account. 

[Karthikhemadri]

@KevinD the report is in "Daily Ops Metrics" folder in MyFolders with Name as "DailyOps Reprot 4"

Company name is Citi Group

[Karthikhemadri]

@KevinD i have got the required report myself, thank you for your discussion.

[KevinD]

@Karthikhemadri Great to hear.