I need to design a chart report where the revenue of the starting promotion start month and the previous two months and next two months should be shown.
what would be the code to find previous 2 months and next two months by using cognos functions ?
_months_between function
Thanks a lot Blom i ll give it a try :)
The _months_between function returns a integer value either in +ve/-ve by taking two timestamp parameters.
Exactly what i need is
Query1 contains
Promotion
Product
Planned revenue
Revenue
Month
Query2 Contains
Planned revenue
Revenue
Product
Month
Query 3 is Join between query 1 and 2
Product
Month
Planned Revenue
Revenue
I need to apply a filter in Query 3 so that only data for the 5 months (Promotion month+previous 2 months+ after 2 months) data will be shown.
Please sugest how to achieve that.
Thanks in advance
Attaching the XML.
That would depend on the definition of month.. If you only use the 12 valued range: 1-12 / Jan-Dec then calculations are a real pain. If you take - for instance - the first / last day of the month (as date) into you model, then temporal calculations become much easier. The best approach is to have a dataitem that contains the month representation and another one to calculate with..
i tried with the filter with expression
cast([Query2].[Month],timestamp) between (_add_months((cast([Query1].[Month],timestamp),-2) and (_add_months((cast([Query1].[Month],timestamp),2)
But its giving the following error
An error occurred while performing operation 'sqlOpenResult' status='-28'.
In your case what does [month] represent? Integer (1-12) , String, Date?
String
like what :o? If it represents a date format you can use it to cast to date/timestamp.
Resolved the issue by using Month(numeric) data item instead of Month data items.
Anyways...Thanks a lot for helping.
If you mean the numeric values running from 1 -12 then you will run into issues transcending years. By the way, the idea of a peer-to-peer forum is to post the solution if a case is resolved. This is to ensure other developers can benefit from a topic (discussion)
RESOLVED
Attaching the xml of the report (with sample package).