YTD in list report

[rajmns]

Hi All,

I am trying to build a YTD list report. The users will be prompted for a date which is in YYYY-MM format. If they select say, 2007-09, the report should contain data from 2007-01 to 2007-09. If they select 2006-07, the report should have values from 2006-01 to 2006-07. Can anyone suggest me on how to do that?

Thank you.

 

[almeids]

Depends on what sort of dating columns you have in your table. If you have year and month in character format, it's as simple as adding a filter something like: [yearcolumn]=substr(?yearmonthparam?,1,4) and [monthcolumn]<=substr(?yearmonthparam?,6,2)

If you have a date column, you'll need to manipulate it or the prompt parameter with your database's or Cognos' date functions to get comparable values for your filter.

 

[rajmns]

Thanks for the reply. The date is in numeic format. And I should use only one field that has month and date. I cannot use the month only field or year only field. How can I do that?

Thanks again.

 

[almeids]

Do you mean that your date column is numeric, like 200,601 for January 2006?

If so use a filter something like...

[datecolumn] BETWEEN to_number(substr(?param?,1,4))*100 AND to_number(substr(?param?),1,4))*100+to_number(substr(?param?,6,2))

...with appropriate Cognos' and/or your db functions.

 

[rajmns]

no, the date is in the format 2007-10 for october etc..

 

[almeids]

OK, so you don't need to do any numeric conversion, you can just substring and/or concatenate as necessary. The gist of it is you need the filter to do 2 things, select the correct year and and select up to the correct month. Whether you do this as two ANDed conditions or a single BETWEEN is your call, you might want to experiment and see if one approach gives better performance than the other.