Skip to Content
0

How do i display all dimension values with restriction applied on previous month?

Nov 27, 2017 at 04:08 AM

112

avatar image
Former Member

dropdown-snapshot.pngHi,

I have dashboard in design studio where i have a dimension MonthYear which contains data for last 2 years month wise. As per the requirement by default dashboard should show the values of last month only. As per current month only value for October 2017 is shown in drop down not for all the months. I have used different functions like get members etc but it only shows value for previous month. Can someone help me how can get value for all months?

Thanks

Harish

10 |10000 characters needed characters left characters exceeded

Harish,

It would be helpful in providing guidance if you provided the following information:

1. What type of data source are you using: BW BEx, HANA, Universe?

2. How is your data source being pre-filtered to default to the current month/year: in the backend or frontend?

3. How are you populating the Dropdown List, with property binding or scripting?

Regards,

Mustafa.

0
Former Member

Hi Mustafa,

My Data source is BW BeX

Restriction is applied in backend

Through scripting

Regards

Harish

0

Some more questions:

1. Can you post the script code you are applying to populate the Dropdown with?

2. Can you clarify exactly HOW the restriction is being applied in the backend?

0
Former Member

Hi Musthafa,

Backend developer has applied current month -1 as default in the back end, i have used following code it shows all values but it doesn't default to last month.

var getMembersArray=DS_1.getMembers("0CALMONTH", 60);

I want by default to show last month data plus option in the drop down to further select values for other periods.

Thanks

Harish

0

When you say your code "shows all values but it doesn't default to last month" do you mean the Dropdown shows all the months? Your previous screenshot only shows the last month value in the Dropdown.

0
Former Member

HI Mustafa,

dropdown.pngSorry i sent old screenshot, at present in my dropdown it shows all months but it doesn't default to last month value.

Thanks

Harish

dropdown.png (7.5 kB)
0
* Please Login or Register to Answer, Follow or Comment.

7 Answers

Best Answer
Mustafa Bensan Dec 02, 2017 at 06:39 AM
0

Hi Harish,

I suggest you update your code as follows:

On Startup Script:

var curr_date = APPLICATION.getInfo().dateNowInternalFormat;

var curr_year  = curr_date.substring(0,4);
var curr_year_num = Convert.stringToInt(curr_year);
var curr_month = curr_date.substring(4,6);

var previous_month_key = "";
var month_key = "";

var years = [""];
years.pop();

var months = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];

var curr_month_index = Convert.stringToInt(curr_month) - 1;

if (curr_month_index == 0) {
	
	curr_year = Convert.floatToString(curr_year_num - 1, "0000");
	curr_month_index = 12; 
	
}

years.push(curr_year_num - 1 + "");
years.push(curr_year_num - 2 + "");
years.push(curr_year_num - 3 + "");
years.push(curr_year_num - 4 + "");

months.forEach(function(month, index) {

	if (index < curr_month_index) 

		{
	
			month_key = curr_year + Convert.floatToString(index + 1.0, "00");
			DDLB_MTHYR.addItem(month_key, months[index] + " " + curr_year);
			previous_month_key = month_key;
	
		}

});

years.forEach(function(year, index) {

	months.forEach(function(month, index) {
	
		month_key = year + Convert.floatToString(index + 1.0, "00");
		DDLB_MTHYR.addItem(month_key, months[index] + " " + year);
	  
	});
  
});

DDLB_MTHYR.setSelectedValue(previous_month_key);

On Select script of DDLB_MTHYR:

APPLICATION.setVariableValue("ZMONTHYEAR1", DDLB_MTHYR.getSelectedValue());

Regards,

Mustafa.

Share
10 |10000 characters needed characters left characters exceeded
Mustafa Bensan Nov 29, 2017 at 11:18 AM
0

Hi Harish,

You can try the following code On Startup:

var myMonths = DS_1.getMembers("0CALMONTH”,100);

myMonths.forEach(function(element, index) {
  DROPDOWN_1.addItem(element.internalKey, element.text);
});

var lastMonth = myMonths.pop();

DROPDOWN_1.setSelectedValue(lastMonth.internalKey);

Regards,

Mustafa.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 01, 2017 at 02:34 AM
0

Hi Mustafa,

Thanks a lot for sending the script when i add the script on start up it is defaulting to previous month but it is not showing all months any idea why it could be?

Thanks

Harish

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 01, 2017 at 07:50 AM
0

Hi Mustafa,

I am not sure why it doesn't display all month values, here is the code one of my colleague helped me with to display all values.

I have created a global script variable and added below script there and calling this script variable on start up.

var curr_year = APPLICATION.getInfo().dateNowInternalFormat.substring(0,4);

var curr_year_1 = Convert.stringToInt(curr_year)-1;

var curr_year_2 = Convert.stringToInt(curr_year)-2;

var curr_year_3 = Convert.stringToInt(curr_year)-3;

var curr_year_4 = Convert.stringToInt(curr_year)-4;

DDLB_MTHYR.addItem("01."+curr_year, "Jan "+curr_year,0);

DDLB_MTHYR.addItem("02."+curr_year, "Feb "+curr_year,1);

DDLB_MTHYR.addItem("03."+curr_year, "Mar "+curr_year,2); DDLB_MTHYR.addItem("04."+curr_year, "Apr "+curr_year,3); DDLB_MTHYR.addItem("05."+curr_year, "May "+curr_year,4); DDLB_MTHYR.addItem("06."+curr_year, "Jun "+curr_year,5); DDLB_MTHYR.addItem("07."+curr_year, "Jul "+curr_year,6); DDLB_MTHYR.addItem("08."+curr_year, "Aug "+curr_year,7); DDLB_MTHYR.addItem("09."+curr_year, "Sep "+curr_year,8); DDLB_MTHYR.addItem("10."+curr_year, "Oct "+curr_year,9); DDLB_MTHYR.addItem("11."+curr_year, "Nov "+curr_year,10); DDLB_MTHYR.addItem("12."+curr_year, "Dec "+curr_year,11);

Same script i have repeated for previous year's by declaring a variable with year -1, year -2, etc. I have added the script you have shared to my code it defaults to previous month but it doesn't display all month values. When i take your script out it displays all months but it doesn't default to previous month.

In drop down i have used below script

APPLICATION.setVariableValueExt("ZMONTHYEAR1", DDLB_MTHYR.getSelectedValue());

Any clue how we can achieve both?

Thanks

Harish

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Harish,

Can you post screenshots of the BEx Query in Query Designer showing exactly how the YearMonth restriction has been applied?

Regards,

Mustafa.

0
avatar image
Former Member Dec 04, 2017 at 01:26 AM
0

Hi Mustafa,

Thanks a lot it worked like charm, i need 2017 at the bottom and previous years at the top which i will try to modify the script.

Regards

Harish

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Harish,

If you need the years in ascending order then you can try the following code variation:

var curr_date = APPLICATION.getInfo().dateNowInternalFormat;

var curr_year  = curr_date.substring(0,4);
var curr_year_num = Convert.stringToInt(curr_year);
var curr_month = curr_date.substring(4,6);

var previous_month_key = "";
var month_key = "";

var years = [""];
years.pop();

var months = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];

var curr_month_index = Convert.stringToInt(curr_month) - 1;

if (curr_month_index == 0) {
	
	curr_year = Convert.floatToString(curr_year_num - 1, "0000");
	curr_month_index = 12; 
	
}

years.push(curr_year_num - 4 + "");
years.push(curr_year_num - 3 + "");
years.push(curr_year_num - 2 + "");
years.push(curr_year_num - 1 + "");

years.forEach(function(year, index) {

	months.forEach(function(month, index) {
	
		month_key = year + Convert.floatToString(index + 1.0, "00");
		DDLB_MTHYR.addItem(month_key, months[index] + " " + year);
	  
	});
  
});

months.forEach(function(month, index) {

	if (index < curr_month_index) 

		{
	
			month_key = curr_year + Convert.floatToString(index + 1.0, "00");
			DDLB_MTHYR.addItem(month_key, months[index] + " " + curr_year);
			previous_month_key = month_key;
	
		}

});

DDLB_MTHYR.setSelectedValue(previous_month_key);
0
avatar image
Former Member Dec 05, 2017 at 01:15 AM
0

Hi Mustafa,

Thanks i could get data for last 5 years, users wanted to have months in descending order when i change the months sequence dec to jan instead of current code showing jan to dec it defaults previous month to Feb. I modified the script but it doesn't default it to nov it defaults to Feb only any ideas how i can defaulted it to previous month by using descending order?

Thanks

Harish

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 05, 2017 at 02:55 AM
0

Hi Mustafa,

I have fixed descending order thing, thanks a lot for your help.

Regards

Harish

Share
10 |10000 characters needed characters left characters exceeded