Pulling Data for entire month prior to current month

[Hinky]

i want to pull benefit deduction amounts for the entire month prior, grouped by employee.
This is what I am using... and it does not work... Please help :)

extract (month,[My Employees].[Main Section].[Pay Date]) = extract (month, _add_months(current_date,-1) )

Thank you kindly  :P

[cognos10]

Hello u can try the below filter


keep a simple filter saying

[My Employees].[Main Section].[Pay Date]
between

first_of_month(_add_months(current_date,-1)
and
last_of_month(_add_months(current_date,-1)


I think this should work

Thanks

[Hinky]

I tried this:


[My Employees].[Main Section].[Pay Date] between first_of_month (_add_months(current_date,-1) and last_of_month (add_months(current_date,-1)


and I'm still getting an error... I'll continue to try and work with this.

[Lynn]

The Cognos function for first of month and last of month have the underscore character in the beginning of the function name:

_first_of_month
_last_of_month

So unless your DBMS has a function called first_of_month then I imagine you'd get a parsing error. It is often useful to specify the error you are getting or the unexpected behavior you see. People can only guess when the question is "i get an error" or "it doesn't work".

[Hinky]

I modified the query to include the underscore as mentioned. This is the error that I am getting:

   Parsing error before or near position: 143 of: "[My Employees].[Main Section].[Pay Date] between _first_of_month (_add_months(current_date,-1) and _last_of_month (add_months(current_date,-1)"

Thanks

[Lynn]

A good way to troubleshoot filter expressions like this is to create data items first to get the syntax figured out and ensure they are returning values you expect.

For example, create a data item in the query and work out the first of month portion.

As soon as you do that you can see there are two opening parenthesis and only one closing parenthesis. Looks like the same issue on the last of month side.

Code Select Expand

_first_of_month (_add_months(current_date,-1)
should be

Code Select Expand

_first_of_month (_add_months(current_date,-1))

Once you have the two items working as expected you can plop them into your filter and take it from there. Hope this helps

[Hinky]

Thanks Lynn! This worked perfectly!! ;)