Function used to display data for specific period

[naqsa]

Hi Cognoise community,

I have a Cognos report that captures all unpaid expense reports in the Concur database. I am automating this report to run on day 1 of each month and load into our ERP. Because it'll be automated (scheduled),   I cannot use prompts to filter on dates. Accounting team requires that only those reports that are dated for the previous month should be loaded into the ERP system. My report will run on Day 1 of the new month and will need to capture all reports dated for the previous month.
E.g. Run date = August 1.
      Report date = July 1 to July 31st

My question is- what logic/function should I use in the Report Date column to pull data for a specific period, in this case, the previous month.

Thanks for reading!

[cognostechie]

You can create a condition in the filter which will resolve to the date range of previous month:

[Date] >=_first_of_month (     _add_months (    current_date, -1) )

and

[Date] <=_last_of_month (     _add_months (    current_date, -1) )

You can also have prompts in a report which is scheduled to run and you can make the report run either by the values selected from the prompt or by a coded filter with a dynamic expression but that is not within the scope of your question so we will not go into that right now.

[naqsa]

Hi cognoisetechie,

Thanks for responding. Can you please more specific about what needs to go in this section:
(     _add_months (    current_date, -1) ).
For this example you may use August as the report date month and Sept 1 as the run date.

Thanks a ton!

[MFGF]

Hi cognoisetechie,

Thanks for responding. Can you please more specific about what needs to go in this section:
(     _add_months (    current_date, -1) ).
For this example you may use August as the report date month and Sept 1 as the run date.

Thanks a ton!

Hi,

It's exactly as Cognostechie posted it. If you use current_date in an expression, the Cognos server substitutes it for the date the report runs.

You can find it in the Common Functions section in the expression editor:



Cheers!

MF.

[naqsa]

Hi,

Based on the instructions above, I've entered this logic:

[Expense].[Entry Information].[Transaction Date] >= _first_of_month (_add_months (current_date,-1)) and [Expense].[Entry Information].[Transaction Date] <= _last_of_month (_add_months (current_date,-1))

And I receive multiple errors (see attached).

I've tried modifying the spacing between the operators. I've also tried using [Transaction Date] alone instead of [Expense].[Entry Information].[Transaction Date]. Even if I get rid of the syntax errors, I'll still have the errors related to the other columns i.e employee.

[ceosp]

try to use ';' instead of ','

[naqsa]

^ Thank you. Unfortunately, it didn't help.

[cognostechie]

I am not getting any errors when I use the same (see attached)

The multiple errors you see does not mean that there are so many errors. Even if the problem is with only one item, it will
repeat the error for other data items in the report also. That's just how it is. Make sure to put the expression only in a filter and not
in a Data Item and also delete the filter so that you can run the report without using it and see if you still get some errors.

Did you get some error in the same session prior to inserting this statement? Clear the cache of the browser, log out , log back in and try it again.
I have seen Cognos showing errors from the cache too.

[naqsa]

Thank you!! As per your suggestion, I've removed the expression from the Data Item and that has helped to a certain degree. I now get a parsing error.
"QE-DEF-0459 CCLException... Parsing error before of near position: 98 of: "[Expense].[Entry Information].[Transaction Date] >=_first_of_month (_add_months ( current_date-1)) and [Expense].[Entry Information].[Transaction Date] <=_last_of_month (_add_months ( current_date-1))".

This error is listed twice. If 'position 98' is the number of characters and spaces, it lands around the 'and'. I've tried deleting the spaces, writing 'and' in CAPS, putting 'and' on a separate line. I have also cleared the cache and tried a different browser as well.

[cognostechie]

"[Expense].[Entry Information].[Transaction Date] >=_first_of_month (_add_months ( current_date-1)) and [Expense].[Entry Information].[Transaction Date] <=_last_of_month (_add_months ( current_date-1))".

It should be like this:

[Expense].[Entry Information].[Transaction Date] >=_first_of_month (_add_months ( current_date, -1)) and [Expense].[Entry Information].[Transaction Date] <=_last_of_month (_add_months ( current_date,-1))

There is a comma missing in your expression and there should not be any double quotes before or at the end of the expression.

[naqsa]

Thank you!!!! My issue is resolved :)

[naqsa]

Hi All,

Can someone help me determine what expression I need to use to run report for last 3 months. This is what I'm using to run report for past 1 month:

[Expense].[Credit Card Transactions].[Transaction Date] >=_first_of_month (_add_months ( current_date, -1)) and[Expense].[Credit Card Transactions].[Transaction Date] <=_last_of_month (_add_months ( current_date,-1))

Thanks!

[BigChris]

What have you tried? And do you understand the functions that you're using, or have you just inherited it and not researched it...?

[Expense].[Credit Card Transactions].[Transaction Date] >=_first_of_month (_add_months ( current_date, -1)) and[Expense].[Credit Card Transactions].[Transaction Date] <=_last_of_month (_add_months ( current_date,-1))

needs to be changed to

[Expense].[Credit Card Transactions].[Transaction Date] >=_first_of_month (_add_months ( current_date, -3)) and[Expense].[Credit Card Transactions].[Transaction Date] <=_last_of_month (_add_months ( current_date,-1))

and if you wanted to future-proof your expression for other numbers of months:

[Expense].[Credit Card Transactions].[Transaction Date] >=_first_of_month (_add_months ( current_date, -?Number of Months?)) and[Expense].[Credit Card Transactions].[Transaction Date] <=_last_of_month (_add_months ( current_date,-1))

[naqsa]

That's very helpful! Thank you.