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

[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

[MustafaBensan]

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.

[Former Member]

Hi Mustafa,

My Data source is BW BeX

Restriction is applied in backend

Through scripting

Regards

Harish

[MustafaBensan]

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?

[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

[MustafaBensan]

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.

[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

[MustafaBensan · Accepted solution]

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.

[Former Member]

Hi Mustafa,

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

Regards

Harish

[Former Member]

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

[Former Member]

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

[MustafaBensan]

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);

[Former Member]

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

[MustafaBensan]

Hi Harish,

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

Regards,

Mustafa.

[Former Member]

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

[MustafaBensan]

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.