on 03-30-2014 8:27 AM
Hello Experts,
We have a requirement where we need to default date range to bex query filter by calculating from date as system date minus 45 days and to date as system date for example "20140215 - 20140330". How can we achieve this in Design Studio 1.2 SP01. I know we can do this using BEx variables, but I am curious by using Java Script if I can calculate dates and pass to BEx Query. Thanks in advance.
Thanks
Mohan Kolli
Hi,
First of all Try To bring Last 45 Days Data from Source System ( SAP BI BEx Query ) .
Its Easy & Fast .
But If for some reason you are not able to do it then Please Try The code below.
You May need to modify it .
var vDate = APPLICATION.getInfo().dateNowInternalFormat;
var vDay = Convert.subString(APPLICATION.getInfo().dateNowInternalFormat, 6,8);
var vMonth = Convert.subString(APPLICATION.getInfo().dateNowInternalFormat, 4,6);
var vYear = Convert.subString(APPLICATION.getInfo().dateNowInternalFormat, 0,4);
var vLastMonth = Convert.stringToInt(vMonth)-1;
var vLasttoLastMonth = Convert.stringToInt(vMonth)-2;
TEXT_13.setText(vDate);
TEXT_14.setText(vDay);
TEXT_15.setText(vMonth);
TEXT_16.setText(vYear);
var vPassday = Convert.stringToFloat(vDay) - 45.00;
var vPassyear = "";
var vPassmonth = "";
// Year Passing
if ((Convert.stringToInt(vMonth) <= 2) && (Convert.stringToInt(vDay) < 15)) {
vPassyear = Convert.floatToString((Convert.stringToFloat(vYear)-
1),"###0");
TEXT_17.setText(vPassyear);
}
else {
vPassyear = Convert.floatToString((Convert.stringToFloat(vYear)),"###0");
TEXT_17.setText(vPassyear);
}
// Month Passing
if ((Convert.stringToInt(vMonth)>= 2) && (Convert.stringToInt(vDay)>= 15)) {
vPassmonth = Convert.floatToString((Convert.stringToFloat(vMonth)- 1),
"#0");
TEXT_18.setText(vPassmonth);
}
else {
if ((Convert.stringToInt(vMonth)== 1) && (Convert.stringToInt(vDay)>= 15)) {
vPassmonth = "12";
TEXT_18.setText(vPassmonth);
}
else {
if ((Convert.stringToInt(vMonth)>= 3) && (Convert.stringToInt(vDay)< 15)) {
vPassmonth = Convert.floatToString((Convert.stringToFloat(vMonth)- 2),
"#0");
TEXT_18.setText(vPassmonth);
}
else {
if ((Convert.stringToInt(vMonth)== 2) && (Convert.stringToInt(vDay) < 15))
{
vPassmonth = "12";
TEXT_18.setText(vPassmonth);
}
if ((Convert.stringToInt(vMonth)== 1) && (Convert.stringToInt(vDay) < 15) ) {
vPassmonth = "11";
TEXT_18.setText(vPassmonth);
}
}
}
}
// Day Passing First Negative State
if (vPassday <= 0) {
if (((vLastMonth == 1) || (vLastMonth == 03) || (vLastMonth == 05) ||
(vLastMonth == 07) || (vLastMonth == 08) || (vLastMonth == 10) || (vLastMonth
== 12))) //
{
vPassday = vPassday + 31.00;
// TEXT_18.setText(vPassday + "");
}
else {
if (((vLastMonth == 4) || (vLastMonth == 06) || (vLastMonth == 09) ||
(vLastMonth == 07) || (vLastMonth == 11))) {
vPassday = vPassday + 30.00;
// TEXT_18.setText(vPassday + "");
}
else {
if ((vLastMonth == 2)) {
vPassday = vPassday + 28.00;
// TEXT_18.setText(vPassday + "");
}
}
}
}
// Day Passing Second Negative State
if (vPassday <= 0) {
if (((vLasttoLastMonth == 1) || (vLasttoLastMonth == 03) || (vLasttoLastMonth == 05) ||
(vLasttoLastMonth == 07) || (vLasttoLastMonth == 08) || (vLasttoLastMonth == 10) || (vLasttoLastMonth
== 12))) //
{
vPassday = vPassday + 31.00;
// TEXT_18.setText(vPassday + "");
}
else {
if (((vLasttoLastMonth == 4) || (vLasttoLastMonth == 06) || (vLasttoLastMonth == 09) ||
(vLasttoLastMonth == 07) || (vLasttoLastMonth == 11))) {
vPassday = vPassday + 30.00;
// TEXT_18.setText(vPassday + "");
}
else {
if ((vLasttoLastMonth == 2)) {
vPassday = vPassday + 28.00;
// TEXT_18.setText(vPassday + "");
}
}
}
}
TEXT_19.setText(vPassday+"");
Try It & Let mw know .
Regards ,
Nikhil
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Mohan,
Design Studio lets you read the system date (not sure if this is the SAP system date or taken from the browser):
APPLICATION.getInfo().dateNowInternalFormat
From that you can get month and year:
var year = Convert.subString(APPLICATION.getInfo().dateNowInternalFormat, 0, 4);
var month = Convert.subString(APPLICATION.getInfo().dateNowInternalFormat, 4, 6);
Since both are strings, you need to convert them to integers to be able to add/substract:
var monthint = Convert.stringToInt(month);
You'll need to handle month and year changes (e.g. if the date 45 days ago is 1 or 2 months ago) by some If-Then logic.
Hope this helps.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 3 | |
| 3 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.