how to Create a Variable for previous month?

[system]

Hi Experts,
I have a problem with creating a variable for previous month.
In my universe i have month code object it is in all reports at query filter level. Now i am scheduling the reports. But problem is every month i change the month in all reports. to overcome this problem i want to create a variable for the Month and automatically chaged based on system or server date.

Could you any body help to write the syntex for this below formula to previous month.If no selection is made then it should always take last month.

@Select(Time\Month Code)= case when @Prompt(’EnterMonth Code’,'D’,mono,free) =‘1900-01-01′ then datename(mm,dateadd(m,-1,getdate())) else @Prompt(’Enter Month Code’,'D’,mono,free) end

FYI
Database:- SQL server 2005
Business Objects : XI 3.1 + SP2 + FP2.1

Please give your inputs to resolve the issuse.

Thanks in Advance

Ramesh

---

venkatarameshc (BOB member since 2011-06-09)

[system]

Hi ,

If i understood your problem correctly then you want to schedule the reprot to the date of previous month date

So for that you can change the code to :
@Select(Time\Month Code)= datename(mm,dateadd(m,-1,getdate()))

SO evry time you schedule it will always take previous maonth date and run the reprot

Regards
Sapan

---

swapnil_taz (BOB member since 2006-04-06)

[system]

Hi,

Have a look also at this sticky topic:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks Marek,

same as i done in universe level by using of Previous month syntax.But when i am trying to parse in universe level for below mentioned code i get a error parse failed:Invalid definition(UNV0023).Error parsing Parameter.

Syntax i used for creating a variable:

@select(Time\Month Code)=Case when @Prompt(‘Enter Month Code’,‘D’,Mono,free)=‘1900-01-01 00:00:00.000’ then month(dateadd(m,-1,cast(convert(char(7),getdate(),23)+‘01’ as datetime))) else @Prompt(‘Enter Month Code’,‘D’,Mono,free) end

Please help how to solve this.

Thanks in Advance
Ramesh

---

venkatarameshc (BOB member since 2011-06-09)

[system]

Hi ,
U are using getdate function , beacuse of this the error is coming . Ignore the parse error .

Try using this in your report with other objects of universe it should work fine .

Regards
Sapan

---

swapnil_taz (BOB member since 2006-04-06)

[system]

Thanks Sapan,

in second parameter i am getting month code like 6,7.But in my table it shows Jan,Jun,Jul like can u help me how to convert code into desc.

Thanks
Ram

---

venkatarameshc (BOB member since 2011-06-09)

[system]

Hey Ram ,

You are using Month Code as your Liast of Value … so what ever is present there would be coming (Time\Month Code).

If you have a Month Name field in your time table use it instead or derive month Name through month code using a case statement .

Regards
Sapan

---

swapnil_taz (BOB member since 2006-04-06)

[system]

Thanks sapan

i.e month code means month desc
Like
Jan
Feb
Mar
…Dec

By using below syntax i am getting month cod like 7.
How to convert this as Jul.

Thanks
Ram

---

venkatarameshc (BOB member since 2011-06-09)

[system]

Its beacuse of the month function you are using:

month(dateadd(m,-1,cast(convert(char(7),getdate(),23)+‘01’ as datetime)
It returns Month Number .

Better you create a if statement at reprot level to handle this

Regards
Sapan

---

swapnil_taz (BOB member since 2006-04-06)

[system]

Thanks,

@Select(Time\Month Code)= case when @Prompt(’EnterMonth Code’,‘D’,mono,free) =‘1900-01-01’ then datename(mm,dateadd(m,-1,getdate())) else @Prompt(’Enter Month Code’,'D’,mono,free) end

i am trying to insert in Month code object in Universe. But it’s showing error-Invalid definition (UNV00023) and i ignore the error & export to server. Now i open the report on that it show invalid Promt definition.

My issuse is Scheduling the reports automatically(Without changing the every month)

In my report i have Year and Month as filters.

Sooo Plz can anyone help how to achieve the results.
Is it possible to impliment ?
If possible plz help me?

Database:- SQL server 2005
Business Objects : XI 3.1 + SP2 + FP2.1

Thanks and Regards,
Ramesh

---

venkatarameshc (BOB member since 2011-06-09)