Hi everyone!
I'm currently stuck on a user request that I can't figure out. The user is requesting to see only data prior to the current month because they can't report current month data. I've tried using extract(month, current_date) but keep getting an error.
Ideally, I would like to use this as a filter so that it brings back only the months prior to the current month.
Thanks in advance!
On relational, that would be:
[date]
<=
_last_of_month (
cast(
if
(_month(current_date) = 1)
then
(_year(current_date)-1)
else
(_year(current_date))
+ '-' +
if
(char_length(
cast(
if
(_month(current_date) = 1)
then
(12)
else
(_month(current_date)-1)
, varchar(2))
)=1)
then
('0')
else
(null)
+
if
(_month(current_date) = 1)
then
(12)
else
(_month(current_date)-1)
+'-05' , date)
/edit: yes, I'm quite sure that is ugly and probably not as efficient as it can be, but it works. ;)
Is your data source relational? Are you filtering on a date or do you have a month/year field of some sort?
If your source is relational and you want to filter so that only dates prior to the current month are returned you can simply use this as your filter:
[Your Date] < _first_of_month ( current_date )
or that, obviously... I'm gonna go sit in a corner now and have my colleagues yell "shame" at me repeatedly. ;)
Quote from: hespora on 27 Sep 2016 06:23:20 AM
or that, obviously... I'm gonna go sit in a corner now and have my colleagues yell "shame" at me repeatedly. ;)
Don't let that discourage you from helping. We all come from different backgrounds, so seeing other people's approach to problems is always good -- even if it's an approach you learn to avoid.
No shame at all :) There's no true mastery of Cognos (it's too damn big!)
Quote from: CognoidJay on 27 Sep 2016 08:18:14 AM
Don't let that discourage you from helping. We all come from different backgrounds, so seeing other people's approach to problems is always good -- even if it's an approach you learn to avoid.
No shame at all :) There's no true mastery of Cognos (it's too damn big!)
Absolutely true! Plus things are always changing. I learn a lot here and appreciate the input from so many different people facing a wide variety of requirements and situations.
Thanks everyone! I will try this out and report back with my results.
Thanks again!
Quote from: hespora on 27 Sep 2016 06:23:20 AM
or that, obviously... I'm gonna go sit in a corner now and have my colleagues yell "shame" at me repeatedly. ;)
You didn't tell me you were going to adopt my role! Whatever will I do now? Sigh. I will be demoted from shame-boy to tea-boy, no doubt. :)
MF.