Calculate date on Filter

[sumancognos]

Hi,

I am new to cognos report studio. I developed a report to extract the data for given dates in the prompt. Report needs to run once in a month for previous month dates. If I am in February I want the dates to be populated as 01/01/2017 and 01/31/2017. If I run the report in March, dates should be populate as 01/01/2017 and 02/28/2017.

I was able to populate prompt dates as per the requirement using JavaScript and it was working fine but when I schedule the report, Javascript is not working. I got to know that Java script will not work in background then I was looking for alternative option to populate dates directly for the filter. Can somebody help me how do I achieve using standard functions for dates population.



Thanks,
Suman

[MFGF]

Hi,

I am new to cognos report studio. I developed a report to extract the data for given dates in the prompt. Report needs to run once in a month for previous month dates. If I am in February I want the dates to be populated as 01/01/2017 and 01/31/2017. If I run the report in March, dates should be populate as 01/01/2017 and 02/28/2017.

I was able to populate prompt dates as per the requirement using JavaScript and it was working fine but when I schedule the report, Javascript is not working. I got to know that Java script will not work in background then I was looking for alternative option to populate dates directly for the filter. Can somebody help me how do I achieve using standard functions for dates population.



Thanks,
Suman

Hi,

Is there a specific reason you are using prompts for this? Couldn't you just code a filter to do this automatically?

eg [your Date item] between _first_of_month (_add_months (current_date, 0 - (extract(month, current_date) -1))) and _last_of_month (_add_months (current_date, -1))

MF.

[sumancognos]

Thanks MF for quick help. I am exploring on writing code for filter but i didn't get logic for from date.
One more question, how can i test this code consider future months as current date so that I can confirm that logic is working for future months.
Also logic should work even year changes that means if i am running in year January 2018 then the dates should be populate as 01/01/2016 to 12/31/2016. I believe the logic given below will fail in that scenario. Please correct me if I am wrong.

Regards,
Suman

[MFGF]

Thanks MF for quick help. I am exploring on writing code for filter but i didn't get logic for from date.
One more question, how can i test this code consider future months as current date so that I can confirm that logic is working for future months.
Also logic should work even year changes that means if i am running in year January 2018 then the dates should be populate as 01/01/2016 to 12/31/2016. I believe the logic given below will fail in that scenario. Please correct me if I am wrong.

Regards,
Suman

Hi,

Ok - let's explain the logic of this expression as a starter

We are using the current_date item to return the current date in the expression, so this would be 3rd February 2017 today.

the _add_months() function adds a number of months to a date, so if you said _add_months(current_date, 1) you would get 3rd March 2017, and add_months(current_date, -1) would return 3rd January 2017

the extract() function extracts parts of dates from a date item, eg year, month or day parts. In our expression we're using it to extract the month part from current_date, so it returns 2 (for February) We are subtracting one from this, (to get 1) and then subtracting this value from 0 to get -1. This is then used in the add_months() function as the number of months to add

so we have add_months(current_date, 0 - (2 - 1)) which gives us add_months(current_date, -1), and thus returns 3rd January 2017. A month from now it will be add_months(current_date, 0 - (3 -1)) which will give us add_months(current_date, -2) and thus will return 3rd January 2017.

The first_of_month() function returns the first day in the month for a given date. So first_of_month(current_date) would give us 1st February 2017. In our expression it's giving us the first day of the month for the result of the add_months() function above, so it's returning 1st January 2017.

last_of_month() is the same concept, but this time it returns the last day in the month for a given date, so _last_of_month (_add_months (current_date, -1)) gives us 31st January 2017. once we move into March it will give us 28th February 2017.

So, onto your next point. From what we've described, we can see this will not give us the whole prior year when we run a report in January. I think we'll need to extent the expression to do this. Something like:

(extract(month, current_date) > 1 AND [your Date item] between _first_of_month (_add_months (current_date, 0 - (extract(month, current_date) -1))) and _last_of_month (_add_months (current_date, -1)))
OR
(extract(month, current_date) = 1 AND [your Date item] between _first_of_month (_add_years (current_date, -1)) and _last_of_month(_add_months(_add_years(current_date, -1), 11)))

This means when you run the report in January 2018 your filter will return rows with dates between 1st January 2017 and 31st December 2017

Cheers!

MF.

[Michael75]

@ MF

What a masterful explanation! And to think that we get advice of this quality for free on Cognoise. There's still some hope for the world . . . :)

[sumancognos]

Hi,

Ok - let's explain the logic of this expression as a starter

We are using the current_date item to return the current date in the expression, so this would be 3rd February 2017 today.

the _add_months() function adds a number of months to a date, so if you said _add_months(current_date, 1) you would get 3rd March 2017, and add_months(current_date, -1) would return 3rd January 2017

the extract() function extracts parts of dates from a date item, eg year, month or day parts. In our expression we're using it to extract the month part from current_date, so it returns 2 (for February) We are subtracting one from this, (to get 1) and then subtracting this value from 0 to get -1. This is then used in the add_months() function as the number of months to add

so we have add_months(current_date, 0 - (2 - 1)) which gives us add_months(current_date, -1), and thus returns 3rd January 2017. A month from now it will be add_months(current_date, 0 - (3 -1)) which will give us add_months(current_date, -2) and thus will return 3rd January 2017.

The first_of_month() function returns the first day in the month for a given date. So first_of_month(current_date) would give us 1st February 2017. In our expression it's giving us the first day of the month for the result of the add_months() function above, so it's returning 1st January 2017.

last_of_month() is the same concept, but this time it returns the last day in the month for a given date, so _last_of_month (_add_months (current_date, -1)) gives us 31st January 2017. once we move into March it will give us 28th February 2017.

So, onto your next point. From what we've described, we can see this will not give us the whole prior year when we run a report in January. I think we'll need to extent the expression to do this. Something like:

(extract(month, current_date) > 1 AND [your Date item] between _first_of_month (_add_months (current_date, 0 - (extract(month, current_date) -1))) and _last_of_month (_add_months (current_date, -1)))
OR
(extract(month, current_date) = 1 AND [your Date item] between _first_of_month (_add_years (current_date, -1)) and _last_of_month(_add_months(_add_years(current_date, -1), 11)))

This means when you run the report in January 2018 your filter will return rows with dates between 1st January 2017 and 31st December 2017

Cheers!

MF.

Thanks MF. I just saw your reply and didn't try your solution yet but I am 100% confident that after reading your explanation, the logic will work for sure. Even though I still have an issue I will not ask you again :) as your explanation educated me a lot and got so much of experience. I really appreciate your help. Hats off to your patience and taking time to explain in detail and encouraging starters like me.

[MFGF]

... Even though I still have an issue I will not ask you again :) ...

We don't impose any restrictions around how many questions you're allowed to ask :) There are lots of people who use the forum, so you wouldn't be asking just me...

I'm really pleased the explanation helped - it's nice to solve a problem and increase your knowledge at the same time. :D

MF.

[camilooso]

Hello; thank you for all your support, I really need to know how to filter the information only in cells where I found data. can you support me?

[MFGF]

Hello; thank you for all your support, I really need to know how to filter the information only in cells where I found data. can you support me?

Hi

Assuming you're also filtering on dates as above, you could extend the filter to exclude null values too, eg

... AND <your measure item> is not null.

Cheers!

MF.