I am trying to show the previous month numbers for a particular ‘CODE’ in a report. Unfortunately, If there are 2 months/weeks I could able to show the numbers.But how do I show the numbers(Previous Month/Week) for more than a year period?.
I tried using the PREVIOUS function, But it’s giving me the wrong result. Instead of getting the previous numbers for the code, it’s showing me the numbers from previous row.
I know I need to define a context and create some variables to get this done…But I am stuck now…
Could anyone help in how do I proceed.
The report is already has 2 breaks in it and I need to show PREVIOUS month values for a particular CODE in a CLASS break.
Currently I am having a single Query with the values from 01/01/2000 to till date.
The report looks like…
Class1
01/01/2000 CurrMonth Values Prev Month Values
A 1000 0
B 2000 0
C 1100 0
03/01/2000
B 3000 2000
C 1000 1100
08/01/2001
A 5000 1000
Class2
05/01/2000
C
03/01/2001
B
D
08/01/2001
D
Etc…
Looking for some help in finding a solution.
*** I did gone through the archives, But seems to be none of them are helpful to me.
“previous” function gives you previous record value
You have to use date functions, to get the previous month.
create two variables
=Month()
=Month(LastDayOfMonth(-31))
You could achieve this by creating a previous month object using add_months function (for eg.to_char(add_months(sysdate,-1),‘MM-YYYY’) and now use this object in the 2nd DP (Prev.Month) this DP will give you the data for the previous month and then pull appropriate objects from the current and previous DPs.
I had already tried those options. The problem with my report is it’s fetching the records for more than 1 year period.
I need to check whether if the previous month numbers exixts or not.
If exists, How do I create a variable ?.
= where <WHAT CONDITION/CONTEXT> I should mention?.
B’z it’s not liking any.
I tried the below,
= where <Rpt_Date> = <Prev_Rpt_Date>
= In (, <Prev_Rpt_Date>)
With the second DP option, It’s not getting the exact numbers for the previous month as the report has a break on the <Rpt_Date> from DP1. And there is no link between the report dates between 2 DP.
Any help would be appreciated.
The requirement is to show a period of monthly values in a report by showing the previous month values on the current month row.
If the table has “break” or dataproviders not linked then it is a challenge to achieve this and also we cannot use “where” clause with variables and it required hardcoded value.
You can try this, it might work,
In the second data provider, get the summay records with month and year (no details), then use “previous” function to get the previous month value.
I’m getting the second DP with Class,Code and the measure based on the NextMonth Report date (Which is a current object in the Universe).
The problem is, When I am trying to show the value from the 2nd DP along with the 1st DP, it’s getting the cumulative totals, but not by Code and previous rpt date.
That is the reason, I was trying to create a flag as Andreas suggested. But seems that is also didn’t work for me.