Dynamic filter for month

[srinu_anu2007]

Hi,

I have a data item which is iam writing like this:

case [BILL_PERIOD_ID]
when 18 then 'January-2014'
when 19 then 'February-2014'
when 20 then 'March-2014'
when 21 then 'April-2014'
when 22 then 'May-2014'
when 23 then 'June-2014'
when 24 then 'July-2014'
when 25 then 'August-2014'
when 26 then 'September-2014'
when 27 then 'October-2014'
when 28 then 'November-2014'
when 29 then 'December-2014'
when 30 then 'January-2015'
when 31 then 'February-2015'
when 32 then 'March-2015'
when 33 then 'April-2015'
when 34 then 'May-2015'
when 35 then 'June-2015'
when 36 then 'July-2015'
when 37 then 'August-2015'
when 38 then 'September-2015'
when 39 then 'October-2015'
when 40 then 'November-2015'
when 41 then 'December-2015'
else 'NA'
end ||' Personal call cost declared in IVCDS'

[BILL_PERIOD_ID] is directly coming from package which is having 1,2,3.....21 like this.

I am using in my detail filter is [BILL_PERIOD_ID] = 36 so i will get as per above statement month data.

Now my client is asking to display dynamically and i have to schedule that report.

Client wants data for every month on 10th.  If he runs on 10th Oct, he wants to see August data(that means skipping on month as per requirement).

Can some one help me how to to get it? I have seen some functions _add_months (2015-10-01,-2) but how to match as per requirement, iam not getting . I hope you people can understand this.

Environment: Cognos 10.2.2, DB2 and FM

Thanks,

[BigChris]

Could you come up with an expression like this:

Code Select Expand

_months_between(_add_months (current_date, -2), _make_timestamp (2012, 06, 01))

You'll need to do some testing and possibly have to tweak the dates, but it ought to come back with roughly the right number...

[srinu_anu2007]

Hi Chris,

Thanks for your help and time. Whatever the expression you have provided is static and i want to do dynamic.

We have to do something with the case statement and that one i have to keep in filters section. ex: now we are into September and client want wants data so  i have given static filter like  [BILL_PERIOD_ID]=38 and iam getting data for this, there is no issue and the same thing i have to do as dynamic.

Thanks,

[bdbits]

It's not static, current_date represents the current date at the time the report runs. The hard-coded date of 2012-06-01 is because that appears to be the base date you are using.

The formula should work, but to be honest if you intend to use this in a lot of places I would add the offset from that date (i.e. the billing period id) as a column in your time dimension at ETL time. Then you can let them pick from a calendar control to filter on the time dimension, and a join between the time dim and your fact based on the billing period id would handle it automagically.

[srinu_anu2007]

Hi Chris and bdbits, Thanks for your suggestions and still iam not clear how to sync with my case statement.

I have the filter: [BILL_PERIOD_ID]=36 which to get July-2015 data or if i need all month [BILL_PERIOD_ID] >=19 i will keep this one.

the expression which is given how do i use in this scenario. Here iam attaching my data and using the above static filter.

Thanks,

[MFGF]

Hi Chris and bdbits, Thanks for your suggestions and still iam not clear how to sync with my case statement.

I have the filter: [BILL_PERIOD_ID]=36 which to get July-2015 data or if i need all month [BILL_PERIOD_ID] >=19 i will keep this one.

the expression which is given how do i use in this scenario. Here iam attaching my data and using the above static filter.

Thanks,

You said you wanted to get the current value dynamically? Coding your filter as [BILL_PERIOD_ID] = _months_between(_add_months (current_date, -2), _make_timestamp (2012, 06, 01)) seems to be exactly what you requested? It's the exact solution Chris suggested to you.

MF.

[BigChris]

Always good to have a solution verified by an expert  :D

[MFGF]

Always good to have a solution verified by an expert  :D

Ha! My take on this is that it's more of an expert's solution verified by a muppet :)

[srinu_anu2007]

I got an error saying query requires local processing of data and i have resolved to enable the processing property as limited local. Thanks all you and its working as expected.

And i want to understand one more thing in the below expression:

_months_between(_add_months (current_date, -2), _make_timestamp (2012, 06, 01)) : every time i need to set up _make_timestamp as static to get the number as per my case statement? as per the expression iam getting the no is 37(August) i.e as per my case statement.

Thanks,

[srinu_anu2007]

Hi,

Can anyone please suggest

_months_between(_add_months (current_date, -2), _make_timestamp (2012, 06, 01))

is it static to make changes as per my case statement everytime: _make_timestamp (2012, 06, 01))

Thanks,

[MFGF]

Hi,

Can anyone please suggest

_months_between(_add_months (current_date, -2), _make_timestamp (2012, 06, 01))

is it static to make changes as per my case statement everytime: _make_timestamp (2012, 06, 01))

Thanks,

Based on the logic in your case statement, BILL_PERIOD_ID would have a value of -1 for June 2012. This is the baseline your expression sets. Normally you'd use a baseline of 0, but your requirement is also to skip back one month, so Chris used the June 2012 month rather than July as the baseline.

MF.

[BigChris]

The _make_timestamp part is the bit that sets the start point. Based on what you posted originally, that ought to be static. Each month you want to count the number of months from that date...and that's what the calculation is meant to do.

[srinu_anu2007]

Exactly what you people said is correct and I have done the changes as per the requirement. My question is if don't change the month in the time stamp that will not effect to my result right? No need to change every time to make static like _make_timestamp (2012, 07, 01)) or _make_timestamp (2012, 08, 01) or _make_timestamp (2012, 09, 01) like this.

Thanks,

[BigChris]

Correct - leave that as it is.

[MFGF]

Exactly what you people said is correct and I have done the changes as per the requirement. My question is if don't change the month in the time stamp that will not effect to my result right? No need to change every time to make static like _make_timestamp (2012, 07, 01)) or _make_timestamp (2012, 08, 01) or _make_timestamp (2012, 09, 01) like this.

Thanks,

No - that simply sets the baseline from which the BILL_PERIOD_ID values start. You don't need to change it. Do you understand how this expression is working? We tried to explain but it seems like you don't get it?

MF.

[srinu_anu2007]

Thanks. I understand the expression and i got it how its working. somewhere i have doubt so want to clarify with experts like you people.

Thanks for your help and time.

Regards,