Cognos reporting - modification existing custom report

[JigneshK]

Hello,

Would you please advise how we can modify existing reports for this month, here is the requirement

Reports are scheduled on 1st of every month, but need to be modified for this month to ensure they are picking up all reports that were not on the last month until the first.

Filter applied in existing report  - Payment status - sent for payment And Last month (Based on paid date)

Fyi, SAE was running on weekly basis till 21st Jan but now scheduled changed to Daily from 21st.

Appreciate your comments or advise on this.

Thanks!

[KevinD · Accepted solution]

@JigneshK let me preface this by saying that I'm assuming you are pretty familiar with navigating around the reporting tools. Otherwise, it would be way too much to type to give you step by step instructions. I've provided the filter expression below. This expression will give you data from the 26th of the previous month. When you run the report on the 26th of each month, the data range would be the 26th of last month up until the 25th of this month. You will need to have your report open and go into the Filters (Yellow funnel icon) and remove the current date range filter and then add this expression below. I hope this helps.

Sent for payment date BETWEEN _make_timestamp ( year (_add_months (current_date, -1), month (_add_months (current_date, -1), 26) AND _add_days( current_date, -1)

[KevinD]

@JigneshK I'm not sure I understand your requirement, but let me restate what I think you said to see if I get it right or not. 🙂

The current report runs on the 1st and includes the previous month's reports. However, you need the report that is scheduled for Feb. 1st to include reports that are not on last month's report (on Feb. 1 Last month would be January 2022), which would have been December 2021. Did I get that correct? 

[JigneshK]

Hello @KevinD

Yes, That's correct. Apologies if I was not clear with the requirements.

How can I modify the existing report which would be scheduled on 1st Feb (includes January expense reports) and should also include the expense reports which was not picked on the  Last report 1st January (December exp. reports).

[KevinD]

@JigneshK what is the name of the report and where is it saved in Cognos? I'd like to take a look at the filter before I provide some steps. It is a bit difficult to do off the top of my head. 

[JigneshK]

Hello @KevinD 

Report is available in Public folder under Cordis US Corp, Report name is Out of Expenses_EMEA (https://reporting13.concursolutions.com/cognos/cgi-bin/cognosisapi.dll?b_action=xts.run&m=portal/cc....).

However, Now the requirement is - report should sent 25th of every month. so it should include all the data from 26 - 25 for every month.

For E.g Next report schedule on 25th Feb ( which should include the reports from 26th Jan to 25th Feb)

Can you please guide to set up this report?

Thanks for your help

[KevinD]

@JigneshK the filter you want isn't as straightforward to build as it sounds. Also, the report you have was built with Query Studio. The filter you need must be built with Report Studio, so you would need access to that tool to be able to add the filter you need. I've just received an example of the expression that would allow for the filter you need. 

Also, one thing to keep in mind. When scheduling reports, because the data warehouse updates each night, you always want to schedule your report to run one day after the last day's data you want included. This ensures reports from that date are included. So, if you want data from the 26th to the 25th, schedule the report to run on the 26th of each month.

You will need to have someone grant you access to Report Studio before you can apply the new filter. Let me know when you get this access. 

[JigneshK]

Hi @KevinD 

Thanks for your feedback, I'll discuss about report studio access if they can provide to build this report. 

I will get back to you on Monday after discussion with client. 

Thanks! 

[JigneshK]

Hi @KevinD 

I've added filter Last Thirty Days (Based on Sent for Payment Date) to get the required data from 25-24, Report will be schedule for 25th Feb which includes all the exp. reports from 25th of Jan to 24th of Feb.

Report is available at below link and name of the report is Out of Pocket Expenses_EMEA_2

https://reporting13.concursolutions.com/cognos/cgi-bin/cognosisapi.dll?b_action=xts.run&m=portal/cc....

Can you please check and advise if this works? as days are very by month, will Concur auto calculate the monthly data?

Thanks for your feedback!

[JigneshK]

Hi @KevinD 

The filter I've applied with query studio doesn't work, I am still waiting for Report studio access, will get back to you once I have the access to report studio.

Thanks!

JigneshK

[JigneshK]

Hi @KevinD 

I got the access to report studio, would please help me to apply filter to get the report date as mentioned above.

Thanks

Jignesh

[KevinD · Accepted solution]

@JigneshK let me preface this by saying that I'm assuming you are pretty familiar with navigating around the reporting tools. Otherwise, it would be way too much to type to give you step by step instructions. I've provided the filter expression below. This expression will give you data from the 26th of the previous month. When you run the report on the 26th of each month, the data range would be the 26th of last month up until the 25th of this month. You will need to have your report open and go into the Filters (Yellow funnel icon) and remove the current date range filter and then add this expression below. I hope this helps.

Sent for payment date BETWEEN _make_timestamp ( year (_add_months (current_date, -1), month (_add_months (current_date, -1), 26) AND _add_days( current_date, -1)

[JigneshK]

Hi @KevinD 

Thanks you so much, I will add this expression to get the required data.

Just to confirm, this will work for every month, right  and no need to run the report manually every month.? as I need to auto schedule this report for every month to send the clients.

Regards,

Jignesh K

[KevinD · Accepted solution]

@JigneshK I believe it will.