Need with 'Previous Month' Query

[advayaaa]

Hi I need to create a filter of date which should have a filter having a static value previous month. I have 3 querys Year to Date,Year to date-last year and Financial year - last year.

Can anyone help me in building filter for these 3 query. Any help is appriciated.

I have data in YYYYMM format (preferable) or year and month separately

[Lynn]

Is this a relational package or a dimensional package? Does YYYYMM refer to a string or an integer? Better answers can be provided when necessary details are provided at the outset.

If relational....

You can subtract 1 month from the current date, and then extract the year and month portions to construct a value you can compare with your data.

This will give you the year of last month as an integer:
extract ( year, _add_months(current_date, -1 ) )

This will give you the month of last month as an integer:
extract ( month, _add_months(current_date, -1 ) )

Cast to character if needed and put them together to match data type of format of your data items. If your data items are string format you will need to include some 0 padding for the month so you have 01, 02, etc. rather than 1, 2, etc.

Once you have the proper expression for last month you can just refer to it in your filter expression.

Dimensional is another thing entirely.

[advayaaa]

Thanks Lynn,

But my problem is that I need year to date data when I select Previous month. For Example if today(March 6 2014) I use the value as Previous month, I want the data from Jan 2014 to Feb 2014 (last month), and I am unable to build up this expression as I am very new in cognos. If would be great if you come help me with the query. The prompt I am using has value in the format YYYYMM

[Lynn]

You didn't answer any of the questions I asked.

Is this a relational package or a dimensional package?

Does YYYYMM refer to a string or an integer?

This is the first mention of a prompt...what choices are presented to the user to select and what is the intended behavior for each choice?

Have you done any experimentation or research on the method I already proposed or are you just throwing up your hands and asking me to spoon feed you?

[advayaaa]

Hi

the package is relational. YYYYMM gets a interger value like 201103 (I may be wrong as I dont know how to check, just a beginner in cognos). I have added a screenshot of the present report.

In the prompt the requirement is to get the year and month and a static choice value "Previous month" .

I have now created two Prompts one for year and one for month and built up the query for YTD
Year :   [PAM Cube Query Studio].[All Dates].[Year Number] = ?Param_Year?

Month :   CASE
WHEN ?Param_Month? = 'PM' THEN
(
[PAM Cube Query Studio].[All Dates].[Month Number of Year] <= to_char(_add_months({sysdate},-1),'MM')
)
ELSE
(
[PAM Cube Query Studio].[All Dates].[Month Number of Year] <= ?Param_Month?
)
END


But when I run the tabular data I do not get any value.  I am working on it along with getting help for u

[Lynn]

It is important that you understand the difference between data types and the particular data types you are working with. If you don't know or can't figure it out, then ask your modeler or your DBA. This isn't a Cognos skill....it is a very basic living-the-good-life-in-IT skill.

In your month expression below you are comparing [Month Number of Year] with a varchar representation of last month's month number, produced by the Oracle to_char function. That function would return '02' since last month was February. What is stored in your [Month Number of Year] data item? Is it 2 rather than 02? Is it an integer or a string?

If you aren't getting any data, then first ensure there is data for your selection criteria. Look at your generated SQL to see if it is behaving as you expect. Try the query with a very simple filter hard coded with just your prior month expression (ignoring the prompt aspect) as an attempt to trouble shoot.

I think it is best to avoid case/if logic in filter expressions. Make it evaluate to a boolean result.

Code Select Expand


( ?Param_Month? = 'PM'
and
[PAM Cube Query Studio].[All Dates].[Month Number of Year] <= to_char(_add_months({sysdate},-1),'MM')
)
OR
(
?Param_Month? <> 'PM'
and
[PAM Cube Query Studio].[All Dates].[Month Number of Year] <= ?Param_Month?
)
END