Question Re: How to write script for DATE Range in Technology Q&A

How to write script for DATE Range

Former Member — Fri, 05 Jul 2013 12:56:21 GMT

HI ALL,

I am new to SAP BO Design Studio, i have five filters Start Date , end date, Plan, profit center material and . Source for application is BW. how to define start date and end date filters. the no of plants and ohter filters should display for next filter should display, in between the start date and end date.

How to define date range filters...

Thanks,

Ramana

Re: How to write script for DATE Range

murali_balreddy — Fri, 05 Jul 2013 13:15:16 GMT

Check if the info in this thread helps: http://scn.sap.com/thread/3381751

Re: How to write script for DATE Range

Former Member — Fri, 05 Jul 2013 14:11:11 GMT

Hi ,

Where i wrote the the code in Datefield_1 on Select or Application level OnStartup.

I wrote the code for remaining three filters like in Onstartup

DROPDOWN_1.setItems(DS_1.getMemberList("Z_PPCD", MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 50,"ALL"));

DROPDOWN_2.setItems(DS_1.getMemberList("Z_PFCPCD", MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 50,"ALL"));

DROPDOWN_3.setItems(DS_1.getMemberList("Z_MATPCD", MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 50,"ALL"));

DROPDOWN_4.setItems(DS_1.getMemberList("Z_POPCD", MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT,50,"ALL"));

and in On Select of Plant Dropdown box , i wrote the code like,

DS_1.setFilter("Z_PPCD",DROPDOWN_1.getSelectedValue());

DROPDOWN_2.setItems(DS_1.getMemberList("Z_PFCPCD",MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 50,"ALL"));

DROPDOWN_3.setItems(DS_1.getMemberList("Z_MATPCD",MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 50,"ALL"));

DROPDOWN_4.setItems(DS_1.getMemberList("Z_POPCD",MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 50,"ALL"));

Now i want to add date Range at top level ( i mean first date range and next those three filters according to date range )

Thanks,

Ramana

Re: How to write script for DATE Range

murali_balreddy — Fri, 05 Jul 2013 22:45:47 GMT

Hi Ramana,

Have two DATEFEILDs for range and a BUTTON to submit the range and have the following code for the BUTTON (If you don't need a button to submit the range, have the code for the second DATEFIELD, provided first DATEFIELD is lower than the second DATEFIELD)

var startDate = DATEFIELD_1.getDate();

var endDate = DATEFIELD_2.getDate();

DS_1.clearAllFilters();

var varRange = startDate + " - " + endDate;

DS_1.setFilterExt("0CALDAY", varRange);

DROPDOWN_1.setItems(DS_1.getMemberList("Z_PPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_2.setItems(DS_1.getMemberList("Z_PFCPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_3.setItems(DS_1.getMemberList("Z_MATPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_4.setItems(DS_1.getMemberList("Z_POPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

In your Application On StartUp have this code to load all drop downs

DROPDOWN_1.setItems(DS_1.getMemberList("Z_PPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_2.setItems(DS_1.getMemberList("Z_PFCPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_3.setItems(DS_1.getMemberList("Z_MATPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_4.setItems(DS_1.getMemberList("Z_POPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

If your dimensions are in this order Z_PPCD >> Z_PFCPCD >> Z_MATPCD >> Z_POPCD, do the following

In your Dropdown (for Z_PPCD) OnSelect event, add

DS_1.setFilter("Z_PPCD", DROPDOWN_1.getSelectedValue());

DROPDOWN_2.setItems(DS_1.getMemberList("Z_PFCPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_3.setItems(DS_1.getMemberList("Z_MATPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_4.setItems(DS_1.getMemberList("Z_POPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

In your Dropdown (for Z_PFCPCD) OnSelect event, add

DS_1.setFilter("Z_PFCPCD", DROPDOWN_1.getSelectedValue());

DROPDOWN_3.setItems(DS_1.getMemberList("Z_MATPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_4.setItems(DS_1.getMemberList("Z_POPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

In your Dropdown (for Z_MATPCD) OnSelect event, add

DS_1.setFilter("Z_MATPCD", DROPDOWN_3.getSelectedValue());

DROPDOWN_4.setItems(DS_1.getMemberList("Z_POPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

In your Dropdown (for Z_POPCD) OnSelect event, add

DS_1.setFilter("Z_POPCD", DROPDOWN_4.getSelectedValue());

See the following

Set up a cascading filter in an application

Alternatively, you can use Filter Panel

Add a filter panel to an application

Thanks.

Re: How to write script for DATE Range

Former Member — Mon, 08 Jul 2013 12:23:49 GMT

As you said like, i wrote the code for DATEFIELD_2

var startDate = DATEFIELD_1.getDate();

var endDate = DATEFIELD_2.getDate();

DS_1.clearAllFilters();

var varRange = startDate + " - " + endDate;

DS_1.setFilterExt("Z_SDPCD", varRange);

DROPDOWN_1.setItems(DS_1.getMemberList("Z_PPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_2.setItems(DS_1.getMemberList("Z_PFCPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_3.setItems(DS_1.getMemberList("Z_MATPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

DROPDOWN_4.setItems(DS_1.getMemberList("Z_POPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

and in Application and Dimensions , i wrote same as above

when i run the report i got the following error,

and i didn't write any thing in On Select event of DateField_1

Can you please help me out , how to resolve the error . and how set date range.

Thanks,

Ramana

Re: How to write script for DATE Range

murali_balreddy — Mon, 08 Jul 2013 13:16:29 GMT

Your date range format is not correct. Check what is needed for the BW query and what the DATEFIELD outputs. Try to display DATEFIELD value as a textbox. It is normally YYYYMMDD. Is this the format your query requires? If it requires in another format, use substring and concatenate to convert the dates to the required format.

Re: How to write script for DATE Range

Former Member — Mon, 08 Jul 2013 15:09:05 GMT

Hi ,

I am new to this tool.Date format for date is MM/DD/YYYY in BW source system.for single date field the date is coming correctly . but for range i am unable to get .

Can you please help me out how to write function for sub string and concatenate function( where to write the date conversion function, in DATEFIELD_2 on event or any onther field)

Can you please provide syntax for conversion date for start date variable.

how can we see prompt output value report, you said use the text field.

Thanks,

Ramana

Re: How to write script for DATE Range

IngoH — Mon, 08 Jul 2013 15:36:36 GMT

Hello Ramana,

this is part of your script:

var varRange = startDate + " - " + endDate;

DS_1.setFilterExt("Z_SDPCD", varRange);

DROPDOWN_1.setItems(DS_1.getMemberList("Z_PPCD", MemberPresentation.EXTERNAL_KEY, MemberDisplay.KEY, 50,"ALL"));

You are using the setFilterExt option to set the filter value, which means that you need to send the external format of the data values.

The external format is also dependent on the users local.

You underlying BEx variable is configured to accept value range ?

regards

Ingo Hilgefort

Re: How to write script for DATE Range

Former Member — Mon, 08 Jul 2013 15:50:25 GMT

Hi Ingo Hilgefort,

"Z_SDPCD" Date info object in BW.

Can you please help me out to define the DATE Range, the data should be display between start date and end date.

can you provide the syntax for the date range, where i write the script on DATEFIELD_1 on event or DATEFIELD_2 on Event.

Re: How to write script for DATE Range

IngoH — Mon, 08 Jul 2013 17:47:13 GMT

Hello Ramana,

as you are "new" to the topic I would also suggest that you look at the available product documentation and existing SDN entries, as this has already been answered on other entries.

You are using the setFilterExt, which means you need to pass the value in the external format and the external format depends on the regional settings of the user.

here is an example:

var date1 = DATEFIELD_1.getDate();

var date2 = DATEFIELD_2.getDate();

var external_date1 =

Convert.subString(date1, 4,6) +

"/" +

Convert.subString(date1, 6,8) +

"/" +

Convert.subString(date1, 0,4);

var external_date2 =

Convert.subString(date2, 4,6) +

"/" +

Convert.subString(date2, 6,8) +

"/" +

Convert.subString(date2, 0,4);

regards

Ingo Hilgefort, SAP

Re: How to write script for DATE Range

rama_shankar3 — Wed, 10 Jul 2013 14:35:50 GMT

Thanks Ingo & Murali!

Re: How to write script for DATE Range

Former Member — Mon, 27 Oct 2014 18:15:07 GMT

This message was moderated.

Re: How to write script for DATE Range

Former Member — Thu, 29 Jan 2015 13:18:57 GMT

Any idea how to handle date range when users are using different regional settings?

We have user in 7 different coutries and we can't find out how to set bex variable date ranges since this requeries external date format.

Thanks

Re: How to write script for DATE Range

Former Member — Thu, 29 Jan 2015 13:23:07 GMT

Hi Mariano,

I had a similar requirement in one of my projects where we had users from 2 different countries with date formats as mm/dd/yyyy and dd/mm/yyyy. Even data in the source system was in that format.

So I added a country filter on the dashboard and depending upon the selection on the country filter, I changed the format of the date in design studio by breaking down the string and appending it back in the desired format before sending it back to HANA.

let me know if you need more details.

Thanks

Re: How to write script for DATE Range

Former Member — Thu, 29 Jan 2015 14:32:17 GMT

Hi Kiran

Sounds good but if i got it right DS is picking the locale from "preferred viewing locale setting" in BO.

We have that setting to "use browser locale" so language depends on user's IE language configuration.

If user pick "Russia" in this country filter it doesn't mean the IE language is set to Russian right?

What did you do to manage that?

btw, thanks for your help!

Re: How to write script for DATE Range

Former Member — Thu, 29 Jan 2015 14:46:59 GMT

Mariano,

Sorry if this confused you. From your initial description, I understood your problem as you are not able to handle the date formats for different countries.

For Ex:

Country 1 : MM/DD/YYYY

Country 2: DD/MM/YYYY

Country 3: YYYY/MM/DD

Consider the above 2 formats that are existing in your source systems. And with the date selection component in design studio, it always gives you the date in MMDDYYYY format.

So to filter the data properly according to the date entered in design studio, you need to break down the string and concatenate it according to the country selection.

if(Dropdown_country.getselectedvalue()=="Country 1")

{

//concatenate the string as MM/DD/YYYY

}

else if..........

so on.

I am not aware of the user locale settings. Could you please share some more information on this so that I can explore this option also.

Thanks.

Re: How to write script for DATE Range

Former Member — Thu, 29 Jan 2015 15:12:08 GMT

Let say my IE es set to "English". Since in BO preferences->"Preferred viewing locale" is set to "Use browser locale" then the date format to use for Bex query variable is mm/dd/yyyy.

If user A has language defined in IE as "Finnish" then the date format to use in bex variable is dd.mm.yyyy.

If user A picks "EEUU" from this country dropdown box date will be formated as mm/dd/yyyy and it will fail since bex external format expected is dd.mm.yyyy.

Just to clarify we're using SAP BW queries using SSO in DS dashboards.

Re: How to write script for DATE Range

Former Member — Wed, 21 Oct 2015 07:31:46 GMT

Hi,

I would like to know the script used to filter crosstab based on a date selected from date field in SAP Design Studio 1.5?

Regards,

Swathi